pg_restore运行时如何锁定数据库避免恢复出错

在PostgreSQL场景下完全可以通过原生能力实现恢复期间的库级锁定，杜绝外部非预期操作干扰恢复流程，两类方案可直接适配你当前的命令场景，无需大幅调整现有执行逻辑。

方案1：轻量隔离（适配绝大多数生产场景） #

这个方案操作成本最低，完全兼容你当前使用的-j 7并行恢复参数，操作步骤如下：

• 正式执行pg_restore前，先连接到数据库实例，批量断开目标库的所有存量外部连接：

  SELECT pg_terminate_backend(pid) 
  FROM pg_stat_activity 
  WHERE datname = 'restdb01' 
    AND pid <> pg_backend_pid();
  

• 给目标库设置连接数为0的限制，开启库级隔离：

  ALTER DATABASE restdb01 CONNECTION LIMIT 0;
  

  这个设置的效果是：普通用户、外部应用的连接请求会被直接拒绝，只有拥有超级管理员权限的账号可以正常连接执行恢复操作，完全不会触发外部程序的非预期写入。
• 待pg_restore全流程执行完成、完成数据校验后，再将数据库的连接数限制改回原有配置，恢复对外访问能力。

方案2：严格隔离（极致防干扰场景） #

如果需要连其他超级管理员账号的临时查询、运维操作都完全阻断，可使用数据库级排他锁实现强隔离：

• 同样先执行前面的存量连接断开SQL，踢掉所有已有的目标库连接
• 打开一个独立的数据库会话，执行锁操作并保持会话不关闭：

  BEGIN;
  LOCK DATABASE restdb01 IN ACCESS EXCLUSIVE MODE;
  

  这个锁的效果是：持有锁的会话是唯一可以操作该数据库的会话，所有其他来源的连接请求（哪怕是超级管理员发起的）都会被阻塞，完全无法对库发起任何读写操作。
• 待整个恢复流程全部结束后，回到持有锁的会话执行COMMIT;释放锁，数据库即可恢复正常访问。

执行注意事项 #

• 上述两个方案都完全适配你当前执行的恢复命令：pg_restore --clean --verbose --no-owner /home/postgres/backup/bkp --if-exists -j 7 -d restdb01 -p 5432，不需要对命令本身做额外修改。
• 若使用低于9.2版本的PostgreSQL，前述断开连接的SQL中pid字段需要替换为老版本对应的procpid字段。
• 注意锁的释放时机，必须等所有恢复操作、完整性校验操作全部完成后再放开数据库访问权限，避免半恢复状态下被外部操作打断。

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