18th October 2018: PostgreSQL 11 Released!
Unsupported versions: 6.5

Backup and Restore

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.

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
            
    
    or
    % 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.