备份数据恢复
恢复全量数据
[udb@udb-81 data]$ mkdir /data/udb
[udb@udb-81 data]$ cp /data/backup/base_2022-08-23-134814/* /data/udb
[udb@udb-81 udb]$ tar zxf tar zxf base.tar.gz -C /data/udb
[udb@udb-81 udb]$ tar zxf /data/udb/ud_wal.tar.gz -C /data/udb/ud_wal/
[root@udb-81 udb]# chown -R udb.udb /data/udb
[root@udb-81 udb]# systemctl start unvdb
[root@udb-81 udb]# ud_sql
ud_sql (2.4)
Type "help" for help.
unvdb=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
a | unvdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
b | unvdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
此时数据已完整恢复到备份时的状态。
结合归档备份，恢复到指定时间
模拟误操作
unvdb=# create database a;
CREATE DATABASE
#创建数据库a
unvdb=# drop database a;
DROP DATABASE
#不小心删除了数据库a，下面尝试恢复它
unvdb=# select pg_switch_wal() ;
#手动触发归档，实际环境会根据wal归档规则自动处理
pg_switch_wal
---------------
0/18003B38
(1 row)
分析要恢复的时间点
gunzip < /data/backup/archive/20220823/000000070000000000000018 > /tmp/000000070000000000000018#解压归档，分析wal,定位要恢复的时间点或lsn位置
ud_waldump /tmp/000000070000000000000018
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/18000028, prev 0/170019E0, desc: RUNNING_XACTS nextXid 53112 latestCompletedXid 53111 oldestRunningXid 53112
rmgr: XLOG len (rec/tot): 49/ 6057, tx: 0, lsn: 0/18000060, prev 0/18000028, desc: FPI_FOR_HINT , blkref #0: rel 1664/0/1262 blk 0 FPW
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/18001810, prev 0/18000060, desc: RUNNING_XACTS nextXid 53112 latestCompletedXid 53111 oldestRunningXid 53112
rmgr: XLOG len (rec/tot): 49/ 545, tx: 0, lsn: 0/18001848, prev 0/18001810, desc: FPI_FOR_HINT , blkref #0: rel 1664/0/2671 blk 1 FPW
rmgr: Heap2 len (rec/tot): 60/ 60, tx: 53112, lsn: 0/18001A70, prev 0/18001848, desc: NEW_CID rel 1664/0/1262; tid 0/25; cmin: 0, cmax: 4294967295, combo: 4294967295
rmgr: Heap len (rec/tot): 291/ 291, tx: 53112, lsn: 0/18001AB0, prev 0/18001A70, desc: INSERT off 25 flags 0x00, blkref #0: rel 1664/0/1262 blk 0
rmgr: Btree len (rec/tot): 64/ 64, tx: 53112, lsn: 0/18001BD8, prev 0/18001AB0, desc: INSERT_LEAF off 2, blkref #0: rel 1664/0/2671 blk 1
rmgr: Btree len (rec/tot): 53/ 553, tx: 53112, lsn: 0/18001C18, prev 0/18001BD8, desc: INSERT_LEAF off 23, blkref #0: rel 1664/0/2672 blk 1 FPW
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/18001E48, prev 0/18001C18, desc: RUNNING_XACTS nextXid 53113 latestCompletedXid 53111 oldestRunningXid 53112; 1 xacts: 53112
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/18001E80, prev 0/18001E48, desc: CHECKPOINT_ONLINE redo 0/18001E48; tli 7; prev tli 7; fpw true; xid 0:53113; oid 32810; multi 1; offset 0; oldest xid 727 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 53112; online
rmgr: Database len (rec/tot): 42/ 42, tx: 53112, lsn: 0/18001EF8, prev 0/18001E80, desc: CREATE copy dir 1663/1 to 1663/24619
#在这里创建了数据库
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/18001F28, prev 0/18001EF8, desc: RUNNING_XACTS nextXid 53113 latestCompletedXid 53111 oldestRunningXid 53112; 1 xacts: 53112
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/18001F60, prev 0/18001F28, desc: CHECKPOINT_ONLINE redo 0/18001F28; tli 7; prev tli 7; fpw true; xid 0:53113; oid 32810; multi 1; offset 0; oldest xid 727 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 53112; online
rmgr: Transaction len (rec/tot): 46/ 46, tx: 53112, lsn: 0/18001FD8, prev 0/18001F60, desc: INVALIDATION ; inval msgs: catcache 21
rmgr: Transaction len (rec/tot): 66/ 66, tx: 53112, lsn: 0/18002020, prev 0/18001FD8, desc: COMMIT 2022-08-23 14:45:41.510217 CST; inval msgs: catcache 21; sync
#commit提交
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/18002068, prev 0/18002020, desc: RUNNING_XACTS nextXid 53113 latestCompletedXid 53112 oldestRunningXid 53113
rmgr: XLOG len (rec/tot): 49/ 6325, tx: 0, lsn: 0/180020A0, prev 0/18002068, desc: FPI_FOR_HINT , blkref #0: rel 1664/0/1262 blk 0 FPW
rmgr: Heap2 len (rec/tot): 60/ 60, tx: 53113, lsn: 0/18003958, prev 0/180020A0, desc: NEW_CID rel 1664/0/1262; tid 0/25; cmin: 4294967295, cmax: 0, combo: 4294967295
rmgr: Heap len (rec/tot): 54/ 54, tx: 53113, lsn: 0/18003998, prev 0/18003958, desc: DELETE off 25 flags 0x00 KEYS_UPDATED , blkref #0: rel 1664/0/1262 blk 0
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/180039D0, prev 0/18003998, desc: RUNNING_XACTS nextXid 53114 latestCompletedXid 53112 oldestRunningXid 53113; 1 xacts: 53113
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/18003A08, prev 0/180039D0, desc: CHECKPOINT_ONLINE redo 0/180039D0; tli 7; prev tli 7; fpw true; xid 0:53114; oid 32810; multi 1; offset 0; oldest xid 727 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 53113; online
rmgr: Database len (rec/tot): 38/ 38, tx: 53113, lsn: 0/18003A80, prev 0/18003A08, desc: DROP dir 1663/24619
#这里是删除操作
rmgr: Transaction len (rec/tot): 46/ 46, tx: 53113, lsn: 0/18003AA8, prev 0/18003A80, desc: INVALIDATION ; inval msgs: catcache 21
rmgr: Transaction len (rec/tot): 66/ 66, tx: 53113, lsn: 0/18003AD8, prev 0/18003AA8, desc: COMMIT 2022-08-23 14:45:48.894563 CST; inval msgs: catcache 21; sync
#删除提交
rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: 0/18003B20, prev 0/18003AD8, desc: SWITCH
从日志得出要恢复的时间点是2022-08-23 14:45:41.510217
开始恢复到指定时间点
[root@udb-81 data]# su udb
[udb@udb-81 data]$ mkdir /data/udb
#准备空白目录
[udb@udb-81 data]$ tar zxf /data/backup/base_2022-08-23-134814/base.tar.gz -C /data/udb/
#解压全量备份
[udb@udb-81 data]$ tar zxf /data/backup/base_2022-08-23-134814/ud_wal.tar.gz -C /data/udb/ud_wal/
[udb@udb-81 data]$ cp -pa /data/backup/archive/20220823 /data/udb/archive
#复制增量归档文件
vi /data/udb/unvdbsvr.conf
restore_command = 'gunzip < /data/udb/archive/%f > %p'
recovery_target_time ='2022-08-23 14:45:41.510217'
#配置要恢复的时间点
touch /data/udb/recovery.signal
#创建恢复标志
chmod -R 0750 /data/udb
chown udb.udb -R /data/udb/
#修改文件权限
[root@udb-81 udb]# systemctl start unvdb
#启动服务
[root@udb-81 udb]# ud_sql
ud_sql (2.4)
Type "help" for help.
unvdb=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
a | unvdb | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
#验证数据，已成功恢复想要的数据