Database Reference
In-Depth Information
Table of Backup/Recovery options:
SQL dump to an
archive file
pg_dump -F c
SQL dump to a
script file
pg_dump -F p
or pg_dumpall
Filesystem backup
using pg_start_
backup
Backup type
Logical
Logical
Physical
Recover to point in time? No
No
Yes
Backup all databases?
One at a time
Yes (pg_dumpall)
Yes
All databases backed up at
same time?
No
No
Yes
Selective backup?
Yes
Yes
No (Note 3)
Incremental backup?
No
No
Possible (Note 4)
Selective restore?
Yes
Possible (Note 1)
No (Note 5)
DROP TABLE recovery
Yes
Yes
Possible (Note 6)
DROP TABLESPACE recovery Possible (Note 2)
Possible (Note 6)
Possible (Note 6)
Compressed backup files? Yes
Yes
Yes
Backup is multiple files?
No
No
Yes
Parallel backup possible? No
No
Yes
Parallel restore possible? Yes
No
Yes
Restore to later release?
Yes
Yes
No
Standalone backup?
Yes
Yes
Yes (Note 7)
Allows DDL during backup No
No
Yes
How to do it...
1. If you've generated a script with
pg_dump
or
pg_dumpall
and need to restore just
a single object, then you're going to need to go deep. You will need to write a Perl
script (or similar) to read the file and extract out the parts you want. It's messy and
time-consuming, but probably faster than restoring the whole thing to a second
server, and then extracting just the parts you need with another
pg_dump
.
2. See recipe
Recovery
of
a
dropped/damaged
tablespace
.
3. Selective backup with physical backup is possible, though will cause later problems
when you try to restore. See note 6.
4. See
Incremental/Differential
backup
.
5. Selective restore with physical backup isn't possible with currently supplied utilities.
6. See recipe for
Recovery
of
a
dropped/damaged
tablespace
.
7. See recipe for
Standalone
hot
physical
backup
.
