

Chapter 8. Backup and Restore

As everything that contains valuable data, Postgres databases should be backed up regularly. While the procedure is essentially simple, it is important to have a basic understanding of the underlying techniques and assumptions.

There are two fundamentally different approaches to backing up Postgres data:

  • SQL dump

  • File system level backup

8.1. SQL Dump

The idea behind this method is to generate a text file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. Postgres provides the utility program pg_dump for this purpose. The basic usage of this command is:

pg_dump dbname > outfile



As any other Postgres client application, pg_dump will by default connect with the database user name that is equal to the current Unix user name. To override this, either specify the -u option to force a prompt for the user name, or set the environment variable PGUSER. Remember that pg_dump connections are subject to the normal client authentication mechanisms (which are described in Chapter 4).


Important: When your database schema relies on OIDs (for instances as foreign keys) you must instruct pg_dump to dump the OIDs as well. To do this, use the -o command line option.

8.1.1. Restoring the dump


psql dbname < infile


The ability of pg_dump and psql to write or read from pipes also make it possible to dump a database directory from one server to another, for example

pg_dump -h host1 dbname | psql -h host2 dbname


8.1.2. Using pg_dumpall


pg_dumpall > outfile

pg_dumpall has one little flaw: It is not prepared for interactively authenticating to each database it dumps. If you are using password authentication then you need to set it the environment variable PGPASSWORD to communicate the password the the underlying calls to pg_dump. More severely, if you have different passwords set up for each database, then pg_dumpall will fail. You can either choose a different authentication mechanism for the purposes of backup or adjust the pg_dumpall shell script to your needs.

8.1.3. Large Databases




pg_dump dbname | gzip > filename.gz
Reload with
createdb dbname
gunzip -c filename.gz | psql dbname
cat filename.gz | gunzip | psql dbname


pg_dump dbname | split -b 1m - filename
Reload with
createdb dbname
cat filename.* | psql dbname


pg_dump -Fc dbname > filename
See the pg_dump and pg_restore reference pages for details.

8.1.4. Caveats



For reasons of backward compatibility, pg_dump does not dump large objects by default. To dump large objects you must use either the custom or the TAR output format, and use the -B option in pg_dump. See the reference pages for details. The directory contrib/pg_dumplo of the Postgres source tree also contains a program that can dump large objects.

Please familiarize yourself with the pg_dump reference page.

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