PostgreSQL Database Migration

Please note that if you are migrating databases using PgBouncer ports 6432 or 5433, you need to switch PgBouncer pooling mode to session on the cluster page in the Settings section. If a different pooling mode is used for PgBouncer, this may change search_path for some connections, that is, the tables will not be available by an incomplete name.

Use the pg_dump utility to create a file containing the data to upload using the COPY command to create a copy of the data and schema. The file generated using pg_dump will upload data to the database with the same name and re-create tables, indexes, and foreign keys.

Creating an sql dump:

pg_dump dbname=DatabaseName -f mydb_dump.sql

Restoring from a created dump:

psql -f mydb_dump.sql --host Host --port 6432 --username UserName --password Password --dbname DatabaseName

It is possible to create a copy of the data in a custom format. The custom format is the most flexible one and it allows you to restore only the schema or data of a specific table.

Please note that the custom format copy is compressed by default.

Creating a dump in a custom format:

pg_dump -Fc -v -h Host -U UserName DatabaseName > archive.dump

To restore from such a backup using pg_restore, create a target database in the Control panel with the same name as the database in the backup.

You can create a target database with a different name. In this case, you should use pg_restore with the -d switch to restore data to a database with a new name:

pg_restore -v -h Host -U UserName -d DatabaseName archive.dump