pg重生（PG之闪回数据库）

作者：小麦苗

误操作恢复的文章参考：

PG中通过pg_waldump来分析pg_WAL日志：

https://www.xmmup.com/pgzhongtongguopg_waldumplaifenxipg_walrizhi.html

PG日志挖掘工具之WalMiner：

https://www.xmmup.com/pgrizhiwajuegongjuzhiwalminer.html

PG工具pg_resetwal介绍：

https://www.xmmup.com/postgresql11xiugaiwal-segsizedaxiao.html

在Oracle中，若发生重大的误操作，那么我们可以使用flashback database命令来把数据库整体闪回到过去的误操作的时间点，当然前提是需要打开数据库的闪回功能。

在PG中，能否也可以执行类型的操作呢，答案是肯定的。

误操作恢复的过程命令参考：

\c lhrdb select pg_switch_wal(); select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn()); create table f_lhr (id int, name varchar(20)); insert into f_lhr (id,name) values (1,'l'); insert into f_lhr (id,name) values (2,'h'); insert into f_lhr (id,name) values (3,'r'); -- 误操作 delete from f_lhr where id =2 ; select * from f_lhr; create table f_lhr2 (id int, name varchar(20)); insert into f_lhr (id,name) values (1,'l'); select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn()); pg_waldump 0000000100000002000000B2 | grep DELETE pg_ctl stop pg_resetwal -x 452490 -D /pg13/pgdata/ pg_ctl start

1、误操作

C:\Users\lhrxxt>psql -U postgres -h192.168.1.35 -p15433 Password for user postgres: psql (13.3) Type 'help' for help. postgres=# select now(); now ------------------------------- 2021-09-28 08:48:11.150389 08 (1 row) postgres=# \c lhrdb You are now connected to database 'lhrdb' as user 'postgres'. lhrdb=# lhrdb=# lhrdb=# select pg_switch_wal(); pg_switch_wal --------------- 2/B100C498 (1 row) lhrdb=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn()); pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset -------------------- -------------------------- ------------------------------- 2/B2000060 | 0000000100000002000000B2 | (0000000100000002000000B2,96) (1 row) lhrdb=# create table f_lhr (id int, name varchar(20)); CREATE TABLE lhrdb=# insert into f_lhr (id,name) values (1,'l'); INSERT 0 1 lhrdb=# insert into f_lhr (id,name) values (2,'h'); INSERT 0 1 lhrdb=# insert into f_lhr (id,name) values (3,'r'); INSERT 0 1 lhrdb=# -- 误操作 lhrdb=# delete from f_lhr where id =2 ; DELETE 1 lhrdb=# select * from f_lhr; id | name ---- ------ 1 | l 3 | r (2 rows) lhrdb=# create table f_lhr2 (id int, name varchar(20)); CREATE TABLE lhrdb=# insert into f_lhr2 (id,name) values (1,'l'); INSERT 0 1

2、查找误操作的事务号

首先可以根据时间来判断大致是哪几个WAL日志文件，再进行逐个日志分析查找，本次实验的WAL日志为“0000000100000002000000B2”，根据得到当前的事务号以及日志文件，开始寻找恢复日志的数据范围

