PostgreSQL流复制参数max_wal_senders的用法说明

数据库 2024/9/21 佚名

3 1 2

饿虎岗资源网 Design By www.oxmxm.com

环境：

PostgreSQL 9.2.4

主机:192.25.10.76

从机:192.25.10.71

做postgresql的流复制主从时，会遇到调整max_wal_sengers这个参数，官方文档对这个参数做了一个简要的说明(9.2.4比早先版本多了几句话并做了一些微调)，但没有实际的例子。

1.参数说明：

Specifies the maximum number of concurrent connections from standby servers or streaming base backup clients (i.e., the maximum number of simultaneously running WAL sender processes). The default is zero, meaning replication is disabled. WAL sender processes count towards the total number of connections, so the parameter cannot be set higher than max_connections. This parameter can only be set at server start. wal_level must be set to archive or hot_standby to allow connections from standby servers.

也就是说，这个参数是在主机上设置的，是从机连接到主机的并发连接数之总和，所以这个参数是个正整型。默认值是0，也即默认没有流复制功能。该并发连接数从进程上看，就是各个wal sender进程数之和，可以通过ps -ef|grep senders来查看，所以该值不能超过系统的最大连接数(max_connections，该BUG在9.1.5被修复)，可以允许超过实际的流复制用户数。该参数更改需要重启DB，比如我只配了一个从机:

[postgres@ndb2 database]$ ps -ef|grep sender postgres 21257 21247 0 20:57 "htmlcode">

2013-08-12 20:53:42.132 CST,,,8859,,5208dad6.229b,1,,2013-08-12 20:53:42 CST,,0,FATAL,XX000,"could not connect to the primary server: FATAL: number of requested standby connections exceeds max_wal_senders (currently 0)
",,,,,,,,,""
2013-08-12 20:53:47.137 CST,,,8861,,5208dadb.229d,1,,2013-08-12 20:53:47 CST,,0,FATAL,XX000,"could not connect to the primary server: FATAL: number of requested standby connections exceeds max_wal_senders (currently 0)
",,,,,,,,,""
2013-08-12 20:53:52.142 CST,,,8862,,5208dae0.229e,1,,2013-08-12 20:53:52 CST,,0,FATAL,XX000,"could not connect to the primary server: FATAL: number of requested standby connections exceeds max_wal_senders (currently 0)
",,,,,,,,,""
2013-08-12 20:53:57.148 CST,,,8864,,5208dae5.22a0,1,,2013-08-12 20:53:57 CST,,0,FATAL,XX000,"could not connect to the primary server: FATAL: number of requested standby connections exceeds max_wal_senders (currently 0)
",,,,,,,,,"

主机上显示的日志异常:

receiver"
2013-08-12 20:43:26.937 CST,,,21064,"",5208d86e.5248,1,"",2013-08-12 20:43:26 CST,,0,LOG,00000,"connection received: host=192.25.10.71 port=46085",,,,,,,,,""
2013-08-12 20:43:26.938 CST,"repuser","",21064,"192.25.10.71:46085",5208d86e.5248,2,"authentication",2013-08-12 20:43:26 CST,2/1195,0,LOG,00000,"replication connection authorized: user=repuser",,,,,,,,,""
2013-08-12 20:43:26.938 CST,"repuser","",21064,"192.25.10.71:46085",5208d86e.5248,3,"startup",2013-08-12 20:43:26 CST,2/0,0,FATAL,53300,"number of requested standby connections exceeds max_wal_senders (currently 0)",,,,,,,,,"walreceiver"
2013-08-12 20:43:26.939 CST,"repuser","",21064,"192.25.10.71:46085",5208d86e.5248,4,"startup",2013-08-12 20:43:26 CST,,0,LOG,00000,"disconnection: session time: 0:00:00.002 user=repuser database= host=192.25.10.71 port=46085",,,,,,,,,"wa
lreceiver"
2013-08-12 20:43:41.513 CST,,,21066,"",5208d87d.524a,1,"",2013-08-12 20:43:41 CST,,0,LOG,00000,"connection received: host=192.25.10.71 port=46086",,,,,,,,,""
2013-08-12 20:43:41.514 CST,"repuser","",21066,"192.25.10.71:46086",5208d87d.524a,2,"authentication",2013-08-12 20:43:41 CST,2/1198,0,LOG,00000,"replication connection authorized: user=repuser",,,,,,,,,""
2013-08-12 20:43:41.514 CST,"repuser","",21066,"192.25.10.71:46086",5208d87d.524a,3,"startup",2013-08-12 20:43:41 CST,2/0,0,FATAL,53300,"number of requested standby connections exceeds max_wal_senders (currently 0)",,,,,,,,,"walreceiver"
2013-08-12 20:43:41.515 CST,"repuser","",21066,"192.25.10.71:46086",5208d87d.524a,4,"startup",2013-08-12 20:43:41 CST,,0,LOG,00000,"disconnection: session time: 0:00:00.002 user=repuser database= host=192.25.10.71 port=46086",,,,,,,,,"wa
lreceiver"
...

