This page in other versions: 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.

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