SQL dump

This backup will create a SQL file, which can be executed on a server to recreate the same database as it was at the moment of the dump. The method is very similar to mysqldump. These kind of backups are often used for quick backups or small databases.

Dumps created by pg_dump are internally consistent, that is, updates to the database while pg_dump is running will not be in the dump. pg_dump does not block other operations on the database while it is working. (Exceptions are those operations that need to operate with an exclusive lock, such as VACUUM FULL.)

The basic usage of this command is:

pg_dump dbname > outfile

pg_dump is a regular psql client, so you can provide other options: host, port, user, password:

pg_dump -U wzdftpd -h localhost wzdftpd > wzdftpd.sql

You often have to run pg_dump as superuser in order to have sufficient privileges to access all objects.

Be careful:

• Users and groups are not saved
• Database is not saved (see above)
• When your database schema relies on OIDs (for instance as foreign keys) you must instruct pg_dump to dump the OIDs as well. To do this, use the -o command line option.

Restoring the dump

The SQL file can be executed directly, using the psql program.

psql dbname < infile

There are some important points though:

• dbname must exist, it is not created. If it does not exist, you should create it from template0, using the command (as superuser):

  createdb -T template0 dbname
  

• psql can require some other arguments (user, host, etc.)
• Important: The dumps produced by pg_dump are relative to template0. This means that any languages, procedures, etc. added to template1 will also be dumped by pg_dump. As a result, when restoring, if you are using a customized template1, you must create the empty database from template0, as in the example above.
• The ability of pg_dump and psql to write to or read from pipes makes it possible to dump a database directly from one server to another; for example:

  $ pg_dump -h host1 dbname | psql -h host2 dbname
  

Backup a cluster

To backup an entire cluster, use the pg_dumpall command. This command must be executed as superuser, and will dump the entire cluster:

• all databases
• users and groups
• permissions
• etc.

The basic usage of this command is:

$ pg_dumpall > outfile

The resulting dump can be restored with psql:

$ psql -f infile postgres

Handling large databases

Since PostgreSQL allows tables larger than the maximum file size on your system, it can be problematic to dump such a table to a file, since the resulting file will likely be larger than the maximum size allowed by your system. Since pg_dump can write to the standard output, you can just use standard Unix tools to work around this possible problem.

Use compressed dumps. You can use your favorite compression program, for example gzip.

pg_dump dbname | gzip > filename.gz

Reload with

createdb dbname
gunzip -c filename.gz | psql dbname

or

cat filename.gz | gunzip | psql dbname

Use split. The split command allows you to split the output into pieces that are acceptable in size to the underlying file system. For example, to make chunks of 1 megabyte:

pg_dump dbname | split -b 1m - filename

Reload with

createdb dbname
cat filename* | psql dbname

Use the custom dump format. If PostgreSQL was built on a system with the zlib compression library installed, the custom dump format will compress data as it writes it to the output file. This will produce dump file sizes similar to using gzip, but it has the added advantage that tables can be restored selectively. The following command dumps a database using the custom dump format:

pg_dump -Fc dbname > filename

A custom-format dump is not a script for psql, but instead must be restored with pg_restore.

pg_restore -Fc filename

Point In Time Recovery (PITR)

If the previous methods are not sufficient, another method can be used: WAL archiving. This is equivalent to incremental backup: changes on the database are logged to files, which you can backup using a classical filesystem method (I would recommend Bacula).

For a complete description, see WAL archiving