PostgreSQL 12 的同步流復(fù)制搭建及主庫Hang問題處理與分析
本文轉(zhuǎn)載自微信公眾號「數(shù)據(jù)和云」，作者王鑫。轉(zhuǎn)載本文請聯(lián)系數(shù)據(jù)和云公眾號。
前言
主備流復(fù)制，是PostgreSQL最常用、最簡單的一種高可用、讀寫分離的形式，類似于Oracle的ADG，主庫用于讀寫，備庫可以只讀。
PostgreSQL流復(fù)制，有兩種方式，分別是異步流復(fù)制和同步流復(fù)制。
- 異步流復(fù)制模式中，當(dāng)備庫出現(xiàn)異常，主庫可以正常的進(jìn)行讀寫;
- 同步流復(fù)制模式中，當(dāng)備庫出現(xiàn)異常，主庫則可能會hang住(DML、DDL)。
這兩天根據(jù)需求，我需要搭建一套同步流復(fù)制庫，但是在過程中遇到了一些小問題，所以記錄下來，作為備忘以及指導(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ù)制核心步驟非常簡單，只需要2-3步：
1. 對源端數(shù)據(jù)庫進(jìn)行基礎(chǔ)備份;
2. 將基礎(chǔ)備份拷貝到目標(biāo)端;
3. 配置備庫參數(shù)，并啟動備庫
那么在基礎(chǔ)備份中，我們直接在目標(biāo)端，通過pg_basebackup工具對源端數(shù)據(jù)庫進(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)沒有recovery.conf文件了，而是用standby.signal文件所代替，且原來需要在recovery.conf文件中配置的primary_conninfo參數(shù)，已經(jīng)融合在postgresql.conf中。
所以，此時我們只需要配置主庫和備庫的postgresql.conf，以及備庫的standby.signal即可，具體如下：
備庫的standby.signal中：
- standby_mode = 'on'
主庫的postgresql.conf中：
- #同步流復(fù)制
- synchronous_standby_names = 'standbydb1' #同步流復(fù)制才配置該值
- synchronous_commit = 'remote_write'
備庫的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'
配置完成后，直接啟動備庫即可。
- pg_ctl -D /pgdata start
三、發(fā)現(xiàn)問題
通過ps -ef|grep postgres，已經(jīng)可以看到主庫和備庫的walsender和walreciver進(jìn)程都已經(jīng)啟動，流復(fù)制已經(jīng)搭建好。
但是，通過pg_stat_replication進(jìn)行查看，發(fā)現(xiàn)當(dāng)前數(shù)據(jù)庫狀態(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
且此時，在進(jìn)行DML或者DDL操作的時候，主庫會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.
但是，通過pg_stat_replication及數(shù)據(jù)比對，發(fā)現(xiàn)主庫的wal已經(jīng)全部都寫入到備庫中。
所以，起初我一直以為是因為備庫中的wal或者disk的相關(guān)寫配置參數(shù)有一些問題，并且進(jìn)行了多次修改。
但是始終無法改變備庫的狀態(tài)為同步流復(fù)制(也嘗試多次設(shè)置synchronous_commit參數(shù)，但是只有當(dāng)該參數(shù)為local的時候，主庫才不會hang住)。
四、原因分析
經(jīng)過近一天的資料的查詢和官方文檔的搭建標(biāo)準(zhǔn)參數(shù)設(shè)置方法查詢，發(fā)現(xiàn)在網(wǎng)上一些帖子中，需要修改postgresql.auto.conf參數(shù)文件。我們知道，postgresql.auto.conf參數(shù)文件是一個動態(tài)參數(shù)文件，一般我們不會手動去修改它，而是通過數(shù)據(jù)庫中alter system set parameter_name=values的方式來修改該文件中的參數(shù)。
但是，另外一個需要知道的點就是，postgresql.auto.conf的優(yōu)先級要高于postgresql.conf，當(dāng)我們啟動數(shù)據(jù)庫的時候，postgresql會先去postgresql.auto.conf中加載參數(shù)，當(dāng)該文件中沒有相應(yīng)的參數(shù)時，則會加載postgresql.conf中的參數(shù)。
所以，此時我進(jìn)入數(shù)據(jù)庫對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ù)庫中的參數(shù)可以看到，此時primary_conninfo加載的參數(shù)，是異步流復(fù)制的參數(shù)，而不是我們在postgresql.conf中配置的同步流復(fù)制的參數(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ù)。
此時，我們就能理解最開始出現(xiàn)的異常的原因了。
其實，在PostgreSQL中通過pg_basebackup進(jìn)行基礎(chǔ)備份的時候(由于加了-R參數(shù))，默認(rèn)就會在postgresql.auto.conf文件中加入primary_conninfo參數(shù)，且該參數(shù)為異步流復(fù)制的參數(shù)(其實我們可以不用在postgresql.conf中配置primary_conninfo參數(shù)了)。
但是，該文件中只有primary_conninfo參數(shù)，當(dāng)我們想要將流復(fù)制搭建為同步的方式的時候，必須配置兩個參數(shù)：synchronous_standby_names和synchronous_commit。當(dāng)我們在postgresql.conf中設(shè)置這兩個參數(shù)后，由于postgresql.auto.conf中沒有，所以只能到這里取。
此時數(shù)據(jù)庫在進(jìn)行DML和DDL操作的時候，則會等待備庫的響應(yīng)，但備庫又為異步流復(fù)制，所以不會給主庫回復(fù)wal已經(jīng)接收到或者寫入磁盤。因此，主庫會一直hang住。
五、解決方案
解決該問題，則有以下幾個方式：
1. 在備庫上通過命令：
- 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ù)，并重啟備庫即可。
2. 手動修改postgresql.auto.conf文件，將primary_conninfo參數(shù)手動改為同步流復(fù)制參數(shù)，并重啟備庫。
3. 備庫上通過命令：
- alter system set primary_conninfo = default;
清空postgresql.auto.conf中primary_conninfo參數(shù)的配置，并重啟備庫，讓數(shù)據(jù)庫識別postgresql.conf文件中的參數(shù)。
4. 手動刪除postgresql.auto.conf文件，將primary_conninfo參數(shù)手動刪除或者注釋，并重啟備庫，讓數(shù)據(jù)庫識別postgresql.conf文件中的參數(shù)。
搞定!