[pg13@lhrpg pg_wal]$ pg_waldump 0000000100000002000000B2 rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 2/B2000028, prev 2/B100C480, desc: RUNNING_XACTS nextXid 452486 latestCompletedXid 452485 oldestRunningXid 452486 rmgr: Storage len (rec/tot): 42/ 42, tx: 0, lsn: 2/B2000060, prev 2/B2000028, desc: CREATE base/16484/41318 rmgr: Heap len (rec/tot): 54/ 1878, tx: 452486, lsn: 2/B2000090, prev 2/B2000060, desc: INSERT off 10 flags 0x00, blkref #0: rel 1663/16484/1247 blk 10 FPW rmgr: Btree len (rec/tot): 53/ 2153, tx: 452486, lsn: 2/B20007E8, prev 2/B2000090, desc: INSERT_LEAF off 103, blkref #0: rel 1663/16484/2703 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 6845, tx: 452486, lsn: 2/B2001058, prev 2/B20007E8, desc: INSERT_LEAF off 161, blkref #0: rel 1663/16484/2704 blk 1 FPW rmgr: Heap len (rec/tot): 54/ 2358, tx: 452486, lsn: 2/B2002B30, prev 2/B2001058, desc: INSERT off 38 flags 0x00, blkref #0: rel 1663/16484/2608 blk 58 FPW rmgr: Btree len (rec/tot): 53/ 6217, tx: 452486, lsn: 2/B2003468, prev 2/B2002B30, desc: INSERT_LEAF off 219, blkref #0: rel 1663/16484/2673 blk 24 FPW rmgr: Btree len (rec/tot): 53/ 6133, tx: 452486, lsn: 2/B2004CD0, prev 2/B2003468, desc: INSERT_LEAF off 216, blkref #0: rel 1663/16484/2674 blk 43 FPW rmgr: Heap len (rec/tot): 207/ 207, tx: 452486, lsn: 2/B20064E0, prev 2/B2004CD0, desc: INSERT off 11 flags 0x00, blkref #0: rel 1663/16484/1247 blk 10 rmgr: Btree len (rec/tot): 64/ 64, tx: 452486, lsn: 2/B20065B0, prev 2/B20064E0, desc: INSERT_LEAF off 103, blkref #0: rel 1663/16484/2703 blk 2 rmgr: Btree len (rec/tot): 72/ 72, tx: 452486, lsn: 2/B20065F0, prev 2/B20065B0, desc: INSERT_LEAF off 22, blkref #0: rel 1663/16484/2704 blk 1 rmgr: Heap len (rec/tot): 80/ 80, tx: 452486, lsn: 2/B2006638, prev 2/B20065F0, desc: INSERT off 39 flags 0x00, blkref #0: rel 1663/16484/2608 blk 58 rmgr: Btree len (rec/tot): 72/ 72, tx: 452486, lsn: 2/B2006688, prev 2/B2006638, desc: INSERT_LEAF off 219, blkref #0: rel 1663/16484/2673 blk 24 rmgr: Btree len (rec/tot): 53/ 2969, tx: 452486, lsn: 2/B20066D0, prev 2/B2006688, desc: INSERT_LEAF off 103, blkref #0: rel 1663/16484/2674 blk 41 FPW rmgr: Heap len (rec/tot): 54/ 6474, tx: 452486, lsn: 2/B2007270, prev 2/B20066D0, desc: INSERT off 34 flags 0x00, blkref #0: rel 1663/16484/1259 blk 0 FPW rmgr: Btree len (rec/tot): 53/ 3233, tx: 452486, lsn: 2/B2008BD8, prev 2/B2007270, desc: INSERT_LEAF off 157, blkref #0: rel 1663/16484/2662 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 6157, tx: 452486, lsn: 2/B2009880, prev 2/B2008BD8, desc: INSERT_LEAF off 34, blkref #0: rel 1663/16484/2663 blk 1 FPW rmgr: Btree len (rec/tot): 53/ 1833, tx: 452486, lsn: 2/B200B0A8, prev 2/B2009880, desc: INSERT_LEAF off 87, blkref #0: rel 1663/16484/3455 blk 4 FPW rmgr: Heap len (rec/tot): 54/ 966, tx: 452486, lsn: 2/B200B7D8, prev 2/B200B0A8, desc: INSERT off 6 flags 0x00, blkref #0: rel 1663/16484/1249 blk 58 FPW rmgr: Btree len (rec/tot): 53/ 2089, tx: 452486, lsn: 2/B200BBA0, prev 2/B200B7D8, desc: INSERT_LEAF off 71, blkref #0: rel 1663/16484/2658 blk 16 FPW rmgr: Btree len (rec/tot): 53/ 5933, tx: 452486, lsn: 2/B200C3E8, prev 2/B200BBA0, desc: INSERT_LEAF off 292, blkref #0: rel 1663/16484/2659 blk 10 FPW rmgr: Heap len (rec/tot): 175/ 175, tx: 452486, lsn: 2/B200DB18, prev 2/B200C3E8, desc: INSERT off 7 flags 0x00, blkref #0: rel 1663/16484/1249 blk 58 rmgr: Btree len (rec/tot): 72/ 72, tx: 452486, lsn: 2/B200DBC8, prev 2/B200DB18, desc: INSERT_LEAF off 72, blkref #0: rel 1663/16484/2658 blk 16 rmgr: Btree len (rec/tot): 64/ 64, tx: 452486, lsn: 2/B200DC10, prev 2/B200DBC8, desc: INSERT_LEAF off 293, blkref #0: rel 1663/16484/2659 blk 10 rmgr: Heap len (rec/tot): 175/ 175, tx: 452486, lsn: 2/B200DC50, prev 2/B200DC10, desc: INSERT off 8 flags 0x00, blkref #0: rel 1663/16484/1249 blk 58 rmgr: Btree len (rec/tot): 72/ 72, tx: 452486, lsn: 2/B200DD00, prev 2/B200DC50, desc: INSERT_LEAF off 71, blkref #0: rel 1663/16484/2658 blk 16 rmgr: Btree len (rec/tot): 64/ 64, tx: 452486, lsn: 2/B200DD48, prev 2/B200DD00, desc: INSERT_LEAF off 292, blkref #0: rel 1663/16484/2659 blk 10 rmgr: Heap len (rec/tot): 175/ 175, tx: 452486, lsn: 2/B200DD88, prev 2/B200DD48, desc: INSERT off 9 flags 0x00, blkref #0: rel 1663/16484/1249 blk 58 rmgr: Btree len (rec/tot): 72/ 72, tx: 452486, lsn: 2/B200DE38, prev 2/B200DD88, desc: INSERT_LEAF off 74, blkref #0: rel 1663/16484/2658 blk 16 rmgr: Btree len (rec/tot): 64/ 64, tx: 452486, lsn: 2/B200DE80, prev 2/B200DE38, desc: INSERT_LEAF off 292, blkref #0: rel 1663/16484/2659 blk 10 rmgr: Heap len (rec/tot): 175/ 175, tx: 452486, lsn: 2/B200DEC0, prev 2/B200DE80, desc: INSERT off 10 flags 0x00, blkref #0: rel 1663/16484/1249 blk 58 rmgr: Btree len (rec/tot): 72/ 72, tx: 452486, lsn: 2/B200DF70, prev 2/B200DEC0, desc: INSERT_LEAF off 71, blkref #0: rel 1663/16484/2658 blk 16 rmgr: Btree len (rec/tot): 64/ 64, tx: 452486, lsn: 2/B200DFB8, prev 2/B200DF70, desc: INSERT_LEAF off 292, blkref #0: rel 1663/16484/2659 blk 10 rmgr: Heap len (rec/tot): 175/ 175, tx: 452486, lsn: 2/B200DFF8, prev 2/B200DFB8, desc: INSERT off 11 flags 0x00, blkref #0: rel 1663/16484/1249 blk 58 rmgr: Btree len (rec/tot): 72/ 72, tx: 452486, lsn: 2/B200E0C0, prev 2/B200DFF8, desc: INSERT_LEAF off 75, blkref #0: rel 1663/16484/2658 blk 16 rmgr: Btree len (rec/tot): 64/ 64, tx: 452486, lsn: 2/B200E108, prev 2/B200E0C0, desc: INSERT_LEAF off 292, blkref #0: rel 1663/16484/2659 blk 10 rmgr: Heap len (rec/tot): 175/ 175, tx: 452486, lsn: 2/B200E148, prev 2/B200E108, desc: INSERT off 12 flags 0x00, blkref #0: rel 1663/16484/1249 blk 58 rmgr: Btree len (rec/tot): 72/ 72, tx: 452486, lsn: 2/B200E1F8, prev 2/B200E148, desc: INSERT_LEAF off 71, blkref #0: rel 1663/16484/2658 blk 16 rmgr: Btree len (rec/tot): 64/ 64, tx: 452486, lsn: 2/B200E240, prev 2/B200E1F8, desc: INSERT_LEAF off 292, blkref #0: rel 1663/16484/2659 blk 10 rmgr: Heap len (rec/tot): 175/ 175, tx: 452486, lsn: 2/B200E280, prev 2/B200E240, desc: INSERT off 13 flags 0x00, blkref #0: rel 1663/16484/1249 blk 58 rmgr: Btree len (rec/tot): 72/ 72, tx: 452486, lsn: 2/B200E330, prev 2/B200E280, desc: INSERT_LEAF off 76, blkref #0: rel 1663/16484/2658 blk 16 rmgr: Btree len (rec/tot): 64/ 64, tx: 452486, lsn: 2/B200E378, prev 2/B200E330, desc: INSERT_LEAF off 292, blkref #0: rel 1663/16484/2659 blk 10 rmgr: Heap len (rec/tot): 80/ 80, tx: 452486, lsn: 2/B200E3B8, prev 2/B200E378, desc: INSERT off 40 flags 0x00, blkref #0: rel 1663/16484/2608 blk 58 rmgr: Btree len (rec/tot): 53/ 3781, tx: 452486, lsn: 2/B200E408, prev 2/B200E3B8, desc: INSERT_LEAF off 119, blkref #0: rel 1663/16484/2673 blk 33 FPW rmgr: Btree len (rec/tot): 53/ 7813, tx: 452486, lsn: 2/B200F2D0, prev 2/B200E408, desc: INSERT_LEAF off 31, blkref #0: rel 1663/16484/2674 blk 37 FPW rmgr: Standby len (rec/tot): 42/ 42, tx: 452486, lsn: 2/B2011170, prev 2/B200F2D0, desc: LOCK xid 452486 db 16484 rel 41318 rmgr: Transaction len (rec/tot): 437/ 437, tx: 452486, lsn: 2/B20111A0, prev 2/B2011170, desc: COMMIT 2021-09-28 09:00:23.823535 CST; inval msgs: catcache 75 catcache 74 catcache 75 catcache 74 catcache 50 catcache 49 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 41318 rmgr: Heap len (rec/tot): 61/ 61, tx: 452487, lsn: 2/B2011358, prev 2/B20111A0, desc: INSERT INIT off 1 flags 0x00, blkref #0: rel 1663/16484/41318 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 452487, lsn: 2/B2011398, prev 2/B2011358, desc: COMMIT 2021-09-28 09:00:23.845814 CST rmgr: Heap len (rec/tot): 61/ 61, tx: 452488, lsn: 2/B20113C0, prev 2/B2011398, desc: INSERT off 2 flags 0x00, blkref #0: rel 1663/16484/41318 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 452488, lsn: 2/B2011400, prev 2/B20113C0, desc: COMMIT 2021-09-28 09:00:23.851271 CST rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 2/B2011428, prev 2/B2011400, desc: RUNNING_XACTS nextXid 452489 latestCompletedXid 452488 oldestRunningXid 452489 rmgr: Heap len (rec/tot): 61/ 61, tx: 452489, lsn: 2/B2011460, prev 2/B2011428, desc: INSERT off 3 flags 0x00, blkref #0: rel 1663/16484/41318 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 452489, lsn: 2/B20114A0, prev 2/B2011460, desc: COMMIT 2021-09-28 09:00:25.727906 CST rmgr: Heap len (rec/tot): 54/ 54, tx: 452490, lsn: 2/B20114C8, prev 2/B20114A0, desc: DELETE off 2 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/16484/41318 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 452490, lsn: 2/B2011500, prev 2/B20114C8, desc: COMMIT 2021-09-28 09:00:36.627123 CST rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 2/B2011528, prev 2/B2011500, desc: RUNNING_XACTS nextXid 452491 latestCompletedXid 452490 oldestRunningXid 452491 rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 2/B2011560, prev 2/B2011528, desc: RUNNING_XACTS nextXid 452491 latestCompletedXid 452490 oldestRunningXid 452491 rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 2/B2011598, prev 2/B2011560, desc: CHECKPOINT_ONLINE redo 2/B2011560; tli 1; prev tli 1; fpw true; xid 0:452491; oid 49453; multi 1; offset 0; oldest xid 478 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 452491; online rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 2/B2011610, prev 2/B2011598, desc: RUNNING_XACTS nextXid 452491 latestCompletedXid 452490 oldestRunningXid 452491 pg_waldump: fatal: error in WAL record at 2/B2011610: invalid record length at 2/B2011648: wanted 24, got 0 [pg13@lhrpg pg_wal]$ [pg13@lhrpg pg_wal]$ pg_waldump 0000000100000002000000B2 | grep DELETE pg_waldump: fatal: error in WAL record at 2/B2011610: invalid record length at 2/B2011648: wanted 24, got 0 rmgr: Heap len (rec/tot): 54/ 54, tx: 452490, lsn: 2/B20114C8, prev 2/B20114A0, desc: DELETE off 2 flags 0x00 KEYS_UPDATED , blkref #0: rel 1663/16484/41318 blk 0 [pg13@lhrpg pg_wal]$

