离上次被凌晨电话叫醒已经两个月了。电话那头是值班同事急促的声音数据库写不进去了所有应用都在报只读事务错误。作为一个常年跟 PostgreSQL 打交道的人我第一反应不是慌而是先问一句你现在执行一下 select pg_is_in_recovery() 看看返回什么这句话基本能区分一半的场景。PostgreSQL 数据库实例出现只读现象在真实生产环境中远比想象中常见可能是连接串误配到了从库可能是磁盘把 WAL 写崩了也可能是运维主动把实例锁成只读来配合变更。这篇文章围绕 PostgreSQL 数据库实例的只读锁定展开把被动排查和主动操作的完整链路都讲清楚包括背后的参数机制、生产场景的实操流程以及我踩过的几个印象深刻的坑。无论你是 DBA、后端开发还是刚接触 PostgreSQL 的新人都能从里面找到直接能用的方法。1. 先分清被动只读与主动锁定排查的第一步1.1 pg_is_in_recovery() 返回 true你连的是从库PostgreSQL 的流复制架构里备库在 hot_standby 参数开启时可以对外提供只读查询服务。这个天然只读是复制协议决定的——备库通过回放主库传来的 WAL 日志来同步数据任何直接写入都会破坏数据一致性所以 PostgreSQL 在事务开始时就把所有事务强制标记为只读。遇到这种被动只读最常见的触发原因是连接配置问题。应用连到了备库端口或者中间件负载均衡把写流量分到了备库。我见过一个小伙子排查了两个小时最后发现是 JDBC URL 里少配置了一个参数流量全被路由到了只读节点。最快的确认方式就是执行SELECT pg_is_in_recovery();返回true说明当前节点是备库或正处于恢复状态返回false才是可写主库。这一步 10 秒内就能完成但它能避免你在错误的方向上浪费大量时间。顺带一提在 psql 里执行\conninfo也能看到连接的是哪台主机、哪个端口配合使用更稳妥。1.2 磁盘爆满导致的伪只读还有一种只读是披着羊皮的狼数据库本身没被设置成只读但底层磁盘满了WAL 段文件写不进去事务提交时崩溃实例重启后进入 recovery 状态。此时对外表现就是写什么都报只读错误而且pg_is_in_recovery()可能返回true很容易被误判成普通的只读故障。判断方法不复杂先df -h看数据目录和 WAL 目录的剩余空间再看 PostgreSQL 日志里有没有could not write to file或No space left on device这类关键字。遇到这种状况第一优先级是清理磁盘空间而不是去折腾只读参数。有同行跟我说他们解锁只读搞了半小时没效果最后发现是归档目录被 WAL 堆满了清理完瞬间恢复。这个案例我一直记着因为太典型了。1.3 主动锁定运维为什么要故意把数据库变成只读如果说被动只读是事故那主动锁定就是有计划的手术。实际生产里主动把数据库实例设置成只读通常出于三个目的备份窗口保护做物理备份或某些特殊逻辑备份时锁住写入可以保证数据文件的一致性避免备份快照读到一半的数据。主备切换静默切换前把主库锁成只读等备库追平 WAL 延迟后再提升确保切换过程不丢数据、不产生脑裂。应用容错演练测试应用在数据库不可写时的降级表现提前暴露代码里那些不处理异常的隐患。三种场景对锁定粒度和操作流程的要求不一样。只锁一个库还是锁整个实例取决于你希望哪个范围的写入被拒绝接下来的参数机制和实操细节会逐一说明。2. transaction_read_only 参数体系只读锁定背后的真正机制2.1 核心参数与优先级规则PostgreSQL 处理只读事务的核心参数有两个default_transaction_read_only决定新开的事务默认是不是只读transaction_read_only是当前事务实际的只读状态。还有一个等价语法SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY本质上也是在修改会话里的默认值。default_transaction_read_only支持在多个层级设置优先级从高到低是会话内 SET包括客户端启动参数角色 数据库的双重限定ALTER ROLE ... IN DATABASE数据库级ALTER DATABASE角色级ALTER ROLE实例级postgresql.conf 或ALTER SYSTEM编译期内置默认值。这个优先级规则是排错的关键。比如你明明改好了 postgresql.conf 里的参数reload 之后新连接仍然只读那就要怀疑是不是某个数据库或角色上单独设了值。用pg_settings视图可以看得一清二楚SELECT name, setting, source, sourcefile, sourceline FROM pg_settings WHERE name default_transaction_read_only;source字段会直接告诉你当前值来自哪里default、configuration file、database、user还是session。我见过不少参数改不动的现场最后都是在这个视图里找到了真相。顺便说一句和 Oracle 的ALTER TABLESPACE ... READ ONLY这类物理级只读不同PostgreSQL 的只读锁定是事务级的逻辑控制。它不改变数据文件的物理状态只是从事务开始就拒绝写操作。好处是切换灵活代价是它更依赖连接和事务的行为——某个连接如果绕过了参数约束锁定就会出现口子。2.2 会话级与事务级只读的生效逻辑SET default_transaction_read_only on;只影响当前会话而且只对之后开启的新事务生效。这条命令在事务块内外都能执行但如果你当前正处在一个事务里执行它不会改变这个已存在事务的读写属性。SET TRANSACTION READ ONLY;则是直接给当前事务打上只读标记。它有两个硬性限制必须在事务块内执行而且必须在任何查询语句之前执行。如果先跑了一条 SELECT 再执行会直接报错ERROR: transaction read-write mode must be set before any query这个设计其实很合理——事务开始时要确定快照行为和锁的获取策略中途切模式会让隔离级别与一致性语义变得不可控。理解了这一点你就明白为什么实例级锁定时已经在跑的长事务不会被中断它们会按原模式继续跑完新事务才进入只读状态。2.3 状态检查的标准动作我在生产环境排查只读问题固定跑一组命令顺序不能乱-- 第一步判断节点角色 SELECT pg_is_in_recovery(); -- 第二步查看默认事务模式及其来源 SELECT name, setting, source FROM pg_settings WHERE name default_transaction_read_only; -- 第三步当前会话的事务只读状态 SHOW transaction_read_only; -- 第四步事务块内的写入冒烟测试 BEGIN; CREATE TABLE _ro_test(id int); ROLLBACK;第四步是关键。在只读模式下CREATE TABLE会立刻收到ERROR: cannot execute CREATE TABLE in a read-only transaction在可写模式下这条命令能正常执行随后被 ROLLBACK 清掉不会在系统里留下任何残留。相比直接对着业务表做 INSERT这个测试方式在生产库上零风险。3. 实例只读锁定的完整操作手册3.1 实例级锁定ALTER SYSTEM 与平滑 reload把整个实例锁成只读标准做法是修改default_transaction_read_only参数。我推荐用ALTER SYSTEM而不是手改 postgresql.conf原因有两个一是它会把设置写入postgresql.auto.conf不需要手工管理文件路径二是语法统一回滚用RESET即可不容易出错。ALTER SYSTEM SET default_transaction_read_only on; SELECT pg_reload_conf();pg_reload_conf()触发的 reload 是平滑的不会中断正在执行的查询已打开的事务继续按原模式跑完。对于大多数连接来说reload 之后新开启的事务就会使用新的默认值。但这里有一个必须强调的点reload 只能影响没有在会话里显式覆盖过该参数的连接。如果应用在连接初始化时执行过SET default_transaction_read_only off那 reload 对它无效这个连接仍然可写。这个问题在后面的踩坑实录里我会专门展开。提示只读锁定只拦新事务已经在跑的长事务不会被强制中断。如果业务需要立即停写必须配合 pg_terminate_backend 处理存量会话。验证锁定是否生效建议用带事务的写入测试BEGIN; INSERT INTO app_user(id, name) VALUES (999999, __test__); -- 期望输出ERROR: cannot execute INSERT in a read-only transaction ROLLBACK;如果确实报了只读错误说明新的写事务已经被挡在门外了。3.2 数据库级与角色级只读按需收紧范围并不是所有场景都需要锁整个实例。有时候只想保护某一个业务库或者只限制某个应用账号的写权限PostgreSQL 支持更细粒度的设置-- 指定数据库的所有新连接只读 ALTER DATABASE business_db SET default_transaction_read_only on; -- 指定角色的所有新连接只读 ALTER ROLE app_user SET default_transaction_read_only on; -- 角色仅连接指定数据库时才只读 ALTER ROLE app_user IN DATABASE business_db SET default_transaction_read_only on;这里有个高频误区ALTER DATABASE ... SET并不是立即生效的。它只对修改之后新建的连接有效已经存在的连接不受影响。所以在生产环境里调整完参数后往往还要配合清理存量连接否则会出现一部分连接只读、一部分可写的诡异状态排错时很容易被表象带偏。3.3 存量连接处理让锁定真正闭环处理存量连接是整个操作流程里最容易被忽略的环节。如果参数修改之前已经有长连接存在这些连接不会自动断开也不会自动切换新的默认值。我的标准处理流程分三步第一步先看当前有哪些活跃事务评估杀连接的代价SELECT pid, application_name, state, now() - xact_start AS transaction_age FROM pg_stat_activity WHERE xact_start IS NOT NULL AND pid pg_backend_pid() ORDER BY transaction_age DESC;第二步确认这些事务可以安全中断后终止所有客户端连接SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE backend_type client backend AND pid pg_backend_pid();第三步让应用连接池自动重建新连接。新连接会从数据库级或实例级配置中读取只读参数锁定才算真正闭环。这里要多说一句pg_terminate_backend会强制回滚目标连接上的事务对应用来说相当于连接被服务端断开。如果是白天业务高峰期做这个操作要提前跟业务方确认应用的连接池能否自动重连、重连后能否正确恢复。我一般建议把存量连接处理放到维护窗口内的低峰时段宁可多等几分钟也不要贸然杀连接。3.4 解锁比锁定更需要谨慎解锁就是反向操作。全局级别的锁定用以下命令解除ALTER SYSTEM RESET default_transaction_read_only; SELECT pg_reload_conf();如果是数据库级或角色级设置的对应使用ALTER DATABASE business_db RESET default_transaction_read_only; ALTER ROLE app_user RESET default_transaction_read_only;RESET和SET off的区别值得单独说明RESET是删除该层级的自定义设置让参数回落到上级设置或内置默认值SET off则是显式写入一个关闭值。绝大多数场景下应该用RESET因为如果你的角色同时还依赖数据库级的on设置SET off反而会覆盖那个on制造一种明明锁了但为什么没锁住的混乱。注意RESET 和 SET off 语义不同。清除锁定建议使用 RESET避免掩盖上级层级可能存在的 on 设置。解锁后同样要回归验证SHOW default_transaction_read_only; -- 期望 off BEGIN; SELECT 1; COMMIT;顺带分享我的习惯实例级锁定时把修改参数、reload、杀连接、验证四个步骤写进自动化脚本每一步输出一行确认信息避免手工操作遗漏。脚本里把pg_is_in_recovery()的状态也一并打印出来切换场景下特别有用。4. 实战场景什么时候最需要只读锁定4.1 备份窗口锁住写入换来一致性快照做物理备份时虽然 PostgreSQL 有 WAL 归档机制能保证恢复一致性但如果备份期间写入量很大恢复时需要回放大量 WAL恢复时间会明显拉长。某些特殊场景下比如存储层快照配合每日备份提前把实例锁成只读可以让快照在任何时间点都保持一致性省去复杂的 WAL 拼接验证。我实际接触过一个客户每晚 24 点整做存储快照快照前 5 分钟通过脚本把数据库锁成只读快照完成确认无误后再解锁。这套方案已经稳定运行了两年。关键点在于只读锁定的时间窗口很短对业务影响极小但备份集的一致性验证成本几乎降为零。比起事后花半小时研究 WAL 是否连续提前锁 5 分钟显然划算得多。4.2 主备切换静默写入后的安全切换主备切换switchover是只读锁定最有价值的应用场景。完整流程我整理如下业务维护窗口开始通知应用停止写入在主库执行ALTER SYSTEM SET default_transaction_read_only on并 reload观察备库回放延迟通过pg_stat_replication确认replay_lsn追上了主库的最新flush_lsn在备库执行SELECT pg_promote();提升为新主库切换访问入口VIP 或连接配置指向新主库旧主库降级为备库并解除只读参数。这套流程里只读锁定起的是兜底闸门作用。即便有某个服务忘了停机数据库层面也会拒绝它的写入避免那些漏网之鱼在切换瞬间产生无法同步的数据。我在多个项目里验证过这层保险平时看不见但真到切换那一刻它就是防止数据分叉的最后一道防线。4.3 故障演练用只读模式做廉价的故障注入还有一个经常被忽略的场景测试应用的容错能力。把数据库切成只读让应用跑一遍核心链路观察它在写入失败时是优雅降级还是直接崩溃。这比用网络故障模拟器的成本低得多却同样能暴露大量真实问题。我在一个金融项目里组织过这样的演练。有个报表模块在写入失败时抛了未捕获异常导致整个查询线程挂掉异步任务又没有重试限制失败后不断重新入队消息队列被越堆越满。这些问题在没有故障注入的日常环境里几乎不可能暴露。演练结束后开发组对所有写路径都加了异常处理和熔断逻辑后面再遇到数据库维护应用侧的表现就从容多了。5. 踩过的坑与经验教训5.1 连接池残留会话让锁定形同虚设我第一次做实例只读锁定时栽过一个不小的跟头。当时改了 postgresql.confreload 之后用新开的 psql 验证确实只读了但业务系统仍然在持续写数据。排查了很久最终发现问题出在连接池上。应用通过中间件维护了一批长连接这些连接是在参数修改之前建立的已经在会话内确定了读写状态。单靠 reload 配置并不会重新连接或重置会话状态应用通过这批老连接继续执行写入锁定形同虚设。从那以后我总结出一条铁律改只读参数之后必须主动处理存量连接顺序很重要——先评估长事务再终止连接最后观察连接池的重建情况。不要反过来否则老连接的问题会一直悬在那里。5.2 只读模式下依然漏网的操作只读锁定拦的是本地表数据的变更但并不是所有副作用都被拦截。以下几个操作在只读事务里仍然可以执行容易让人产生疑惑会话变量和参数设置SET、SHOW、set_config()正常运行会话级咨询锁pg_advisory_lock()系列可以正常获取因为咨询锁不改变表数据通过dblink扩展连接到的远端库本地事务只读并不会传递到远端连接远端数据照样可以写序列的currval()在会话内曾经调用过nextval()之后可以正常返回但nextval()本身在只读事务里会报错。理解这个边界很重要。如果你的业务里有函数通过dblink把数据同步到另一个库只读锁定并不会阻止它。需要同步锁定目标库或者在架构设计时把这种跨库写操作的开关独立出来单独由运维控制。5.3 一个把参数设反了的事故复盘最后分享一个真实的误操作。有次要安排主备切换原计划是主库在切换前锁只读。结果因为同时在多个终端操作我不小心在备库上执行了ALTER SYSTEM SET default_transaction_read_only on。备库本来天然只读再加这个参数毫无感觉问题出在切换完成后备库被提升为新主库但那个参数还挂在postgresql.auto.conf里导致切换后新主库一直处于只读状态应用写不进去业务直接停摆。当时的排查过程值得复盘。我第一反应是看SHOW default_transaction_read_only结果显示on但潜意识里觉得刚才不是才提升为主库吗怎么会是 on。后来通过pg_settings视图看到source configuration file再打开postgresql.auto.conf才发现那行设置。删掉并 reload 后恢复正常。这个事故给我三个教训第一跨实例操作前必须用\conninfo或SELECT inet_server_addr();确认连接目标第二提升主库后的检查清单里必须包含确认 default_transaction_read_only 为 off这一项第三从库上没必要设置的参数就不要设置一个多余的参数会在未来某个时刻咬你一口。最后整理一个快速定位表覆盖我在生产环境见过的高频场景症状最可能的原因排查动作所有写入报 read-only transaction连到了从库或实例级参数被设为 onpg_is_in_recovery()、查pg_settings.source部分连接可写、部分不可写连接池残留老会话或角色/数据库级参数查pg_stat_activity查角色和数据库设置实例重启后写入全部失败磁盘写满或数据目录异常df -h、日志关键词主备切换后新主库持续只读从库时代遗留的全局参数检查postgresql.auto.conf写到这里最想分享的体会是只读锁定本身并不神秘它只是一个开关真正考验人的是它和连接池、从库、磁盘状态、参数层级这些因素交织在一起时的判断力。我现在的习惯是所有涉及只读锁定的操作都走同一套脚本先检查节点角色和参数来源再执行锁定验证写入失败然后处理存量连接最后回归验证。每一步都有输出。如果有一天你再被数据库写不进去了的电话叫醒试着先问对方一句pg_is_in_recovery()的结果——很多问题从这一问开始就有了答案。