Move a DBaaS instance to a newer engine version

Move a DBaaS instance to a newer engine version to run your database on a newer MariaDB or PostgreSQL release. A DBaaS instance keeps its engine version for as long as it exists, so the move is a new DBaaS instance on the newer version with your data copied into it. The versions on offer are listed in DBaaS engines and versions.

Before you begin

• The current DBaaS instance is Running, and you can connect to it; see Connect to a DBaaS instance.
• The dump tools are on your machine: the MySQL 8.0 client for MariaDB, or the PostgreSQL client tools of the newer version for PostgreSQL.
• You have picked a quiet time. Pause writes from your application while you copy the data, so the dump holds every change.

Create the new DBaaS instance

1. Create a DBaaS instance on the same engine, and choose the newer version as its Database Version; see Create a DBaaS instance.
2. Once it is Running, give it the settings your application relies on. Whitelist entries and VPCs belong to each DBaaS instance, so add them again; see Whitelist an IP address for a DBaaS instance and Attach a VPC to a DBaaS instance.
3. If you use a parameter group, create one for the newer version, set the values it offers that you set before, attach it, and sync it, since a parameter group belongs to one engine version; see Create and edit a parameter group and Attach a parameter group to a DBaaS instance.

On each DBaaS instance’s details page, in the CA Certificate row under Connection Details, click Download. Save the files as ca-old.pem and ca-new.pem.

Copy a MariaDB database

In these commands, <old-endpoint> and <new-endpoint> are the Endpoint of each DBaaS instance under Connection Details. <username> is the Username there, admin by default, and <database-name> is the database to move. Each command asks for the password when it connects.

Dump the database from the current DBaaS instance:

mysqldump -h <old-endpoint> -P 3306 -u <username> -p --ssl-mode=VERIFY_CA --ssl-ca=ca-old.pem \
  --single-transaction --routines --events --skip-triggers --set-gtid-purged=OFF \
  --databases <database-name> > move.sql

Load it into the new DBaaS instance:

mysql -h <new-endpoint> -P 3306 -u <username> -p --ssl-mode=VERIFY_CA --ssl-ca=ca-new.pem < move.sql

Tables, views, stored procedures, events, and the data arrive, with the database’s character set. Run scheduled jobs from your application, since the event scheduler is off. To bring triggers and stored functions across as well, turn them on for the new DBaaS instance with a parameter group before you load, and drop --skip-triggers from the dump command; see Import a database into a DBaaS instance.

Copy a PostgreSQL database

In these commands, <old-endpoint> and <new-endpoint> are the Endpoint of each DBaaS instance under Connection Details. <username> is the Username there, admin by default. <database-name> is the database to move, and <new-database> is the Database Name of the new DBaaS instance. Each command asks for the password when it connects.

Dump the database from the current DBaaS instance in custom format:

pg_dump "host=<old-endpoint> port=5432 user=<username> dbname=<database-name> sslmode=verify-ca sslrootcert=ca-old.pem" \
  -Fc -f move.pgc

Restore it into the new DBaaS instance, with the admin user as the owner of everything restored:

pg_restore --no-owner --no-privileges \
  -d "host=<new-endpoint> port=5432 user=<username> dbname=<new-database> sslmode=verify-ca sslrootcert=ca-new.pem" \
  move.pgc

Tables, views, trigger functions and triggers, stored procedures, extensions that need no superuser, and the data arrive, and the triggers keep running on the newer version.

Create your users again

The dump carries your data. The users and roles your applications sign in with belong to each DBaaS instance. Create them on the new DBaaS instance with the same names and rights; see Manage database users on a DBaaS instance.

Check that the data arrived

Count the rows of a table you know on both DBaaS instances, where <table-name> is that table. For MariaDB:

mysql -h <new-endpoint> -P 3306 -u <username> -p --ssl-mode=VERIFY_CA --ssl-ca=ca-new.pem \
  -D <database-name> -e "SELECT COUNT(*) FROM <table-name>;"

For PostgreSQL:

psql "host=<new-endpoint> port=5432 user=<username> dbname=<new-database> sslmode=verify-ca sslrootcert=ca-new.pem" \
  -c "SELECT count(*) FROM <table-name>;"

Run the same command on the current DBaaS instance with <old-endpoint> and ca-old.pem, and for PostgreSQL with dbname=<database-name>; the counts match. If a load or restore prints an error, see Troubleshooting DBaaS.

Switch your application

Point your application at the new DBaaS instance’s Endpoint, with the users you created, and resume writes. Once your application runs on the new DBaaS instance, delete the old one to end its charge. Deleting it also deletes its snapshots, so keep move.sql or move.pgc until you are sure; see Delete a DBaaS instance.

Your database now runs on the newer engine version.