psql以read-write模式连接Postgres失败的原因及解决方法

使用psql连接PostgreSQL数据库时，target_session_attrs参数取默认值时，可以正常以read-only模式建立连接；但将该参数设置为read-write、尝试以读写模式连接时，抛出如下报错：

psql: could not make a writable connection to server 'server_name'

• 连接地址指向的是PostgreSQL流复制集群的只读备库。备库默认处于介质恢复状态，本身不支持写入操作，target_session_attrs=read-write模式下libpq驱动会在连接建立后检测节点是否支持写入事务，备节点无法通过校验就会抛出该错误。
• 目标节点被配置为只读状态：管理员手动设置了实例级、数据库级或用户级的只读参数，即使是主节点也会被判定为不可写。
• 连接链路中间存在代理层（常见如PgBouncer、HAProxy、云负载均衡）做读写路由，配置的连接地址默认被代理转发到只读节点；或是代理版本过旧，未适配libpq的读写状态检测逻辑，返回了错误的节点状态。
• 目标节点处于不可写的异常状态：比如主库磁盘使用率触达阈值自动进入只读保护、主备切换过程中主节点被临时降级、集群发生脑裂导致原主库被踢出集群，这类场景下节点暂时不支持写入，状态检测会失败。
• 本地psql依赖的libpq驱动版本过旧，和服务端的状态检测逻辑不兼容，会将正常的可写节点误判为只读。

• 先定位集群内的可写主节点：登录任意集群节点执行select pg_is_in_recovery();，返回结果为f的节点即为支持写入的主节点，将psql连接地址替换为该主节点地址直连测试，优先排除连错节点的问题。
• 检查目标节点的只读配置：连接到目标节点后，依次检查三个层级的只读参数：

  -- 检查实例级只读配置
  SHOW default_transaction_read_only;
  -- 检查当前连接数据库是否配置只读规则
  SELECT datname, datconfig FROM pg_database WHERE datname = current_database();
  -- 检查当前连接用户是否配置只读规则
  SELECT rolname, rolconfig FROM pg_roles WHERE rolname = current_user;
  

  如果查到default_transaction_read_only参数值为on，或是对应数据库、用户的配置中存在default_transaction_read_only=on的规则，将对应配置修改为off，执行SELECT pg_reload_conf();重载配置后重试即可。
• 排查代理层配置：如果连接链路使用了代理/负载均衡，先绕过代理直连主节点测试，若直连正常则检查代理规则：确认携带target_session_attrs=read-write属性的连接请求会被转发到主节点，同时将代理升级到兼容当前PostgreSQL版本的稳定版，避免检测报文透传异常。
• 排查节点运行异常：检查主库所在磁盘的剩余空间，清理占用空间的无用日志、过期备份文件，解除磁盘满触发的只读保护；如果集群正在执行故障切换，等待选主完成、集群状态稳定后再尝试连接；如果出现集群脑裂，先修复集群拓扑，确认集群内存在唯一可写主节点后再连接。
• 排除版本兼容问题：如果直连主节点、确认所有配置均正常仍报错，将本地psql客户端及依赖的libpq驱动升级到与服务端大版本匹配的稳定版本，解决旧版本检测逻辑不兼容的问题。
• 排查时可以在连接命令中增加-v参数打印详细连接日志，快速定位检测失败的具体环节，测试命令参考：

  psql "host=你的连接地址 port=数据库端口 dbname=目标库名 user=连接用户 target_session_attrs=read-write" -v
  

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