PostgreSQL流復(fù)制參數(shù)max_wal_senders的用法說明
環(huán)境：
PostgreSQL 9.2.4
主機:192.25.10.76
從機:192.25.10.71
做postgresql的流復(fù)制主從時，會遇到調(diào)整max_wal_sengers這個參數(shù)，官方文檔對這個參數(shù)做了一個簡要的說明(9.2.4比早先版本多了幾句話并做了一些微調(diào))，但沒有實際的例子。
1.參數(shù)說明：
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.
也就是說，這個參數(shù)是在主機上設(shè)置的，是從機連接到主機的并發(fā)連接數(shù)之總和，所以這個參數(shù)是個正整型。默認(rèn)值是0，也即默認(rèn)沒有流復(fù)制功能。該并發(fā)連接數(shù)從進程上看，就是各個wal sender進程數(shù)之和，可以通過ps -ef|grep senders來查看，所以該值不能超過系統(tǒng)的最大連接數(shù)(max_connections，該BUG在9.1.5被修復(fù))，可以允許超過實際的流復(fù)制用戶數(shù)。該參數(shù)更改需要重啟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.異常情況
很多時候配置主從的時候會遺漏這個參數(shù)，或者沒有設(shè)置正確(比如實際配的從機數(shù)超過設(shè)置的連接數(shù))，這個時候一般會報錯 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參數(shù)了。
3.參數(shù)生效
文檔上說明需要重啟DB使之生效，如果動態(tài)使之生效會報錯如下：
[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基于流復(fù)制 (streaming replication)的warm-standby
實例一枚：
Primary:
l 歸檔設(shè)置：
Wal_level=archive Archive_mode=on archive_command = 'cp -i %p /data/pgsql/archived_wal/%f'
l 流復(fù)制相關(guān)設(shè)置：
max_wal_senders = '10' #啟動復(fù)制進程數(shù)量限制，必須大于0 max_replication_slots = '10' #為使用replication slot，必須大于0；replication slot作用是保證wal沒有同步到standby之前不能從pg_xlog移走； wal_keep_segments = '50' #指定pg_xlog中最少保留的wal數(shù)量 select pg_create_physical_replication_slot(‘gp1_a_slot'); #創(chuàng)建replication slot select * from pg_replication_slots; #查詢創(chuàng)建的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 聯(lián)機備份過程（基礎(chǔ)備份）
#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上的基礎(chǔ)備份傳輸?shù)絪tandby上
$scp primary: /var/lib/pgsql/backup.tar .
解壓備份到standby上的$PGDATA
l 啟動standby
$pg_ctl start –D $PGDATA
啟動standby后，postgres開始從primary上接收wal日志進行恢復(fù)，并且一直保持恢復(fù)狀態(tài)，psql不能登錄；
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
Postgresql設(shè)置遠(yuǎn)程訪問的方法(需要設(shè)置防火墻或者關(guān)閉防火墻)
這篇文章主要介紹了Postgresql設(shè)置遠(yuǎn)程訪問的方法(需要設(shè)置防火墻或者關(guān)閉防火墻),本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-03-03
Postgresql?REGEXP開頭的正則函數(shù)用法圖文詳解
正則表達式是指一個用來描述或者匹配一系列符合某個句法規(guī)則的字符串的單個字符串,下面這篇文章主要給大家介紹了關(guān)于Postgresql?REGEXP開頭的正則函數(shù)用法的相關(guān)資料,需要的朋友可以參考下2024-02-02
PostgreSQL upsert(插入更新)數(shù)據(jù)的操作詳解
這篇文章主要介紹了PostgreSQL upsert(插入更新)教程詳解,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-01-01
這篇文章主要給大家介紹了關(guān)于Postgresql備份和增量恢復(fù)的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用Postgresql具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-10-10
這篇文章主要介紹了基于PostgreSQL 權(quán)限解讀，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgreSQL報錯 解決操作符不存在的問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
很多小伙伴在把mysql數(shù)據(jù)庫里面的表導(dǎo)入pgsql數(shù)據(jù)庫的時候,會遇到新增數(shù)據(jù)的時候id不自增,這篇文章主要給大家介紹了關(guān)于PostgreSQL中實現(xiàn)自增的三種方式,需要的朋友可以參考下2024-02-02
postgreSQL使用pgAdmin備份服務(wù)器數(shù)據(jù)的方法
這篇文章主要介紹了postgreSQL使用pgAdmin備份服務(wù)器數(shù)據(jù)的方法,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-02-02
這篇文章主要介紹了postgresql SQL語句變量的使用說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql 切換 log、xlog日志的實現(xiàn)
這篇文章主要介紹了postgresql 切換 log、xlog日志的實現(xiàn)方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01