Comment on page
You can migrate your existing databases over to DBaaS using the
To dump a database use the following command:
pg_dump -U <username> -h <host> -p <port> -t <tablename> <databasename> -f dump.sql
-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.
pg_dump -U <username> -h <host> -p <port> -F c <databasename> -f dump
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
pg_restorecan 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_dumpallis not possible because it requires a superuser role to work correctly. Superuser roles are not obtainable on managed databases.