Even it is not a day to day operation for a database administrator database copying is one of the mandatory skills. In this article we will talk about copying PostgreSQL database to another server.

TL;DR pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

Dumping SQL

pg_dump utility is included in PostgreSQL to dump any database object you want to dump. If you want to deep dive into pg_dump command, you can refer to PostgreSQL documentation.

pg_dump -U postgres -W -F t postgres > /tmp/backup/backup."$(date +%Y-%m-%d_%H-%M-%S.txt)"

pg_dump command is also very helpful for mobility of object definitions and it’s portable between different PostgreSQL versions.

Import SQL Dump

Recently we’ve published and article on this blog about Import SQL Dump Into PostgreSQL Database.

If you don’t have time to read that article you can use the following command:

psql database_name < /tmp/path/to/dump.sql

Single Line Way

It is possible to achieve same with a single line of command. pg_dump can use standard output and psql can process pg_dump output with psql.

Since psql can connect to a remote server and execute command, export and import can be used as a single line command for copying PostgreSQL database to another server.

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