PostgreSQL 12: max_wal_senders 连接数从 max_connections 剥离

PostgreSQL 控制总连接数的参数为 max_connections,应用程序、数据库备份(pg_basebackup)、复制(replication)、以及psql连接数据库都会占用 max_connections 设置的连接数,如果应用程序连接数膨胀占满连接数,将导致数据库备份(pg_basebackup)、复制(replication)和运维操作因连接数耗尽而无法开展。

连接数相关的三个参数,如下:

  • max_connections: 数据库实例允许的最大并发连接数
  • max_wal_senders: 通过 pg_basebackup 备份或流复制备库和主库同步占用主库的最大并发连接数
  • superuser_reserved_connections: 给超级用户预留连接数

superuser_reserved_connections 和 max_wal_senders 参数设置的连接数是 max_connections 的子集,PostgreSQL 11 版本前三者的配置关系为:

1
max_connections > ( max_wal_senders + superuser_reserved_connections )

虽然 superuser_reserved_connections 参数可以给超级用户预留连接数,依然不是一个妥当的方式,例如,如果流复制用户不是超级用户,当主库连接数占满时,备库无法保持和主库同步。

PostgreSQL 12 版本 Michael Paquier 提交了一个补丁,将 max_wal_senders 参数占用的连接数从 max_connections 剥离出来,能够将应用程序占用的连接数和数据库备份、复制占用的连接数分开,从而很好的解决本文开头提出的问题。

发行说明

Have max_wal_senders not count as part of max_connections (Alexander Kukushkin)

补丁说明

Michael Paquier 提交的补丁说明,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
commit: ea92368cd1da1e290f9ab8efb7f60cb7598fc310
author: Michael Paquier <michael@paquier.xyz>
date: Tue, 12 Feb 2019 10:07:56 +0900
Move max_wal_senders out of max_connections for connection slot handling

Since its introduction, max_wal_senders is counted as part of
max_connections when it comes to define how many connection slots can be
used for replication connections with a WAL sender context. This can
lead to confusion for some users, as it could be possible to block a
base backup or replication from happening because other backend sessions
are already taken for other purposes by an application, and
superuser-only connection slots are not a correct solution to handle
that case.

This commit makes max_wal_senders independent of max_connections for its
handling of PGPROC entries in ProcGlobal, meaning that connection slots
for WAL senders are handled using their own free queue, like autovacuum
workers and bgworkers.

One compatibility issue that this change creates is that a standby now
requires to have a value of max_wal_senders at least equal to its
primary. So, if a standby created enforces the value of
max_wal_senders to be lower than that, then this could break failovers.
Normally this should not be an issue though, as any settings of a
standby are inherited from its primary as postgresql.conf gets normally
copied as part of a base backup, so parameters would be consistent.

Author: Alexander Kukushkin
Reviewed-by: Kyotaro Horiguchi, Petr Jelínek, Masahiko Sawada, Oleksii
Kliukin
Discussion: https://postgr.es/m/CAFh8B=nBzHQeYAu0b8fjK-AF1X4+_p6GRtwG+cCgs6Vci2uRuQ@mail.gmail.com

手册和补丁说明已经很清楚,本文可做个简单演示。

PostgreSQL 11 演示

设置 postgresql.conf 参数,如下:

1
2
3
max_connections = 3
superuser_reserved_connections = 0
max_wal_senders = 2

在数据库主机通过 psql 开启会话1和会话2,占用两个连接。

之后在数据库主机上执行 pg_basebackup 命令备份数据库,如下:

1
2
[pg11@pghost6 ~]$ pg_basebackup -D backup -Ft -P
pg_basebackup: could not connect to server: FATAL: sorry, too many clients already

pg_basebackup 命令消耗的是 max_wal_senders 设置的连接数,max_wal_senders 连接数是 max_connections 的子集,由于pg_basebackup 备份数据库需占用两个连接,因此以上报连接数不足。

PostgreSQL 12 演示

设置 postgresql.conf 参数,如下:

1
2
3
max_connections = 3
superuser_reserved_connections = 0
max_wal_senders = 2

在数据库主机通过 psql 开启会话1和会话2,占用两个连接。

之后在数据库主机上执行 pg_basebackup 命令备份数据库,如下:

1
2
[pg12@pghost6 ~]$ pg_basebackup -D backup -Ft -P
2963944/2963944 kB (100%), 1/1 tablespace

备份正常,验证了 12 版本 max_wal_senders 参数不受 max_connections 参数影响。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%