基于PostgreSQL流復制的容災庫架構設想及實現

作者：王鑫 2021-07-07 21:07:16

我們知道在PostgreSQL中，其mvcc機制并不像Oracle或者MySQL一樣，將舊版本數據存放在另外的空間中，而是通過對事務號(xid)的控制對舊版本數據不可見的方式進行實現。所以PostgreSQL中無法實現類似于Oracle的閃回機制。

[[409992]]

本文轉載自微信公眾號「數據和云」，作者王鑫。轉載本文請聯系數據和云公眾號。

一、前言

這幾天在對PostgreSQL流復制的架構進行深入研究，其中一個關鍵的參數：recovery_min_apply_delay引起了我的注意，設置該參數的大概意思是：在進行流復制的時候，備庫會延遲主庫recovery_min_apply_delay的時間進行應用。比如說，我們在主庫上insert10條數據，不會立即在備庫上生效，而是在recovery_min_apply_delay的時間后，備庫才能完成應用。

另外，我們知道在PostgreSQL中，其mvcc機制并不像Oracle或者MySQL一樣，將舊版本數據存放在另外的空間中，而是通過對事務號(xid)的控制對舊版本數據不可見的方式進行實現。所以PostgreSQL中無法實現類似于Oracle的閃回機制。

在日常操作過程中，對表進行delete、truncate、drop等誤操作都不能通過閃回來快速恢復。不怕一萬，就怕萬一，在做數據庫維護的6年多里，遇到過的誤操作還是很多。那么在PostgreSQL這種無法實現閃回的數據庫中，如果出現誤操作如何快速恢復呢?

二、架構簡介

對于PostgreSQL數據庫這種無法進行閃回的數據庫來講，最常用的辦法就是通過備份+歸檔的方式進行數據恢復。但是這種恢復方式也有弊端，當數據庫非常大時，恢復全量備份也會非常的慢，而且如果全量備份是一周前或者更久前的，那么恢復歸檔也會需要比較長的時間。這段時間內，可能業務就會長時間停擺，造成一定的損失。

如果通過流復制延遲特性作為生產數據庫的容災庫，則可以從一定程度上解決該問題，其簡單架構如下：

三、恢復步驟

PostgreSQL流復制容災庫架構的誤操作恢復步驟如下：

1.主庫出現誤操作，查看流復制的replay狀態;

2.在recovery_min_apply_delay時間內，暫停備庫的replay;

3.判斷主庫出現的誤操作類型(delete/truncate/drop);

4.根據主庫誤操作類型，對備庫進行相應的操作;

5.通過pg_dump將誤操作表導出;

6.在主庫對pg_dump出的表進行恢復。

假設當前備庫與主庫相差10min，則誤操作可以分為以下兩個場景：

1)delete操作：

首先我們需要知道的是，針對delete操作，PostgreSQL會給相關表加一個ROW EXCLUSIVE鎖，而該鎖不會對select等dql操作進行阻塞。

所以當我們在主庫進行delete誤操作后，備庫則會晚10min中進行replay。且此時可以對該表進行查詢和pg_dump的導出。針對于主庫delete誤操作，恢復步驟如下：

第一步，查看流復制replay的狀態，重點關注replay_lsn字段：

select * from pg_stat_replication； 
postgres=# select * from pg_stat_replication; 
-[ RECORD 1 ]----+------------------------------ 
pid              | 55694 
usesysid         | 24746 
usename          | repl 
application_name | walreceiver 
client_addr      | 192.168.18.82 
client_hostname  |  
client_port      | 31550 
backend_start    | 2021-01-20 09:54:57.039779+08 
backend_xmin     |  
state            | streaming 
sent_lsn         | 6/D2A17120 
write_lsn        | 6/D2A17120 
flush_lsn        | 6/D2A17120 
replay_lsn       | 6/D2A170B8 
write_lag        | 00:00:00.000119 
flush_lag        | 00:00:00.000239 
replay_lag       | 00:00:50.653858 
sync_priority    | 0 
sync_state       | async 
reply_time       | 2021-01-20 14:11:31.704194+08

此時可以發現數據庫中的replay_lsn字段的lsn值要比sent_lsn/write_lsn/flush_lsn都要小;

第二步，為了防止處理或者導出時間過慢而導致的數據同步，立即暫停備庫的replay：

select * from pg_wal_replay_pause();

查看同步狀態：

postgres=# select * from pg_is_wal_replay_paused();   
 
 pg_is_wal_replay_paused  
------------------------- 
 t 
(1 row)

第三步，在備庫查看數據是否存在：

select * from wangxin1;

第四步，通過pg_dump，將表內容導出：

