Database Migration
You can migrate your existing databases over to DBaaS using the pg_dump
, pg_restore
and psql
tools.
Using the SQL script format
To dump a database use the following command:
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.
The flag -F c
is selecting the custom archive format. For more information, see 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.
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.
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
see the official 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.
Last updated