PostgreSQL Database Migration
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