如何无用户中断复制PostgreSQL 9.6版本数据库

完全可以在不中断任何用户连接、不影响线上业务正常访问的前提下，克隆PostgreSQL 9.6生产库用于测试，根据库规模和生产配置情况，有两种成熟方案可选，全程都不会强制断开用户连接：

方案1：物理克隆（适合100G以上大库，恢复速度快） #

使用PostgreSQL原生自带的pg_basebackup工具做在线热备，该工具不会对生产库加排他锁，不会阻塞现有读写请求。

前置要求 #

如果生产库已经部署过流复制从库，相关配置已经默认开启，无需额外操作；如果没开过，需要调整两个参数：wal_level = replica、max_wal_senders = 2（数值大于1即可，预留一个连接给备份用），同时在pg_hba.conf中给备份账号放开replication连接权限。如果你的9.6版本调整wal_level需要重启实例、暂时不方便重启，直接选方案2即可。

操作命令 #

直接在目标测试服务器上执行以下操作，不要在生产服务器本地备份占用生产资源：

1. 创建测试库数据目录并授权

   mkdir -p /var/lib/postgresql/9.6/main
   chown postgres:postgres /var/lib/postgresql/9.6/main
   

2. 执行远程克隆

   su - postgres -c "pg_basebackup -h <生产库IP地址> -p <生产库端口> -U <具备replication权限的账号> -D /var/lib/postgresql/9.6/main -Fp -Xs -P -R"
   

3. 克隆完成后调整配置启动测试库
   进入数据目录，找到自动生成的recovery.conf文件，注释掉standby_mode = on和primary_conninfo两行配置（避免测试库启动后持续连接生产库当从库），之后直接启动数据库服务即可，得到的是克隆操作启动时间点的生产库全量一致性副本。

方案2：逻辑备份（适合中小规模库，零配置侵入） #

如果不想调整生产库任何参数，直接用pg_dump+pg_restore的逻辑备份方式即可，全程只对表加短暂的读锁，不会中断用户连接，也不会阻塞正常写入。

操作命令 #

1. 在任意能连通生产库的机器上执行备份，导出压缩格式的备份文件

   pg_dump -h <生产库IP> -p <生产库端口> -U <有业务库读权限的账号> -d <要克隆的数据库名> -F c -Z 6 -f prod_db.dump
   

2. 在测试机上先创建空的目标数据库，再执行并行恢复

   createdb -h localhost -p <测试库端口> -U postgres test_db
   pg_restore -h localhost -p <测试库端口> -U postgres -d test_db -j 4 prod_db.dump
   

注意点 #

• 逻辑备份恢复后的库不会携带生产库的全局账号、表空间等物理属性，适合只需要业务数据的测试场景
• 单库大小超过200G时，逻辑备份恢复速度会明显慢于物理克隆，且备份期间长事务可能导致生产库WAL日志堆积，需要提前评估生产磁盘剩余空间。

内容的提问来源于stack exchange，提问作者CPI