Database Administration - PostgreSQL: Up and Running - page 40

Systemwide Backup Using pg_dumpall
Use the pg_dumpall utility to back up all databases into a single plain-text file, along
with server globals such as tablespace definitions and roles. See “Server Backup:
pg_dumpall” on page 197 for a listing of available pg_dumpall command options.
It's a good idea to back up globals such as roles and tablespace definitions on a daily
basis. Although you can use pg_dumpall to back up databases as well, we generally don't
bother or do it—or use it at most once a month—because waiting for a huge plain-text
backup to restore tries our patience.
To back up roles and tablespaces:
pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only
If you care only about backing up roles and not tables spaces, use the --roles-only
option:
pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql --roles-only
Restore
There are two ways to restore data in PostgreSQL:
• Using psql to restore plain-text backups generated with pg_dumpall or pg_dump
• Using the pg_restore utility to restore compressed, TAR, and directory backups
created with pg_dump
Using psql to restore plain-text SQL backups
A plain SQL backup is nothing more than a text file containing a chunky SQL script.
It's the least convenient of backups to have, but it's the most versatile. With SQL backup,
you must execute the entire script. You can't cherry-pick objects unless you're willing
to manually edit the file. Run all of the following examples from the OS console or the
interactive psql prompt.
To restore a full backup and ignore errors:
psql -U postgres -f myglobals.sql
To restore, stopping if any error is found:
psql -U postgres --set ON_ERROR_STOP=on -f myglobals.sql
To restore to a specific database:
psql -U postgres -d mydb -f select_objects.sql