1. 项目概述1.1 场景拆解什么时候需要把PostgreSQL实例锁成只读直接说结论PostgreSQL数据库实例只读锁定就是把整个数据库集群或某个数据库切成“只能查、不能改”的状态。你可能因为主从切换、数据迁移、审计需求、误删防护而要做这件事也可能是在准备把一套库迁移上云或者交接给另一个团队维护。不管是哪种场景你需要的不是一个“把表锁住”的工具而是一整套“怎么锁、锁多深、怎么优雅地解锁”的操作方案。我在生产环境里踩过不少坑之后最深的体会是只读锁定看似简单——不就是改个配置嘛——但实际上它牵扯到会话级参数、事务生命周期、连接池行为、复制槽状态四个层面。哪一个没考虑到位都会在切换回读写的时候暴雷。适合看这篇文章的人被业务方要求“必须让开发环境只读”的DBA、正在做数据库迁移的运维同学、写脚本批量处理多个PG实例的工程师以及任何想搞懂“为什么我设了只读还是能写进去”的PG使用者。1.2 核心需求与常见误区只读锁定的核心需求其实就一句话阻止任何非预期的数据变更但保证查询行为完全正常。听起来简单但这句话拆开看就有几个容易忽略的点这里的“数据变更”包括INSERT、UPDATE、DELETE也包括DDLCREATE TABLE、ALTER TABLE等还包括序列的nextval操作、临时表写入和函数内的写操作。这里的“查询正常”意味着索引扫描、并行查询、只读事务都不应该受影响。解锁必须是可预期、可回滚的——你不能把库锁成一个需要重启才能恢复的死状态。我见过好几起事故都是运维同学执行了ALTER SYSTEM SET default_transaction_read_only on;之后过几天忘了等到要写数据时才发现连CREATE TEMP TABLE都报错然后手忙脚乱地在生产库上排查。这种时候最耽误时间的不是改回配置而是搞清楚现场有哪些长事务、有哪些连接池会话还持有旧配置。一个基本认知PostgreSQL的只读控制不是“一把全局大锁”而是一套由事务级参数、数据库级配置、表空间级权限组合出来的防护网。你要做的是根据场景选择正确的组合而不是找到某个“万能开关”。2. 方案选型四种只读手段的取舍2.1 方案全景对比PostgreSQL里实现只读锁定常见的手段有四种我直接给一张对比表看完你就知道它们各自的适用范围了。实现方式隔离范围是否影响已有会话是否需要重启推荐场景default_transaction_read_only实例级新连接/新事务不影响已开启事务不需要整体维护、限时冻结ALTER DATABASE ... ALLOW_CONNECTIONS配合权限回收数据库连接层立即断开配合terminate不需要迁移后的最终封存pg_ctl/pg_rewind等维护模式单实例全库强制断开需要主备切换、故障恢复表空间或文件系统只读物理层立即只读大多需要归档、灾备演练这里最容易犯的错误是把“默认只读”当成了全局强制只读。它的缺陷在于——它只对新事务生效。如果你有一堆连接池里的长连接处于idle in transaction状态它们之前已经开启的事务仍然可以继续写数据。对你没看错PostgreSQL在这个问题上就是这么“宽容”。2.2 为什么建议优先用实例级参数我的习惯是凡是“限时只读”的需求优先走default_transaction_read_only配合ALTER SYSTEM写进配置文件而不是只对当前会话执行SET。原因很简单——SESSION级别的SET default_transaction_read_only on只对当前会话的下一个事务生效连接池一回收连接就失效非常不可靠。而ALTER SYSTEM会把参数写进postgresql.auto.conf对后续所有新建连接生效而且可以用ALTER SYSTEM RESET干净地撤销。具体执行方式-- 让后续所有新事务默认只读 ALTER SYSTEM SET default_transaction_read_only on; -- 重新加载配置不需要重启 SELECT pg_reload_conf();还需要一个前置动作把当前所有活跃事务处理掉否则它们仍然能够写入。-- 查看当前活跃事务 SELECT pid, state, xact_start, query_start, query FROM pg_stat_activity WHERE state active OR state idle in transaction;对于读取类的活跃会话不用动但只要有写事务在跑你就得评估是等它结束还是主动终止-- 主动终止指定的写事务谨慎使用 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state active AND pid pg_backend_pid();这里我特别提醒一句在只读锁定前终止事务和锁定后终止事务性质完全不一样。锁定前终止是清理环境锁定后你如果发现还有漏网之鱼说明你的锁定方案本身有漏洞需要回头看是不是连接池没有重建、是不是有会话绕过了参数。3. 实操全流程从锁定前检查到真正锁死3.1 锁定前的环境体检你有多少次是直接执行ALTER SYSTEM然后被业务方一句“还能写啊”打脸为了避免这种情况锁定前必须做三件事第一确认当前实例的角色。如果你操作的是一个流复制备库它本身就是只读的但你没法用default_transaction_read_only去改变它因为备库根本不接受写事务。别慌这反而简单——你只需要确认没有级联备库在往它上面转发写入就好。-- 检查当前实例是主库还是备库 SELECT pg_is_in_recovery();第二摸清连接来源。生产环境里连接池pgbouncer、odyssey、应用自带连接池是只读锁定最大的变数。因为连接池里已有的连接不会自动感知ALTER SYSTEM的参数变化除非连接池配置了自动重置会话参数否则你可能会看到明明服务都停了库里却还有一堆来自连接池的空闲连接。处理方式很简单但很多人会忘重建连接池。# pgbouncer 示例让现有连接全部销毁应用自动新建连接 psql -p 6432 pgbouncer -c PAUSE; psql -p 6432 pgbouncer -c KILL; psql -p 6432 pgbouncer -c RESUME;第三查看复制槽和逻辑复制的状态。如果你的实例上有逻辑订阅或pgoutput插件它们本身不会写业务数据但复制槽的推进会写系统表。这时候要评估只读期间是否允许复制槽更新如果不允许可能需要挂起逻辑复制的工作进程。3.2 执行锁定的标准操作序列下面这套流程我在多个项目里验证过可以称为“标准操作序列”。它可以保证从你执行第一个命令开始到最终确认只读生效中间不存在任何可写的窗口除了一种情况后面讲。设置实例级默认只读ALTER SYSTEM SET default_transaction_read_only on; SELECT pg_reload_conf();等一两秒确认参数已经加载SHOW default_transaction_read_only;我要求看到的结果是on。如果你看到的是off不要往下走先排查为什么pg_reload_conf()没有生效。新建一个测试连接验证新事务确实只读psql -U postgres -h 127.0.0.1 -d postgres -c CREATE TABLE test_readonly_check(id int);正常情况下你会看到报错ERROR: cannot execute CREATE TABLE in a read-only transaction再验证老连接回去找一个锁定前就建立的psql会话尝试写入。这个时候你大概率发现它能写成功——这是预期的因为旧会话的事务参数不会自动变。这就触发了一个决策点是终止这些老会话还是等它们自然结束我的建议是如果是限时维护比如半小时内把应用流量切换走之后直接终止这些连接利索。SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid pg_backend_pid() AND usename NOT IN (postgres, replicator); -- 根据自己的环境调整最后做一个覆盖性验证用一个新连接连续跑几条DML、DDL、序列操作确认全部被拒。下面是我常用的验证脚本片段BEGIN; INSERT INTO t_readonly_probe VALUES (1); -- 应当失败 ROLLBACK; BEGIN; CREATE TABLE t_readonly_probe2(id int); -- 应当失败 ROLLBACK; SELECT nextval(some_sequence); -- 应当失败这三条都失败才算锁死。如果任何一条成功对不起你的只读配置根本没有覆盖到对应路径。3.3 解锁比锁定更需要小心解锁看起来就是逆向操作但我实际遇到最多的问题反而出在解锁环节。原因是很多人忘了事务隔离级别和会话参数的残留状态。解锁的标准操作ALTER SYSTEM RESET default_transaction_read_only; SELECT pg_reload_conf();随后同样要重建连接池、终止旧的只读事务否则你的应用会因为连接池里某些连接还持有只读设置而频繁报错。这里最坑的是ALTER SYSTEM RESET只是重置了默认值已经开启的事务不会恢复读写能力。所以解锁后我建议强制重置所有连接SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid pg_backend_pid() AND state idle in transaction;注意我只终止了idle in transaction状态的连接因为它们在事务中持有旧设置。普通的idle连接反而不怕——它们还没开启事务下一个事务会读取新的默认值。另外一个容易忽略的地方如果你在只读期间有应用在重试写入会积累一堆失败连接和错误日志。解锁后要盯一下应用的连接池日志确认有没有连接还处于error state。4. 纵深防御只读锁定与权限控制组合4.1 为什么说单靠参数不够只用default_transaction_read_only做只读锁定在我眼里只能打60分。因为任何能登录实例并执行SET default_transaction_read_only off的用户都能绕过这个限制。而PostgreSQL里SET这个命令本身没有细粒度的权限控制普通用户在自己的会话里改这个参数是被允许的。这意味着如果你的“只读锁定”是为了防某个账号误写那你等于没锁。解决办法是组合两层防御第一层保留参数层的只读设置挡住所有不注意细节的工具和应用第二层收紧数据库权限把业务的写权限在数据库角色层面直接撤销。-- 假设业务账号叫 app_user REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM app_user; REVOKE CREATE ON SCHEMA public FROM app_user; ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE INSERT, UPDATE, DELETE ON TABLES FROM app_user;这一套操作做完即使有人SET default_transaction_read_only off他也没有对应的表权限去写。这才是真正的“锁死”。4.2 序列和临时表两个特别难以察觉的写入路径我发现很多DBA在只读锁定时会漏掉两个点序列和临时表。先说序列nextval()本身不是DML但它会修改序列的当前值这在PostgreSQL里被视为对系统目录的修改。在default_transaction_read_only on下它确实会被拒。但如果你用的是非默认的序列缓存比如CACHE 100应用可能已经在本地缓存了一批序列值在只读期间往表里插数据时用缓存序列号——不过别担心INSERT本身会被拦所以序列这块的坑主要是应用日志里面会刷一大批nextval: cannot execute nextval() in a read-only transaction的错误。再说临时表很多人以为临时表只对自己可见写入不算“改数据”。但在只读事务里PostgreSQL同样禁止CREATE TEMP TABLE。如果你有批处理脚本习惯先建临时表再跑数据在只读锁定期间会直接失败。要解决也不难在只读锁定期间如果业务确实需要临时表做复杂计算可以把临时表改成CTE或者改用UNLOGGED表但不建议因为UNLOGGED表是真实的数据写入。4.3 只读期间的监控指标锁定只是开始不是结束。只读期间你要盯几类指标才能判断锁定是否被破坏、是否有异常行为指标命令/工具判断标准新写入尝试SELECT * FROM pg_stat_activity WHERE query ILIKE %insert% OR query ILIKE %update%应为空或全部报错中止只读参数状态SHOW default_transaction_read_only;应为on连接数变化SELECT count(*) FROM pg_stat_activity;与基线对比死锁/锁等待SELECT * FROM pg_locks WHERE NOT granted;应无新增如果只读期间你想要更主动的告警可以部署一个定时探测脚本每分钟尝试向一张探针表插入数据并捕获错误。这张探针表本身不用真实存在——用一条必然失败的SQL即可INSERT INTO pg_probe_should_not_exist VALUES (1);抓到错误码25006read_only_sql_transaction就说明锁定还在生效。这个定时任务可以跑在应用服务器上通过psql执行不要占用数据库侧的资源。5. 常见问题与故障排查实录5.1 为什么设置了default_transaction_read_only还是能写这是我被问过最多的问题没有之一。原因有四种可能按出现概率排序你设置的是当前会话的参数而不是实例级参数。SET default_transaction_read_only on只影响当前会话其他人照常。新参数没有加载。ALTER SYSTEM之后必须pg_reload_conf()或者重启懒一次就会出问题。连接池里旧连接还没重建这些连接持有旧的配置。有人显式执行了SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE这个命令会覆盖默认值让当前会话强制开启读写。排查顺序建议先看SHOW default_transaction_read_only;的结果是否在所有相关会话上都是on再排查连接池最后去数据库日志里翻SET SESSION CHARACTERISTICS的痕迹。5.2 只读后主从切换/备份为什么会失败这个问题典型到值得单独拿出来讲。在只读锁定开启时如果你触发pg_basebackup或者使用归档命令可能会发现备份失败。原因是备份过程需要写入备份标签文件复制槽的推进也需要写入pg_replslot目录。虽然这些不是普通业务写入但只读模式下部分物理写入路径确实会受影响。如果只读期间确实需要做备份我建议用pg_dump逻辑备份代替pg_basebackup如果必须做物理备份就先临时解锁备份完成后再重新锁定。这种限时窗口只要严格控制在维护窗口内风险是可控的。5.3 只读状态下VACUUM和autovacuum是否正常这个问题很多资深DBA都会答错。其实VACUUM在只读事务里是允许的因为它清理的是死元组本质上是回收存储空间而不是修改逻辑数据。但要注意autovacuum不会因为只读就停下来它照常跑。手动VACUUM FULL不行因为VACUUM FULL需要重写表会产生写事务。ANALYZE本身是允许的但如果统计信息表也需要更新那它在只读模式下会静默跳过部分工作。所以只读期间你不需要手动去关autovacuum反而应该让它正常工作避免表膨胀。5.4 解锁后应用仍然报read-only错误解锁后最多见的情况是应用还在报cannot execute ... in a read-only transaction我总结下来多半是这两种一是连接池里的连接并没有重新建立还带着旧事务的特性。解决办法是重启连接池或者让应用主动重连。二是应用代码里手动执行了SET TRANSACTION READ ONLY。这种是应用层写死的和实例配置无关最隐蔽。排查方法在数据库侧开启log_statement all一段时间或者直接查pg_stat_activity看应用连接刚创建时有没有执行SET命令。找到之后需要应用发版去掉这个命令否则它连的就是一个“永远只读”的会话。5.5 常见问题速查表症状可能原因处理思路新连接只能读但旧连接还能写只读参数只影响新事务终止旧会话或等待自然结束所有连接都只能读无法恢复写入有连接池保存了旧配置重启连接池、重建连接设置参数时报权限不足当前账号不是超级用户用超级用户或申请权限只读后DML报错但DDL不报错参数可能只在事务级别生效确认是否在事务内执行DDL只读实例上备份失败备份路径涉及物理写入改用逻辑备份或临时解锁解锁后应用立刻恢复但几分钟后又只读应用代码显式设置了只读事务检查应用连接池/ORM配置6. 经验总结与维护建议6.1 一套可复用的脚本化方案如果你需要周期性执行“限时只读——写操作——解锁”的维护流程别靠手敲命令建议直接脚本化。下面是我常用的一个最小可用的Bash脚本骨架你按自己的环境改改就能用#!/bin/bash # usage: ./pg_readonly_lock.sh host port dbname superuser HOST$1 PORT$2 DBNAME$3 SUPERUSER$4 echo Step 1: set default_transaction_read_only psql -h $HOST -p $PORT -U $SUPERUSER -d $DBNAME SQL ALTER SYSTEM SET default_transaction_read_only on; SELECT pg_reload_conf(); SQL sleep 2 echo Step 2: verify new transaction is read-only psql -h $HOST -p $PORT -U $SUPERUSER -d $DBNAME \ -c CREATE TABLE pg_probe_ro_check(id int); 21 || echo Expected error: read-only transaction echo Step 3: terminate idle in transaction connections psql -h $HOST -p $PORT -U $SUPERUSER -d $DBNAME SQL SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid pg_backend_pid() AND state idle in transaction; SQL echo Done: instance is now read-only 解锁脚本类似把ALTER SYSTEM SET换成ALTER SYSTEM RESET即可。注意脚本执行前检查PGPASSWORD环境变量避免密码暴露在命令行历史里。6.2 最后的经验提醒做只读锁定这件事我个人的原则是越小范围的锁越是好锁。能锁一个表级别的事务比如用LOCK TABLE ... IN ACCESS EXCLUSIVE MODE就别锁整个实例能锁半小时就别锁半天能只影响新会话就别强行终止老连接。因为数据库锁的范围越大恢复时需要清理的现场就越复杂。另一点想说的是只读锁定是“看起来简单但排查起来绕”的操作。如果你跟我一样管理着几十套PG实例建议给每套实例的只读/解锁操作都写进变更记录包括执行时间、影响会话数、异常事件。等到某次大版本升级或者云迁移时你会感谢自己这些记录的。最后不管用哪种方式锁定一定先在测试环境完整演练一遍尤其是解锁——测试环境里把锁定、验证、解锁、连接重建走通生产上才不会手忙脚乱。这个习惯救过我太多次了。 PostgreSQL实例只读锁定全攻略：参数配置、连接池与权限防御
1. 项目概述1.1 场景拆解什么时候需要把PostgreSQL实例锁成只读直接说结论PostgreSQL数据库实例只读锁定就是把整个数据库集群或某个数据库切成“只能查、不能改”的状态。你可能因为主从切换、数据迁移、审计需求、误删防护而要做这件事也可能是在准备把一套库迁移上云或者交接给另一个团队维护。不管是哪种场景你需要的不是一个“把表锁住”的工具而是一整套“怎么锁、锁多深、怎么优雅地解锁”的操作方案。我在生产环境里踩过不少坑之后最深的体会是只读锁定看似简单——不就是改个配置嘛——但实际上它牵扯到会话级参数、事务生命周期、连接池行为、复制槽状态四个层面。哪一个没考虑到位都会在切换回读写的时候暴雷。适合看这篇文章的人被业务方要求“必须让开发环境只读”的DBA、正在做数据库迁移的运维同学、写脚本批量处理多个PG实例的工程师以及任何想搞懂“为什么我设了只读还是能写进去”的PG使用者。1.2 核心需求与常见误区只读锁定的核心需求其实就一句话阻止任何非预期的数据变更但保证查询行为完全正常。听起来简单但这句话拆开看就有几个容易忽略的点这里的“数据变更”包括INSERT、UPDATE、DELETE也包括DDLCREATE TABLE、ALTER TABLE等还包括序列的nextval操作、临时表写入和函数内的写操作。这里的“查询正常”意味着索引扫描、并行查询、只读事务都不应该受影响。解锁必须是可预期、可回滚的——你不能把库锁成一个需要重启才能恢复的死状态。我见过好几起事故都是运维同学执行了ALTER SYSTEM SET default_transaction_read_only on;之后过几天忘了等到要写数据时才发现连CREATE TEMP TABLE都报错然后手忙脚乱地在生产库上排查。这种时候最耽误时间的不是改回配置而是搞清楚现场有哪些长事务、有哪些连接池会话还持有旧配置。一个基本认知PostgreSQL的只读控制不是“一把全局大锁”而是一套由事务级参数、数据库级配置、表空间级权限组合出来的防护网。你要做的是根据场景选择正确的组合而不是找到某个“万能开关”。2. 方案选型四种只读手段的取舍2.1 方案全景对比PostgreSQL里实现只读锁定常见的手段有四种我直接给一张对比表看完你就知道它们各自的适用范围了。实现方式隔离范围是否影响已有会话是否需要重启推荐场景default_transaction_read_only实例级新连接/新事务不影响已开启事务不需要整体维护、限时冻结ALTER DATABASE ... ALLOW_CONNECTIONS配合权限回收数据库连接层立即断开配合terminate不需要迁移后的最终封存pg_ctl/pg_rewind等维护模式单实例全库强制断开需要主备切换、故障恢复表空间或文件系统只读物理层立即只读大多需要归档、灾备演练这里最容易犯的错误是把“默认只读”当成了全局强制只读。它的缺陷在于——它只对新事务生效。如果你有一堆连接池里的长连接处于idle in transaction状态它们之前已经开启的事务仍然可以继续写数据。对你没看错PostgreSQL在这个问题上就是这么“宽容”。2.2 为什么建议优先用实例级参数我的习惯是凡是“限时只读”的需求优先走default_transaction_read_only配合ALTER SYSTEM写进配置文件而不是只对当前会话执行SET。原因很简单——SESSION级别的SET default_transaction_read_only on只对当前会话的下一个事务生效连接池一回收连接就失效非常不可靠。而ALTER SYSTEM会把参数写进postgresql.auto.conf对后续所有新建连接生效而且可以用ALTER SYSTEM RESET干净地撤销。具体执行方式-- 让后续所有新事务默认只读 ALTER SYSTEM SET default_transaction_read_only on; -- 重新加载配置不需要重启 SELECT pg_reload_conf();还需要一个前置动作把当前所有活跃事务处理掉否则它们仍然能够写入。-- 查看当前活跃事务 SELECT pid, state, xact_start, query_start, query FROM pg_stat_activity WHERE state active OR state idle in transaction;对于读取类的活跃会话不用动但只要有写事务在跑你就得评估是等它结束还是主动终止-- 主动终止指定的写事务谨慎使用 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state active AND pid pg_backend_pid();这里我特别提醒一句在只读锁定前终止事务和锁定后终止事务性质完全不一样。锁定前终止是清理环境锁定后你如果发现还有漏网之鱼说明你的锁定方案本身有漏洞需要回头看是不是连接池没有重建、是不是有会话绕过了参数。3. 实操全流程从锁定前检查到真正锁死3.1 锁定前的环境体检你有多少次是直接执行ALTER SYSTEM然后被业务方一句“还能写啊”打脸为了避免这种情况锁定前必须做三件事第一确认当前实例的角色。如果你操作的是一个流复制备库它本身就是只读的但你没法用default_transaction_read_only去改变它因为备库根本不接受写事务。别慌这反而简单——你只需要确认没有级联备库在往它上面转发写入就好。-- 检查当前实例是主库还是备库 SELECT pg_is_in_recovery();第二摸清连接来源。生产环境里连接池pgbouncer、odyssey、应用自带连接池是只读锁定最大的变数。因为连接池里已有的连接不会自动感知ALTER SYSTEM的参数变化除非连接池配置了自动重置会话参数否则你可能会看到明明服务都停了库里却还有一堆来自连接池的空闲连接。处理方式很简单但很多人会忘重建连接池。# pgbouncer 示例让现有连接全部销毁应用自动新建连接 psql -p 6432 pgbouncer -c PAUSE; psql -p 6432 pgbouncer -c KILL; psql -p 6432 pgbouncer -c RESUME;第三查看复制槽和逻辑复制的状态。如果你的实例上有逻辑订阅或pgoutput插件它们本身不会写业务数据但复制槽的推进会写系统表。这时候要评估只读期间是否允许复制槽更新如果不允许可能需要挂起逻辑复制的工作进程。3.2 执行锁定的标准操作序列下面这套流程我在多个项目里验证过可以称为“标准操作序列”。它可以保证从你执行第一个命令开始到最终确认只读生效中间不存在任何可写的窗口除了一种情况后面讲。设置实例级默认只读ALTER SYSTEM SET default_transaction_read_only on; SELECT pg_reload_conf();等一两秒确认参数已经加载SHOW default_transaction_read_only;我要求看到的结果是on。如果你看到的是off不要往下走先排查为什么pg_reload_conf()没有生效。新建一个测试连接验证新事务确实只读psql -U postgres -h 127.0.0.1 -d postgres -c CREATE TABLE test_readonly_check(id int);正常情况下你会看到报错ERROR: cannot execute CREATE TABLE in a read-only transaction再验证老连接回去找一个锁定前就建立的psql会话尝试写入。这个时候你大概率发现它能写成功——这是预期的因为旧会话的事务参数不会自动变。这就触发了一个决策点是终止这些老会话还是等它们自然结束我的建议是如果是限时维护比如半小时内把应用流量切换走之后直接终止这些连接利索。SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid pg_backend_pid() AND usename NOT IN (postgres, replicator); -- 根据自己的环境调整最后做一个覆盖性验证用一个新连接连续跑几条DML、DDL、序列操作确认全部被拒。下面是我常用的验证脚本片段BEGIN; INSERT INTO t_readonly_probe VALUES (1); -- 应当失败 ROLLBACK; BEGIN; CREATE TABLE t_readonly_probe2(id int); -- 应当失败 ROLLBACK; SELECT nextval(some_sequence); -- 应当失败这三条都失败才算锁死。如果任何一条成功对不起你的只读配置根本没有覆盖到对应路径。3.3 解锁比锁定更需要小心解锁看起来就是逆向操作但我实际遇到最多的问题反而出在解锁环节。原因是很多人忘了事务隔离级别和会话参数的残留状态。解锁的标准操作ALTER SYSTEM RESET default_transaction_read_only; SELECT pg_reload_conf();随后同样要重建连接池、终止旧的只读事务否则你的应用会因为连接池里某些连接还持有只读设置而频繁报错。这里最坑的是ALTER SYSTEM RESET只是重置了默认值已经开启的事务不会恢复读写能力。所以解锁后我建议强制重置所有连接SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid pg_backend_pid() AND state idle in transaction;注意我只终止了idle in transaction状态的连接因为它们在事务中持有旧设置。普通的idle连接反而不怕——它们还没开启事务下一个事务会读取新的默认值。另外一个容易忽略的地方如果你在只读期间有应用在重试写入会积累一堆失败连接和错误日志。解锁后要盯一下应用的连接池日志确认有没有连接还处于error state。4. 纵深防御只读锁定与权限控制组合4.1 为什么说单靠参数不够只用default_transaction_read_only做只读锁定在我眼里只能打60分。因为任何能登录实例并执行SET default_transaction_read_only off的用户都能绕过这个限制。而PostgreSQL里SET这个命令本身没有细粒度的权限控制普通用户在自己的会话里改这个参数是被允许的。这意味着如果你的“只读锁定”是为了防某个账号误写那你等于没锁。解决办法是组合两层防御第一层保留参数层的只读设置挡住所有不注意细节的工具和应用第二层收紧数据库权限把业务的写权限在数据库角色层面直接撤销。-- 假设业务账号叫 app_user REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM app_user; REVOKE CREATE ON SCHEMA public FROM app_user; ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE INSERT, UPDATE, DELETE ON TABLES FROM app_user;这一套操作做完即使有人SET default_transaction_read_only off他也没有对应的表权限去写。这才是真正的“锁死”。4.2 序列和临时表两个特别难以察觉的写入路径我发现很多DBA在只读锁定时会漏掉两个点序列和临时表。先说序列nextval()本身不是DML但它会修改序列的当前值这在PostgreSQL里被视为对系统目录的修改。在default_transaction_read_only on下它确实会被拒。但如果你用的是非默认的序列缓存比如CACHE 100应用可能已经在本地缓存了一批序列值在只读期间往表里插数据时用缓存序列号——不过别担心INSERT本身会被拦所以序列这块的坑主要是应用日志里面会刷一大批nextval: cannot execute nextval() in a read-only transaction的错误。再说临时表很多人以为临时表只对自己可见写入不算“改数据”。但在只读事务里PostgreSQL同样禁止CREATE TEMP TABLE。如果你有批处理脚本习惯先建临时表再跑数据在只读锁定期间会直接失败。要解决也不难在只读锁定期间如果业务确实需要临时表做复杂计算可以把临时表改成CTE或者改用UNLOGGED表但不建议因为UNLOGGED表是真实的数据写入。4.3 只读期间的监控指标锁定只是开始不是结束。只读期间你要盯几类指标才能判断锁定是否被破坏、是否有异常行为指标命令/工具判断标准新写入尝试SELECT * FROM pg_stat_activity WHERE query ILIKE %insert% OR query ILIKE %update%应为空或全部报错中止只读参数状态SHOW default_transaction_read_only;应为on连接数变化SELECT count(*) FROM pg_stat_activity;与基线对比死锁/锁等待SELECT * FROM pg_locks WHERE NOT granted;应无新增如果只读期间你想要更主动的告警可以部署一个定时探测脚本每分钟尝试向一张探针表插入数据并捕获错误。这张探针表本身不用真实存在——用一条必然失败的SQL即可INSERT INTO pg_probe_should_not_exist VALUES (1);抓到错误码25006read_only_sql_transaction就说明锁定还在生效。这个定时任务可以跑在应用服务器上通过psql执行不要占用数据库侧的资源。5. 常见问题与故障排查实录5.1 为什么设置了default_transaction_read_only还是能写这是我被问过最多的问题没有之一。原因有四种可能按出现概率排序你设置的是当前会话的参数而不是实例级参数。SET default_transaction_read_only on只影响当前会话其他人照常。新参数没有加载。ALTER SYSTEM之后必须pg_reload_conf()或者重启懒一次就会出问题。连接池里旧连接还没重建这些连接持有旧的配置。有人显式执行了SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE这个命令会覆盖默认值让当前会话强制开启读写。排查顺序建议先看SHOW default_transaction_read_only;的结果是否在所有相关会话上都是on再排查连接池最后去数据库日志里翻SET SESSION CHARACTERISTICS的痕迹。5.2 只读后主从切换/备份为什么会失败这个问题典型到值得单独拿出来讲。在只读锁定开启时如果你触发pg_basebackup或者使用归档命令可能会发现备份失败。原因是备份过程需要写入备份标签文件复制槽的推进也需要写入pg_replslot目录。虽然这些不是普通业务写入但只读模式下部分物理写入路径确实会受影响。如果只读期间确实需要做备份我建议用pg_dump逻辑备份代替pg_basebackup如果必须做物理备份就先临时解锁备份完成后再重新锁定。这种限时窗口只要严格控制在维护窗口内风险是可控的。5.3 只读状态下VACUUM和autovacuum是否正常这个问题很多资深DBA都会答错。其实VACUUM在只读事务里是允许的因为它清理的是死元组本质上是回收存储空间而不是修改逻辑数据。但要注意autovacuum不会因为只读就停下来它照常跑。手动VACUUM FULL不行因为VACUUM FULL需要重写表会产生写事务。ANALYZE本身是允许的但如果统计信息表也需要更新那它在只读模式下会静默跳过部分工作。所以只读期间你不需要手动去关autovacuum反而应该让它正常工作避免表膨胀。5.4 解锁后应用仍然报read-only错误解锁后最多见的情况是应用还在报cannot execute ... in a read-only transaction我总结下来多半是这两种一是连接池里的连接并没有重新建立还带着旧事务的特性。解决办法是重启连接池或者让应用主动重连。二是应用代码里手动执行了SET TRANSACTION READ ONLY。这种是应用层写死的和实例配置无关最隐蔽。排查方法在数据库侧开启log_statement all一段时间或者直接查pg_stat_activity看应用连接刚创建时有没有执行SET命令。找到之后需要应用发版去掉这个命令否则它连的就是一个“永远只读”的会话。5.5 常见问题速查表症状可能原因处理思路新连接只能读但旧连接还能写只读参数只影响新事务终止旧会话或等待自然结束所有连接都只能读无法恢复写入有连接池保存了旧配置重启连接池、重建连接设置参数时报权限不足当前账号不是超级用户用超级用户或申请权限只读后DML报错但DDL不报错参数可能只在事务级别生效确认是否在事务内执行DDL只读实例上备份失败备份路径涉及物理写入改用逻辑备份或临时解锁解锁后应用立刻恢复但几分钟后又只读应用代码显式设置了只读事务检查应用连接池/ORM配置6. 经验总结与维护建议6.1 一套可复用的脚本化方案如果你需要周期性执行“限时只读——写操作——解锁”的维护流程别靠手敲命令建议直接脚本化。下面是我常用的一个最小可用的Bash脚本骨架你按自己的环境改改就能用#!/bin/bash # usage: ./pg_readonly_lock.sh host port dbname superuser HOST$1 PORT$2 DBNAME$3 SUPERUSER$4 echo Step 1: set default_transaction_read_only psql -h $HOST -p $PORT -U $SUPERUSER -d $DBNAME SQL ALTER SYSTEM SET default_transaction_read_only on; SELECT pg_reload_conf(); SQL sleep 2 echo Step 2: verify new transaction is read-only psql -h $HOST -p $PORT -U $SUPERUSER -d $DBNAME \ -c CREATE TABLE pg_probe_ro_check(id int); 21 || echo Expected error: read-only transaction echo Step 3: terminate idle in transaction connections psql -h $HOST -p $PORT -U $SUPERUSER -d $DBNAME SQL SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid pg_backend_pid() AND state idle in transaction; SQL echo Done: instance is now read-only 解锁脚本类似把ALTER SYSTEM SET换成ALTER SYSTEM RESET即可。注意脚本执行前检查PGPASSWORD环境变量避免密码暴露在命令行历史里。6.2 最后的经验提醒做只读锁定这件事我个人的原则是越小范围的锁越是好锁。能锁一个表级别的事务比如用LOCK TABLE ... IN ACCESS EXCLUSIVE MODE就别锁整个实例能锁半小时就别锁半天能只影响新会话就别强行终止老连接。因为数据库锁的范围越大恢复时需要清理的现场就越复杂。另一点想说的是只读锁定是“看起来简单但排查起来绕”的操作。如果你跟我一样管理着几十套PG实例建议给每套实例的只读/解锁操作都写进变更记录包括执行时间、影响会话数、异常事件。等到某次大版本升级或者云迁移时你会感谢自己这些记录的。最后不管用哪种方式锁定一定先在测试环境完整演练一遍尤其是解锁——测试环境里把锁定、验证、解锁、连接重建走通生产上才不会手忙脚乱。这个习惯救过我太多次了。