可以找到本次误操作的事务号为“452490”。

“CREATE base/16484/41318”为新建表的操作，表示数据库oid为16484，表的oid为41318。

[pg13@lhrpg pg_wal]$ oid2name All databases: Oid Database Name Tablespace ---------------------------------- 16430 bench pg_default 16635 db1 pg_default 16711 db10 pg_default 16655 db2 pg_default 16657 db3 pg_default 16658 db4 pg_default 16659 db5 pg_default 16660 db6 pg_default 16661 db7 pg_default 16656 korean pg_default 16484 lhrdb pg_default 12661 postgres pg_default 12660 template0 pg_default 1 template1 pg_default [pg13@lhrpg pg_wal]$ oid2name -d lhrdb -f 41318 -x From database 'lhrdb': Filenode Table Name Oid Schema Tablespace ------------------------------------------------- 41318 f_lhr 41318 public pg_default [pg13@lhrpg pg_wal]$ oid2name -d lhrdb -o 41318 -x From database 'lhrdb': Filenode Table Name Oid Schema Tablespace ------------------------------------------------- 41318 f_lhr 41318 public pg_default

3、在找到并确认事务号的情况下，关闭数据库服务

[pg13@lhrpg pg_wal]$ pg_ctl stop waiting for server to shut down....... done server stopped

