Full Backup
Creating database backups is essential in any server setup in case of a server crash or if a modification goes wrong and data is accidentally deleted. The file must be dumped as a sql fileYou 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
pg_restore
...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.
Warning |
---|
TBC by Roderick |
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).