Database Reference
In-Depth Information
How it works...
To backup all databases, you may be told you need to use the
pg_dumpall
utility. I have four
reasons why you shouldn't do that, which are as follows:
F
If you use
pg_dumpall
, then the only output produced is into a script file. Script files
can't use the parallel restore feature of
pg_restore
, so by taking your backup in
this way you will be forcing the restore to be slower than it needs to be.
F
pg_dumpall
produces dumps of each database, one after another. This means that:
pg_dumpall
is slower than running multiple
pg_dump
tasks in parallel, one
against each database.
The dumps of individual databases are not consistent to a particular point in
time. If you start the dump at 04:00 and it ends at 07:00 then we're not sure
exactly when the dump relates to—sometime between 0400 and 07:00.
F
Options for
pg_dumpall
are similar in many ways to
pg_dump
, though not all of
them exist, so some things aren't possible. In summary,
pg_dumpall
is slower to
backup, slow to restore, and gives you less control over the dump. I suggest you don't
use it for those reasons. If you have multiple databases, then I suggest you take your
backup by doing either
F
Dump global information for the database server using
pg_dumpall
-g
. Then dump
all databases in parallel using a separate
pg_dump
for each database, taking care to
check for errors if they occur. Use the physical database backup technique instead.
Hot logical backup of one database
Logical backup makes a copy of the data in the database by dumping out the contents
of each table.
How to do it...
The command to do this is simple and as follows:
pg_dump -F c > dumpfile
or
pg_dump -F c -f dumpfile
