This page in other versions: Unsupported versions: 6.5

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:

  • Use compressed dumps:

    % pg_dump dbname | gzip > filename.dump.gz
    reload with
    % createdb dbname
    % gunzip -c filename.dump.gz | psql dbname
    % 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.

Submit correction

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.

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group