PostgreSQL实例只读锁定：从事务属性到权限控制的完整方案

你有没有遇到过这种场景临到月末财务要出报表业务系统还在不停往里写数据DBA 接到通知说“今晚把数据库锁成只读”或者项目要整体迁移甲方要求源库在指定时间后不允许有任何数据变更。在 PostgreSQL 里把整个数据库实例做成只读并不是一条命令这么简单它背后牵扯到事务语义、权限体系、连接管理和复制机制。这篇文章不打算写成枯燥的官方文档而是结合我这些年实际处理过的数据库项目实例围绕“PostgreSQL 数据库实例只读锁定”这个需求把从方案选型到操作踩坑的完整过程讲清楚。不管你是刚照着 PostgreSQL 安装教程把库装起来的新手还是正在做 SAP 系统迁移、Oracle 转 PostgreSQL 的运维同学这部分知识点都会用得上。1. 先把“只读锁定”这四个字拆明白1.1 数据库实例到底指什么很多人一说“实例”脑子里先冒出 SAP 系统里那一堆名词message 实例、PAS 实例、AAS 实例。这里要先划一道线SAP 语境下的 message 实例、PAS、AAS本质上是应用服务器层的进程实例负责调度对话、队列、更新等业务作业而数据库实例在 PostgreSQL 语境下指的是一个正在运行的 postgres 服务进程加上它打开的那套数据目录。一台物理机上可以同时跑多个 PostgreSQL 实例最常见的是不同端口、不同数据目录通过pg_ctl -D或 systemd 单元分开管理。所以“数据库实例只读锁定”这句话真正的落点是整套正在运行的数据库服务体系而不是某一张表、某一个 schema。你锁表的锁锁行的锁在 PostgreSQL 里都有专门语法但那种锁和“实例只读”是完全不同维度的事。如果领导跟你说“把数据库锁上别让任何人改数据”你得先确认他到底是要锁整库、锁某个业务库还是锁某个账号否则后面所有操作都会跑偏。1.2 只读锁定不是表级锁也不是行级锁PostgreSQL 平时聊得最多的锁是表锁、行锁以及 MVCC 机制下的并发控制。LOCK TABLE、SELECT FOR UPDATE这些属于并发控制锁解决的是“多个事务同时操作同一份数据时怎么排队、怎么避免互相踩踏”。而只读锁定解决的是“这个实例是不是允许发生任何写操作”的问题。真正把实例锁成只读后INSERT、UPDATE、DELETE、MERGE、COPY 写入、DDL 建表改表统统会被拒绝。它不是去抢什么锁而是从事务属性或权限边界上直接关掉写通道。理解这一点很重要因为很多从 Oracle 过来的人会习惯性地去找“改库状态”的命令但 PostgreSQL 的哲学是“事务本身是只读的数据库就只读”方向上完全不同。1.3 Oracle 用户最容易蒙圈的差异点聊到 Oracle 和 PostgreSQL 语法区别这一块算是最典型的代表。Oracle 里有ALTER DATABASE OPEN READ ONLY可以把整个数据库切到只读挂载状态也有ALTER TABLESPACE ... READ ONLY这种东西。PostgreSQL 没有直接对应的单条命令。在 PostgreSQL让实例只读通常是一组机制的组合核心是default_transaction_read_only这个 GUC 参数辅助手段包括权限回收、连接控制、物理从库等。Oracle 改的是“数据库状态”PG 改的是“事务读写属性”和“权限边界”。你如果还按 Oracle 的老思路去操作很容易卡在“找不到这个语法”这一步。后面我会把这些做法拆开讲并给出可以直接抄的实操流程。2. 让实例只读的几种主流方式与取舍2.1 会话级只读速度快但不持久最轻量的一种做法是在单个会话里执行SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;这等价于把当前会话的default_transaction_read_only设为 on。执行之后这个会话新开启的事务都不能写数据你甚至能在事务中间用SET TRANSACTION READ ONLY把当前事务直接切成只读。但会话级只读有致命短板它只管当前这一个连接换个连接就失效数据库重启也失效。所以我一般只在临时排查、或者给某个测试账号做心理安慰时用。生产环境如果说“今晚整个实例必须只读”靠这一句是远远不够的你必须把级别拉到实例级。2.2 实例级全局只读最常用的一套组合拳真正要做实例级只读核心命令是ALTER SYSTEM SET default_transaction_read_only on; SELECT pg_reload_conf();ALTER SYSTEM会把配置写进postgresql.auto.confpg_reload_conf()触发所有进程重新加载配置不需要重启数据库。执行完后新建连接里的新事务默认都是只读的。如果你的业务允许按库或按账号隔离还可以用更细的粒度ALTER DATABASE mydb SET default_transaction_read_only on; ALTER ROLE readonly_user SET default_transaction_read_only on;ALTER DATABASE适合只锁某个业务库ALTER ROLE适合给只读账号做固定属性。生产环境我常用的策略是先用ALTER SYSTEM做全局兜底再配合账号层权限限制双保险。注意ALTER SYSTEM不等于立刻把已经存在的连接全部切成只读。它对“新建立的连接”最有效老连接、连接池里的残留连接不一定马上感受到变化。后面我会讲怎么处理。2.3 从权限层面禁止写入除了事务属性权限回收也是一条路。比如创建一个专门账号只给 SELECT 权限CREATE ROLE app_readonly LOGIN PASSWORD xxx; GRANT CONNECT ON DATABASE mydb TO app_readonly; GRANT USAGE ON SCHEMA public TO app_readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;这种方案适合给“永远只读”的分析账号、报表账号用但它有几个问题第一superuser 和对象 owner 可以无视大多数权限限制第二如果业务账号本身已经建好了靠 GRANT/REVOKE 一条条往里补很容易漏后面某张新表建出来还带着默认权限应用照样能写。权限方案更适合长期设计的只读账号不适合临时把生产实例锁死。还有人会想用 event trigger 拦截 DDL比如在ddl_command_start里抛异常。这个能拦 CREATE TABLE、ALTER TABLE 这类结构变更但拦不住 INSERT、UPDATE、DELETE所以它顶多算补充手段不能当主力方案。2.4 standby 和文件系统只读到底能不能用物理从库standby在热备模式下也是一个“只读实例”你可以在备库上跑 SELECT、跑报表但不能写。这种只读是复制机制自带的属性不是把主库锁住的手段。如果你是为了“读扩展”或“报表分离”搭一个 hot standby 非常合适但你是想把生产主库临时锁定做迁移那 standby 解决不了主库问题。文件系统级只读就更极端的。你可以把整个数据目录所在文件系统 remount 成只读PostgreSQL 确实也没法往里写 WAL 和临时文件但这种做法风险极高数据库进程如果正在写数据会出现一堆 IO 错误搞不好直接把实例搞崩。我见过有人这么“锁库”结果恢复的时候费了大力气。所以我的态度很明确正规维护窗口里不要用文件系统只读优先用参数和连接控制。下表把这几种方式的核心差异汇总一下方便你选型方案生效级别对已有连接推荐场景注意点会话级 SET当前会话当前会话后续事务临时自测、单个会话调试重启失效、换连接失效ALTER SYSTEM实例级新连接有效老连接需重连/确认生产实例锁定配合断开连接更稳妥ALTER DATABASE/ROLE库/账号级按配置来源决定只锁业务库、报表库可能和已有连接配置冲突权限回收 GRANT/REVOKE账号级对新命令生效长期只读账号superuser/owner 可绕过standby 备库实例级备库自带只读报表分离、灾备不能被当主库锁定的手段文件系统只读实例级强制但危险不建议可能引发 IO 异常3. 完整实操把生产 PG 实例锁成只读的安全操作流程3.1 锁定前必须做的检查清单“锁库”听起来是十秒钟的事但摔过跟头的人都知道真正的功夫全在锁之前的检查。结合我处理过的生产故障下面这几项一定别跳确认维护窗口有没有定时任务、批处理、ETL 在这个时间段跑。摸清连接来源把pg_stat_activity拉出来看看有哪些应用、哪些 IP、哪些连接池在连。检查复制状态如果有从库主库锁定只读本身不影响复制但要注意备库延迟和主备切换策略。确认备份任务pg_basebackup、WAL 归档、第三方备份工具都要跟窗口错开。找好“逃生门”确保你自己有一条管理员连接通道别把自己关在门外。检查长事务和活跃写入可以用这条 SQLSELECT pid, usename, application_name, state, query, xact_start, now() - xact_start AS duration FROM pg_stat_activity WHERE state idle AND pid pg_backend_pid() ORDER BY duration DESC;如果发现某个事务已经跑了几个小时先别急着锁要跟业务确认这个长事务能不能打断。只读参数对“已经开启且正在写入的事务”有时候也管不到把长事务排在前面处理是保住数据一致性最关键的一步。3.2 绑定实例配置并重载参数确认没问题后执行ALTER SYSTEM SET default_transaction_read_only on; SELECT pg_reload_conf();然后验证SHOW default_transaction_read_only;正常情况下应该看到on。再开一个新连接执行一个最简单的写入CREATE TABLE test_no_write(id int); -- ERROR: cannot execute CREATE TABLE in a read-only transaction如果只想锁某一个业务库用ALTER DATABASE business_db SET default_transaction_read_only on;如果要恢复同样是ALTER SYSTEM RESET和pg_reload_conf()ALTER SYSTEM RESET default_transaction_read_only; SELECT pg_reload_conf(); SHOW default_transaction_read_only;这里有个细节如果当初你用了ALTER DATABASE或ALTER ROLE做了库级、账号级设置恢复时也要对应地ALTER DATABASE ... RESET、ALTER ROLE ... RESET否则全局虽然放开了某个库某账号还是只读很容易留下“幽灵只读”的坑。3.3 断掉现有连接别让老连接拖后腿如前面说的ALTER SYSTEM对已有连接不一定立刻生效。稳妥的做法分两步先限制新连接进来再把老写连接切掉。限制新连接最直接是临时改pg_hba.conf把应用账号的连接规则改成reject然后pg_ctl reload或执行SELECT pg_reload_conf();。这一步要特别小心建议先保证自己有一条独立的本地管理连接否则把自己拒之门外就尴尬了。切断老连接使用SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname business_db AND pid pg_backend_pid();pg_terminate_backend会强制终止连接正在执行的事务也会被取消。生产环境建议逐步批量清理别一次性把所有连接都断掉尤其是业务高峰期。维护窗口内做这件事配合应用团队重启连接池效果最好。如果你用了 pgbouncer、JDBC 连接池这类中间件光在数据库层断连接还不够应用连接池里可能还有空闲连接会被复用。最理想的做法是让应用团队在锁库的同时把连接池清掉或重连一次这样新连接就会拿到全局只读属性行为才是确定性的。3.4 验证“锁”到底锁没锁死很多新手以为执行完SHOW default_transaction_read_only就万事大吉其实不然。真正到位的验证应该是尽量模拟业务写入-- DML 验证 BEGIN; INSERT INTO t1 VALUES (1); -- ERROR: cannot execute INSERT in a read-only transaction -- DDL 验证 CREATE TABLE tmp_test(id int); -- ERROR: cannot execute CREATE TABLE in a read-only transaction -- 数据装载验证 \copy t1 from /tmp/data.csv with csv -- ERROR: cannot execute COPY FROM in a read-only transaction理论上只读模式下你还能执行 SELECT、SET、SHOW、EXPLAIN这是符合预期的。如果某个应用账号还能写优先检查它是不是 superuser、是不是表 owner或者它的连接是不是没有重新建立。搞清楚这三个原因基本能覆盖 90% 的“没锁住”现场。3.5 恢复流程要按反方向走锁库容易解锁的时候反而容易乱。我的建议是把恢复流程当成一次正式的发布操作至少包含以下步骤业务侧确认所有写入任务已停止不需要再保持只读。恢复全局参数ALTER SYSTEM RESET default_transaction_read_only; SELECT pg_reload_conf();恢复库级/角色级参数对当初设置过的数据库和角色执行对应RESET。恢复pg_hba.conf里临时加的reject规则并 reload。验证写入开新连接执行INSERT或CREATE TABLE确认能成功。通知应用团队恢复连接池和业务流量。这里特别提醒一句如果有从库在主库只读期间一直在提供服务解锁主库前要跟 DBA 确认当前主备关系。别在主备状态没确认的情况下直接解只读场景一复杂很容易把复制搞乱。4. 常见坑与问题排查实录4.1 superuser 和对象 owner 绕过只读这是最容易翻车的点。很多人以为把default_transaction_read_only打开后全天下都不能写了。实际上default_transaction_read_only对 superuser 也是生效的因为它属于事务属性但 superuser 可以在自己的会话里执行SET default_transaction_read_only off;然后继续写。换句话说这道锁防的是普通账号防不住有心的管理员。如果你要做的是强合规场景得配合更严格的手段比如限制账号权限、控制管理员访问、或者在连接池层面对 superuser 连接做隔离。别天真地以为一个参数就能锁住全世界。对象 owner 的坑也类似。默认建表的人对表有全部权限当初如果权限体系不规范很多应用账号本身就是 owner权限回收不一定覆盖到位。所以长期方案一定要把“应用账号不应该是表 owner”这个原则立起来。4.2 为什么 ALTER SYSTEM 之后还是能写遇到“我明明设了只读为什么还能写”的问题从这三条查起查当前连接的配置来源SELECT current_setting(default_transaction_read_only);如果显示 on那就是新建连接问题如果显示 off看看是不是有ALTER DATABASE、ALTER ROLE、会话级SET覆盖了全局值。查连接池应用连的到底是数据库直连还是 pgbouncer 代理。连接池里的连接如果不重连可能一直拿着旧配置。查执行用户用业务账号去测别拿 superuser 账号测否则结果没有任何参考价值。另外一个容易忽略的点ALTER DATABASE设置只在“数据库默认参数”这一层生效它会被会话级SET覆盖。如果应用在建立连接后自己执行了SET default_transaction_read_only off那库级配置也没用。遇到这种应用要联合开发改代码而不是单方面找数据库的问题。4.3 连接池带来的“幽灵写入”我处理过一起非常抓狂的事故锁库窗口执行了ALTER SYSTEM SET default_transaction_read_only on也 reload 了连 pg_stat_activity 都查了一遍没看出问题结果业务还是报出某条 UPDATE 执行成功了。后来顺着日志查发现写入来源是一条应用连接池里长期复用的连接它是在锁库之前建立的事务边界被应用框架控制得乱七八糟锁库后它复用了旧的事务上下文绕过了检查。从那以后我的操作习惯就改了锁库前先和应用团队对齐连接池策略要么全部重连要么干脆停应用流量。数据库侧能做的只是“新建连接强制只读”你把连接清掉或让连接池重新初始化这一步无论如何省不了。4.4 只读和复制、归档任务打架怎么办很多环境不是单库而是有流复制从库和 WAL 归档。主库只读后业务写入停了WAL 增长会变慢从库压力也会下降这本身没毛病。但要注意几点备份任务如果备份工具在只读窗口里尝试执行pg_start_backup()可能因为写入备份标签等操作失败。建议提前把备份窗口调开。主备切换只读锁定通常只针对主库如果此时发生 failover原备库会被提升为主库并变为可写。原主库的只读状态在它降级后可能会随着配置被重新加载而解除具体情况要看你当初是用哪一层做的锁定。长事务导致的 vacuum 阻塞只读窗口里如果还有长事务挂着会影响 autovacuum 的推进锁库时间特别长时要注意 vacuum 和事务 ID 回卷风险。这里的原则是只读锁定不是“把数据库冻住”它只是不让业务写数据后台的维护逻辑、复制协议、备份周期仍然需要单独评估。4.5 和 SAP 系统混在一起时最容易踩的坑前面说过SAP 系统里的 message 实例、PAS 实例、AAS 实例都属于应用层数据库实例是独立一层。如果你把 PostgreSQL 数据库实例设成只读SAP 应用实例还开着前端用户会看到一堆数据库连接错误、写入失败反过来你只把 SAP 应用停了数据库实例继续可写数据层面还是有可能被外部工具改动。这两件事必须当成两次变更来对待。我在做某个 SAP 外围系统的数据迁移时就吃过这个亏数据库团队把 PG 库锁成只读然后等应用团队停 batch job结果两边没对齐batch job 还在继续重试数据库日志刷屏最后不得不加班复盘。后来的流程固定成三个步骤先由 SAP Basis 团队停相关作业和对话实例再由应用团队确认没有活动写入最后数据库团队才上只读锁定。顺序反了谁都难受。4.6 从 Oracle 转过来的操作习惯要改一改Oracle 和 PostgreSQL 语法区别如果你没仔细研究过很容易在只读锁定这个场景里出问题。举例来说Oracle 里可以用ALTER SYSTEM ENABLE RESTRICTED SESSION来阻止新会话PostgreSQL 没有这个命令你得用pg_hba.conf加拒连规则或者用pg_terminate_backend断连接Oracle 里可以ALTER TABLESPACE users READ ONLYPostgreSQL 没有表空间级只读开关要做只能往实例参数、库参数或权限方向走。我把这两个常用动作列成对照表方便大家快速抓重点操作意图Oracle 常用命令PostgreSQL 实践整库只读ALTER DATABASE OPEN READ ONLY;ALTER SYSTEM SET default_transaction_read_onlyon; SELECT pg_reload_conf();阻止新会话ALTER SYSTEM ENABLE RESTRICTED SESSION;临时改pg_hba.conf reload断开已有会话查 v$session 后 kill查 pg_stat_activity 后pg_terminate_backend(pid)表空间只读ALTER TABLESPACE tbs READ ONLY;没有直接开关考虑库级/账号级只读明白了这些差异你在做数据库迁移或双轨运维时就能少走弯路。不是说 Oracle 的方法好或不好而是换了个数据库防线的位置和操作入口都要换一套思维。5. 几个我踩过之后不想你再踩的建议把 PostgreSQL 数据库实例锁成只读听起来是个很小的操作实际却是个系统工程。我个人的体会是不管用什么参数都要先问自己三个问题——这个锁是针对谁生效的已有连接会不会漏恢复流程有没有人负责三个问题答不上来就别急着执行。再分享一个小技巧执行只读锁定后不要只查SHOW default_transaction_read_only要主动用一个普通业务账号开新连接真实执行一条 INSERT 试试。命令报错才叫锁住命令不报错说明一定还有某个层级漏了。这个习惯帮我挡掉过好几次“假成功”的尴尬成本很低收益很高。如果是多人协作的团队建议把锁库和解锁做成带确认环节的操作单每次变更都记录执行时间、执行人、恢复时间。听起来有点重但数据库实例只读锁定往往是迁移、割接、合规审计的关键节点留好记录后续出问题你能少掉很多头发。