PostgreSQL 的 “cannot execute CREATE TABLE in a read-only transaction” 错误正导致许多团队的迁移任务失败，导致模式（schema）应用了一半，且迁移历史表被锁定。这种失败通常出现在写操作落在从库（replica）而非主库（primary）时，并会使任何依赖模式变更的工具（如 Flyway、Liquibase、Django ORM 等）陷入停滞。

为什么会出现该错误

当事务被标记为只读时，PostgreSQL 会禁用所有写操作、模式变更和序列更新。迁移进入这种状态的最常见原因包括：

  • 连接池工具（例如 PgBouncer）无意中将迁移连接发送到了只读从库。
  • 角色（Roles） 默认将 default_transaction_read_only 参数设置为 on。
  • 云端终端节点 暴露了独立的读写 URL；使用读取端 URL（如 AWS RDS 或 Aurora 的常见做法）会将写操作路由到从库。

当满足上述任一条件时，迁移过程可能会尝试创建表、添加列或更新序列，但最终会撞上限制，导致数据库处于部分迁移的状态。

事务内的立即修复方案

如果你已经遇到了该错误，可以在不影响会话其余部分的情况下，为当前事务覆盖只读标志——这在连接池重用同一会话处理其他工作时至关重要。

BEGIN;
SET LOCAL default_transaction_read_only = off;
SET TRANSACTION READ WRITE;
CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);
COMMIT;

SET LOCAL 仅在当前事务期间更改设置。使用普通的 SET 会使更改在整个会话中持续生效，这可能会破坏其他合法需要只读默认值的操作。

预防措施

1. 验证你是否在主节点上

在运行任何迁移之前添加快速检查：

SELECT CASE WHEN pg_is_in_recovery() THEN 'REPLICA' ELSE 'PRIMARY' END;

如果结果是 REPLICA，请中止迁移。pg_is_in_recovery() 函数在备用服务器（standby server）上返回 true，这能保证你不会尝试向只读副本写入数据。

2. 使用专用的迁移角色

创建一个默认事务模式为写启用的角色，并仅授予其所需的权限：

  • 目标数据库的 CREATE 权限。
  • CONNECT 权限，允许迁移工具开启会话。

避免给该角色设置有时会分配给通用用户的 default_transaction_read_only = on 属性。

3. 将基础设施指向写端点

在 IaC 脚本（Terraform、CloudFormation 等）中，配置迁移运行器使用集群的 writer 终端节点，而不是 reader 终端节点。writer 终端节点会解析到主节点，而 reader 终端节点会解析到拒绝写入的从库。

4. 添加 CI/CD 门禁

在你的流水线（GitHub Actions、GitLab CI 等）中插入一个 shell 步骤，运行 pg_is_in_recovery() 查询。如果返回 true，则以非零状态退出任务，从而提前停止部署。

if psql $DATABASE_URL -c "SELECT pg_is_in_recovery()" | grep -q t; then
  echo "Connected to replica – aborting migration"
  exit 1
fi

5. 调整迁移工具的重试机制

像 Flyway 这样的工具通常会自动重试连接。对于 Flyway 9+，请设置 flyway.connectRetries=0。这可以防止工具反复尝试连接从库，否则可能会增加复制延迟并浪费资源。

后续注意事项

  • 复制延迟指标：延迟增加可能表明迁移无意中指向了从库，导致写操作尝试被排队。
  • 连接池路由规则：确保连接池配置明确地将迁移流量路由到主节点。
  • 升级后的角色默认值：数据库升级有时会重置角色参数；在主要版本变更后，请重新审计 default_transaction_read_only。

底线是：只读事务错误很少是 PostgreSQL 的 bug；它通常是流量被发送到错误节点或角色配置错误的症状。通过检查节点角色、使用专用迁移账户并强化你的 CI/CD 流水线，你可以保持迁移顺利运行，避免因模式应用不全而导致下游开发瘫痪。