PostgreSQL Backups

Full Backup

You must be logged in as a superuser to preform any backups and the file must be created with a sql extension.

pg_dumpall > backupfile.sql

pg_dumpall backs up all databases, users and permissions. This is good for small databases .

Incremental Backup

To make incremental backups you need to create a base backup 

pg_dump db > backupfile.sql

pg_dump only backs up one database at a time and does not save the users or permissions. Restoring from pg_dump requires that you have a base backup set in before you can restore otherwise you will get errors when you restore. 

-Fc

To create custom backups in file format tar or dump.

Restoring Database

There are several ways to restore a database.

psql < backupfile.sql

...

Database Commands

 \l

List of all databases, owners and access privileges.

\du

List of roles, attributes and Group affiliation.

\d

List of all tables.

References

http://www.postgresql.org/docs/8.1/static/backup.html - not too clear, for example how do I specify a user?

http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/ - looks promising, syntax looks very different from official manual though.

Also, there is a backup interface via the gui... figure out how to get the command line equivalent. It worked well enough for my 52MB database. Just took a while as it went over the network (gui ran from a client machine).