pg_dump -h 192.168.18.182 -p 18802 -d postgres -U postgres -t wangxin1 --data-only --inserts -f wangxin1_data_only.sql

第五步，在主庫執行sql文件，將數據重新插入：

psql -p 18801 
\i wangxin1_data_only.sql

恢復即完成。

2)truncate和drop：

這里首先需要知道的是，truncate和drop操作會給表加上一個access exclusive鎖，該類型鎖是PostgreSQL數據庫中最嚴重的鎖。如果表上有該鎖，則會阻止所有對該此表的訪問操作，其中也包括select和pg_dump操作。

所以說，在我們對主庫中的某張表進行truncate或者drop后，同樣，備庫會由于recovery_min_apply_delay參數比主庫晚完成truncate或drop動作10min(從參數理論上是這樣理解的，但實際并不是)。

那么針對truncate和drop的恢復過程我們也參考delete的方式來進行：

-[ RECORD 2 ]----+------------------------------ 
pid              | 67008 
usesysid         | 24746 
usename          | repl 
application_name | walreceiver 
client_addr      | 192.168.18.82 
client_hostname  |  
client_port      | 32122 
backend_start    | 2021-01-20 23:33:05.538858+08 
backend_xmin     |  
state            | streaming 
sent_lsn         | 7/3F0593E0 
write_lsn        | 7/3F0593E0 
flush_lsn        | 7/3F0593E0 
replay_lsn       | 7/3F059330 
write_lag        | 00:00:00.000141 
flush_lag        | 00:00:00.000324 
replay_lag       | 00:00:11.471699 
sync_priority    | 0 
sync_state       | async 
reply_time       | 2021-01-20 23:33:58.303686+08

接下來，為防止處理或導出時間過慢而導致的數據同步，應立即暫停備庫的replay：

select * from pg_wal_replay_pause();

查看同步狀態：

postgres=# select * from pg_is_wal_replay_paused();   
 
 pg_is_wal_replay_paused  
------------------------- 
 t 
(1 row)

接著，在備庫查看數據是否存在：

select * from wangxin1;

但是，此時就會發現問題：數據無法select出來，整個select進程會卡住(pg_dump也一樣)：

^CCancel request sent 
ERROR:  canceling statement due to user request

此時，可以對備庫上的鎖信息進行查詢：

select s.pid, 
s.datname, 
s.usename, 
l.relation::regclass, 
s.client_addr, 
now()-s.query_start, 
s.wait_event, 
s.wait_event_type, 
l.granted, 
l.mode, 
s.query 
from pg_stat_activity s ,pg_locks l 
where s.pid<>pg_backend_pid() 
and s.pid=l.pid; 
 
  pid  | datname | usename | relation | client_addr | ?column? |     wait_event     | wait_event_type | granted |        mode         | query  
-------+---------+---------+----------+-------------+----------+--------------------+-----------------+---------+---------------------+------- 
 55689 |         |         |          |             |          | RecoveryApplyDelay | Timeout         | t       | ExclusiveLock       |  
 55689 |         |         | wangxin1 |             |          | RecoveryApplyDelay | Timeout         | t       | AccessExclusiveLock |  
(2 rows)

發現此時truncate的表被鎖住了，而pid進程則是備庫的recover進程，所以此時我們根本無法訪問該表，也就無法做pg_dump操作了。

因此，想要恢復則必須想辦法將數據庫還原到鎖表之前的操作。于是對PostgreSQL的wal日志進行分析查看：

