[[409992]]

本文轉(zhuǎn)載自微信公眾號(hào)「數(shù)據(jù)和云」，作者王鑫。轉(zhuǎn)載本文請(qǐng)聯(lián)系數(shù)據(jù)和云公眾號(hào)。

一、前言

這幾天在對(duì)PostgreSQL流復(fù)制的架構(gòu)進(jìn)行深入研究，其中一個(gè)關(guān)鍵的參數(shù)：recovery_min_apply_delay引起了我的注意，設(shè)置該參數(shù)的大概意思是：在進(jìn)行流復(fù)制的時(shí)候，備庫(kù)會(huì)延遲主庫(kù)recovery_min_apply_delay的時(shí)間進(jìn)行應(yīng)用。比如說(shuō)，我們?cè)谥鲙?kù)上insert10條數(shù)據(jù)，不會(huì)立即在備庫(kù)上生效，而是在recovery_min_apply_delay的時(shí)間后，備庫(kù)才能完成應(yīng)用。

另外，我們知道在PostgreSQL中，其mvcc機(jī)制并不像Oracle或者M(jìn)ySQL一樣，將舊版本數(shù)據(jù)存放在另外的空間中，而是通過(guò)對(duì)事務(wù)號(hào)(xid)的控制對(duì)舊版本數(shù)據(jù)不可見(jiàn)的方式進(jìn)行實(shí)現(xiàn)。所以PostgreSQL中無(wú)法實(shí)現(xiàn)類似于Oracle的閃回機(jī)制。

在日常操作過(guò)程中，對(duì)表進(jìn)行delete、truncate、drop等誤操作都不能通過(guò)閃回來(lái)快速恢復(fù)。不怕一萬(wàn)，就怕萬(wàn)一，在做數(shù)據(jù)庫(kù)維護(hù)的6年多里，遇到過(guò)的誤操作還是很多。那么在PostgreSQL這種無(wú)法實(shí)現(xiàn)閃回的數(shù)據(jù)庫(kù)中，如果出現(xiàn)誤操作如何快速恢復(fù)呢?

二、架構(gòu)簡(jiǎn)介

對(duì)于PostgreSQL數(shù)據(jù)庫(kù)這種無(wú)法進(jìn)行閃回的數(shù)據(jù)庫(kù)來(lái)講，最常用的辦法就是通過(guò)備份+歸檔的方式進(jìn)行數(shù)據(jù)恢復(fù)。但是這種恢復(fù)方式也有弊端，當(dāng)數(shù)據(jù)庫(kù)非常大時(shí)，恢復(fù)全量備份也會(huì)非常的慢，而且如果全量備份是一周前或者更久前的，那么恢復(fù)歸檔也會(huì)需要比較長(zhǎng)的時(shí)間。這段時(shí)間內(nèi)，可能業(yè)務(wù)就會(huì)長(zhǎng)時(shí)間停擺，造成一定的損失。

如果通過(guò)流復(fù)制延遲特性作為生產(chǎn)數(shù)據(jù)庫(kù)的容災(zāi)庫(kù)，則可以從一定程度上解決該問(wèn)題，其簡(jiǎn)單架構(gòu)如下：

三、恢復(fù)步驟

PostgreSQL流復(fù)制容災(zāi)庫(kù)架構(gòu)的誤操作恢復(fù)步驟如下：

1.主庫(kù)出現(xiàn)誤操作，查看流復(fù)制的replay狀態(tài);

2.在recovery_min_apply_delay時(shí)間內(nèi)，暫停備庫(kù)的replay;

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

4.根據(jù)主庫(kù)誤操作類型，對(duì)備庫(kù)進(jìn)行相應(yīng)的操作;

5.通過(guò)pg_dump將誤操作表導(dǎo)出;

6.在主庫(kù)對(duì)pg_dump出的表進(jìn)行恢復(fù)。

假設(shè)當(dāng)前備庫(kù)與主庫(kù)相差10min，則誤操作可以分為以下兩個(gè)場(chǎng)景：

1)delete操作：

首先我們需要知道的是，針對(duì)delete操作，PostgreSQL會(huì)給相關(guān)表加一個(gè)ROW EXCLUSIVE鎖，而該鎖不會(huì)對(duì)select等dql操作進(jìn)行阻塞。

所以當(dāng)我們?cè)谥鲙?kù)進(jìn)行delete誤操作后，備庫(kù)則會(huì)晚10min中進(jìn)行replay。且此時(shí)可以對(duì)該表進(jìn)行查詢和pg_dump的導(dǎo)出。針對(duì)于主庫(kù)delete誤操作，恢復(fù)步驟如下：

