PostgreSQL 在WAL文件中查找drop table、update、delete操作

配套视频

要还原数据前必须查找对应的wal文件中的lsn号,步骤如下:

• 根据大致的操作时间先确认大至wal文件的范围.

• 解析此范围内的wal文件然后输出至文本.

• 在文本中根据特征中查找.如果未找到请扩大wal文件范围.

• 找到lsn后就可以恢复数据了

1 相关操作的WAL特征

1.1 查找drop table的正则表达式

#WAL解析后删除表特征字符串:
#   表特征字符串: $PGDATA/pg_tblspc/spc_oid/PG_MAJORVER_CATVER/db_oid/relfilenode
#   例如:pg_tblspc/16387/PG_14_202107181/16389/18616
#       pg_tblspc:固定值
#       16387:表空间OID
#       PG_14_202107181: PG固定值, 14当前安装的pg主要版本号,202107181初始化数据库的时间pg_controldata-> Catalog version number输出
#       16389:数据库OID
#       18616:表OID
pg_tblspc/16387/PG\_[\d]{2}_[\d]{9}/16389/18616
#或者
pg_tblspc/16387/\w+/16389/18616

1.2 查找delete的正则表达式

#WAL解析后删除表中数据的特征字符串:
#   例如:rmgr: Heap        len (rec/tot):     54/    54, tx:        780, lsn: 0/CB000B38, prev 0/CB000B00, desc: DELETE off 30 flags 0x00 KEYS_UPDATED , blkref #0: rel 16387/16389/18634 blk 38820#0: rel 16387/16389/18634 blk 38820
#       16387:表空间OID
#       16389:数据库OID
#       18634:表OID

DELETE(.*)?16387/16389/18634

1.3 查找update的正则表达式

#WAL解析后修改表中数据的特征字符串:
#   例如:rmgr: Heap        len (rec/tot):     71/    71, tx:        758, lsn: 0/410000E8, prev 0/410000B0, desc: HOT_UPDATE off 110 xmax 758 flags 0x60 ; new off 121 xmax 0, blkref #0: rel 16387/16389/18634 blk 26279
#       16387:表空间OID
#       16389:数据库OID
#       18634:表OID

UPDATE(.*)?16387/16389/18634

2 获取drop table的lsn

#显示所有归档的WAL文件,注意查看日期
ls -la --time-style=long-iso /archive | egrep '[A-Za-z0-9]{24}$'


#在指定的WAL范围(由ls日期来确定)和删除表特征查找lsn
rm -rf ~/wal_find_results.txt && \
pg_waldump -p /archive  000000010000000000000044 000000010000000000000065 > ~/wal_find_results.txt

#使用"4.1 解析后的WAL特征"中的正则表达式搜索.
cat<~/wal_find_results.txt | grep -E 'pg_tblspc/16387/\w+/16389/18616'

#得到结果lsn 0/3FFFA8A0,根据0/3FFFA8A0定位wal文件
ssh postgres@pgser01 "/usr/local/pgsql/bin/psql -h localhost -U test -d test -c \"select pg_walfile_name('0/63A653C0')\"";

#解析WAL文件再次确认lsn
pg_waldump -p /archive  000000010000000000000063
#0/63A64F38

3 获取delete的lsn

#显示所有归档的WAL文件,注意查看日期
ls -la --time-style=long-iso /archive | egrep '[A-Za-z0-9]{24}$'

#在指定的WAL范围(由ls日期来确定)和删除表特征查找lsn
rm -rf ~/wal_find_results.txt && \
pg_waldump -p /archive  000000010000000000000044 000000010000000000000065 > ~/wal_find_results.txt

#使用"4.1 解析后的WAL特征"中的正则表达式搜索.
cat<~/wal_find_results.txt | grep -E 'DELETE(.*)?16387/16389/18634'


#得到结果lsn 0/3FFFA8A0,根据0/3FFFA8A0定位wal文件
ssh postgres@pgser01 "/usr/local/pgsql/bin/psql -h localhost -U test -d test -c \"select pg_walfile_name('0/64000060')\"";


#解析WAL文件再次确认lsn
pg_waldump -p /archive  000000010000000000000064
#0/64000028

4 获取update的lsn

#显示所有归档的WAL文件,注意查看日期
ls -la --time-style=long-iso /archive | egrep '[A-Za-z0-9]{24}$'

#在指定的WAL范围(由ls日期来确定)和删除表特征查找lsn
rm -rf ~/wal_find_results.txt && \
pg_waldump -p /archive  000000010000000000000044 000000010000000000000065 > ~/wal_find_results.txt

#使用"4.1 解析后的WAL特征"中的正则表达式搜索.
cat<~/wal_find_results.txt | grep -E 'UPDATE(.*)?16387/16389/18634'


#得到结果lsn 0/3FFFA8A0,根据0/3FFFA8A0定位wal文件
ssh postgres@pgser01 "/usr/local/pgsql/bin/psql -h localhost -U test -d test -c \"select pg_walfile_name('0/650000E8')\"";


#解析WAL文件再次确认lsn
pg_waldump -p /archive  000000010000000000000065
#0/650000B0

5 其它

wal最多保存一年,也就是相关的历史数据最多保存一年.一年前的数据将无法恢复.可以用shell调用pg_archivecleanup实现