pg_waldump -p /pgdata/pg_wal -s 7/3F000000 
 
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 7/3F050D70, prev 7/3F050D40, desc: RUNNING_XACTS nextXid 13643577 latestCompletedXid 13643576 oldestRunningXid 13643577 
rmgr: Heap2       len (rec/tot):     60/    60, tx:   13643577, lsn: 7/3F050DA8, prev 7/3F050D70, desc: NEW_CID rel 1663/13593/2619; tid 20/27; cmin: 4294967295, cmax: 0, combo: 4294967295 
rmgr: Heap2       len (rec/tot):     60/    60, tx:   13643577, lsn: 7/3F050DE8, prev 7/3F050DA8, desc: NEW_CID rel 1663/13593/2619; tid 20/23; cmin: 0, cmax: 4294967295, combo: 4294967295 
rmgr: Heap        len (rec/tot):     65/  6889, tx:   13643577, lsn: 7/3F050E28, prev 7/3F050DE8, desc: HOT_UPDATE off 27 xmax 13643577 flags 0x00 ; new off 23 xmax 0, blkref #0: rel 1663/13593/2619 blk 20 FPW 
rmgr: Heap2       len (rec/tot):     60/    60, tx:   13643577, lsn: 7/3F052930, prev 7/3F050E28, desc: NEW_CID rel 1663/13593/2619; tid 20/28; cmin: 4294967295, cmax: 0, combo: 4294967295 
rmgr: Heap2       len (rec/tot):     60/    60, tx:   13643577, lsn: 7/3F052970, prev 7/3F052930, desc: NEW_CID rel 1663/13593/2619; tid 20/24; cmin: 0, cmax: 4294967295, combo: 4294967295 
rmgr: Heap        len (rec/tot):     76/    76, tx:   13643577, lsn: 7/3F0529B0, prev 7/3F052970, desc: HOT_UPDATE off 28 xmax 13643577 flags 0x20 ; new off 24 xmax 0, blkref #0: rel 1663/13593/2619 blk 20 
rmgr: Heap        len (rec/tot):     53/  7349, tx:   13643577, lsn: 7/3F052A00, prev 7/3F0529B0, desc: INPLACE off 13, blkref #0: rel 1663/13593/1259 blk 1 FPW 
rmgr: Transaction len (rec/tot):    130/   130, tx:   13643577, lsn: 7/3F0546D0, prev 7/3F052A00, desc: COMMIT 2021-01-20 23:31:23.009466 CST; inval msgs: catcache 58 catcache 58 catcache 50 catcache 49 relcache 24780 
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 7/3F054758, prev 7/3F0546D0, desc: RUNNING_XACTS nextXid 13643578 latestCompletedXid 13643577 oldestRunningXid 13643578 
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 7/3F054790, prev 7/3F054758, desc: RUNNING_XACTS nextXid 13643578 latestCompletedXid 13643577 oldestRunningXid 13643578 
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 7/3F0547C8, prev 7/3F054790, desc: CHECKPOINT_ONLINE redo 7/3F054790; tli 1; prev tli 1; fpw true; xid 0:13643578; oid 33072; multi 1; offset 0; oldest xid 479 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 13643578; online 
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 7/3F054840, prev 7/3F0547C8, desc: RUNNING_XACTS nextXid 13643578 latestCompletedXid 13643577 oldestRunningXid 13643578 
rmgr: Standby     len (rec/tot):     42/    42, tx:   13643578, lsn: 7/3F054878, prev 7/3F054840, desc: LOCK xid 13643578 db 13593 rel 24780  
rmgr: Storage     len (rec/tot):     42/    42, tx:   13643578, lsn: 7/3F0548A8, prev 7/3F054878, desc: CREATE base/13593/24885 
rmgr: Heap2       len (rec/tot):     60/    60, tx:   13643578, lsn: 7/3F0548D8, prev 7/3F0548A8, desc: NEW_CID rel 1663/13593/1259; tid 1/13; cmin: 4294967295, cmax: 0, combo: 4294967295 
rmgr: Heap2       len (rec/tot):     60/    60, tx:   13643578, lsn: 7/3F054918, prev 7/3F0548D8, desc: NEW_CID rel 1663/13593/1259; tid 1/14; cmin: 0, cmax: 4294967295, combo: 4294967295 
rmgr: Heap        len (rec/tot):     65/  7537, tx:   13643578, lsn: 7/3F054958, prev 7/3F054918, desc: UPDATE off 13 xmax 13643578 flags 0x00 ; new off 14 xmax 0, blkref #0: rel 1663/13593/1259 blk 1 FPW 
rmgr: Heap2       len (rec/tot):     76/    76, tx:   13643578, lsn: 7/3F0566E8, prev 7/3F054958, desc: CLEAN remxid 13642576, blkref #0: rel 1663/13593/1259 blk 1 
rmgr: Btree       len (rec/tot):     53/  3573, tx:   13643578, lsn: 7/3F056738, prev 7/3F0566E8, desc: INSERT_LEAF off 141, blkref #0: rel 1663/13593/2662 blk 2 FPW 
rmgr: Btree       len (rec/tot):     53/  5349, tx:   13643578, lsn: 7/3F057530, prev 7/3F056738, desc: INSERT_LEAF off 117, blkref #0: rel 1663/13593/2663 blk 2 FPW 
rmgr: Btree       len (rec/tot):     53/  2253, tx:   13643578, lsn: 7/3F058A30, prev 7/3F057530, desc: INSERT_LEAF off 108, blkref #0: rel 1663/13593/3455 blk 4 FPW 
rmgr: Heap        len (rec/tot):     42/    42, tx:   13643578, lsn: 7/3F059300, prev 7/3F058A30, desc: TRUNCATE nrelids 1 relids 24780 
rmgr: Transaction len (rec/tot):    114/   114, tx:   13643578, lsn: 7/3F059330, prev 7/3F059300, desc: COMMIT 2021-01-20 23:33:46.831804 CST; rels: base/13593/24884; inval msgs: catcache 50 catcache 49 relcache 24780 
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 7/3F0593A8, prev 7/3F059330, desc: RUNNING_XACTS nextXid 13643579 latestCompletedXid 13643578 oldestRunningXid 13643579 
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 7/3F0593E0, prev 7/3F0593A8, desc: RUNNING_XACTS nextXid 13643579 latestCompletedXid 13643578 oldestRunningXid 13643579 
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn:

