• 售前

  • 售后

热门帖子
入门百科

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

[复制链接]
穿雨捶 显示全部楼层 发表于 2021-10-26 13:18:59 |阅读模式 打印 上一主题 下一主题
情况:

PostgreSQL 9.2.4
主机:192.25.10.76
从机:192.25.10.71
做postgresql的流复制主从时,会遇到调解max_wal_sengers这个参数,官方文档对这个参数做了一个扼要的分析(9.2.4比早先版本多了几句话并做了一些微调),但没有现实的例子。
1.参数分析:
  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,好比我只配了一个从机:
  1. [postgres@ndb2 database]$ ps -ef|grep sender
  2. postgres 21257 21247 0 20:57 ?  00:00:00 postgres: wal sender process repuser 192.25.10.71(46161) streaming 0/4018ED8
  3. postgres 22193 20949 0 23:02 pts/0 00:00:00 grep sender
复制代码
2.异常情况

许多时间设置主从的时间会遗漏这个参数,大概没有设置正确(好比现实配的从机数高出设置的毗连数),这个时间一般会报错 number of requested standby connections exceeds max_wal_senders (currently X): 备机上显示的日记异常:
  1. 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)
  2. ",,,,,,,,,""
  3. 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)
  4. ",,,,,,,,,""
  5. 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)
  6. ",,,,,,,,,""
  7. 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)
  8. ",,,,,,,,,"
复制代码
主机上显示的日记异常:
  1. receiver"
  2. 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",,,,,,,,,""
  3. 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",,,,,,,,,""
  4. 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"
  5. 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
  6. lreceiver"
  7. 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",,,,,,,,,""
  8. 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",,,,,,,,,""
  9. 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"
  10. 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
  11. lreceiver"
  12. ...
复制代码
遇到如此题目,则必要检查postgresql.conf文件的max _wal_senders参数了。
3.参数见效

文档上分析必要重启DB使之见效,如果动态使之见效会报错如下:
  1. [postgres@proxy1 ]$ psql
  2. psql (9.2.4)
  3. Type "help" for help.
  4. postgres=# show max_wal_senders ;
  5. max_wal_senders
  6. -----------------
  7. 0
  8. (1 row)
  9. postgres=# set max_wal_senders=1;
  10. ERROR: parameter "max_wal_senders" cannot be changed without restarting the server
  11. postgres=#
复制代码
补充: postgresql基于流复制 (streaming replication)的warm-standby
实例一枚:
Primary:

l 归档设置:
  1. Wal_level=archive
  2. Archive_mode=on
  3. archive_command = 'cp -i %p /data/pgsql/archived_wal/%f'
复制代码
l 流复制干系设置:
  1. max_wal_senders = '10'  #启动复制进程数量限制,必须大于0
  2. max_replication_slots = '10' #为使用replication slot,必须大于0;replication slot作用是保证wal没有同步到standby之前不能从pg_xlog移走;
  3. wal_keep_segments = '50'    #指定pg_xlog中最少保留的wal数量
  4. select pg_create_physical_replication_slot(‘gp1_a_slot'); #创建replication slot
  5. select * from pg_replication_slots;       #查询创建的replication slot
复制代码
l 编辑pg_hba.conf
  1. # Allow replication connections from localhost, by a user with the replication privilege.
  2. #host replication  postgres  127.0.0.1/32   trust
  3. #host replication  postgres  ::1/128     trust
  4. local replication  postgres        trust
  5. host replication  postgres  192.168.12.0/24   trust
复制代码
l 联机备份过程(底子备份)
  1. #touch /var/lib/pgsql/backup_in_progress
  2. $psql –c "select pg_start_backup('hot_backup');"
  3. $tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
  4. $psql -c "select pg_stop_backup();"
  5. #rm /var/lib/pgsql/backup_in_progress
  6. tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/ #打包归档
复制代码
实例:
  1. psql -c "select pg_start_backup('pgbk10');"
  2. tar -zcf pgbk10.tgz data/
  3. psql -c "select pg_stop_backup();"
复制代码
Standby:

l 编辑recovery.conf
  1. standby_mode = 'on'
  2. primary_conninfo = 'host=192.168.12.38 port=5666 user=postgres'
  3. primary_slot_name='gp1_a_slot'
  4. #restore_command = 'cp /data/pgsql/archived_wal/%f %p'
  5. #archive_cleanup_command = 'pg_archivecleanup /data/pgsql/archived_wal %r'
复制代码
l 将primary上的底子备份传输到standby上
  1. $scp primary: /var/lib/pgsql/backup.tar .
复制代码
解压备份到standby上的$PGDATA
l 启动standby
  1. $pg_ctl start –D $PGDATA
复制代码
启动standby后,postgres开始从primary上吸收wal日记进行恢复,而且不停保持恢复状态,psql不能登录;
以上为个人履历,渴望能给大家一个参考,也渴望大家多多支持脚本之家。如有错误或未思量完全的地方,望不吝赐教。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作