前言

如果不认真阅读PostgreSQL中的相关文档，也不自己动手实验去验证，那么有些东西，可能就不能准确的掌握了，也会在实际使用场合中判断失误。这篇文章的内容很简单，就是介绍给刚想进入PG，学习PG的人准备的。

PG中就有这么个参数：

max_connections = 100

初一看，就是允许最大连接为100个。

Show me the Verification

不多说，直接验证下看看，以PostgreSQL14 为例，

pg_bench是比较好的并发测试验证工具，就用它了。

咱们把效果放小点儿，也便于测试。将该参数值设为： 10

vi postgresql.conf
max_connections = 10

然后重启PG。

提前准备好普通测试用户：iihero

postgres=# create user iihero with password 'test123';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 iihero    |                                                            | {}
 mydb      |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

准备好pgbench的脚本：

pgbench.sql

BEGIN;
select txid_current();
select pg_sleep(0.5);
END;

1、先用用户postgres跑一个10个的看看，跑一分钟.

[09:09:53-postgres@sean-rh1:/var/lib/pgsql]$ pgbench -U postgres -c 10 -f pgbench.sql -T 60
pgbench (14.8)
starting vacuum...pgbench: error: ERROR:  relation "pgbench_branches" does not exist
pgbench: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_tellers" does not exist
pgbench: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_history" does not exist
pgbench: (ignoring this error and continuing anyway)
end.
transaction type: pgbench.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 60 s
number of transactions actually processed: 1200
latency average = 501.055 ms
initial connection time = 23.376 ms
tps = 19.957906 (without initial connection time)

10个并发 (-c 10), 使用超级用户postgres完全正常。

2、仍然用postgres用户，跑11个试下：

[09:13:10-postgres@sean-rh1:/var/lib/pgsql]$ pgbench -U postgres -c 11 -f pgbench.sql -T 60
pgbench (14.8)
starting vacuum...pgbench: error: ERROR:  relation "pgbench_branches" does not exist
pgbench: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_tellers" does not exist
pgbench: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_history" does not exist
pgbench: (ignoring this error and continuing anyway)
end.
pgbench: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5555" failed: FATAL:  sorry, too many clients already
transaction type: pgbench.sql
scaling factor: 1
query mode: simple
number of clients: 11
number of threads: 1
duration: 60 s
number of transactions actually processed: 0
pgbench: fatal: Run was aborted; the above results are incomplete.

我们能明显看到上边报错：pgbench: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5555" failed: FATAL: sorry, too many clients already

这个好理解。因为确实超了。

3、使用普通用户iihero，10个并发试试：

[09:17:42-postgres@sean-rh1:/var/lib/pgsql]$ pgbench -U iihero -d postgres -c 10 -f pgbench.sql -T 60
pgbench (14.8)
pgbench: pghost: /var/run/postgresql pgport: 5555 nclients: 10 duration: 60 dbName: postgres
starting vacuum...pgbench: error: ERROR:  relation "pgbench_branches" does not exist
pgbench: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_tellers" does not exist
pgbench: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_history" does not exist
pgbench: (ignoring this error and continuing anyway)
end.
pgbench: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5555" failed: FATAL:  remaining connection slots are reserved for non-replication superuser connections
transaction type: pgbench.sql

我们能看到错误提示：

pgbench: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5555" failed: FATAL: remaining connection slots are reserved for non-replication superuser connections

意味着这个10个并发不能全部给普通用户。

遇事不决，查看官方文档吧:

https://www.postgresql.org/docs/14/runtime-config-connection.html

文档里头是这么说的：

max_connections (integer)

Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.

When running a standby server, you must set this parameter to the same or higher value than on the primary server. Otherwise, queries will not be allowed in the standby server. 备库上的这个参数值不能小于主库上的此值

superuser_reserved_connections (integer)

Determines the number of connection “slots” that are reserved for connections by PostgreSQL superusers. At most max_connections connections can ever be active simultaneously. Whenever the number of active concurrent connections is at least max_connections minus superuser_reserved_connections, new connections will be accepted only for superusers, and no new replication connections will be accepted.

The default value is three connections. The value must be less than max_connections. This parameter can only be set at server start.

