一、前言
這幾天在對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；
發現此時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的步驟，停在正常的事務操作下，此時誤操作的表的事務還沒有執行，那麼這個容災庫還是比較有作用的。