Database Migration

You can migrate your existing databases over to DBaaS using the pg_dump, pg_restore and psql tools.

Use the SQL script format

To dump a database use the following command:

pg_dump -U <username> -h <host> -p <port> -t <tablename> <databasename> -f dump.sql

The -t <tablename> flag is optional and can be added if you only want to dump a single table.

This command will create a script file containing all instructions to recreate your database. It is the most portable format available. To restore it, simply feed it into psql. The database to restore to has to already exist.

psql -U <username> -h <host> -p <port> -d <databasename> -f dump.sql

Use the custom format

pg_dump can also be used to dump a database in an archived format. Archived formats cannot be restored using psql, instead pg_restore is used. This has some advantages like smaller filesize and bandwith savings, but also speedups in the restore process by restoring concurrently.

In this guide we will use the "custom" format. It is compressed by default and provides the most flexible restore options.

pg_dump -U <username> -h <host> -p <port> -F c <databasename> -f dump

The flag -F c is selecting the custom archive format. For more information, refer to the PostgreSQL Documentation.

To restore from a custom format archive you have to use pg_restore. The following command assumes that the database to be restored already exists.

pg_restore -U <username> -h <host> -p <port> -F c -d <databasename> dump

When specifying the -C parameter, pg_restore can be instructed to recreate the database for you. For this to work you will need to specify a database that already exists. This database is used for initially connecting to and creating the new database. In this example we will use the database "postgres", which is the default database that should exist in every PostgreSQL cluster. The name of the database to restore to will be read from the archive.

pg_restore -U <username> -h <host> -p <port> -F c -C -d postgres dump

Large databases can be restored concurrently by adding the -j <number> parameter and specifying the number of jobs to run concurrently.

For more information on pg_restore refer to the PostgreSQL Documentation.

Note: The use of pg_dumpall is not possible because it requires a superuser role to work correctly. Superuser roles are not obtainable on managed databases.