Comment on page
Database Migration
You can migrate your existing databases over to DBaaS using the
pg_dump
, pg_restore
and psql
tools.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
### Using 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.
```bash
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, 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.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.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 modified 5mo ago