4、通过指定事务号的方式重置数据库

[pg13@lhrpg pg_wal]$ pg_resetwal -x 452490 -D /pg13/pgdata/ Write-ahead log reset [pg13@lhrpg pg_wal]$ ll total 16388 -rw------- 1 pg13 postgres 16777216 Sep 28 09:12 0000000100000002000000E2 drwx------ 1 pg13 postgres 4096 Sep 28 09:12 archive_status

5、启动数据库并且查看数据是否恢复

[pg13@lhrpg pg_wal]$ pg_ctl start waiting for server to start....2021-09-28 09:12:43.590 CST [18571] LOG: redirecting log output to logging collector process 2021-09-28 09:12:43.590 CST [18571] HINT: Future log output will appear in directory 'pg_log'. done server started lhrdb=# select * from f_lhr; FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. lhrdb=# select * from f_lhr; id | name ---- ------ 1 | l 2 | h 3 | r (3 rows) lhrdb=# select * from f_lhr2; ERROR: relation 'f_lhr2' does not exist LINE 1: select * from f_lhr2;

经过确认数据已经恢复。

6、注意

1、在进行了闪回数据库操作后，设置的LSN号后的数据都被回滚了，相当于回滚到指定的事务LSN号的位置

2、WAL日志整体被清理，原有的日志都没有了。

