色婷婷综合久久久久中文一区二区 ,玖玖色在线视频,国产精品成人国产乱一区

環境：

主機: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 ?  00:00:00 postgres: wal sender process repuser 192.25.10.71(46161) streaming 0/4018ED8

									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)：備機上顯示的日志異常:

									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'