Delete有备份恢复
数据准备
数据准备插入数据量为500000条，删除后数据量为400000，其中中间还有一次数据更新
本次恢复的要求是把数据恢复到之前的500000条。
-bash-4.2$ psql psql (12.4) Type "help" for help. postgres=# create table t(id int); CREATE TABLE postgres=# insert into t select generate_series(1,500000); INSERT 0 500000 postgres=# delete from t where id<=100000; DELETE 100000 postgres=# update t set id=999999 where id=9999; UPDATE 0 postgres=# update t set id=999999 where id=99999; UPDATE 0 postgres=# update t set id=999999 where id=199999; UPDATE 1 postgres=# select count(*) from t; count -------- 400000 (1 row) |
查看表的filenode
postgres=# select oid from pg_class where relname='t'; oid ------- 16399 (1 row) postgres=# select pg_relation_filenode(16399); pg_relation_filenode ---------------------- 16399 (1 row) postgres=# select pg_relation_filepath(16399); pg_relation_filepath ---------------------- base/14187/16399 (1 row) |
pg_wal日志分析
根据该时间点或者事务号，可以恢复删除的数据。
根据预计的数据删除的时间点去恢复数据，比如数据删除的时间点为：9：10分，因此需要分析9：10之前的一个日志以及9：10分之后的所有日志
其中00000001000000000000000D 为9：09分的日志
000000010000000000000010 为数据库所产生的最新的日志
pg_waldump -b 00000001000000000000000D 000000010000000000000010 > 20220118.log |
提取关键字分析事务号
根据关键字commit，取前10行，后10行的数据，结合前面查出来的pg_relation_filepath（base/14187/16399）判断数据delete的删除的时间点
delete 之前时间点 2022-01-18 11:12:18.553377 CST
xid:523
grep -A 10 -B 10 -i "COMMIT" 20220118.log rmgr: Heap len (rec/tot): 59/ 59, tx: 523, lsn: 0/1ECC0A78, prev 0/1ECC0A38, desc: INSERT off 88 flags 0x00 blkref #0: rel 1663/14187/16399 fork main blk 2212 rmgr: Transaction len (rec/tot): 34/ 34, tx: 523, lsn: 0/1ECC0AB8, prev 0/1ECC0A78, desc: COMMIT 2022-01-18 11:12:18.553377 CST rmgr: Heap len (rec/tot): 54/ 54, tx: 524, lsn: 0/1ECC0AE0, prev 0/1ECC0AB8, desc: DELETE off 1 flags 0x00 KEYS_UPDATED |
同样的方式可以找到update语句的更新时间点
update 时间点 2022-01-18 11:12:59.410691 CST
xid:526
rmgr: Heap len (rec/tot): 54/ 54, tx: 526, lsn: 0/1F2AFCD8, prev 0/1F2AFC90, desc: LOCK off 215: xid 526: flags 0x00 LOCK_ONLY EXCL_LOCK blkref #0: rel 1663/14187/16399 fork main blk 884 rmgr: Heap len (rec/tot): 78/ 78, tx: 526, lsn: 0/1F2AFD10, prev 0/1F2AFCD8, desc: UPDATE off 215 xmax 526 flags 0x03 ; new off 1 xmax 0 blkref #0: rel 1663/14187/16399 fork main blk 0 blkref #1: rel 1663/14187/16399 fork main blk 884 rmgr: Transaction len (rec/tot): 34/ 34, tx: 526, lsn: 0/1F2AFD60, prev 0/1F2AFD10, desc: COMMIT 2022-01-18 11:12:59.410691 CST |
停库
bash-4.2$ pg_ctl stop waiting for server to shut down.... done server stopped -bash-4.2$ |
移动源目录，创建新目录
-bash-4.2$ mv /pgsql/data /pgsql/data_bak20210927 -bash-4.2$ mkdir data -bash-4.2$ ll total 28 drwxr-xr-x 2 postgres postgres 16384 Sep 27 21:40 arclog drwxr-xr-x 4 postgres postgres 38 Sep 27 21:37 backups drwxr-xr-x 2 postgres postgres 6 Sep 27 21:42 data drwx------ 20 postgres postgres 4096 Sep 27 21:40 data_bak20210927 drwxr-xr-x 2 postgres postgres 4096 Sep 27 21:23 pg_rman_arclog drwxr-xr-x 5 postgres postgres 104 Sep 27 11:10 pg_rman_backups drwxr-xr-x 2 postgres postgres 6 Sep 13 16:38 pg_rman_srvlog drwxr-xr-x 2 postgres postgres 117 Sep 27 21:23 scripts |
设置目录权限
-bash-4.2$ chmod 0700 ./data -bash-4.2$ ll total 28 drwxr-xr-x 2 postgres postgres 16384 Sep 27 21:40 arclog drwxr-xr-x 4 postgres postgres 38 Sep 27 21:37 backups drwxrwx--- 2 postgres postgres 6 Sep 27 21:42 data drwx------ 20 postgres postgres 4096 Sep 27 21:40 data_bak20210927 drwxr-xr-x 2 postgres postgres 4096 Sep 27 21:23 pg_rman_arclog drwxr-xr-x 5 postgres postgres 104 Sep 27 11:10 pg_rman_backups drwxr-xr-x 2 postgres postgres 6 Sep 13 16:38 pg_rman_srvlog drwxr-xr-x 2 postgres postgres 117 Sep 27 21:23 scripts |
解压到指定新的数据文件目录
--注意，如果有多个表空间,解压基础包后，可参考tablespace_map 文件，解压其他表空间到指定目录
tar -zxvf 090946.tar.gz -C /pgsql/data/ ...... pg_xact/0000 pg_logical/ pg_logical/snapshots/ pg_logical/mappings/ pg_logical/replorigin_checkpoint PG_VERSION postgresql.auto.conf pg_hba.conf pg_ident.conf log/ log/postgresql-Mon.log log/postgresql-Tue.log postgresql.conf current_logfiles global/pg_control pg_wal/00000001000000000000000C pg_wal/archive_status/00000001000000000000000C.done |
创建recovery.signal
touch /pgsql/data/recovery.signal |
修改 postgresq.conf 文件：
把归档内容注释 #archive_mode = on #archive_command = 'cp %p /var/lib/pgsql/archive/%f' 修改恢复的内容 restore_command = 'cp /var/lib/pgsql/archive/%f %p' recovery_target_xid = '523' --请注意该事务号为之前分析的delete 操作之前的事务号 |
启动验证
pg_ctl start postgres=# select count(*) from public.t; count -------- 500000 (1 row) |
配置文件还原
注释取消 archive_mode = on archive_command = 'cp %p /var/lib/pgsql/archive/%f' 添加注释 #restore_command = 'cp /var/lib/pgsql/archive/%f %p' #recovery_target_xid = '523' --请注意该事务号为之前分析的delete 操作之前的事务号 |
删除recovery.signal
评论
0 点赞