PostgreSQL的连续备份配置

发布 需要2分钟读完

更新说明

2025-04-11 补充一些恢复数据库的操作细节和日志信息

需求定义

之前使用postgresql数据库备份是基于两种方式：一种是定期使用pg_dump作数据库的备份，另一种是用ZFS做硬盘快照备份。但是这两种方法都有其局限，前者在数据量大了以后太慢，后者的问题则是依赖于ZFS，而且也是有风险的。所以需要新的方案。

官方文档提供的方案是：基于WAL的连续归档。类似于我以前用过的ms sql或mysql的日志备份，提供了指定时间点恢复的能力。

实现方法

结合官方文档和GPT/Gemini的指导，备份和恢复需要以下步骤：

备份

  • 启用WAL归档
  • 生成基础备份（同时记录WAL中的检查点，以便未来使用WAL恢复），如有通过链接加入的表空间，也需要同时备份
  • 备份WAL归档

恢复

  • 停止服务
  • 备份现有数据文件（特别是未归档的WAL）
  • 删除现有数据文件（不要和上一步合来直接使用移动操作，以免出错导致数据不一致）
  • 恢复基础备份（包括链接的表空间）
  • 删除恢复出来的未归档WAL
  • 恢复第二步备份的未归档WAL
  • 配置postgresql.conf为恢复模式，从指定位置恢复归档过的WAL
  • 启动服务，自动开始恢复（注意配置pg_hba.conf，禁止期间有不必要的数据库操作）
  • 检查恢复结果

备份的具体配置

首先是启用WAL归档。修改postgresql.conf，加入以下内容：

archive_mode = on
archive_command = 'test ! -f /backup/archive_wal/%f && gzip < %p > /backup/archive_wal/%f && chmod g+r /backup/archive_wal/%f'
wal_level = replica
archive_timeout = 3600  # 每小时强制归档一次

其中的重点是archive_command，这里是一句shell命令，当它返回成功的时候，pgsql才会认为归档成功，并定期删除已归档成功的WAL。所以这里按官方文档作了一个测试，并且对要归档的WAL文件进行压缩。最后为了方便复制备份内容，加上了group的读权限，然后把复制备份的用户加入到postgres用户组（GID=70）中。

重启服务令这个配置生效后，准备做一个基础备份。

pg_basebackup -U postgres -h localhost -D /backup/$DBNAME -Ft -z -Xs -R

DBNAME变量为一个目录名，基础备份会在这里保存几个文件：包括数据库基础数据，未归档的WAL，以及一个描述文件记录校验信息等。参数中的-Ft -z表示以tar.gz形式压缩备份，否则以数据库原始数据方式备份。-Xs表示以stream方式记录WAL。-R表示生成recovery.conf文件以便恢复。

最后将这个基础备份目录和前面配置的那个/backup/archive_wal/中的归档WAL文件全部保存好即完成备份。

如果要定期重建基础备份的话，可以同时把旧的WAL归档删除以节约空间。

# 删除旧的基础备份
rm /backup/$(DBNAME)/*
if
    pg_basebackup -U postgres -h localhost -D /backup/$(DBNAME) -Ft -z -Xs -R
then
    # 必要时修改权限，以便其它用户复制备份
    chown 1000:1000 /backup/${DBNAME}/*
    # 基础备份成功则删除一天以前的WAL归档
    find ${BAKWAL}/ -type f -mtime +1 -exec rm {} \;
else
    # 基础备份失败则退出
    echo "pg_basebackup fail"
    exit 1
fi

恢复的具体操作

首先是停止现有服务并备份数据文件，如果是新服务器则可以略过这步。

systemctl stop postgresql
cp -R /var/lib/postgresql /var/backup/new
rm -r /var/lib/postgresql/*

恢复基础数据：

# 因为是使用压缩打包的格式，所以这里需要解压
cd /var/lib/postgresql
tar -xvf /var/backup/old/base.tar.gz
cd pg_wal
tar -xvf /var/backup/old/pg_wal.tar.gz

因为只恢复基础数据，不含WAL，所以可以略过删除未归档WAL的操作，如不是新服务器，有未归档wal在原服务备份中，则需要恢复过来：

cp -R /var/backup/new/pg_wal/* /var/lib/postgresql/pg_wal/

然后配置postgresql服务为恢复模式（Postgresql 12+，旧版用法不同，请参考文档），修改postgresql.conf，其中的路径是存放归档WAL的地方：

restore_command = 'gunzip -c /backup/archive_wal/%f > %p'
recovery_target_timeline = 'latest'

注意，因为之前归档WAL时使用了gzip压缩，此处需要解压出来恢复。

另外，如果这个WAL是备份过的，要注意一下文件权限，必须是postgres用户(UID=70)可读。

还有就是基础备份恢复出来的配置文件里有archive wal配置要先去掉。

修改pg_hba.conf，暂时禁止不必要的连接，然后启动postgresql服务：

systemctl start postgresql

然后查看日志文件，里面会有恢复进度以及最后的恢复结果。正常恢复完成后会有类似的结果：

2025-04-11 09:08:11.707 UTC [27] LOG:  entering standby mode
2025-04-11 09:08:11.711 UTC [27] LOG:  redo starts at 69/F9325D80
2025-04-11 09:08:11.712 UTC [27] LOG:  consistent recovery state reached at 69/FA000000
2025-04-11 09:08:11.712 UTC [27] LOG:  unexpected pageaddr 69/BE000000 in log segment 0000000100000069000000FA, offset 0
2025-04-11 09:08:11.713 UTC [1] LOG:  database system is ready to accept read only connections
2025-04-11 09:08:11.718 UTC [31] LOG:  started streaming WAL from primary at 69/FA000000 on timeline 1

显示数据库已经恢复完成，但最后一个WAL没有恢复成功，因为源数据库还在运行中，所以最后一个WAL可能不完整（还有事务未提交等），但没有关系，我们已经恢复到了最后一个完成的事务点，现在的数据库已经是可用的了。但是因为当前是恢复模式，所以显示为standby模式，只能接受只读连接。

现在需要修改postgresql.conf把restore相关设置去掉，然后把/var/lib/postgresql/data中的standby.signal文件删除（退出standby模式）。如果改过pg_hba.conf，也要恢复正常的配置。

再重启数据库服务，这样就正常了：

database system is ready to accept connections

如果日志显示失败，可以看看日志里的原因，当然更简单的办法就是从源数据库重新备份一次。

推送到[go4pro.org]