The most efficient way to move psql databases

Question

What is the most efficient, secure way to pipe the contents of a postgresSQL database into a compressed tarfile, then copy to another machine?

This would be used for localhosting development, or backing up to a remote server, using *nix based machines at both ends.

Answer 1

This page has a complete backup script for a webserver, including the pg_dump output.

Here is the syntax it uses:

BACKUP="/backup/$NOW"
PFILE="$(hostname).$(date +'%T').pg.sql.gz"
PGSQLUSER="vivek"
PGDUMP="/usr/bin/pg_dump"

$PGDUMP -x -D -U${PGSQLUSER} | $GZIP -c > ${BACKUP}/${PFILE}

After you have gzipped it, you can transfer it to the other server with scp, rsync or nfs depending on your network and services.

Answer 2

pg_dump is indeed the proper solution. Be sure to read the man page. In Espo's example, some options are questionable (-x and -D) and may not suit you.

As with every other database manipulation, test a lot!