Backup and Restore


Every database should be backed up on a regular basis. Since Postgres manages it's own files in the file system, it is not advisable to rely on system backups of your file system for your database backups; there is no guarantee that the files will be in a usable, consistant state after restoration.

Postgres provides two utilities to backup your system: pg_dump to backup individual databases and pg_dumpall to backup your installation in one step.

An individual database can be backed up using the following command:

% pg_dump dbname > dbname.pgdump
and can be restored using
cat dbname.pgdump | psql dbname

This technique can be used to move databases to new locations, and to rename existing databases.

Large Databases

Author: Written by Hannu Krosing on 1999-06-19.

Since Postgres allows tables larger than the maximum file size on your system, it can be problematic to dump the table to a file, since the resulting file will likely be larger than the maximum size allowed by your system.

As pg_dump writes to stdout, you can just use standard *nix tools to work around this possible problem:

Of course, the name of the file (filename) and the content of the pg_dump output need not match the name of the database. Also, the restored database can have an arbitrary new name, so this mechanism is also suitable for renaming databases.