Scale Grid中PostgreSQL主从连接异常：INSERT只读事务错误解决方案咨询

是否可以配置让服务等待主库连接？ #

可以，通过调整数据库连接池和PostgreSQL客户端的配置，能强制服务优先获取主库连接，或在检测到只读连接时自动重试/等待主库可用。以下是Java和Node.js的具体实现方案：

Java 端配置方案 #

1. HikariCP连接池配置（Spring Boot常用） #

在application.properties或application.yml中添加连接验证规则，自动过滤只读从库连接：

# 验证连接是否为主库（pg_is_in_recovery()返回true表示是从库）
spring.datasource.hikari.connection-test-query=SELECT pg_is_in_recovery()
# 初始化连接时强制关闭只读模式
spring.datasource.hikari.connection-init-sql=SET transaction_read_only = false
# 验证超时时间，避免长时间等待无效连接
spring.datasource.hikari.validation-timeout=5000

HikariCP会自动丢弃返回true的连接，重新获取新连接，直到拿到主库的可用连接。

2. 自定义连接验证逻辑（适用于其他连接池） #

如果使用非HikariCP的连接池，可以在获取连接时主动校验：

public Connection getValidMasterConnection() throws SQLException {
    Connection conn = dataSource.getConnection();
    try (Statement stmt = conn.createStatement()) {
        ResultSet rs = stmt.executeQuery("SELECT pg_is_in_recovery()");
        rs.next();
        boolean isReplica = rs.getBoolean(1);
        if (isReplica) {
            conn.close();
            Thread.sleep(1000); // 等待1秒后重试
            return getValidMasterConnection();
        }
        conn.setReadOnly(false);
        return conn;
    } catch (InterruptedException e) {
        Thread.currentThread().interrupt();
        throw new SQLException("等待主库连接被中断", e);
    }
}

Node.js 端配置方案 #

1. pg客户端连接池验证（常用pg库） #

在创建连接池时添加钩子，自动过滤只读从库连接：

const { Pool } = require('pg');

const pool = new Pool({
  user: 'your_db_user',
  host: 'your_scalegrid_host',
  database: 'your_db_name',
  password: 'your_db_pwd',
  port: 5432,
  // 连接前校验是否为主库
  async connect(client) {
    const res = await client.query('SELECT pg_is_in_recovery()');
    const isReplica = res.rows[0].pg_is_in_recovery;
    if (isReplica) {
      client.release(true); // 销毁只读连接
      throw new Error('连接到只读从库，重试中...');
    }
    await client.query('SET transaction_read_only = false');
  },
  // 重试配置
  retryDelayMillis: 1000,
  maxRetries: 5
});

连接池会在捕获到错误后自动重试，直到获取到主库连接。

2. 定期清理只读连接 #

定时检查连接池中的连接，移除无效的只读连接：

// 每30秒检查一次连接池
setInterval(async () => {
  const totalClients = pool.totalCount;
  for (let i = 0; i < totalClients; i++) {
    const client = await pool.connect();
    try {
      const res = await client.query('SELECT pg_is_in_recovery()');
      if (res.rows[0].pg_is_in_recovery) {
        client.release(true); // 销毁只读连接
      } else {
        client.release();
      }
    } catch (err) {
      client.release(true);
    }
  }
}, 30000);

其他通用解决方案 #

• ScaleGrid 端路由配置：在ScaleGrid控制台中调整读写分离策略，给写操作分配专属的主库连接地址，避免使用通用负载均衡地址导致路由到从库。
• 业务层读写分离：将写操作和读操作明确拆分，写请求固定使用主库连接，读请求按需使用从库，从根源上避免写请求落到从库。
• 错误重试机制：在业务代码中捕获ERROR: cannot execute INSERT in a read-only transaction错误，自动重试写操作。比如Java用Spring Retry，Node.js用自定义重试函数。
• 监控告警：配置监控规则，当出现只读事务错误时触发告警，及时排查ScaleGrid的主从切换或负载均衡异常。

内容的提问来源于stack exchange，提问作者Selvakumar Ponnusamy