遇到如此问题，则需要检查postgresql.conf文件的max _wal_senders参数了。

3.参数生效

文档上说明需要重启DB使之生效，如果动态使之生效会报错如下：

[postgres@proxy1 ]$ psql
psql (9.2.4)
Type "help" for help.
postgres=# show max_wal_senders ;
 max_wal_senders 
-----------------
 0
(1 row)
postgres=# set max_wal_senders=1;
ERROR: parameter "max_wal_senders" cannot be changed without restarting the server
postgres=#

补充： postgresql基于流复制 (streaming replication)的warm-standby

实例一枚：

Primary:

l 归档设置：

Wal_level=archive
Archive_mode=on
archive_command = 'cp -i %p /data/pgsql/archived_wal/%f'

l 流复制相关设置：

max_wal_senders = '10'  #启动复制进程数量限制，必须大于0
max_replication_slots = '10' #为使用replication slot，必须大于0；replication slot作用是保证wal没有同步到standby之前不能从pg_xlog移走；
wal_keep_segments = '50'    #指定pg_xlog中最少保留的wal数量
 
select pg_create_physical_replication_slot(‘gp1_a_slot'); #创建replication slot
select * from pg_replication_slots;       #查询创建的replication slot

l 编辑pg_hba.conf

# Allow replication connections from localhost, by a user with the replication privilege.
#host replication  postgres  127.0.0.1/32   trust
#host replication  postgres  ::1/128     trust
local replication  postgres        trust
host replication  postgres  192.168.12.0/24   trust

l 联机备份过程（基础备份）

#touch /var/lib/pgsql/backup_in_progress
$psql –c "select pg_start_backup('hot_backup');"
$tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
$psql -c "select pg_stop_backup();"
#rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/ #打包归档

实例：

psql -c "select pg_start_backup('pgbk10');"
tar -zcf pgbk10.tgz data/
psql -c "select pg_stop_backup();"

Standby：

l 编辑recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=192.168.12.38 port=5666 user=postgres'
primary_slot_name='gp1_a_slot'
#restore_command = 'cp /data/pgsql/archived_wal/%f %p'
#archive_cleanup_command = 'pg_archivecleanup /data/pgsql/archived_wal %r'

l 将primary上的基础备份传输到standby上

$scp primary: /var/lib/pgsql/backup.tar .

解压备份到standby上的$PGDATA

l 启动standby

$pg_ctl start –D $PGDATA

启动standby后，postgres开始从primary上接收wal日志进行恢复，并且一直保持恢复状态，psql不能登录；

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

PostgreSQL,流复制,max_wal_senders

饿虎岗资源网 Design By www.oxmxm.com

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

饿虎岗资源网 Design By www.oxmxm.com

评论“PostgreSQL流复制参数max_wal_senders的用法说明”

暂无评论...

www.oxmxm.com 饿虎岗资源网

120,135影音资源

344,641技术资源

22,817软件资源

435,032站长资源

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

三星在GTC上展示了专为下一代游戏GPU设计的GDDR7内存。

首次推出的GDDR7内存模块密度为16GB，每个模块容量为2GB。其速度预设为32 Gbps（PAM3），但也可以降至28 Gbps，以提高产量和初始阶段的整体性能和成本效益。
据三星表示，GDDR7内存的能效将提高20%，同时工作电压仅为1.1V，低于标准的1.2V。通过采用更新的封装材料和优化的电路设计，使得在高速运行时的发热量降低，GDDR7的热阻比GDDR6降低了70%。

更新日志

2024年09月21日

PostgreSQL流复制参数max_wal_senders的用法说明

环境：

1.参数说明：

3.参数生效

Primary:

Standby：

PostgreSQL 允许远程访问设置的操作

postgresql中wal_level的三个参数用法说明

评论“PostgreSQL流复制参数max_wal_senders的用法说明”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

PostgreSQL流复制参数max_wal_senders的用法说明

环境：

1.参数说明：

3.参数生效

Primary:

Standby：

PostgreSQL 允许远程访问设置的操作

postgresql中wal_level的三个参数用法说明

评论“PostgreSQL流复制参数max_wal_senders的用法说明”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存