另外有这么个参数：superuser_reserved_connections, 大概是预留给super user使用的。

默认是有3个连接，而且这三个连接值是从max_connections中扣除的。

[09:17:52-postgres@sean-rh1:/var/lib/pgsql]$ psql
psql (14.8)
Type "help" for help.

postgres=# show superuser_reserved_connections;
 superuser_reserved_connections
--------------------------------
 3
(1 row)

这么一来，我们用上边的普通用户iihero，只能拿到最多7个并发了。

再用pgbench验证一下：

[09:21:33-postgres@sean-rh1:/var/lib/pgsql]$  pgbench -U iihero -d postgres -c 7 -f pgbench.sql -T 60
pgbench (14.8)
pgbench: pghost: /var/run/postgresql pgport: 5555 nclients: 7 duration: 60 dbName: postgres
starting vacuum...pgbench: error: ERROR:  relation "pgbench_branches" does not exist
pgbench: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_tellers" does not exist
pgbench: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_history" does not exist
pgbench: (ignoring this error and continuing anyway)
end.
.............
transaction type: pgbench.sql
scaling factor: 1
query mode: simple
number of clients: 7
number of threads: 1
duration: 60 s
number of transactions actually processed: 840
latency average = 501.084 ms
initial connection time = 16.357 ms
tps = 13.969726 (without initial connection time)

总结一下，就是普通用户只能拿到最多(max_connections - superuser_reserved_connections)个并发连接。

类生产环境模拟

用一段短的java代码来模拟:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;

public class VerifyPgMultiConn {

    private ExecutorService threadPool;
    private static final int MAX_SIZE = 10;

    private void test() {
        threadPool = Executors.newFixedThreadPool(MAX_SIZE);
        for (int i = 0; i < MAX_SIZE; i++) {
            threadPool.submit(() -> {
                try {
                    Connection conn = DriverManager.getConnection("jdbc:postgresql://<ip_address>:5555/mydb", "mydb",
                            "test123");
                    System.out.println(Thread.currentThread().getName() + " connected ...");
                    Thread.sleep(3000L);
                } catch (SQLException | InterruptedException e) {
                    e.printStackTrace();
                }
            });
        }

        try {
            if (!threadPool.awaitTermination(40, TimeUnit.SECONDS)) {
                threadPool.shutdownNow();
                System.out.println("stopped.");
            }
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        new VerifyPgMultiConn().test();
    }

}

结果会发现报如下的错：

org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
    at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2787)
    at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:173)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:290)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:223)
    at org.postgresql.Driver.makeConnection(Driver.java:402)
    at org.postgresql.Driver.connect(Driver.java:261)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at com.sean.workshop.demo.debug.VerifyPgMultiConn.lambda$0(VerifyPgMultiConn.java:28)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:836)
org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
    at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2787)
    at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:173)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:290)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:223)
    at org.postgresql.Driver.makeConnection(Driver.java:402)
    at org.postgresql.Driver.connect(Driver.java:261)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at com.sean.workshop.demo.debug.VerifyPgMultiConn.lambda$0(VerifyPgMultiConn.java:28)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:836)
org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
    at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2787)
    at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:173)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:290)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:223)
    at org.postgresql.Driver.makeConnection(Driver.java:402)
    at org.postgresql.Driver.connect(Driver.java:261)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at com.sean.workshop.demo.debug.VerifyPgMultiConn.lambda$0(VerifyPgMultiConn.java:28)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:836)
pool-1-thread-6 connected ...
pool-1-thread-2 connected ...
pool-1-thread-8 connected ...
pool-1-thread-9 connected ...
pool-1-thread-3 connected ...
pool-1-thread-7 connected ...
pool-1-thread-4 connected ...

这上边报的异常非常清晰，有7个线程连接正常，有三个线程连接报错。如果你把用户改为super user: postgres，则10个线程都能正常连接。

小结：

阅读并细致阅读文档，是学习PostgreSQL的方法之一。边读边动手试验，加深印象。

Show me the code。

Show me the verification and result。

ITPub博客

从验证PostgreSQL中的最大连接数说起......

前言

Show me the Verification

类生产环境模拟

小结：

最新文章