【小工具】 - 解决postgres数据库remaining connection slots are reserved for non-replication superuser connectio

psycopg2.OperationalError: FATAL: remaining connection slots are reserved for non-replication superuser connection

报错信息

Traceback (most recent call last):
  File "/opt/wavepoint/agent/main.py", line 9, in <module>
    from monitor import moduleMonitor
  File "/opt/wavepoint/agent/monitor/moduleMonitor.py", line 26, in <module>
    from helper.helper_postgresql import postgres_helper
  File "/opt/wavepoint/agent/helper/helper_postgresql.py", line 15, in <module>
    postgres_helper = DBHelper(
  File "/opt/wavepoint/agent/helper/helper_db.py", line 26, in __init__
    self.get_client()
  File "/opt/wavepoint/agent/helper/helper_db.py", line 151, in get_client
    connection = psycopg2.connect(
  File "/opt/wavepoint/data-engine/external-libraries/psycopg2/__init__.py", line 127, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  remaining connection slots are reserved for non-replication superuser connections

问题分析

从字面意思理解来看，是由于超级用户连接数过多导致的问题。可以增加超级用户连接数限制，或者改用非超级用户进行业务操作(最大连接数设置大一些，比如20000)。

解决方案

解决方案一、增加超级用户连接数

vi /var/lib/postgresql/data/postgresql.conf

max_connections = 20000             
superuser_reserved_connections = 10000    
reserved_user_name = 'airflow' 

reserved_user_name为预留用户。

解决方案二、增加普通用户连接数

vi /var/lib/postgresql/data/postgresql.conf

max_connections = 20000             
superuser_reserved_connections = 100    
reserved_user_name = 'postgres' 

reserved_user_name为系统内置用户。