PG日志挖掘工具之WalMiner：

https://www.xmmup.com/pgrizhiwajuegongjuzhiwalminer.html

其实，如果只是误操作删除了为数不多的一些数据，那么我们完全没有必要执行闪回数据库的操作，尤其在生产库中，执行闪回数据库的操作可能性非常低，在这种情况下，我们可以使用PG日志挖掘工具之WalMiner来生成Undo数据，如下示例：

create table walminer_lhr (id int, name varchar(20)); insert into walminer_lhr (id,name) values (1,'x'); insert into walminer_lhr (id,name) values (2,'x'); insert into walminer_lhr (id,name) values (3,'t'); -- 误操作 delete from walminer_lhr where id =2 ; select * from walminer_lhr; create table walminer_lhr2 (id int, name varchar(20)); insert into walminer_lhr2 (id,name) values (1,'l'); select pg_walfile_name(pg_current_wal_lsn()); select pg_switch_wal(); select walminer_wal_list(); select count(*) from walminer_contents; select walminer_wal_add('/pg13/pgdata/pg_wal/0000000100000002000000E9'); select walminer_all(); select count(*) from walminer_contents; select walminer_stop(); select * from walminer_lhr; INSERT INTO public.walminer_lhr(id ,name) VALUES(2 ,'x'); select * from walminer_lhr;

执行过程：

