[[411315]]

本文轉(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中。

所以，此時(shí)我們只需要配置主庫和備庫的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 

且此時(shí)，在進(jìn)行DML或者DDL操作的時(shí)候，主庫會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)全部都寫入到備庫中。

所以，起初我一直以為是因?yàn)閭鋷熘械膚al或者disk的相關(guān)寫配置參數(shù)有一些問題，并且進(jìn)行了多次修改。

但是始終無法改變備庫的狀態(tài)為同步流復(fù)制(也嘗試多次設(shè)置synchronous_commit參數(shù)，但是只有當(dāng)該參數(shù)為local的時(shí)候，主庫才不會hang住)。

四、原因分析

經(jīng)過近一天的資料的查詢和官方文檔的搭建標(biāo)準(zhǔn)參數(shù)設(shè)置方法查詢，發(fā)現(xiàn)在網(wǎng)上一些帖子中，需要修改postgresql.auto.conf參數(shù)文件。我們知道，postgresql.auto.conf參數(shù)文件是一個(gè)動態(tài)參數(shù)文件，一般我們不會手動去修改它，而是通過數(shù)據(jù)庫中alter system set parameter_name=values的方式來修改該文件中的參數(shù)。

但是，另外一個(gè)需要知道的點(diǎn)就是，postgresql.auto.conf的優(yōu)先級要高于postgresql.conf，當(dāng)我們啟動數(shù)據(jù)庫的時(shí)候，postgresql會先去postgresql.auto.conf中加載參數(shù)，當(dāng)該文件中沒有相應(yīng)的參數(shù)時(shí)，則會加載postgresql.conf中的參數(shù)。

所以，此時(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ù)可以看到，此時(shí)primary_conninfo加載的參數(shù)，是異步流復(fù)制的參數(shù)，而不是我們在postgresql.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í)，我們就能理解最開始出現(xiàn)的異常的原因了。

其實(shí)，在PostgreSQL中通過pg_basebackup進(jìn)行基礎(chǔ)備份的時(shí)候(由于加了-R參數(shù))，默認(rèn)就會在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)我們在postgresql.conf中設(shè)置這兩個(gè)參數(shù)后，由于postgresql.auto.conf中沒有，所以只能到這里取。

此時(shí)數(shù)據(jù)庫在進(jìn)行DML和DDL操作的時(shí)候，則會等待備庫的響應(yīng)，但備庫又為異步流復(fù)制，所以不會給主庫回復(fù)wal已經(jīng)接收到或者寫入磁盤。因此，主庫會一直hang住。

五、解決方案

解決該問題，則有以下幾個(gè)方式：

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ù)。

搞定!