Pgpool+Aurora配置后Jira启动报错：无法在只读事务执行UPDATE

核心问题 #

Jira启动时执行的UPDATE public.clusternode写操作被Pgpool错误路由到了Aurora只读实例，触发cannot execute UPDATE in a read-only transaction错误，导致启动失败。根源在于Pgpool的主从状态检测与路由规则配置不完善，无法正确区分读写操作的目标节点。

解决方案步骤 #

1. 修正Pgpool核心配置

修改pgpool.conf参数，确保主从节点状态检测正常、路由逻辑准确：

listen_addresses = '*'
port = 5432

backend_hostname0 = 'writerinstanceclusterendpoint'
backend_port0 = 5432
backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER'

backend_hostname1 = 'readerinstanceclusterendpoint'
backend_port1 = 5432
backend_flag1 = 'DISALLOW_TO_FAILOVER'
backend_clustering_mode = 'streaming_replication'

enable_pool_hba = On
pool_passwd = 'pool_passwd'
Ssl = On
replication_mode = Off
load_balance_mode = On
master_slave_mode = On
master_slave_sub_mode = Stream

# 开启主从状态检测，周期5秒（可按需调整）
sr_check_period = 5
# 指定用于状态检测的数据库用户（需具备主从查询权限）
sr_check_user = 'postgres'
sr_check_password = 'your_sr_check_password'

# 开启节点健康检查，周期10秒
health_check_period = 10

max_pool = 350
# 开启故障自动切换，避免节点异常时路由错误
failover_on_backend_error = On

2. 验证Pgpool节点状态

重启Pgpool后，执行以下命令确认主从节点识别正确：

pgpool -c show pool_nodes

输出需满足：

• backend0状态为up，角色为primary
• backend1状态为up，角色为standby

3. 调整Jira数据库连接配置

在dbconfig.xml中明确指定连接为非只读，避免Pgpool误判：

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>postgres72</database-type>
  <schema-name>public</schema-name>
  <jdbc-datasource>
    <url>jdbc:postgresql://pgpoolIP:5432/jiradb</url>
    <driver-class>org.postgresql.Driver</driver-class>
    <username>jira</username>
    <password>mypass</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>300</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
    <!-- 添加readOnly=false明确非只读连接 -->
    <connection-properties>tcpKeepAlive=true;socketTimeout=240;readOnly=false</connection-properties>
    <validation-query>select version();</validation-query>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>

    <pool-test-while-idle>true</pool-test-while-idle>
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <validation-query-timeout>3</validation-query-timeout>
  </jdbc-datasource>
</jira-database-config>

4. 验证流量路由

• 重启Pgpool与Jira，确认启动日志无只读事务错误
• 执行只读操作（如浏览项目），查看Pgpool日志确认流量路由到只读实例
• 执行写操作（如创建Issue），确认流量路由到主实例

内容的提问来源于stack exchange，提问作者Sahil Gulati