lhrdb=# create table walminer_lhr (id int, name varchar(20)); CREATE TABLE lhrdb=# insert into walminer_lhr (id,name) values (1,'x'); INSERT 0 1 lhrdb=# insert into walminer_lhr (id,name) values (2,'x'); INSERT 0 1 lhrdb=# insert into walminer_lhr (id,name) values (3,'t'); INSERT 0 1 lhrdb=# lhrdb=# -- 误操作 lhrdb=# delete from walminer_lhr where id =2 ; DELETE 1 lhrdb=# select * from walminer_lhr; id | name ---- ------ 1 | x 3 | t (2 rows) lhrdb=# lhrdb=# create table walminer_lhr2 (id int, name varchar(20)); CREATE TABLE lhrdb=# insert into walminer_lhr2 (id,name) values (1,'l'); INSERT 0 1 lhrdb=# lhrdb=# select pg_walfile_name(pg_current_wal_lsn()); pg_walfile_name -------------------------- 0000000100000002000000E9 (1 row) lhrdb=# select pg_switch_wal(); pg_switch_wal --------------- 2/E905C0A0 (1 row) lhrdb=# select walminer_wal_list(); ERROR: function walminer_wal_list() does not exist LINE 1: select walminer_wal_list(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. lhrdb=# lhrdb=# lhrdb=# select * from pg_available_extensions where name='walminer'; name | default_version | installed_version | comment ---------- ----------------- ------------------- -------------------- walminer | 3.0 | | analyse wal to SQL (1 row) lhrdb=# select * from pg_extension; oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition ------- -------------------- ---------- -------------- ---------------- ------------ ----------- -------------- 12647 | plpgsql | 10 | 11 | f | 1.0 | | 16755 | pg_stat_statements | 10 | 2200 | t | 1.8 | | 24712 | pg_repack | 10 | 2200 | f | 1.4.6 | | (3 rows) lhrdb=# create extension walminer; CREATE EXTENSION lhrdb=# select * from pg_extension; oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition ------- -------------------- ---------- -------------- ---------------- ------------ ----------- -------------- 12647 | plpgsql | 10 | 11 | f | 1.0 | | 16755 | pg_stat_statements | 10 | 2200 | t | 1.8 | | 24712 | pg_repack | 10 | 2200 | f | 1.4.6 | | 41336 | walminer | 10 | 2200 | t | 3.0 | | (4 rows) lhrdb=# select walminer_wal_list(); ERROR: wal list has not been loaded or has been removed. lhrdb=# lhrdb=# select walminer_wal_add('/pg13/pgdata/pg_wal/0000000100000002000000E9'); walminer_wal_add -------------------- 1 file add success (1 row) lhrdb=# select walminer_all(); NOTICE: Switch wal to 0000000100000002000000E9 on time 2021-09-28 10:14:19.752328 08 NOTICE: Switch wal to 0000000100000002000000E9 on time 2021-09-28 10:14:19.756057 08 walminer_all --------------------- pg_minerwal success (1 row) lhrdb=# lhrdb=# select count(*) from walminer_contents; count ------- 5 (1 row) lhrdb=# select * from walminer_contents; sqlno | xid | topxid | sqlkind | minerd | timestamp | op_text | undo_text | complete | schema | relation | start_lsn | commit_lsn ------- -------- -------- --------- -------- ------------------------------- ----------------------------------------------------------- ---------------------------------------------------------- ---------- -------- --------------- ------------ ------------ 1 | 452514 | 0 | 1 | t | 2021-09-28 10:13:17.477757 08 | INSERT INTO public.walminer_lhr(id ,name) VALUES(1 ,'x') | DELETE FROM public.walminer_lhr WHERE id=1 AND name='x' | t | public | walminer_lhr | 2/E905AB30 | 2/E905AB98 1 | 452515 | 0 | 1 | t | 2021-09-28 10:13:17.904026 08 | INSERT INTO public.walminer_lhr(id ,name) VALUES(2 ,'x') | DELETE FROM public.walminer_lhr WHERE id=2 AND name='x' | t | public | walminer_lhr | 2/E905ABD0 | 2/E905AC38 1 | 452516 | 0 | 1 | t | 2021-09-28 10:13:20.917843 08 | INSERT INTO public.walminer_lhr(id ,name) VALUES(3 ,'t') | DELETE FROM public.walminer_lhr WHERE id=3 AND name='t' | t | public | walminer_lhr | 2/E905AC38 | 2/E905ACA0 1 | 452517 | 0 | 3 | t | 2021-09-28 10:13:25.838449 08 | DELETE FROM public.walminer_lhr WHERE id=2 AND name='x' | INSERT INTO public.walminer_lhr(id ,name) VALUES(2 ,'x') | t | public | walminer_lhr | 2/E905ACA0 | 2/E905AD00 1 | 452519 | 0 | 1 | t | 2021-09-28 10:13:32.284701 08 | INSERT INTO public.walminer_lhr2(id ,name) VALUES(1 ,'l') | DELETE FROM public.walminer_lhr2 WHERE id=1 AND name='l' | t | public | walminer_lhr2 | 2/E905BFD0 | 2/E905C050 (5 rows) lhrdb=# select * from walminer_lhr; id | name ---- ------ 1 | x 3 | t (2 rows) lhrdb=# INSERT INTO public.walminer_lhr(id ,name) VALUES(2 ,'x'); INSERT 0 1 lhrdb=# lhrdb=# select * from walminer_lhr; id | name ---- ------ 1 | x 3 | t 2 | x (3 rows)

更多内容请参考：

https://www.xmmup.com/pgzhishanhuishujuku.html