PostgreSQL - pg_dump to copy PostgreSQL Database From One Remote Server to Another

While involved in Python + Django based projects and using postgreSQL as a database backend, there are occasions where I need to switch between servers and at times need to copy databases as well. For transferring the django application data + schema from one remote server to another, I use following ways as per convenience, available options, server restrictions etc.

1) Copying Django application data + schema (PostgreSQL) from one remote server by dumping the data in a file and then transferring that file to another remote server and restoring it. In this approach, you have got three options:

• Dump both schema as well as Data to the file:

pg_dump -C -h host -U username db_name > /any_directory/dump_schema_and_data_file 

• Dump only schema from PostgreSQL Database to a file:

pg_dump -Cs -h host -U username db_name > /any_directory/dump_schema_file

• Dump only data from PostgreSQL Database to file:

pg_dump -a -h host -U username db_name > /any_directory/dump_data_file

After copying the above file to target server, you can easily restore database from file by using following command:

psql -h host -U username db_name < data_schema_file

2) Second approach I use to copy PostgreSQL database from one remote server to another is by directly using following command i.e without copying the data to intermediate file:

pg_dump -C -h localhost -U localuser db_name | psql remotehost -U remoteuser db_name