EDB PostgreSQL 12.6中如何临时禁用读权限1小时避免Schema同步锁冲突？

以下是几种在Schema删除与同步期间阻止传入查询的可行方案，适配CentOS 7环境下的独立版EDB PostgreSQL 12.6：

方案1：临时禁用数据库新连接并清理现有会话 #

该方案通过切断现有访问会话、阻止新连接，彻底隔离同步操作与业务查询：

• 清理目标Schema的现有会话：执行SQL终止所有访问目标Schema的非超级用户进程

  SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
  WHERE datname = '你的数据库名'
    AND usename != 'postgres' -- 保留超级用户连接用于操作
    AND query LIKE '%你的目标Schema名称%';
  

• 禁止新连接到目标数据库：修改数据库参数拒绝新连接

  ALTER DATABASE 你的数据库名 WITH ALLOW_CONNECTIONS false;
  

• 执行Schema同步操作：运行你原本的删除、同步脚本逻辑（如DROP SCHEMA ... CASCADE、结构同步等）
• 恢复数据库连接权限：同步完成后恢复正常访问

  ALTER DATABASE 你的数据库名 WITH ALLOW_CONNECTIONS true;
  

方案2：通过事务锁隔离Schema操作 #

如果不想完全禁止数据库连接，可通过高隔离级别事务+排他锁阻止其他查询：

• 开启串行化隔离级别的事务：

  BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  

• 锁定目标Schema下所有对象：使用ACCESS EXCLUSIVE锁阻止所有类型的访问（包括SELECT）

  LOCK TABLE 你的Schema名称.* IN ACCESS EXCLUSIVE MODE;
  

• 执行Schema删除与同步：在此事务内完成DROP SCHEMA、结构同步等操作
• 提交事务释放锁：

  COMMIT;
  

注：若存在长时间运行的SELECT会话，锁操作可能会等待超时，建议先终止这类会话再执行。

方案3：临时修改pg_hba.conf限制访问 #

通过修改认证配置临时限制非超级用户的数据库访问：

• 备份原配置文件：

  cp /var/lib/edb/as12/data/pg_hba.conf /var/lib/edb/as12/data/pg_hba.conf.bak
  

  （路径根据你的EDB PostgreSQL安装目录调整，默认12版本数据目录为/var/lib/edb/as12/data）
• 添加访问限制规则：在pg_hba.conf开头插入以下规则，仅允许本地超级用户连接

  host    你的数据库名    all         0.0.0.0/0          reject
  host    你的数据库名    postgres    127.0.0.1/32       md5
  

• 重载PostgreSQL配置：

  /usr/edb/as12/bin/pg_ctl reload -D /var/lib/edb/as12/data
  

• 清理现有非超级用户会话：执行方案1中的pg_terminate_backend语句
• 执行Schema同步操作：运行同步脚本
• 恢复原配置并重载：

  cp /var/lib/edb/as12/data/pg_hba.conf.bak /var/lib/edb/as12/data/pg_hba.conf
  /usr/edb/as12/bin/pg_ctl reload -D /var/lib/edb/as12/data
  

关键注意事项 #

• 所有操作需使用超级用户（如postgres或edb用户）执行，确保权限充足
• 操作前建议提前通知业务方，避免影响正常业务
• 务必在测试环境验证脚本逻辑后，再部署到生产环境
• EDB PostgreSQL的命令路径与社区版略有差异，需根据实际安装路径调整

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