使用psycopg2更新PostgreSQL表遇服务异常终止错误如何解决

常见原因排查 #

• 首先是连接超时被服务端主动断开：PostgreSQL默认的tcp_keepalives_idle、idle_in_transaction_session_timeout参数会自动关闭长时间空闲的连接，你当前代码拿到连接后如果出现异常不会释放，下次循环复用已经被服务端断开的旧连接就会触发该报错。
• 其次是连接池使用逻辑错误：你把SimpleConnectionPool的初始化放在了while True循环内部，每次循环都会新建一个连接池，长期运行会耗尽服务端连接资源，被服务端强制断开连接。
• 第三是异常分支未处理失效连接：当出现数据库报错后，你没有判断连接是否存活，也没有将无效连接从连接池中移除，后续复用无效连接必然触发连接断开报错。
• 还有可能是网络抖动：如果你的应用和PostgreSQL服务部署在不同的服务器上，临时网络波动也会导致偶发连接断开。

修复方案 #

1. 调整连接池使用逻辑 #

连接池全局只初始化一次，不要放在循环内部，同时配置连接的keepalive参数，避免服务端主动断开空闲连接：

from psycopg2 import pool
import psycopg2
import time
from datetime import datetime

# 全局初始化连接池，增加keepalive配置避免空闲断开
postgreSQL_pool = pool.SimpleConnectionPool(
    1, 20, 
    host = db_host,
    database = db_name,
    port = db_port,
    user = db_user,
    password = db_pwd,
    keepalives=1,
    keepalives_idle=30,
    keepalives_interval=10,
    keepalives_count=5
)

now  = datetime.now()
logoff_time = datetime(now.year, now.month, now.day, 15, 0, 0)                      
while True:            
    time.sleep(1)
    conn = None
    cur = None
    try:
        status = 'EXECUTED'
        exec_type1 = 'CANCELLED'
        exec_type2 = 'COMPLETED'
        sql = """ UPDATE orders SET status = %s, executed_type = %s WHERE order_id = %s"""
        updated_rows = 0

        # 从连接池拿连接前先判断连接是否存活
        try:
            conn = postgreSQL_pool.getconn()
            # 执行ping操作验证连接有效性
            cur = conn.cursor()
            cur.execute('SELECT 1')
        except (Exception, psycopg2.DatabaseError) as error:
            print("连接失效，丢弃该连接:", error)
            if conn:
                # 把无效连接从连接池中移除
                postgreSQL_pool.putconn(conn, close=True)
            conn = None
            continue

        try:
            cur.execute(sql, (status, exec_type1, order_id,))
            conn.commit()
            updated_rows = cur.rowcount
            print(updated_rows)
            break
        except (Exception, psycopg2.DatabaseError) as error:
            print("执行SQL报错:", error)
            if conn:
                conn.rollback()
        finally:
            if cur:
                cur.close()
            if conn:
                # 正常连接放回连接池
                postgreSQL_pool.putconn(conn)
    except Exception as e:
        print("全局异常:", e)
        if conn:
            try:
                postgreSQL_pool.putconn(conn, close=True)
            except:
                pass

2. 调整PostgreSQL服务端配置 #

检查postgresql.conf里的以下参数，避免过于激进的连接回收策略：

# 空闲会话超时时间，单位ms，设置为0表示不主动回收空闲会话
idle_in_transaction_session_timeout = 0
# TCP保活相关参数，和客户端配置保持一致即可
tcp_keepalives_idle = 30
tcp_keepalives_interval = 10
tcp_keepalives_count = 5

修改配置后需要重启PostgreSQL服务生效。

3. 增加失败重试机制 #

对于偶发的网络抖动导致的连接断开，在SQL执行失败后增加3-5次的重试逻辑，重试前先重新获取有效连接即可。

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