Best PostgreSQL incremental backup method

Question

I am currently using pg_dump with gzip number with split number. But the problem is that all output files always change. Thus, a backup based on a control copy backs up all the data.

Are there any other good ways to perform an incremental backup of a PostgreSQL database where a full data backup can be restored from backup data?

For example, if pg_dump can make everything absolutely orderly, therefore, all changes are applied only at the end of the dump or similar.

Answer 1

Update: Check out Barman to simplify setting up WAL archiving for backup.

You can use the PostgreSQL continuous WAL archiving method . First you need to set wal_level=archive , then perform a full backup at the file system level (between issuing pg_start_backup() and pg_stop_backup() ), and then just copy the newer WAL files by setting the archive_command parameter.

Benefits:

• Incremental, WAL archives include everything needed to restore the current state of the database.
• Almost no overhead; copying WAL files is cheap
• You can restore the database at any time (this function is called PITR or restore by time)

Disadvantages:

• Harder to configure than pg_dump
• A full backup will be much larger than pg_dump, because all internal table structures and indexes are included
• This does not work well for write databases, as recovery will take a long time.

There are some tools, such as pitrtools and omnipitr , that can simplify the configuration and restoration of these configurations. But I didn’t use them myself.

Answer 2

Also check out http://www.pgbackrest.org

pgBackrest is another backup tool for PostgreSQL that you should evaluate as you support it:

• parallel backup (tested to scale almost linearly to 32 cores, but can probably go much further ..)
• backups with backup compression
• incremental and differential (compressed!) backups
• streaming compression (data is compressed only once in the source, and then transmitted over the network and stored)
• parallel, delta restore (the ability to update the old version to the latest version)
• Fully supports table spaces
• Backup and archive expiration
• Ability to resume backup, which for some reason failed
• etc. etc.

Answer 3

Another method is backing up to plain text and using rdiff to create incremental differences.