第一步，查看流復(fù)制replay的狀態(tài)，重點(diǎn)關(guān)注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 

此時(shí)可以發(fā)現(xiàn)數(shù)據(jù)庫(kù)中的replay_lsn字段的lsn值要比sent_lsn/write_lsn/flush_lsn都要小;

第二步，為了防止處理或者導(dǎo)出時(shí)間過(guò)慢而導(dǎo)致的數(shù)據(jù)同步，立即暫停備庫(kù)的replay：

select * from pg_wal_replay_pause(); 

查看同步狀態(tài)：

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

第三步，在備庫(kù)查看數(shù)據(jù)是否存在：

select * from wangxin1; 

第四步，通過(guò)pg_dump，將表內(nèi)容導(dǎo)出：

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

第五步，在主庫(kù)執(zhí)行sql文件，將數(shù)據(jù)重新插入：

psql -p 18801 
\i wangxin1_data_only.sql 

恢復(fù)即完成。

2)truncate和drop：

這里首先需要知道的是，truncate和drop操作會(huì)給表加上一個(gè)access exclusive鎖，該類型鎖是PostgreSQL數(shù)據(jù)庫(kù)中最嚴(yán)重的鎖。如果表上有該鎖，則會(huì)阻止所有對(duì)該此表的訪問(wèn)操作，其中也包括select和pg_dump操作。

所以說(shuō)，在我們對(duì)主庫(kù)中的某張表進(jìn)行truncate或者drop后，同樣，備庫(kù)會(huì)由于recovery_min_apply_delay參數(shù)比主庫(kù)晚完成truncate或drop動(dòng)作10min(從參數(shù)理論上是這樣理解的，但實(shí)際并不是)。

那么針對(duì)truncate和drop的恢復(fù)過(guò)程我們也參考delete的方式來(lái)進(jìn)行：

-[ 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 

接下來(lái)，為防止處理或?qū)С鰰r(shí)間過(guò)慢而導(dǎo)致的數(shù)據(jù)同步，應(yīng)立即暫停備庫(kù)的replay：

select * from pg_wal_replay_pause(); 

查看同步狀態(tài)：

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

接著，在備庫(kù)查看數(shù)據(jù)是否存在：

select * from wangxin1; 

但是，此時(shí)就會(huì)發(fā)現(xiàn)問(wèn)題：數(shù)據(jù)無(wú)法select出來(lái)，整個(gè)select進(jìn)程會(huì)卡住(pg_dump也一樣)：

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

此時(shí)，可以對(duì)備庫(kù)上的鎖信息進(jìn)行查詢：

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) 

發(fā)現(xiàn)此時(shí)truncate的表被鎖住了，而pid進(jìn)程則是備庫(kù)的recover進(jìn)程，所以此時(shí)我們根本無(wú)法訪問(wèn)該表，也就無(wú)法做pg_dump操作了。

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

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點(diǎn)后(恢復(fù)的起始點(diǎn))，正常來(lái)說(shuō)，數(shù)據(jù)庫(kù)會(huì)繼續(xù)執(zhí)行l(wèi)sn為7/3F054840的步驟開(kāi)啟事務(wù)，并在下一步lsn為7/3F054878的步驟直接對(duì)oid為24780(通過(guò)oid2name可以知道，這張表就是我們誤操作表)的表進(jìn)行l(wèi)ock操作，做一系列相關(guān)的操作后，進(jìn)行了truncate，最后進(jìn)行commit操作。

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

由于我們知道checkpoint點(diǎn)是數(shù)據(jù)庫(kù)的恢復(fù)起始點(diǎn)，那么我們是否可以將數(shù)據(jù)庫(kù)恢復(fù)到這一點(diǎn)的lsn呢?此時(shí)的lsn肯定不會(huì)對(duì)表進(jìn)行l(wèi)ock操作，那么我們就可以對(duì)該表進(jìn)行pg_dump操作了。

想法是好的，但是實(shí)際操作則沒(méi)那么順利。我們可以通過(guò)對(duì)備庫(kù)PostgreSQL的配置文件進(jìn)行修改，加入?yún)?shù)：

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

重啟數(shù)據(jù)庫(kù)。

此時(shí)卻發(fā)現(xiàn)數(shù)據(jù)庫(kù)無(wú)法啟動(dòng)，通過(guò)對(duì)日志查看，發(fā)現(xiàn)原因竟然是：

這個(gè)恢復(fù)點(diǎn)，是一致性恢復(fù)點(diǎn)之前的點(diǎn)，所以無(wú)法正?；謴?fù)。

