Database Reference
In-Depth Information
Compression for WAL files from physical backups was discussed earlier: pg_lesslog, available
at the following website.
http://pgfoundry.org/frs/?group_id=1000310
. Physical
backups can be compressed in various ways, depending upon the exact backup mechanism
used. By default, the custom dump format for logical backups will be compressed. Even when
compressed, the objects can be accessed individually if required.
Using
--compress
with script dumps will result in a compressed text file, just as if you had
dumped the file, and then compressed it. Access to individual tables is not possible.
PostgreSQL utilities do have a compress/decompress option, though this isn't always that
efficient. Put another way:
pg_dump --compress=0
will typically be slower than:
pg_dump | gzip
Of course, feel free to use your favorite fast compression tool instead, which is likely to vary,
depending upon the type of data in use.
Using multiple processes is known as pipeline parallelism. If you're using physical backup,
then you can copy the data away in multiple streams, which also allows you to take advantage
of parallel compression/decompression.
See also
If taking a backup is an expensive operation, then one way around that is to take the backup
from a replica instead that offloads the cost of the backup operation away from the master.
Look at the recipes in the chapter on
Replication
to see how to set up a replica.
Incremental/Differential backup and restore
If you have performance problems with backup of a large PostgreSQL database, then you may
ask about incremental or differential backup.
An incremental backup is a backup of all files that have changed since the last full backup. In
order to restore, you must restore the full backup and then each set of incremental changes.
A differential backup is a backup of all individual changes since the last full backup. Again,
restoration requires you to restore the full backup and then apply any changes since then.
