场景
假设有凌晨1点做了全备，当天下午4点误删了数据库，需要恢复到删数据库之前
思路
- 恢复全备数据
- 日志应用(备份归档+凌晨一点到下午2点的归档+在线redo)
- 主要参数
recovery_target_xid = '' # 恢复到事务号
recovery_target_lsn = '' # 恢复到日志序列号
恢复使用这两个参数会更精确一些
一、备份环境
###备份脚本
[postgres@postgresql ~]$ pg_basebackup -D /data/backupsets/ -R -Ft -Pv -Upostgres
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_8831"
32243/32243 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
# 备份集
[postgres@postgresql backupsets]$ ll
total 48872
-rw-------. 1 postgres postgres 178976 Jul 14 17:41 backup_manifest
-rw-------. 1 postgres postgres 33079808 Jul 14 17:41 base.tar
-rw-------. 1 postgres postgres 16778752 Jul 14 17:41 pg_wal.tar
二、模拟drop库
testdb=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop database testdb;
DROP DATABASE
postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
00000001000000000000001E
可以看到当前使用的wal日志文件是00000001000000000000001E，那么drop操作一定是在这个日志中，需要使用pg_waldump工具查看日志文件，找出drop之前的事务号或者日志序列号
三、恢复数据库
[postgres@postgresql ~]$ psql
psql (13.6)
Type "help" for help.
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/1E0008D8
(1 row)
--在关闭数据库前手工切换下日志，确保归档目录下有误操作的日志文件
[postgres@postgresql ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@postgresql ~]$ cd $PGDATA
[postgres@postgresql data]$ rm -rf *
---这里为了方便，删除原库操作
1.恢复全备数据
[postgres@postgresql backupsets]$ tar xf base.tar -C /data/pg13.6/data/
2.日志应用前配置
归档文件
[postgres@postgresql pgarchive]$ ll
total 131080
-rw-------. 1 postgres postgres 16777216 Jul 14 17:35 000000010000000000000018
-rw-------. 1 postgres postgres 16777216 Jul 14 17:35 000000010000000000000019
-rw-------. 1 postgres postgres 16777216 Jul 14 17:37 00000001000000000000001A
-rw-------. 1 postgres postgres 16777216 Jul 14 17:37 00000001000000000000001B
-rw-------. 1 postgres postgres 340 Jul 14 17:37 00000001000000000000001B.00000028.backup
-rw-------. 1 postgres postgres 16777216 Jul 14 17:41 00000001000000000000001C
-rw-------. 1 postgres postgres 16777216 Jul 14 17:41 00000001000000000000001D
-rw-------. 1 postgres postgres 340 Jul 14 17:41 00000001000000000000001D.00000028.backup
-rw-------. 1 postgres postgres 16777216 Jul 14 18:32 00000001000000000000001E
-rw-------. 1 postgres postgres 16777216 Jul 14 18:38 00000001000000000000001F
找出恢复点
[postgres@postgresql pg_wal]$ pg_waldump 00000001000000000000001E
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/1E000028, prev 0/1D000100, desc: RUNNING_XACTS nextXid 511 latestCompletedXid 510 oldestRunningXid 511
rmgr: Heap len (rec/tot): 59/ 1511, tx: 511, lsn: 0/1E000060, prev 0/1E000028, desc: DELETE off 7 flags 0x00 KEYS_UPDATED , blkref #0: rel 1664/0/1262 blk 0 FPW
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/1E000648, prev 0/1E000060, desc: RUNNING_XACTS nextXid 512 latestCompletedXid 510 oldestRunningXid 511; 1 xacts: 511
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/1E000680, prev 0/1E000648, desc: RUNNING_XACTS nextXid 512 latestCompletedXid 510 oldestRunningXid 511; 1 xacts: 511
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/1E0006B8, prev 0/1E000680, desc: CHECKPOINT_ONLINE redo 0/1E000680; tli 1; prev tli 1; fpw true; xid 0:512; oid 24590; 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 511; online
rmgr: Database len (rec/tot): 38/ 38, tx: 511, lsn: 0/1E000730, prev 0/1E0006B8, desc: DROP dir 1663/16398
rmgr: Transaction len (rec/tot): 66/ 66, tx: 511, lsn: 0/1E000758, prev 0/1E000730, desc: COMMIT 2022-07-14 17:42:26.981178 CST; inval msgs: catcache 21; sync
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/1E0007A0, prev 0/1E000758, desc: RUNNING_XACTS nextXid 512 latestCompletedXid 511 oldestRunningXid 512
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/1E0007D8, prev 0/1E0007A0, desc: RUNNING_XACTS nextXid 512 latestCompletedXid 511 oldestRunningXid 512
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/1E000810, prev 0/1E0007D8, desc: CHECKPOINT_ONLINE redo 0/1E0007D8; tli 1; prev tli 1; fpw true; xid 0:512; oid 24590; 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 512; online
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/1E000888, prev 0/1E000810, desc: RUNNING_XACTS nextXid 512 latestCompletedXid 511 oldestRunningXid 512
rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: 0/1E0008C0, prev 0/1E000888, desc: SWITCH
从挖掘的日志来看，只要恢复到事务号511之前即可(drop database 操作在日志中记录为drop dir字眼)如果日志内容过多，可以根据时间点仔细分析，而lsn: 0/1E000028为510的最后序列号
所以postgresql.auto.conf 添加如下内容
[postgres@postgresql data]$ vim postgresql.auto.conf
restore_command='cp /pgarchive/%f %p'
recovery_target_lsn = '0/1E000028'
注：因在原先的机器上，且归档目录不在$PGDATA目录下，归档文件完整，所以没必要解压一遍/data/backupsets/pg_wal.tar到归档目录下
四、启动数据库
[postgres@postgresql data]$ pg_ctl start
waiting for server to start....2022-07-14 18:58:23.398 CST [15123] LOG: redirecting log output to logging collector process
2022-07-14 18:58:23.398 CST [15123] HINT: Future log output will appear in directory "log".
done
server started
[postgres@postgresql data]$ psql
psql (13.6)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# select * from test;
id | name | phone | country | numberrange
----+----------------+----------------+----------+-------------
1 | Wade Sykes | 1-917-342-3132 | Turkey | 3
2 | Barrett Boyer | 1-264-304-0665 | Germany | 9
3 | Alana Kaufman | (213) 254-4997 | India | 0
4 | Emmanuel Lopez | (543) 493-0137 | Germany | 9
5 | Timon Bauer | 1-269-448-2772 | Pakistan | 6
(5 rows)
这时候数据库还是只读模式，需要执行select pg_wal_replay_resume();来结束日志应用
[postgres@postgresql data]$ psql
psql (13.6)
Type "help" for help.
postgres=# create database xx;
ERROR: cannot execute CREATE DATABASE in a read-only transaction
postgres=# select pg_wal_replay_resume();
pg_wal_replay_resume
----------------------
(1 row)
postgres=# create database xx;
CREATE DATABASE
postgres=#
五、部署备份任务计划(仅供参考)
创建backup.sh备份脚本
[postgres@postgresql backupsets]$ cd /data/scripts/
[postgres@postgresql scripts]$ cat backup.sh
#!/bin/bash
DATE=$(date +%Y%m%d)
BACKDIR=/data/backupsets/
REV_DATE=1
function green_echo(){
echo -e "\e[40;32;1m$1\e[0m"
}
function red_echo(){
echo -e "\e[40;31;1m$1\e[0m"
}
#全量备份所有数据到 以备份日期命名的文件夹
function backup(){
pg_basebackup -D /data/backupsets/bkdata_$DATE/ -R -Ft -Pv -Upostgres
}
#开始备份
green_echo "Begin backup data At Date:`date`"
backup
green_echo "End backup data At Date:`date`"
#删除7天前的备份文件
cd $BACKDIR
if [ $? -eq 0 ];then
find ${BACKDIR:=/tmp} -type d -mtime +$REV_DATE |xargs rm -rvf
green_echo "delete Success"
else
red_echo "delete fail please check log!!!"
exit 1
fi
配置crontab任务计划
[postgres@postgresql scripts]$ crontab -l
30 03 * * * /bin/bash /data/scripts/backup.sh >/data/scripts/backup.log 2>&1
最后修改时间：2022-07-15 19:45:10
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
