PostgreSQL Database Migration

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