cancel
Showing results for 
Search instead for 
Did you mean: 

Multi-Host Pg_dump

PostgreSQL Core Team - EDB

 

You have probably looked at logical dumps as supported by pg_dump and restores by pg_restore or, more simply, psql. What you might not have realized are the many options for dumping and restoring when multiple computers are involved.

 

The most simple case is dumping and restoring on the same server:

 

 

$ pg_dump -h localhost -Fc test > /home/postgres/dump.sql
$ pg_restore -h localhost test < /home/postgres/dump.sql

 

 

or with a plain text dump:

 

 

$ pg_dump -h localhost -f /home/postgres/dump.sql test
$ psql -h localhost -f /home/postgres/dump.sql test

 

 

Where this gets interesting is with multiple hosts. You can:

 

 

$ # dump a remote database to your local machine
$ pg_dump -h remotedb.mydomain.com -f /home/postgres/dump.sql test
 
$ # dump a local database and write to a remote machine
$ pg_dump -h remotedb.mydomain.com test | ssh postgres@remotedb.mydomain.com 'cat > dump.sql'
 
$ # dump a remote database and write to the same remote machine
$ pg_dump -h remotedb.mydomain.com test | ssh postgres@remotedb.mydomain.com 'cat > dump.sql'
 
$ # or a different remote machine
$ pg_dump -h remotedb1.mydomain.com test | ssh postgres@remotedb2.mydomain.com 'cat > dump.sql'

 

 

You also have similar restore options. I will use psql below but pg_restore works the same:

 

 

$ # dump a remote database and restore to your local machine
$ pg_dump -h remotedb.mydomain.com test1 | psql test2
 
$ # dump a local database and restore to a remote machine
$ pg_dump -h remotedb.mydomain.com test | ssh postgres@remotedb.mydomain.com 'psql test'
 
$ # dump a remote database and restore to the same remote machine
$ pg_dump -h remotedb.mydomain.com test1 | ssh postgres@remotedb.mydomain.com 'psql test2'
 
$ # or a different remote machine
$ pg_dump -h remotedb1.mydomain.com test | ssh postgres@remotedb2.mydomain.com 'psql test'

  

As you can see, there is a lot of flexibility possible.

 

(Article originally published in Bruce's personal blog - Wednesday, September 12, 2018, at https://momjian.us/main/blogs/pgblog/2018.html#September_12_2018)