【性能调优】PostgreSQL修改最大连接数，提高数据库性能

在使用PostgreSQL 的时候，经常会遇到这样的错误提示， sorry, too many clients already，这是因为默认PostgreSQL最大连接数是 100, 一般情况下，个人使用时足够的，但是在生产环境，这个连接数是远远不够的；

修改最大连接数

要修改 PostgreSQL 的最大连接数，可以按照以下步骤进行操作：

打开PostgreSQL配置文件

配置文件的路径可能因操作系统和 PostgreSQL 版本而异。以下是一些常见的配置文件路径：

• Ubuntu/Debian：/etc/postgresql/<version>/main/postgresql.conf
• CentOS/RHEL：/var/lib/pgsql/<version>/data/postgresql.conf
• macOS Homebrew 安装：/usr/local/var/postgres/postgresql.conf

打开配置文件并找到 max_connections 参数。

修改最大连接数

在配置文件中找到 max_connections 参数，并将其值修改为所需的最大连接数。例如，将最大连接数设置为 1000，可以将以下行添加到配置文件中：

max_connections = 1000

保存配置文件。

重启 PostgreSQL 服务

保存配置文件后，需要重启 PostgreSQL 服务才能使修改生效。

可以使用以下命令重启 PostgreSQL 服务：

sudo service postgresql restart

或者，根据不同的操作系统和安装方式，可能需要使用其他命令来重启 PostgreSQL 服务。

如果是使用Docker 的方式启动的 PostgreSQL , 需要通过一下命令重启 Docker 服务：

docker restart <postgresql_docker_name>

验证修改是否生效

重启 PostgreSQL 服务后，可以使用以下命令来验证最大连接数是否已经修改：

psql -U <username> -c "SHOW max_connections;"

将 <username> 替换为实际的 PostgreSQL 用户名。该命令将显示当前的最大连接数。

如果可以通过Navicat直接连接到 PostgreSQL ，可以直接新建查询窗口查询：

SHOW max_connections;

请注意，修改最大连接数可能会影响系统的性能和资源使用情况。在增加最大连接数之前，请确保系统具有足够的资源来支持所需的连接数，并考虑数据库服务器的硬件配置和负载情况。

修改操作系统文件描述符限制

在某些情况下，可能存在其他配置项的限制，导致 max_connections 的修改不生效。例如，操作系统的文件描述符限制（ulimit）可能会限制 PostgreSQL 的最大连接数。

可以通过以下命令查看当前的文件描述符限制：

ulimit -n

如果文件描述符限制较低，可以尝试增加限制。具体的操作方法可以参考操作系统的文档或相关资源。

要修改操作系统的 ulimit 最大值，可以按照以下步骤进行操作：

查看当前的 ulimit 最大值

可以使用以下命令查看当前的 ulimit 最大值：

ulimit -a

这将显示当前的 ulimit 配置，包括最大文件描述符数、最大用户进程数等。

临时修改 ulimit 最大值

可以使用以下命令临时修改 ulimit 最大值：

ulimit -n <new_value>

其中 <new_value> 是要设置的新的最大值。例如，要将最大文件描述符数设置为 65536;

可以运行以下命令：

ulimit -n 65536

注意，这种方式修改的最大值只在当前会话中有效，重新登录后将恢复为默认值。

永久修改 ulimit 最大值

要永久修改 ulimit 最大值，需要编辑系统的配置文件。具体的配置文件路径可能因操作系统而异。以下是一些常见的配置文件路径：

• Ubuntu/Debian：/etc/security/limits.conf
• CentOS/RHEL：/etc/security/limits.conf
• macOS：/etc/launchd.conf

打开配置文件，并添加或修改以下行：

* hard nofile <new_value>
* soft nofile <new_value>
* hard nproc <new_value>
* soft nproc <new_value>

其中 <new_value> 是要设置的新的最大值。

例如，所有用户的硬/软限制（soft limit）文件描述符数设置为 65536，所有用户的硬/软限制进程数设置为 65536，可以添加以下行：

* hard nofile 65536
* soft nofile 65536
* hard nproc 65536
* soft nproc 65536

* 代表针对所有用户

noproc 是代表最大进程数

nofile 是代表最大文件打开数

1. 保存文件并重新启动系统，使修改生效：

   reboot
   

2. 验证修改是否生效：

   重新登录系统后，可以再次运行 ulimit -n 命令来验证修改是否生效。确保最大值已经更新为所设置的新值。

请注意，修改操作系统的 ulimit 最大值可能需要管理员权限。在进行任何系统级别的修改之前，请确保了解修改的影响，并备份重要的配置文件。

查询数据库连接情况

除以上使用 SHOW max_connections; 查看PostgreSQL的最大连接数, 这里也整理了一些常用的

查询数据库配置的最大连接数

select setting from pg_catalog.pg_settings where "name" ='max_connections';

查询数据库当前连接信息

select datname ,pid ,usename ,query_start ,wait_event ,wait_event_type ,state ,query from pg_catalog.pg_stat_activity order by query_start desc ;

根据进程 ID 取消正在执行的查询

select pg_cancel_backend(pid);

根据进程 ID 终止指定的连接

select pg_terminate_backend(pid);

根据进程 ID 获取连接的详细信息

select pg_stat_get_activity(pid);

查询当前使用的连接数

select count(*) from pg_catalog.pg_stat_activity ;

查询当前空余连接数

select setting::int2 -(select count(*) from pg_catalog.pg_stat_activity) from pg_catalog.pg_settings where "name" ='max_connections';