Table of Contents
As with everything that contains valuable data, PostgreSQL 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 three fundamentally different approaches to backing up PostgreSQL data:
SQL dump
File system level backup
Continuous archiving
Each has its own strengths and weaknesses.
The idea behind this dump 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. PostgreSQL provides the utility program pg_dump for this purpose. The basic usage of this command is:
pg_dumpdbname
>outfile
As you see, pg_dump writes its results to the standard output. We will see below how this can be useful.
pg_dump is a regular PostgreSQL client application (albeit a particularly clever one). This means that you can do this backup procedure from any remote host that has access to the database. But remember that pg_dump does not operate with special permissions. In particular, it must have read access to all tables that you want to back up, so in practice you almost always have to run it as a database superuser.
To specify which database server pg_dump should
contact, use the command line options -h
and host
-p
. The
default host is the local host or whatever your
port
PGHOST
environment variable specifies. Similarly,
the default port is indicated by the PGPORT
environment variable or, failing that, by the compiled-in default.
(Conveniently, the server will normally have the same compiled-in
default.)
As any other PostgreSQL client application,
pg_dump will by default connect with the database
user name that is equal to the current operating system user name. To override
this, either specify the -U
option or set the
environment variable PGUSER
. Remember that
pg_dump connections are subject to the normal
client authentication mechanisms (which are described in Chapter 20, Client Authentication).
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
.)
If 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.
The text files created by pg_dump are intended to be read in by the psql program. The general command form to restore a dump is
psqldbname
<infile
where infile
is what
you used as outfile
for the pg_dump command. The database dbname
will not be created by this
command, so you must create it yourself from template0
before executing psql (e.g., with
createdb -T template0
). psql
supports similar options to pg_dump for specifying
the database server to connect to and the user name to use. See
the psql reference page for more information.
dbname
Before restoring a SQL dump, all the users who own objects or were granted permissions on objects in the dumped database must already exist. If they do not, then the restore will fail to recreate the objects with the original ownership and/or permissions. (Sometimes this is what you want, but usually it is not.)
By default, the psql script will continue to execute after an SQL error is encountered. You may wish to use the following command at the top of the script to alter that behaviour and have psql exit with an exit status of 3 if an SQL error occurs:
\set ON_ERROR_STOP
Either way, you will only have a partially restored
dump. Alternatively, you can specify that the whole dump should be
restored as a single transaction, so the restore is either fully
completed or fully rolled back. This mode can be specified by
passing the -1
or --single-transaction
command-line options to psql. When using this
mode, be aware that even the smallest of errors can rollback a
restore that has already run for many hours. However, that may
still be preferable to manually cleaning up a complex database
after a partially restored dump.
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 -hhost1
dbname
| psql -hhost2
dbname
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.
After restoring a backup, it is wise to run ANALYZE on each
database so the query optimizer has useful statistics. An easy way
to do this is to run vacuumdb -a -z
; this is
equivalent to running VACUUM ANALYZE
on each database
manually. For more advice on how to load large amounts of data
into PostgreSQL efficiently, refer to Section 13.4, “Populating a Database”.
pg_dump dumps only a single database at a time, and it does not dump information about roles or tablespaces (because those are cluster-wide rather than per-database). To support convenient dumping of the entire contents of a database cluster, the pg_dumpall program is provided. pg_dumpall backs up each database in a given cluster, and also preserves cluster-wide data such as role and tablespace definitions. The basic usage of this command is:
pg_dumpall > outfile
The resulting dump can be restored with psql:
psql -f infile
postgres
(Actually, you can specify any existing database name to start from,
but if you are reloading in an empty cluster then postgres
should generally be used.) It is always necessary to have
database superuser access when restoring a pg_dumpall
dump, as that is required to restore the role and tablespace information.
If you use tablespaces, be careful that the tablespace paths in the
dump are appropriate for the new installation.
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 use standard Unix tools to work around this possible problem.
Use compressed dumps. You can use your favorite compression program, for example gzip.
pg_dumpdbname
| gzip >filename
.gz
Reload with
createdbdbname
gunzip -cfilename
.gz | psqldbname
or
catfilename
.gz | gunzip | psqldbname
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_dumpdbname
| split -b 1m -filename
Reload with
createdbdbname
catfilename
* | psqldbname
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 -Fcdbname
>filename
A custom-format dump is not a script for psql, but instead must be restored with pg_restore. See the pg_dump and pg_restore reference pages for details.