postgresql PostgreSQL增量备份的最佳方法

Best method for PostgreSQL incremental backup

提问by Dennis Thrys?e

I am currently using pg_dumppiped to gzippiped to split. But the problem with this is that all output files are always changed. So checksum-based backup always copies all data.

我目前正在使用pg_dump管道到gzip管道到split. 但问题是所有输出文件总是被更改。所以基于校验和的备份总是复制所有数据。

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

有没有其他好的方法来执行 PostgreSQL 数据库的增量备份，可以从备份数据中恢复一个完整的数据库？

For instance, if pg_dumpcould make everything absolutely ordered, so all changes are applied only at the end of the dump, or similar.

例如，如果pg_dump可以使所有内容绝对有序，那么所有更改仅在转储结束时应用，或类似。

采纳答案by intgr

Update:Check out Barmanfor an easier way to set up WAL archiving for backup.

更新：查看 Barman以获得一种更简单的方法来设置 WAL 归档以进行备份。

You can use PostgreSQL's continuous WAL archivingmethod. First you need to set wal_level=archive, then do a full filesystem-level backup (between issuing pg_start_backup()and pg_stop_backup()commands) and then just copy over newer WAL files by configuring the archive_commandoption.

您可以使用PostgreSQL 的连续 WAL 归档方法。首先你需要设置wal_level=archive，然后做一个完整的文件系统级备份（在发布pg_start_backup()和pg_stop_backup()命令之间），然后通过配置archive_command选项复制更新的 WAL 文件。

Advantages:

好处：

• Incremental, the WAL archives include everything necessary to restore the current state of the database
• Almost no overhead, copying WAL files is cheap
• You can restore the database at anypoint in time (this feature is called PITR, or point-in-time recovery)

• 增量，WAL 档案包括恢复数据库当前状态所需的一切
• 几乎没有开销，复制 WAL 文件很便宜
• 您可以在任何时间点恢复数据库（此功能称为 PITR，或时间点恢复）

Disadvantages:

缺点：

• More complicated to set up than pg_dump
• The full backup will be much larger than a pg_dump because all internal table structures and indexes are included
• Does not work well for write-heavy databases, since recovery will take a long time.

• 设置比 pg_dump 更复杂
• 完整备份将比 pg_dump 大得多，因为包括所有内部表结构和索引
• 不适用于大量写入的数据库，因为恢复需要很长时间。

There are some tools such as pitrtoolsand omnipitrthat can simplify setting up and restoring these configurations. But I haven't used them myself.

有一些工具，例如pitrtools和omn​​ipitr，可以简化设置和恢复这些配置。但我自己没有使用过它们。

回答by Stephen Frost

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

另请查看http://www.pgbackrest.org

pgBackrest is another backup tool for PostgreSQL which you should be evaluating as it supports:

pgBackrest 是 PostgreSQL 的另一个备份工具，您应该对其进行评估，因为它支持：

• parallel backup (tested to scale almost linearly up to 32 cores but can probably go much farther..)
• compressed-at-rest backups
• incremental and differential (compressed!) backups
• streaming compression (data is compressed only once at the source and then transferred across the network and stored)
• parallel, delta restore (ability to update an older copy to the latest)
• Fully supports tablespaces
• Backup rotation and archive expiration
• Ability to resume backups which failed for some reason
• etc, etc..

• 并行备份（测试几乎线性扩展到 32 个内核，但可能可以走得更远..）
• 静态压缩备份
• 增量和差异（压缩！）备份
• 流压缩（数据在源头只压缩一次，然后通过网络传输并存储）
• 并行，增量还原（将旧副本更新到最新副本的能力）
• 完全支持表空间
• 备份轮换和存档过期
• 能够恢复由于某种原因失败的备份
• 等等等等。

回答by Scott Marlowe

Another method is to backup to plain text and use rdiff to create incremental diffs.

另一种方法是备份到纯文本并使用 rdiff 创建增量差异。