PostgreSQL 11.16无法在只读事务中执行CREATE TABLE错误排查

pg_is_in_recovery()返回true时，代表当前会话连接的是PostgreSQL流复制备库（处于恢复状态的只读实例），这类实例天然不支持CREATE TABLE这类写操作，直接触发cannot execute CREATE TABLE in a read-only transaction报错。你和同事执行相同操作结果不同，核心原因是两边连接的根本不是同一个数据库节点，和SQL语法、客户端版本、账号权限没有直接关系。

• 首先核对两端连接的实际实例信息：分别在你的本地PGAdmin、Python脚本连接会话、同事的PGAdmin会话中执行以下SQL，对比返回的服务端IP、端口、连接库名、用户名信息：

  SELECT inet_server_addr() AS server_ip, inet_server_port() AS server_port, current_database() AS db_name, current_user AS login_user;
  

  绝大多数场景下你会发现本地连接指向的IP和同事的不一致，说明连接地址配置错误。
• 核对Azure PostgreSQL实例的端点配置：Azure部署的PostgreSQL（含单服务器、灵活服务器配置高可用/只读副本的场景）会默认提供两类独立访问端点：一类是指向主库的读写端点，一类是指向备库/只读副本的只读端点，检查你本地保存的连接配置，大概率是误填了只读端点地址。
• 排查本地DNS解析异常：如果确认你和同事填写的连接域名、端口、认证信息完全一致，分别在两台设备上对连接使用的数据库域名做nslookup/ping解析，对比返回的IP地址。如果IP不一致，说明你本地DNS缓存了错误的解析记录，把主库域名指向了备库IP。
• 次要排查项：检查本地PGAdmin连接属性、SQLAlchemy连接串中是否手动配置了default_transaction_read_only = on的会话参数，不过这类配置不会导致pg_is_in_recovery()返回true，仅在前几步排查无异常时验证即可。

• 连接地址配置错误场景：直接将本地PGAdmin连接配置、SQLAlchemy连接串中的地址替换为Azure PostgreSQL主库的读写端点，保留原有端口、账号、密码、库名配置，重新连接后即可正常执行建表等写操作。
• DNS解析异常场景：清空本地DNS缓存后重新连接，不同系统的清空命令如下：
  • Windows系统（管理员权限执行）：ipconfig /flushdns
  • Linux系统：sudo systemd-resolve --flush-caches
  • Mac系统：sudo dscacheutil -flushcache; sudo killall -HUP mDNSResponder
• 注意：PostgreSQL备库的只读属性是内核层面的限制，没有参数可以绕过，如果业务需要执行写操作，必须连接主库节点。

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