Backup and Restore

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
    

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.