Koril:
背景
我在自己的阿里云服务器上( 2C2G ,3M ,Debian 12 )装了一个 PostgreSQL ( 15 ),安装后,仅仅做了以下配置改动:
pg_hba.conf:
添加:host all all 0.0.0.0/0 md5
postgresql.conf
开放端口:listen_addresses = '*'
给 postgres 设置了密码,sudo -i -u postgres -> psql -> \password
异常
无论是 Navicat 还是 Python 的 Psycopg2 在超过一定时间(大概 3-5 分钟),就会连接失效了。
Navicat 报错如下:
Server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request.
Psycopg2 报错如下:
psycopg2.OperationalError: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
已知
服务器没有太大的负载,cpu ,内存,磁盘,网络,占用率都很低,除了 Prometheus/Grafana/nginx 之外,这个机器上只有刚刚安装的 PostgreSQL 。
重新获取连接,能够正常访问,但是我使用的 Psycopg2 的 ThreadedConnectionPool 连接池,连接池没法保持连接么?代码如下:
def get_pg_pool(pg_config):
pool = psycopg2.pool.ThreadedConnectionPool(
minconn=1,
maxconn=200,
host=pg_config['PG_HOST'],
port=pg_config['PG_PORT'],
dbname=pg_config['PG_DB'],
user=pg_config['PG_USER'],
password=pg_config['PG_PASSWORD'],
connect_timeout=5,
)
return pool
@flask_app.route('/task/log', methods=['GET'])
def task_log():
task_log_list = []
# 在进程启动时,给 flask_app 初始化了一个 pg_pool 对象
conn = flask_app.pg_pool.getconn()
try:
with conn.cursor() as cur:
cur.execute('SELECT * FROM t_log')
task_log_list = cur.fetchall()
except psycopg2.Error as e:
return JsonResult.failed('获取日志列表失败')
finally:
flask_app.pg_pool.putconn(conn)
return JsonResult.successful(task_log_list)
问题
这个问题是和 Linux 服务器配置有关呢?还是跟 PostgreSQL 配置有关?请问如何排查和解决呢?