PostgreSQL backup and upgrade guide for Gentoo Linux

General command-line options (which may need to be added to all the commands below):

• -h host (default: local Unix socket)
• -p port (default: 5432)
• -U user (default: current system user)

As always, rtfm for more details. This is just a quick guide.

Backup

Back up a database (compressed):

pg_dump -F c -b -f file.backup dbname

Back up a table (compressed):

pg_dump -F c -b -f file.backup -t tablename dbname

Back up all databases from a server (cluster), in plain SQL:

pg_dumpall -f file.sql

Back up all databases separately into individual compressed files:

for db in $( psql -qAt -c 'select datname from pg_database where not datistemplate' ); do pg_dump -F c -b -f $db.backup $db; done

Back up only roles ("users"):

pg_dumpall -r -f file.sql

Copy a database to a different server:

pg_dump -C dbname | bzip2 | ssh server2 "bunzip2 | psql postgres"

Get a "create table" statement:

pg_dump -s -t tablename dbname

Restore

Restore a database (from compressed backup):

pg_restore -d dbname -v file.backup

Restore table data (from compressed backup):

pg_restore -d dbname -a -t tablename -v file.backup

Restore plain SQL file:

psql -f file.sql dbname

Major upgrade

Adjust any USE flags, install and switch to the new PostgreSQL version while the old one is running:

emerge -av postgresql-server
eselect postgresql set newversion

Check "pg_dumpall --version", it should show the new version.
Back up the databases:

pg_dumpall -f backup.sql

Stop the old service, configure the new server (if necessary) and start the new service:

/etc/init.d/postgresql-oldversion stop
emerge postgresql-server --config -a
/etc/init.d/postgresql-newversion start

Restore the databases:

psql -f backup.sql postgres

Update the configuration files, then the autostart settings if applicable:

rc-update del postgresql-oldversion default
rc-update add postgresql-newversion default