how to backup postgres over the network

to copy a database (dbname) from postgres server 1 (pg1) to server 2 (pg2):

on pg1:

pg_dump -Fc dbname | ssh pg2 pg_restore -Fc -v -O -x -d dbname
  • -Fc - alternate format (binary, compressed, and faster than standard dump of sql)

  • -v - verbose

ahhh… mebbe…

  • -O - no owner

  • -x - no acl’s