Postgres中恢复删除的表数据
参考文档：
pg9的文档：
http://postgres.cn/docs/9.6/app-pgresetxlog.html
http://postgres.cn/docs/9.6/pgxlogdump.html
pg10的文档：
http://postgres.cn/docs/10/pgwaldump.html
http://postgres.cn/docs/10/app-pgresetwal.html
-- 测试环境，PG9.4. 手头上电脑上有2年前的pg9，顺便测试下。如果是pg10及以上版本，则一些函数会不一样。
[postgres@redhat762100 pg_xlog]$ pg_config | grep VERSION
VERSION = PostgreSQL 9.4.23
[postgres@redhat762100 pg_xlog]$ 会用到查询pg的日志信息的一些函数，这些函数在不同的版本中是不一样的。
-- 10之前写法
select pg_current_xlog_location(),
pg_xlogfile_name(pg_current_xlog_location()),
pg_xlogfile_name_offset(pg_current_xlog_location());-- 10 以后写法
select pg_current_wal_lsn(),
pg_walfile_name(pg_current_wal_lsn()),
pg_walfile_name_offset(pg_current_wal_lsn());-- 插入5行数据
mydb=# create table t1 (id int,name text);
CREATE TABLE
mydb=# insert into t1 values(1,'aa');
INSERT 0 1
mydb=# insert into t1 values (2,'bb');
INSERT 0 1
mydb=# insert into t1 values(3,'cc');
INSERT 0 1
mydb=# insert into t1 values(4,'dd');
INSERT 0 1
mydb=# insert into t1 values(5,'ee');
INSERT 0 1
mydb=# select * from t1;
id | name
----+------
1 | aa
2 | bb
3 | cc
4 | dd
5 | ee
(5 rows)
mydb=# -- 将ID=4的记录删除掉
mydb=# delete from t1 where id =4;
DELETE 1
mydb=#-- 查看此时的xlog的信息
mydb=# select pg_current_xlog_location(),
mydb-# pg_xlogfile_name(pg_current_xlog_location()),
mydb-# pg_xlogfile_name_offset(pg_current_xlog_location());
pg_current_xlog_location | pg_xlogfile_name | pg_xlogfile_name_offset
--------------------------+--------------------------+-----------------------------------
0/A0CF5C8 | 00000001000000000000000A | (00000001000000000000000A,849352)
(1 row)
mydb=# -- pg_xlogdump出日志中的信息，这里面可以看到insert日期，delete日期 。delete操作对应的事务id是108532
rmgr: Transaction len (rec/tot): 12/ 44, tx: 108526, lsn: 0/0A0CF2D0, prev 0/0A0CF280, bkp: 0000, desc: commit: 2021-09-30
15:07:57.784808 CST
rmgr: Heap len (rec/tot): 34/ 66, tx: 108527, lsn: 0/0A0CF300, prev 0/0A0CF2D0, bkp: 0000, desc: insert(init): rel 1
663/16393/32871; tid 0/1
rmgr: Transaction len (rec/tot): 12/ 44, tx: 108527, lsn: 0/0A0CF348, prev 0/0A0CF300, bkp: 0000, desc: commit: 2021-09-30
15:08:09.698156 CST
rmgr: Heap len (rec/tot): 34/ 66, tx: 108528, lsn: 0/0A0CF378, prev 0/0A0CF348, bkp: 0000, desc: insert: rel 1663/16
393/32871; tid 0/2
rmgr: Transaction len (rec/tot): 12/ 44, tx: 108528, lsn: 0/0A0CF3C0, prev 0/0A0CF378, bkp: 0000, desc: commit: 2021-09-30
15:08:18.007196 CST
rmgr: Heap len (rec/tot): 34/ 66, tx: 108529, lsn: 0/0A0CF3F0, prev 0/0A0CF3C0, bkp: 0000, desc: insert: rel 1663/16
393/32871; tid 0/3
rmgr: Transaction len (rec/tot): 12/ 44, tx: 108529, lsn: 0/0A0CF438, prev 0/0A0CF3F0, bkp: 0000, desc: commit: 2021-09-30
15:08:25.447322 CST
rmgr: Heap len (rec/tot): 34/ 66, tx: 108530, lsn: 0/0A0CF468, prev 0/0A0CF438, bkp: 0000, desc: insert: rel 1663/16
393/32871; tid 0/4
rmgr: Transaction len (rec/tot): 12/ 44, tx: 108530, lsn: 0/0A0CF4B0, prev 0/0A0CF468, bkp: 0000, desc: commit: 2021-09-30
15:08:34.940943 CST
rmgr: Heap len (rec/tot): 34/ 66, tx: 108531, lsn: 0/0A0CF4E0, prev 0/0A0CF4B0, bkp: 0000, desc: insert: rel 1663/16
393/32871; tid 0/5
rmgr: Transaction len (rec/tot): 12/ 44, tx: 108531, lsn: 0/0A0CF528, prev 0/0A0CF4E0, bkp: 0000, desc: commit: 2021-09-30
15:08:45.259812 CST
rmgr: Heap len (rec/tot): 26/ 58, tx: 108532, lsn: 0/0A0CF558, prev 0/0A0CF528, bkp: 0000, desc: delete: rel 1663/16
393/32871; tid 0/4 KEYS_UPDATED
rmgr: Transaction len (rec/tot): 12/ 44, tx: 108532, lsn: 0/0A0CF598, prev 0/0A0CF558, bkp: 0000, desc: commit: 2021-09-30
15:09:42.075268 CST
rmgr: XLOG len (rec/tot): 72/ 104, tx: 0, lsn: 0/0A0CF5C8, prev 0/0A0CF598, bkp: 0000, desc: checkpoint: redo 0/
A0CF5C8; tli 1; prev tli 1; fpw true; xid 0/108533; oid 41063; multi 1; offset 0; oldest xid 1884 in DB 1; oldest multi 1 in DB 1; old
est running xid 0; online-- 关闭数据库
[postgres@redhat762100 pg_xlog]$ pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped
[postgres@redhat762100 pg_xlog]$-- 通过事务id 108532 来重置xlog ，注意这里没有-D，具体用法可以参考下help
pg_resetxlog -x 108532 /pgdata/94/data
[postgres@redhat762100 pg_xlog]$ pg_resetxlog -x 108532 /pgdata/94/data
Transaction log reset
[postgres@redhat762100 pg_xlog]$ -- 启动数据库，查看
[postgres@redhat762100 pg_xlog]$ pg_ctl start -D /pgdata/94/data
server starting
[postgres@redhat762100 pg_xlog]$ LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".-- 查看数据，发现被删除的id=4的数据，回来了。
mydb=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=#
mydb=#
mydb=# select * from t1;
id | name
----+------
1 | aa
2 | bb
3 | cc
4 | dd
5 | ee
(5 rows)
mydb=# END