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 / export

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

Export a table (including data) to plain SQL:

pg_dump -t tablename --inserts dbname

Restore / import

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.
Configure the new server:

emerge postgresql-server --config

Back up the databases (stop any client activity first):

pg_dumpall -f backup.sql

Stop the old service and start the new service:

/etc/init.d/postgresql-oldversion stop
/etc/init.d/postgresql-newversion start

Restore the databases:

psql -f backup.sql postgres

Set the new version to autostart:

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