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.pgdumpand 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
% 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.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.