Help:Adding a Database to a Cloud VPS Project

From Wikitech
Jump to navigation Jump to search


This page explains how to add a database server to a Cloud VPS project.

Trove: Database as a Service for Cloud VPS

Cloud VPS provides database management via the OpenStack Trove project. A Trove database is fully managed by OpenStack and consists of a database instance and a Cinder volume.

Database engines and storage are separate components from standard server instances or cinder volumes and can be found under the 'Database' menu in Horizon. This menu supports creating, resizing, and destroying database servers; it also supports minimal database configuration within a given instance: creation and destruction of databases and some limited management.

Horizon Trove Screenshot.png

Managing Trove Databases

A database instance can contain one or more actual databases. Once an instance is created with Trove, you have a few options for user and database management. Individual databases and users can be created using the Databases and Users tabs, respectively.

If you prefer a command line interface for database management (or need more fine-grained control than that offered by Horizon) you can enable a root user on the Instances page and use the resulting credentials to access a root database login.

Trove root access.png
Enable trove root.png

Database Configuration

Trove databases are not configured via puppet or text files. They are configured through Openstack. There are default configurations, visible in Horizon in the instance details in the "Defaults" tab. The values here are applied to your database at start up and may be different from the database system's defaults.

If you wish to change the configuration of your database in a persistent way (a way that will survive restarts), you need to create a Configuration Group. Select the Configurations item in the Database menu at the left.

Trove configurations.png

Select "+ Create Configuration Group" and give it a descriptive name, an optional description, and select the database type and version you are using as the "datastore". Then select the configuration group to add things to it. In this screen, "Add Parameter" gives you a dropdown of possible parameters for your database and a text box for you to enter the desired value. Some parameters will require a restart of the database when applied, while others will take right away without disruption. Unfortunately, that information is not available in the web interface. If you need it, the quickest place to check is the source code for the validation rules on GitHub, such as this template of the parameters for Mariadb.

Trove configuration parameters.png

When you have added all the parameters you want, you need to go back to the database instances view and select your instance. There, you can select "Attach Configuration Group" from the dropdown menu.

Attach trove configuration group.png

Now you will need to click Configurations again and select the configuration group you attached. In the confusingly-named Instances tab, you should see your database instance that you attached to this group.

Instances inside the configuration group.png

Click the Values tab again and select the "Apply Changes" button. This should apply your configuration to your Trove database instance in a persistent way.

Accessing Trove Databases From Your VM

The trove instance Overview tab includes shortcuts for database access: a shell command example and an example URI.

Note: By default you can only access the DB from VMs within the same Cloud VPS project, if you need to connect directly from your desktop/laptop, you can use ssh tunnels to a VM with access or similar.

Trove db access.png

The command line can be copy-and-pasted into your shell and used to get a mysql, psql, or equivalent prompt. You will need to substitute in the proper username and have a password at the ready. For a root shell, enable and set a password using 'Manage Root Access' menu as documented above.

Postgres databases in Trove

Trove is primarily designed to target mysql and mariadb servers. Most features documented here and displayed in the Horizon UI should work reliably with those servers. Trove can also create and manage Postgres databases, but many of the runtime configuration options are unsupported.

This means that, for example, the 'Configure Access' tab is largely useless for configuring Postgres servers. Most configuration will need to be done via the psql CLI on the default config database, which is named 'postgres.' You can get there using the horizon-suggested connection command but adding -d postgres

Snapshots, Backups, Replicas

Generic Trove documentation describes several backup and snapshot features which are not currently available from WMCS cloud services. Support for these services requires a Swift storage backend; we have near-term plans to add Swift support along with database backups but as of July 2021, this is not yet available.

Until WMCS provides better services in this space, users are encouraged to run periodic database dumps using mysqldump and storing the backups on a separate Cinder volume.

Usage Quotas in Trove

A Trove database instance uses Cinder volume and a Nova instance. Nevertheless, a database instance is not counted against a project's storage or virtualization quotas. Rather, Trove has separate quotas to govern database use; the instances and volumes themselves are stored in a separate project and cannot be accessed directly by users.

By default, any Cloud VPS project should be able to create one or two small Trove instances. When you're ready for more cores or gigabytes, open a quota request in Phabricator. You should not need to rebuild your database instances; with adequate quotas, your instance and storage can be resized as needed without a rebuild.

Orchestration is Hard

A properly functioning Trove database instance is quite complicated and relies on many different OpenStack features working in concert: storage, compute, DNS, networking, and docker. This means that if OpenStack APIs are the least bit fragile, database instance creation may fail with cryptic error messages. Similarly, existing, running database instances may appear as 'down' in Horizon if any of OpenStack's communication channels are unreliable.

The good news is that Trove and Openstack have very little interaction with the database once it's up and running. That means that fragility in the orchestration layer does not necessarily mean that your data or database is itself fragile. Do please reach out to the WMCS team if you encounter poor Trove behavior, but also please do not panic.

Communication and support

Support and administration of the WMCS resources is provided by the Wikimedia Foundation Cloud Services team and Wikimedia movement volunteers. Please reach out with questions and join the conversation:

Discuss and receive general support
Receive mail announcements about critical changes
Subscribe to the cloud-announce@ mailing list (all messages are also mirrored to the cloud@ list)
Track work tasks and report bugs
Use the Phabricator workboard #Cloud-Services for bug reports and feature requests about the Cloud VPS infrastructure itself
Learn about major near-term plans
Read the News wiki page
Read news and stories about Wikimedia Cloud Services
Read the Cloud Services Blog (for the broader Wikimedia movement, see the Wikimedia Technical Blog)