企业官网建设流程全解析

1. 项目概述

1.1 场景拆解：什么时候需要把PostgreSQL实例锁成只读

直接说结论：PostgreSQL数据库实例只读锁定，就是把整个数据库集群或某个数据库切成“只能查、不能改”的状态。你可能因为主从切换、数据迁移、审计需求、误删防护而要做这件事，也可能是在准备把一套库迁移上云，或者交接给另一个团队维护。不管是哪种场景，你需要的不是一个“把表锁住”的工具，而是一整套“怎么锁、锁多深、怎么优雅地解锁”的操作方案。

我在生产环境里踩过不少坑之后，最深的体会是：只读锁定看似简单——不就是改个配置嘛——但实际上它牵扯到会话级参数、事务生命周期、连接池行为、复制槽状态四个层面。哪一个没考虑到位，都会在切换回读写的时候暴雷。

适合看这篇文章的人：被业务方要求“必须让开发环境只读”的DBA、正在做数据库迁移的运维同学、写脚本批量处理多个PG实例的工程师，以及任何想搞懂“为什么我设了只读还是能写进去”的PG使用者。

1.2 核心需求与常见误区

只读锁定的核心需求其实就一句话：阻止任何非预期的数据变更，但保证查询行为完全正常。听起来简单，但这句话拆开看就有几个容易忽略的点：

• 这里的“数据变更”包括INSERT、UPDATE、DELETE，也包括DDL（CREATE TABLE、ALTER TABLE等），还包括序列的nextval操作、临时表写入和函数内的写操作。
• 这里的“查询正常”意味着索引扫描、并行查询、只读事务都不应该受影响。
• 解锁必须是可预期、可回滚的——你不能把库锁成一个需要重启才能恢复的死状态。

我见过好几起事故，都是运维同学执行了ALTER SYSTEM SET default_transaction_read_only = on;之后，过几天忘了，等到要写数据时才发现连CREATE TEMP TABLE都报错，然后手忙脚乱地在生产库上排查。这种时候最耽误时间的不是改回配置，而是搞清楚现场有哪些长事务、有哪些连接池会话还持有旧配置。

一个基本认知：PostgreSQL的只读控制不是“一把全局大锁”，而是一套由事务级参数、数据库级配置、表空间级权限组合出来的防护网。你要做的，是根据场景选择正确的组合，而不是找到某个“万能开关”。

2. 方案选型：四种只读手段的取舍

2.1 方案全景对比

PostgreSQL里实现只读锁定常见的手段有四种，我直接给一张对比表，看完你就知道它们各自的适用范围了。

这里最容易犯的错误是：把“默认只读”当成了全局强制只读。它的缺陷在于——它只对新事务生效。如果你有一堆连接池里的长连接处于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 执行锁定的标准操作序列

下面这套流程我在多个项目里验证过，可以称为“标准操作序列”。它可以保证：从你执行第一个命令开始，到最终确认只读生效，中间不存在任何可写的窗口（除了一种情况，后面讲）。

1. 设置实例级默认只读：

ALTER SYSTEM SET default_transaction_read_only = on; SELECT pg_reload_conf();

2. 等一两秒，确认参数已经加载：

SHOW default_transaction_read_only;

我要求看到的结果是on。如果你看到的是off，不要往下走，先排查为什么pg_reload_conf()没有生效。

3. 新建一个测试连接，验证新事务确实只读：

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

4. 再验证老连接：回去找一个锁定前就建立的psql会话，尝试写入。这个时候你大概率发现它能写成功——这是预期的，因为旧会话的事务参数不会自动变。这就触发了一个决策点：是终止这些老会话，还是等它们自然结束？

我的建议是：如果是限时维护（比如半小时内），把应用流量切换走之后，直接终止这些连接，利索。

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND usename NOT IN ('postgres', 'replicator'); -- 根据自己的环境调整

5. 最后做一个覆盖性验证：用一个新连接连续跑几条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 只读期间的监控指标

锁定只是开始，不是结束。只读期间你要盯几类指标，才能判断锁定是否被破坏、是否有异常行为：

如果只读期间你想要更主动的告警，可以部署一个定时探测脚本，每分钟尝试向一张探针表插入数据并捕获错误。这张探针表本身不用真实存在——用一条必然失败的SQL即可：

INSERT INTO pg_probe_should_not_exist VALUES (1);

抓到错误码25006（read_only_sql_transaction）就说明锁定还在生效。这个定时任务可以跑在应用服务器上，通过psql执行，不要占用数据库侧的资源。

5. 常见问题与故障排查实录

5.1 为什么设置了default_transaction_read_only还是能写

这是我被问过最多的问题，没有之一。原因有四种可能，按出现概率排序：

1. 你设置的是当前会话的参数，而不是实例级参数。SET default_transaction_read_only = on只影响当前会话，其他人照常。
2. 新参数没有加载。ALTER SYSTEM之后必须pg_reload_conf()或者重启，懒一次就会出问题。
3. 连接池里旧连接还没重建，这些连接持有旧的配置。
4. 有人显式执行了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 常见问题速查表

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);" 2>&1 || 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实例，建议给每套实例的只读/解锁操作都写进变更记录，包括执行时间、影响会话数、异常事件。等到某次大版本升级或者云迁移时，你会感谢自己这些记录的。

最后，不管用哪种方式锁定，一定先在测试环境完整演练一遍，尤其是解锁——测试环境里把锁定、验证、解锁、连接重建走通，生产上才不会手忙脚乱。这个习惯救过我太多次了。