此時(shí)就出現(xiàn)了令我們奇怪的點(diǎn)，我們知道checkpoint的兩個(gè)主要作用是：將臟數(shù)據(jù)進(jìn)行刷盤(pán);將wal日志的checkpoint進(jìn)行記錄。此時(shí)，肯定是數(shù)據(jù)庫(kù)一致的點(diǎn)，但是為什么會(huì)報(bào)不一致呢?

經(jīng)過(guò)一點(diǎn)一點(diǎn)的嘗試，發(fā)現(xiàn)能夠恢復(fù)的lsn點(diǎn)，只有truncate或者drop的commit操作的前面。那么這樣我們還是無(wú)法對(duì)誤操作表進(jìn)行解鎖。

最后，只能通過(guò)一種方式，即pg_resetwal的方式，強(qiáng)制指定備庫(kù)恢復(fù)到我們想要的lsn點(diǎn):

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

再進(jìn)行pg_dump即可。

但是，此時(shí)PostgreSQL的主備流復(fù)制關(guān)系已經(jīng)被破壞，只能重新搭建或者以其他方式進(jìn)行恢復(fù)(比如pg_rewind)。

四、問(wèn)題分析

再次返回到進(jìn)行truncate或drop的恢復(fù)步驟中，我們可以發(fā)現(xiàn)一個(gè)問(wèn)題，為什么在checkpoint點(diǎn)后、truncate點(diǎn)前，無(wú)法將數(shù)據(jù)庫(kù)恢復(fù)到一致點(diǎn)呢?為什么會(huì)報(bào)錯(cuò)呢?

按照常理來(lái)講，checkpoint點(diǎn)就是恢復(fù)數(shù)據(jù)庫(kù)的起始點(diǎn)，也是一致點(diǎn)，但是卻無(wú)法恢復(fù)了。

繼續(xù)進(jìn)行詳細(xì)的探究后發(fā)現(xiàn)一個(gè)現(xiàn)象：

延遲流復(fù)制過(guò)程中，我們配置了recovery_min_apply_delay參數(shù)，對(duì)源端數(shù)據(jù)庫(kù)做truncate后，備庫(kù)replay的lsn，停留在truncate表后的commit操作。而從主庫(kù)的pg_stat_replication的replay_lsn值來(lái)看，此時(shí)備庫(kù)的recover進(jìn)程，應(yīng)該就是在執(zhí)行最后的commit的lsn;

更形象的來(lái)說(shuō)，此時(shí)備庫(kù)類似于我執(zhí)行以下命令：

begin; 
 
truncate table; 

也就是說(shuō)，此時(shí)我并沒(méi)有提交，而備庫(kù)也正在等待我進(jìn)行提交，所以此時(shí)誤操作表會(huì)被鎖定。

但實(shí)際上，truncate table這個(gè)動(dòng)作，已經(jīng)在我的備庫(kù)上進(jìn)行了replay，只是最后的commit動(dòng)作沒(méi)有進(jìn)行replay。因此，對(duì)于truncate動(dòng)作之前所有l(wèi)sn的操作已經(jīng)是我當(dāng)前數(shù)據(jù)庫(kù)狀態(tài)的一個(gè)過(guò)去式，無(wú)法恢復(fù)了，故會(huì)報(bào)錯(cuò)。

為了驗(yàn)證想法，在大佬的幫助下，又對(duì)PostgreSQL的源碼進(jìn)行查看，發(fā)現(xiàn)猜想原因確實(shí)沒(méi)錯(cuò)：

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

/* 
* 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. 
*/ 

大概意思是，當(dāng)record中沒(méi)有時(shí)間戳(timestamp)的時(shí)候，數(shù)據(jù)庫(kù)就已經(jīng)進(jìn)行了replay。replay只會(huì)等待有時(shí)間戳的record，而所有的record中，只有commit操作有時(shí)間戳，故replay會(huì)等待一個(gè)commit操作。

不過(guò)在實(shí)際的生產(chǎn)環(huán)境中，我們通常會(huì)把recovery_min_apply_delay參數(shù)設(shè)置的較大，而在這之間，一般都會(huì)有一些其他的事務(wù)進(jìn)行操作，當(dāng)主庫(kù)出現(xiàn)誤操作(哪怕說(shuō)truncate/drop)，只要及時(shí)發(fā)現(xiàn)，我們可以暫停replay的步驟，停在正常的事務(wù)操作下，此時(shí)誤操作的表的事務(wù)還沒(méi)有執(zhí)行，那么這個(gè)容災(zāi)庫(kù)還是比較有作用的。