從wal日志的分析中，可以非常明顯的看到，在最后一次checkpoint點后(恢復的起始點)，正常來說，數據庫會繼續執行lsn為7/3F054840的步驟開啟事務，并在下一步lsn為7/3F054878的步驟直接對oid為24780(通過oid2name可以知道，這張表就是我們誤操作表)的表進行lock操作，做一系列相關的操作后，進行了truncate，最后進行commit操作。

而這一系列操作，我們則可以認為是truncate一張表的正常操作。

由于我們知道checkpoint點是數據庫的恢復起始點，那么我們是否可以將數據庫恢復到這一點的lsn呢?此時的lsn肯定不會對表進行lock操作，那么我們就可以對該表進行pg_dump操作了。

想法是好的，但是實際操作則沒那么順利。我們可以通過對備庫PostgreSQL的配置文件進行修改，加入參數：

recovery_target_lsn= ‘7/3F0547C8’
recovery_target_action= ‘pause’

重啟數據庫。

此時卻發現數據庫無法啟動，通過對日志查看，發現原因竟然是：

這個恢復點，是一致性恢復點之前的點，所以無法正常恢復。

此時就出現了令我們奇怪的點，我們知道checkpoint的兩個主要作用是：將臟數據進行刷盤;將wal日志的checkpoint進行記錄。此時，肯定是數據庫一致的點，但是為什么會報不一致呢?

經過一點一點的嘗試，發現能夠恢復的lsn點，只有truncate或者drop的commit操作的前面。那么這樣我們還是無法對誤操作表進行解鎖。

最后，只能通過一種方式，即pg_resetwal的方式，強制指定備庫恢復到我們想要的lsn點:

pg_resetwal -D data1 -x 559 Write-ahead log reset

再進行pg_dump即可。

但是，此時PostgreSQL的主備流復制關系已經被破壞，只能重新搭建或者以其他方式進行恢復(比如pg_rewind)。

四、問題分析

再次返回到進行truncate或drop的恢復步驟中，我們可以發現一個問題，為什么在checkpoint點后、truncate點前，無法將數據庫恢復到一致點呢?為什么會報錯呢?

按照常理來講，checkpoint點就是恢復數據庫的起始點，也是一致點，但是卻無法恢復了。

繼續進行詳細的探究后發現一個現象：

延遲流復制過程中，我們配置了recovery_min_apply_delay參數，對源端數據庫做truncate后，備庫replay的lsn，停留在truncate表后的commit操作。而從主庫的pg_stat_replication的replay_lsn值來看，此時備庫的recover進程，應該就是在執行最后的commit的lsn;

更形象的來說，此時備庫類似于我執行以下命令：

begin; 
 
truncate table;

也就是說，此時我并沒有提交，而備庫也正在等待我進行提交，所以此時誤操作表會被鎖定。

但實際上，truncate table這個動作，已經在我的備庫上進行了replay，只是最后的commit動作沒有進行replay。因此，對于truncate動作之前所有lsn的操作已經是我當前數據庫狀態的一個過去式，無法恢復了，故會報錯。

為了驗證想法，在大佬的幫助下，又對PostgreSQL的源碼進行查看，發現猜想原因確實沒錯：

在/src/backend/access/transam/xlog.c中，對于recovery_min_apply_delay參數有以下的一段描述：

/* 
* Is it a COMMIT record? 
* 
* We deliberately choose not to delay aborts since they have no effect on 
* MVCC. We already allow replay of records that don't have a timestamp, 
* so there is already opportunity for issues caused by early conflicts on 
* standbys. 
*/

大概意思是，當record中沒有時間戳(timestamp)的時候，數據庫就已經進行了replay。replay只會等待有時間戳的record，而所有的record中，只有commit操作有時間戳，故replay會等待一個commit操作。

不過在實際的生產環境中，我們通常會把recovery_min_apply_delay參數設置的較大，而在這之間，一般都會有一些其他的事務進行操作，當主庫出現誤操作(哪怕說truncate/drop)，只要及時發現，我們可以暫停replay的步驟，停在正常的事務操作下，此時誤操作的表的事務還沒有執行，那么這個容災庫還是比較有作用的。

責任編輯：武曉燕來源：數據和云

PostgreSQL 架構容災庫