| Caution |
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.
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:
Use compressed dumps:
% pg_dump dbname | gzip > filename.dump.gzreload with
% createdb dbname % gunzip -c filename.dump.gz | psql dbnameor
% cat filename.dump.gz | gunzip | psql dbname
Use split:
% pg_dump dbname | split -b 1m - filename.dump.reload with
% createdb dbname % cat filename.dump.* | pgsql dbname
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.