PostgreSQL 12 的同步流復(fù)制搭建及主庫(kù)Hang問(wèn)題處理與分析
本文轉(zhuǎn)載自微信公眾號(hào)「數(shù)據(jù)和云」，作者王鑫。轉(zhuǎn)載本文請(qǐng)聯(lián)系數(shù)據(jù)和云公眾號(hào)。
前言
主備流復(fù)制，是PostgreSQL最常用、最簡(jiǎn)單的一種高可用、讀寫(xiě)分離的形式，類(lèi)似于Oracle的ADG，主庫(kù)用于讀寫(xiě)，備庫(kù)可以只讀。
PostgreSQL流復(fù)制，有兩種方式，分別是異步流復(fù)制和同步流復(fù)制。
- 異步流復(fù)制模式中，當(dāng)備庫(kù)出現(xiàn)異常，主庫(kù)可以正常的進(jìn)行讀寫(xiě);
- 同步流復(fù)制模式中，當(dāng)備庫(kù)出現(xiàn)異常，主庫(kù)則可能會(huì)hang住(DML、DDL)。
這兩天根據(jù)需求，我需要搭建一套同步流復(fù)制庫(kù)，但是在過(guò)程中遇到了一些小問(wèn)題，所以記錄下來(lái)，作為備忘以及指導(dǎo)，幫助大家避坑。
一、基本環(huán)境
本次搭建，我的PostgreSQL基本環(huán)境如下：
- PostgreSQL版本為12.5;
- 操作系統(tǒng)為CentOS 7.6;
- Server: 192.168.18.181:18801
- Slave: 192.168.18.182:18802
二、同步流復(fù)制搭建
我們知道，搭建PostgreSQL的流復(fù)制核心步驟非常簡(jiǎn)單，只需要2-3步：
1. 對(duì)源端數(shù)據(jù)庫(kù)進(jìn)行基礎(chǔ)備份;
2. 將基礎(chǔ)備份拷貝到目標(biāo)端;
3. 配置備庫(kù)參數(shù)，并啟動(dòng)備庫(kù)
那么在基礎(chǔ)備份中，我們直接在目標(biāo)端，通過(guò)pg_basebackup工具對(duì)源端數(shù)據(jù)庫(kù)進(jìn)行基礎(chǔ)備份并將數(shù)據(jù)目錄直接放在目標(biāo)端的/pgdata中：
- pg_basebackup -h 192.168.18.181 -p 18801 -U repl -l pg_basebackup_`date +%Y%m%d%H%M%S` -Fp -X fetch -P -v -R -D /pgdata
在PostgreSQL 12中，已經(jīng)沒(méi)有recovery.conf文件了，而是用standby.signal文件所代替，且原來(lái)需要在recovery.conf文件中配置的primary_conninfo參數(shù)，已經(jīng)融合在postgresql.conf中。
所以，此時(shí)我們只需要配置主庫(kù)和備庫(kù)的postgresql.conf，以及備庫(kù)的standby.signal即可，具體如下：
備庫(kù)的standby.signal中：
- standby_mode = 'on'
主庫(kù)的postgresql.conf中：
- #同步流復(fù)制
- synchronous_standby_names = 'standbydb1' #同步流復(fù)制才配置該值
- synchronous_commit = 'remote_write'
備庫(kù)的postgresql.conf中：
- hot_standby = 'on'
- primary_conninfo = 'application_name=standbydb1 user=repl password=repl123 host=192.168.18.181 port=18801 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
配置完成后，直接啟動(dòng)備庫(kù)即可。
- pg_ctl -D /pgdata start
三、發(fā)現(xiàn)問(wèn)題
通過(guò)ps -ef|grep postgres，已經(jīng)可以看到主庫(kù)和備庫(kù)的walsender和walreciver進(jìn)程都已經(jīng)啟動(dòng)，流復(fù)制已經(jīng)搭建好。
但是，通過(guò)pg_stat_replication進(jìn)行查看，發(fā)現(xiàn)當(dāng)前數(shù)據(jù)庫(kù)狀態(tài)仍然還是異步流復(fù)制狀態(tài)：
- postgres=# select * from pg_stat_replication;
- -[ RECORD 2 ]----+------------------------------
- pid | 11767
- usesysid | 24746
- usename | repl
- application_name | walreceiver
- client_addr | 192.168.18.182
- client_hostname |
- client_port | 29946
- backend_start | 2021-01-17 22:48:36.529698+08
- backend_xmin |
- state | streaming
- sent_lsn | 0/91000148
- write_lsn | 0/91000148
- flush_lsn | 0/91000148
- replay_lsn | 0/91000148
- write_lag |
- flush_lag |
- replay_lag |
- sync_priority | 0
- sync_state | async
- reply_time | 2021-01-17 22:48:46.545856+08
且此時(shí)，在進(jìn)行DML或者DDL操作的時(shí)候，主庫(kù)會(huì)hang住。
- postgres=# insert into wangxin1 values (1,'aaa');
- ^CCancel request sent
- WARNING: canceling wait for synchronous replication due to user request
- DETAIL: The transaction has already committed locally, but might not have been replicated to the standby.
但是，通過(guò)pg_stat_replication及數(shù)據(jù)比對(duì)，發(fā)現(xiàn)主庫(kù)的wal已經(jīng)全部都寫(xiě)入到備庫(kù)中。
所以，起初我一直以為是因?yàn)閭鋷?kù)中的wal或者disk的相關(guān)寫(xiě)配置參數(shù)有一些問(wèn)題，并且進(jìn)行了多次修改。
但是始終無(wú)法改變備庫(kù)的狀態(tài)為同步流復(fù)制(也嘗試多次設(shè)置synchronous_commit參數(shù)，但是只有當(dāng)該參數(shù)為local的時(shí)候，主庫(kù)才不會(huì)hang住)。
四、原因分析
經(jīng)過(guò)近一天的資料的查詢(xún)和官方文檔的搭建標(biāo)準(zhǔn)參數(shù)設(shè)置方法查詢(xún)，發(fā)現(xiàn)在網(wǎng)上一些帖子中，需要修改postgresql.auto.conf參數(shù)文件。我們知道，postgresql.auto.conf參數(shù)文件是一個(gè)動(dòng)態(tài)參數(shù)文件，一般我們不會(huì)手動(dòng)去修改它，而是通過(guò)數(shù)據(jù)庫(kù)中alter system set parameter_name=values的方式來(lái)修改該文件中的參數(shù)。
但是，另外一個(gè)需要知道的點(diǎn)就是，postgresql.auto.conf的優(yōu)先級(jí)要高于postgresql.conf，當(dāng)我們啟動(dòng)數(shù)據(jù)庫(kù)的時(shí)候，postgresql會(huì)先去postgresql.auto.conf中加載參數(shù)，當(dāng)該文件中沒(méi)有相應(yīng)的參數(shù)時(shí)，則會(huì)加載postgresql.conf中的參數(shù)。
所以，此時(shí)我進(jìn)入數(shù)據(jù)庫(kù)對(duì)primary_conninfo參數(shù)進(jìn)行查看：
- show primary_conninfo
- primary_conninfo | 'user=repl password=repl123 host=192.168.18.181 port=18801 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
從數(shù)據(jù)庫(kù)中的參數(shù)可以看到，此時(shí)primary_conninfo加載的參數(shù)，是異步流復(fù)制的參數(shù)，而不是我們?cè)趐ostgresql.conf中配置的同步流復(fù)制的參數(shù)。
此時(shí)，我們到postgresql.auto.conf中查看：
- cat postgresql.auto.conf
- # Do not edit this file manually!
- # It will be overwritten by the ALTER SYSTEM command.
- primary_conninfo = 'user=repl password=repl123 host=192.168.18.181 port=18801 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
可以看到，在postgresql.auto.conf中有參數(shù)primary_conninfo的設(shè)置，且為異步流復(fù)制的配置參數(shù)。
此時(shí)，我們就能理解最開(kāi)始出現(xiàn)的異常的原因了。
其實(shí)，在PostgreSQL中通過(guò)pg_basebackup進(jìn)行基礎(chǔ)備份的時(shí)候(由于加了-R參數(shù))，默認(rèn)就會(huì)在postgresql.auto.conf文件中加入primary_conninfo參數(shù)，且該參數(shù)為異步流復(fù)制的參數(shù)(其實(shí)我們可以不用在postgresql.conf中配置primary_conninfo參數(shù)了)。
但是，該文件中只有primary_conninfo參數(shù)，當(dāng)我們想要將流復(fù)制搭建為同步的方式的時(shí)候，必須配置兩個(gè)參數(shù)：synchronous_standby_names和synchronous_commit。當(dāng)我們?cè)趐ostgresql.conf中設(shè)置這兩個(gè)參數(shù)后，由于postgresql.auto.conf中沒(méi)有，所以只能到這里取。
此時(shí)數(shù)據(jù)庫(kù)在進(jìn)行DML和DDL操作的時(shí)候，則會(huì)等待備庫(kù)的響應(yīng)，但備庫(kù)又為異步流復(fù)制，所以不會(huì)給主庫(kù)回復(fù)wal已經(jīng)接收到或者寫(xiě)入磁盤(pán)。因此，主庫(kù)會(huì)一直hang住。
五、解決方案
解決該問(wèn)題，則有以下幾個(gè)方式：
1. 在備庫(kù)上通過(guò)命令：
- alter system set primary_conninfo = 'application_name=standbydb1 user=repl password=repl123 host=192.168.18.181 port=18801 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any';
設(shè)置postgresql.auto.conf中primary_conninfo參數(shù)，并重啟備庫(kù)即可。
2. 手動(dòng)修改postgresql.auto.conf文件，將primary_conninfo參數(shù)手動(dòng)改為同步流復(fù)制參數(shù)，并重啟備庫(kù)。
3. 備庫(kù)上通過(guò)命令：
- alter system set primary_conninfo = default;
清空postgresql.auto.conf中primary_conninfo參數(shù)的配置，并重啟備庫(kù)，讓數(shù)據(jù)庫(kù)識(shí)別postgresql.conf文件中的參數(shù)。
4. 手動(dòng)刪除postgresql.auto.conf文件，將primary_conninfo參數(shù)手動(dòng)刪除或者注釋?zhuān)⒅貑鋷?kù)，讓數(shù)據(jù)庫(kù)識(shí)別postgresql.conf文件中的參數(shù)。
搞定!
