本文转载请联系数据云公众号。一、前言这几天一直在深入研究PostgreSQL流复制的架构。其中一个关键参数：recovery_min_apply_delay引起了我的注意。设置该参数的大致意思是：在进行流复制时，备库会延迟主库recovery_min_apply_delay的时间被应用。比如我们在主库上插入10条数据，在备库上不会立即生效，但是备库可以在recovery_min_apply_delay时间后完成申请。另外，我们知道在PostgreSQL中，它的mvcc机制并没有像Oracle或者MySQL那样把旧版本的数据存储到另一个空间中。相反，它控制事务号(xid)以使旧版本数据不可见。完成。所以PostgreSQL无法实现类似于Oracle的闪回机制。在日常操作中，对表的delete、truncate、drop等误操作无法通过flashback快速恢复。不怕一万，以防万一。在过去6年的数据库维护中，我遇到过很多误操作。那么像PostgreSQL这种不能闪回的数据库，如果误操作了如何快速恢复呢？恢复数据的方法。但是，这种恢复方法也有缺点。当数据库很大时，恢复全量备份会很慢，如果全量备份是在一周或更长时间前做的，恢复归档需要很长时间。这段时间可能会长时间停业，造成一定的损失。如果利用流复制的延迟特性作为生产数据库的容灾数据库，可以在一定程度上解决这个问题。简单结构如下：3.恢复步骤PostgreSQL流复制容灾数据库架构误操作的恢复步骤如下：1.主库如果有误操作，查看流复制的replay状态；2、在recovery_min_apply_delay时间内，暂停备库的replay；3、判断主库误操作类型（delete/truncate/drop）；4.根据主库Type的误操作，对备库进行相应的操作；5.通过pg_dump导出误操作表；6.从主数据库中的pg_dump恢复表。假设当前备库和主库相差10分钟，误操作可以分为以下两种场景：1）删除操作：首先我们要知道，对于删除操作，PostgreSQL会添加对相关表加ROWEXCLUSIVE锁，该锁不会阻塞select等dql操作。因此，当我们对主库进行删除操作时，备库会在10分钟后重放。并且此时可以从pg_dump中查询并导出该表。对于主库的删除操作，恢复步骤如下：第一步，查看流复制重放状态，重点查看replay_lsn字段：select*frompg_stat_replication；postgres=#select*frompg_stat_replication;-[RECORD1]----+----------------------------pid|55694usesysid|24746usename|repapplication_name|walreceiverclient_addr|192.168.18.82client_hostname|client_port|31550backend_start|2021-01-2009:54:57.039779+08backend_xmin|state|streamingsent_lsn|6/D2A17120write_lsn|6/D2A17120flush_lsn|6/D2A17120replay_lsn|6/D2A170B8write_lag|00:00:00.000119flush_lag|00:00:00.000239replay_lag|00:00:50.653858sync_priority|0sync_state|asyncreply_time|2021-01-2014:11:31.704194+08此时可以发现数据库中replay_lsn字段的lsn值小于sent_lsn/write_lsn/flush_lsn；第二步，防止处理或导出时间过慢导致数据同步，立即暂停备库重放：select*frompg_wal_replay_pause();查看同步状态：postgres=#select*frompg_is_wal_replay_paused();pg_is_wal_replay_paused----------------------t(1row)第三步查看数据是否存在于备库：select*fromwangxin1；第四步通过pg_dump导出表内容：pg_dump-h192.168.18.182-p18802-dpostgres-Upostgres-twangxin1--data-only--inserts-fwangxin1_data_only.sql第五步，执行主库中的sql文件，重新插入数据：psql-p18801\iwangxin1_data_only.sql恢复。2）truncate和drop：这里首先要知道truncate和drop操作是给表加一个accessexclusive锁，这是PostgreSQL数据库中最严重的锁。如果表上存在锁，所有对该表的访问操作都会被阻塞，包括select和pg_dump操作。所以，我们在主库中truncate或者drop一个表之后，由于recovery_min_apply_delay这个参数，备库也会比主库晚10分钟完成truncate或者drop动作（这个从参数上理论上可以理解，但不是实际上没有）。然后truncate和drop的恢复过程我们也参考delete方法：-[RECORD2]----+--------------------------pid|67008usesysid|24746usename|replapplication_name|walreceiverclient_addr|192.168.18.82client_hostname|client_port|32122backend_start|2021-01-2023:33:05.538858+08backend_xmin|state|streamingsent_lsn|7/3F0593E0write_lsn|7/3F0593E0flush_lsn|7/3F0593E0replay_lsn|7/3F059330write_lag|00:00:00.000141flush_lag|00:00:00.000324replay_lag|00:00:11.471699sync_priority|0sync_state|asyncreply_time|2021-01-3583.6对于export时间过慢导致的数据同步，备库replay应该立即暂停：select*frompg_wal_replay_pause();查看同步状态：postgres=#select*frompg_is_wal_replay_paused();pg_is_wal_replay_paused----------------------t(1row)接下来查看数据是否存在备库：select*fromwangxin1；但是这时候会发现一个问题：无法选中数据，整个select过程会卡住Live（pg_dump也是一样）：^CCancelrequestsentERROR:cancelingstatementduetouserrequest这时候可以在standby上查询锁信息数据库：selects.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.queryfrompg_stat_activitys,pg_lockslwheres.pid<>pg_backend_pid()ands.pid=l.pid;pid|datname|usename|relation|client_addr|?column?|wait_event|wait_event_type|granted|模式|查询------+--------+--------+--------+-------------+------------+--------------------+-----------------+--------+--------------------+--------55689||||||RecoveryApplyDelay|Timeout|t|ExclusiveLock|55689|||wangxin1|||RecoveryApplyDelay|Timeout|t|AccessExclusiveLock|(2rows)发现此时truncate表被锁住了，pid进程是recovery进程standbydatabase，所以此时我们根本无法访问该表，也无法进行pg_dump操作。所以，如果我们要恢复，就必须想办法把数据库恢复到表被锁之前的操作。于是对PostgreSQL的wal日志进行分析查看：pg_waldump-p/pgdata/pg_wal-s7/3F000000rmgr:Standbylen(rec/tot):50/50,tx:0,lsn:7/3F050D70,prev7/3F050D40,desc:RUNNING_XACTSnextXid13643577latestCompletedXid13643576oldestRunningXid13643577rmgr:Heap2len(rec/tot):60/60,tx:13643577,lsn:7/3F050DA8,prev7/3F050D70,desc:NEW_CIDrel1663/13593/2619;tid20/27;cmin:4294967295,cmax:0,combo:4294967295rmgr:Heap2len(rec/tot):60/60,tx:13643577,lsn:7/3F050DE8,prev7/3F050DA8,desc:NEW_CIDrel1663/13593/2619;tid20/23;cmin:0,cmax:4294967295,combo:4294967295rmgr:Heaplen(rec/tot):65/6889,tx:13643577,lsn:7/3F050E28,prev7/3F050DE8,desc:HOT_UPDATEoff27xmax13643577flags0x00;newoff23xmax0,blkref#0:rel1663/13593/2619blk20FPWrmgrc/Heap2,tx:13643577,lsn:7/3F052930,prev7/3F050E28,desc:NEW_CIDrel1663/13593/2619;tid20/28;cmin:4294967295,cmax:0,combo:4294967295rmgr:Heap2len(rec/tot):60/60,tx:13643577,lsn:7/3F052970,prev7/3F052930,desc:NEW_CIDrel1663/13593/2619;tid20/24;cmin:0,cmax:4294967295,组合：4294967295RMGR：HAPLEN（REC/TOT）：76/76，TX：13643577，LSN：7/3F0529B0，PEV7/3F052970，DESC：HOT_UPDATEOFF28XMAX136643577FLAGSBLEN1BLEN1BLENBLENBLENBBLENBLBLBLBLBLBLBLBLBLBLKSTERBENBLKSOTINGISBLKSSSS03433MENKENKENT220;):53/7349,tx:13643577,lsn:7/3F052A00,prev7/3F0529B0,desc:INPLACEoff13,blkref#0:rel1663/13593/1259blk1FPWrmgr:Transactionlen(rec/tot):130/130,tx:13643577,lsn:7/3F0546D0,prev7/3F052A00,desc:COMMIT2021-01-2023:31:23.009466CST;invalmsgs:catcache58catcache58catcache50catcache49relcache24780rmgr:Standbylen(rec/tot):50/50,tx:0,lsn47/38F047/5,desc:RUNNING_XACTSnextXid13643578latestCompletedXid13643577oldestRunningXid13643578rmgr:Standbylen(rec/tot):50/50,tx:0,lsn:7/3F054790,prev7/3F054758,desc:RUNNING_XACTSnextXid13643578latestCompletedXid13643577oldestRunningXid13643578rmgr:XLOGlen(rec/tot):114/114,tx:0,lsn:7/3F0547C8,prev7/3F054790,desc:CHECKPOINT_ONLINEredo7/3F054790;tli1;prevtli1;fpwtrue;xid0:13643578;oid33072;multi1;offset0;oldestxid479inDB1;oldestmulti1inDB1;oldest/newestcommittimestampxid:0/0;oldestrunningxid13643578;onlinermgr:Standbylen(rec/tot):50/50,tx:0,lsn:7/3F054840,prev7/3F0547C8,desc:RUNNING_XACTSnextXid13643578latestCompletedXid13643577oldestRunningXid13643578rmgr:Standbylen(rec/tot):42/42,tx:13643578,lsn:7/3F054878,prev7/3F054840,desc:LOCKxid13643578db13593rel24780rmgr:Storagelen(rec/tot):42/42,tx:13643578,lsn:7/3F0548A8,prev48CATE0desc5C7/3/13593/24885rmgr:Heap2len(rec/tot):60/60,tx:13643578,lsn:7/3F0548D8,prev7/3F0548A8,desc:NEW_CIDrel1663/13593/1259;tid1/13;cmin:4294967295,cmax:0,combo:4294967295rmgr:Heap2len(rec/tot):60/60,tx:13643578,lsn:7/3F054918,prev7/3F0548D8,desc:NEW_CIDrel1663/13593/1259;tid1/14;cmi??n:0,cmax:4294967295,combo：4294967295RMGR：HEAPLEN（REC/TOT）：65/7537，TX：13643578，LSN：7/3F054958，PEV7/3F054918，DESC：DESC：DESC：UPDACE13XMAX13XMAX13643578FLAGS0BER：33643578FLAGSERPER：BLKENBERQUERBERQUENBRKQUENBLK#NEWBBLK#NENBBMBLKQUMER;:76/76,tx:13643578,lsn:7/3F0566E8,prev7/3F054958,desc:CLEANremxid13642576,blkref#0:rel1663/13593/1259blk1rmgr:Btreelen(rec/tot):53/3573,tx:13643578,lsn:7/3F056738,prev7/3F0566E8,desc:INSERT_blkref631:1LEAF631/13593/2662blk2FPWrmgr:Btreelen(rec/tot):53/5349,tx:13643578,lsn:7/3F057530,prev7/3F056738,desc:INSERT_LEAFoff117,blkref#0:rel1663/13593/2663blk2FPWrmgrc/totreelent)(:53/2253,tx:13643578,lsn:7/3F058A30,prev7/3F057530,desc:INSERT_LEAFoff108,blkref#0:rel1663/13593/3455blk4FPWrmgr:Heaplen(rec/tot):42/42,tx:13643578,lsn:7/3F059300,prev7/3F058A30,desc:TRUNCATEnrelids1relids24780rmgr:Transactionlen(rec/tot):114/114,tx:13643578,lsn:7/3F059330,prev7/3F059300,desc:COMMIT2021-01-20464.ST33;rels:base/13593/24884;invalmsgs:catcache50catcache49relcache24780rmgr:Standbylen(rec/tot):50/50,tx:0,lsn:7/3F0593A8,prev7/3F059330,desc:RUNNING_XACTSnextXid13643579latestCompletedXid13643578oldestRunningXid13643579rmgr:Standbylen(rec/tot):50/50,tx:0,lsn:7/3F0593E0,prev7/3F0593A8,desc:RUNNING_XACTSnextXid13643579latestCompletedXid13643578oldestRunningXid13643579rmgr:XLOGlen(rec/tot):114/114,tx:0,lsn:从wal日志分析可以清楚的看到，在最后一个checkpoint点（恢复起点）之后，正常情况下，数据库会继续执行lsn为7/3F054840的那一步开始事务，下一步lsn为7/3F054878的时候，会直接锁住oid为24780的表（从oid2name可以知道这张表是我们误操作的桌子）。做完一系列相关操作后，进行truncate，最后进行commit操作。我们可以把这一系列操作看成是正常的truncateatable操作。既然我们知道checkpoint点是数据库恢复的起点，那么我们是否可以在这个点将数据库恢复到lsn呢？这时候lsn肯定不会锁表，所以我们可以对表进行pg_dump操作。想法是好的，但实际操作起来却不是那么顺利。我们可以修改备库PostgreSQL的配置文件，增加参数：recovery_target_lsn='7/3F0547C8'recovery_target_action='pause'来重启数据库。这时发现数据库无法启动。查看日志发现原因是：这个恢复点在一致恢复点之前，所以无法正常恢复。在这一点上，我们有一个奇怪的点。我们知道checkpoint的两个主要作用是：清理脏数据；记录wal日志的checkpoint。这时候肯定是数据库一致的点，但是为什么会报不一致呢？稍微尝试了一下，发现可以恢复的lsn点只有truncate或者drop的commit操作前面。所以我们还是无法解锁误操作表。最后只有一种方法，就是pg_resetwal，强制指定备库恢复到我们想要的lsn点：pg_resetwal-Ddata1-x559Write-aheadlogreset然后pg_dump。但是，此时PostgreSQL的主从流复制关系已经被破坏，只能通过其他方式（如pg_rewind）重建或恢复。4.问题分析回到truncate或者drop的恢复步骤，我们可以发现一个问题。为什么数据库无法恢复到检查点之后和截断点之前的一致点？为什么会报错？按照常识，checkpoint点是恢复数据库的起点，也是一个一致点，但是不能恢复。继续详细研究后发现一个现象：在延迟流复制过程中，我们配置了recovery_min_apply_delay参数，truncatesourcedatabase后，standbydatabasereplay的lsn停留在truncatetable之后的commit操作。从主库pg_stat_replication的replay_lsn值来看，此时备库的恢复进程应该是在执行上次commit的lsn；更形象一点，备库类似于我执行下面的命令：begin;truncatetable;也就是说这个时候我没有提交，备库在等我提交，所以这个时候误操作的表会被锁住。但实际上truncatetable的动作已经在我的standby数据库上重放了，只是上次的commit动作还没有重放。所以，所有在truncate动作之前的lsn操作，已经是我当前数据库状态的过去式，无法恢复，所以会报错。为了验证思路，在大佬的帮助下查看了PostgreSQL的源码，发现猜测的原因确实是正确的：在/src/backend/access/transam/xlog.c中，有recovery_min_apply_delay参数的以下描述：/**IsitaCOMMITrecord?**Wedelliberatelychoosenottodelayabortssincetheyhavenoeffecton*MVCC.Wealreadyallowreplayofrecordthatdon'tthaveatimestamp,*sothereisalreadyopportunityforissuescausedbearlyconflictson*standbys.*/可能意味着当记录中没有时间戳（timestamp数据库）已经被重播时，记录.Replay只会等待有时间戳的记录，而在所有记录中，只有commit操作有时间戳，所以replay会等待commit操作。但是在实际的生产环境中，我们通常会将recovery_min_apply_delay参数设置的比较大，中间通常会有一些其他的事务。我们发现我们可以在正常的事务操作下暂停重放步骤并停止。这个时候误操作的表的事务还没有执行，所以这个容灾库还是比较有用的。
