PostgreSQL 恢复误删数据的操作
在oracle中；删除表或者误删表记录；有个闪回特性，不需要停机操作，可以完美找回记录。当然也有一些其他的恢复工具；例如odu工具，gdul工具。都可以找回数据。而postgresql目前没有闪回特性。如何在不停机情况下恢复误删数据。还好是有完整的热备份。
本文描述的方法是：利用热备份在另一台服务器进行数据恢复；再导入正式环境；这样不影响数据库操作。这方法也适用在oracle恢复。必须满足几个条件
1、有完整的基础数据文件备份和归档文件备份.所以备份是很重要的。
2、有一台装好同款postgres软件的服务器
实例模拟讲解
过程模拟误删表tbl_lottu_drop后；后续进行dml/ddl操作；表明正式数据库还是进行正常工作。在另外一台数据库基于数据库pitr恢复。恢复表tbl_lottu_drop的数据。
1、创建一个有效的备份
postgres201 ： 线上数据库服务器 postgres202 ： 操作服务器 postgres=# select pg_start_backup(now()::text); pg_start_backup ----------------- 0/f000060 (1 row) [postgres@postgres201 ~]$ rsync -acvz -l --exclude "pg_xlog" --exclude "pg_log" $pgdata /data/backup/20180428 postgres=# select pg_stop_backup(); notice: pg_stop_backup complete, all required wal segments have been archived pg_stop_backup ---------------- 0/f000168 (1 row)
2、模拟误操作
2.1 创建一个需要恢复对象表tbl_lottu_drop。并插入1000记录。也保证数据从数据缓存写入磁盘中。
lottu=> create table tbl_lottu_drop (id int); create table lottu=> insert into tbl_lottu_drop select generate_series(1,1000); insert 0 1000 lottu=> \c lottu postgres you are now connected to database "lottu" as user "postgres".
2.2 这个获取一个时间：用于后面基于数据库pitr恢复(当然现实操作后只能记住一个大概的时间；还往往是不准；可能记住的时间是误操作之后。后面有讲解如何获取需要恢复到那个时间点)
lottu=# select now();
now
-------------------------------
2018-04-28 20:47:31.617808+08
(1 row)
lottu=# checkpoint;
checkpoint
lottu=# select pg_xlogfile_name(pg_switch_xlog());
pg_xlogfile_name
--------------------------
000000030000000000000010
(1 row)
2.3 进行drop表
lottu=# drop table tbl_lottu_drop; drop table
2.4 后续进行dml/ddl操作；表明正式数据库还是进行正常工作
lottu=# create table tbl_lottu_log (id int); create table lottu=# insert into tbl_lottu_log values (1),(2); insert 0 2 lottu=# checkpoint; checkpoint lottu=# select pg_xlogfile_name(pg_switch_xlog()); pg_xlogfile_name -------------------------- 000000030000000000000011 (1 row)
3、恢复操作
3.1 将备份拷贝到postgres202数据库上
[postgres@postgres201 20180428]$ cd /data/backup/20180428 [postgres@postgres201 20180428]$ ll total 4 drwx------. 18 postgres postgres 4096 apr 28 20:42 data [postgres@postgres201 20180428]$ rsync -acvz -l data postgres@192.168.1.202:/data/postgres
3.2 删除不必要的文件
[postgres@postgres202 data]$ cd $pgdata [postgres@postgres202 data]$ rm backup_label.old postmaster.pid tablespace_map.old
3.3 还原备份表空间软链接
[postgres@postgres202 data]$ cat tablespace_map 16385 /data/pg_data/lottu [postgres@postgres202 data]$ mkdir -p /data/pg_data [postgres@postgres202 data]$ cd pg_tblspc/ [postgres@postgres202 pg_tblspc]$ mv 16385/ /data/pg_data/lottu [postgres@postgres202 pg_tblspc]$ ln -s /data/pg_data/lottu ./16385 [postgres@postgres202 pg_tblspc]$ ll total 0 lrwxrwxrwx. 1 postgres postgres 19 apr 28 23:12 16385 -> /data/pg_data/lottu
3.4 将wal日志拷贝到postgres202数据库上pg_xlog目录下；从哪个日志开始拷贝？
[postgres@postgres202 data]$ mkdir -p pg_xlog/archive_status [postgres@postgres202 data]$ cat backup_label start wal location: 0/f000060 (file 00000003000000000000000f) checkpoint location: 0/f000098 backup method: pg_start_backup backup from: master start time: 2018-04-28 20:42:15 cst label: 2018-04-28 20:42:13.244358+08
查看backup_label；知道00000003000000000000000f开始到正在写入的wal日志。
[postgres@postgres202 pg_xlog]$ ll total 65540 -rw-------. 1 postgres postgres 16777216 apr 28 20:42 00000003000000000000000f -rw-------. 1 postgres postgres 313 apr 28 20:42 00000003000000000000000f.00000060.backup -rw-------. 1 postgres postgres 16777216 apr 28 20:48 000000030000000000000010 -rw-------. 1 postgres postgres 16777216 apr 28 20:50 000000030000000000000011 -rw-------. 1 postgres postgres 16777216 apr 28 20:55 000000030000000000000012
3.5 编辑recovery.conf文件
[postgres@postgres202 data]$ vi recovery.conf restore_command = 'cp /data/arch/%f %p' # e.g. 'cp /mnt/server/archivedir/%f %p' recovery_target_time = '2018-04-28 20:47:31.617808+08' recovery_target_inclusive = false recovery_target_timeline = 'latest'
3.6 启动数据库；并验证数据
[postgres@postgres202 data]$ pg_start server starting [postgres@postgres202 data]$ ps -ef | grep postgres root 1098 1083 0 22:32 pts/0 00:00:00 su - postgres postgres 1099 1098 0 22:32 pts/0 00:00:00 -bash root 1210 1195 0 22:55 pts/1 00:00:00 su - postgres postgres 1211 1210 0 22:55 pts/1 00:00:00 -bash postgres 1442 1 1 23:16 pts/0 00:00:00 /opt/pgsql96/bin/postgres postgres 1450 1442 0 23:16 ? 00:00:00 postgres: checkpointer process postgres 1451 1442 0 23:16 ? 00:00:00 postgres: writer process postgres 1459 1442 0 23:16 ? 00:00:00 postgres: wal writer process postgres 1460 1442 0 23:16 ? 00:00:00 postgres: autovacuum launcher process postgres 1461 1442 0 23:16 ? 00:00:00 postgres: archiver process last was 00000005.history postgres 1462 1442 0 23:16 ? 00:00:00 postgres: stats collector process postgres 1464 1099 0 23:16 pts/0 00:00:00 ps -ef postgres 1465 1099 0 23:16 pts/0 00:00:00 grep postgres [postgres@postgres202 data]$ psql psql (9.6.0) type "help" for help. postgres=# \c lottu lottu you are now connected to database "lottu" as user "lottu". lottu=> \dt list of relations schema | name | type | owner --------+----------------+-------+------- public | pitr_test | table | lottu public | tbl_lottu_drop | table | lottu lottu=> select count(1) from tbl_lottu_drop; count ------- 1000 (1 row)
从这看数据是恢复了；copy到线上数据库操作略。
延伸点
下面讲解下如何找到误操作的时间。即recovery_target_time = '2018-04-28 20:47:31.617808+08'的时间点。上文是前面已经获取的；
1. 用pg_xlogdump解析这段日志。
[postgres@postgres201 pg_xlog]$ pg_xlogdump -b 00000003000000000000000f 000000030000000000000012 > lottu.log pg_xlogdump: fatal: error in wal record at 0/12000648: invalid record length at 0/12000680: wanted 24, got 0
2. 从lottu.log中可以找到这段日志
rmgr: transaction len (rec/tot): 8/ 34, tx: 1689, lsn: 0/100244a0, prev 0/10024460, desc: commit 2018-04-28 20:45:49.736013 cst rmgr: standby len (rec/tot): 24/ 50, tx: 0, lsn: 0/100244c8, prev 0/100244a0, desc: running_xacts nextxid 1690 latestcompletedxid 1689 oldestrunningxid 1690 rmgr: heap len (rec/tot): 3/ 3130, tx: 1690, lsn: 0/10024500, prev 0/100244c8, desc: insert off 9 blkref #0: rel 16385/16386/2619 fork main blk 15 (fpw); hole: offset: 60, length: 5116 rmgr: btree len (rec/tot): 2/ 7793, tx: 1690, lsn: 0/10025140, prev 0/10024500, desc: insert_leaf off 385 blkref #0: rel 16385/16386/2696 fork main blk 1 (fpw); hole: offset: 1564, length: 452 rmgr: heap len (rec/tot): 2/ 184, tx: 1690, lsn: 0/10026fd0, prev 0/10025140, desc: inplace off 16 blkref #0: rel 16385/16386/1259 fork main blk 0 rmgr: transaction len (rec/tot): 88/ 114, tx: 1690, lsn: 0/10027088, prev 0/10026fd0, desc: commit 2018-04-28 20:46:37.718442 cst; inval msgs: catcache 49 catcache 45 catcache 44 relcache 32784 rmgr: standby len (rec/tot): 24/ 50, tx: 0, lsn: 0/10027100, prev 0/10027088, desc: running_xacts nextxid 1691 latestcompletedxid 1690 oldestrunningxid 1691 rmgr: standby len (rec/tot): 24/ 50, tx: 0, lsn: 0/10027138, prev 0/10027100, desc: running_xacts nextxid 1691 latestcompletedxid 1690 oldestrunningxid 1691 rmgr: xlog len (rec/tot): 80/ 106, tx: 0, lsn: 0/10027170, prev 0/10027138, desc: checkpoint_online redo 0/10027138; tli 3; prev tli 3; fpw true; xid 0:1691; oid 40976; multi 1; offset 0; oldest xid 1668 in db 1; oldest multi 1 in db 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 1691; online rmgr: standby len (rec/tot): 24/ 50, tx: 0, lsn: 0/100271e0, prev 0/10027170, desc: running_xacts nextxid 1691 latestcompletedxid 1690 oldestrunningxid 1691 rmgr: standby len (rec/tot): 24/ 50, tx: 0, lsn: 0/10027218, prev 0/100271e0, desc: running_xacts nextxid 1691 latestcompletedxid 1690 oldestrunningxid 1691 rmgr: xlog len (rec/tot): 80/ 106, tx: 0, lsn: 0/10027250, prev 0/10027218, desc: checkpoint_online redo 0/10027218; tli 3; prev tli 3; fpw true; xid 0:1691; oid 40976; multi 1; offset 0; oldest xid 1668 in db 1; oldest multi 1 in db 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 1691; online rmgr: xlog len (rec/tot): 0/ 24, tx: 0, lsn: 0/100272c0, prev 0/10027250, desc: switch rmgr: standby len (rec/tot): 24/ 50, tx: 0, lsn: 0/11000028, prev 0/100272c0, desc: running_xacts nextxid 1691 latestcompletedxid 1690 oldestrunningxid 1691 rmgr: standby len (rec/tot): 16/ 42, tx: 1691, lsn: 0/11000060, prev 0/11000028, desc: lock xid 1691 db 16386 rel 32784 rmgr: heap len (rec/tot): 8/ 2963, tx: 1691, lsn: 0/11000090, prev 0/11000060, desc: delete off 16 keys_updated blkref #0: rel 16385/16386/1247 fork main blk 8 (fpw); hole: offset: 88, length: 5288
根据“32784”日志可以看到是表tbl_lottu_drop在2018-04-28 20:46:37.718442插入1000条记录(所以恢复时间点选2018-04-28 20:47:31.617808+08没毛病)；即也是在事务id为1690操作的。并在事务id为1691进行删除操作。
所以上面的recovery.conf 也可以改写为：
restore_command = 'cp /data/arch/%f %p' # e.g. 'cp /mnt/server/archivedir/%f %p' recovery_target_xid = '1690' recovery_target_inclusive = false recovery_target_timeline = 'latest'
补充：postgresql多种恢复实例分析
postgresql归档恢复实例分析(时间线机制)
这篇文章根据实例介绍postgresql归档恢复的方法，时间线的含义。
1 参数配置
sed -ir "s/#*max_wal_senders.*/max_wal_senders = 10/" $pgdata/postgresql.conf
sed -ir "s/#*wal_level.*/wal_level = replica/" $pgdata/postgresql.conf
sed -ir "s/#*archive_mode.*/archive_mode = on/" $pgdata/postgresql.conf
sed -ir "s/#*archive_command.*/archive_command = 'test ! -f \${pghome}\/archive\/%f \&\& cp %p \${pghome}\/archive\/%f'/" $pgdata/postgresql.conf
2 数据操作
date;psql -c "create table test00 (id int primary key, info text)" sat apr 1 10:09:55 cst 2017 date;psql -c "insert into test00 values(generate_series(1,50000), repeat(md5(random()::text), 1000))" sat apr 1 10:10:10 cst 2017 date;psql -c "create table test01 (id int primary key, info text)" sat apr 1 10:10:48 cst 2017 date;psql -c "insert into test01 values(generate_series(1,50000), repeat(md5(random()::text), 1000))" sat apr 1 10:10:53 cst 2017
3 制作基础备份
sed -ir "s/#*max_wal_senders.*/max_wal_senders = 10/" $pgdata/postgresql.conf
配置pg_hba.conf通道
pg_basebackup -fp -p -x -d ~/bin/data/pg_root21 -l basebackup21
4 数据库上继续进行业务操作（模拟在基础备份后，业务继续下发，然后发生故障）
date;psql -c "create table test02 (id int primary key, info text)" sat apr 1 10:15:59 cst 2017 date;psql -c "insert into test02 values(generate_series(1,100000), repeat(md5(random()::text), 1000))" sat apr 1 10:16:09 cst 2017
时间轴（第三行的缩写代表create insert）
-10:09:55----10:10:10-----10:10:48-----10:10:53------10:15:59----10:16:09-- ----|-----------|------------|------------|-------------|-----------|------ c test00-----i test00-----c test01-----i test01-----c test01-----i test01--
情况1
没有设置archive_timeout，整个数据目录被删除
5 数据目录被rm -rf掉了（模拟误操作）
rm -rf pg_root20/
6 归档恢复
cp -r pg_root21 pg_root20
修改pg_hba.conf阻止用户连接
cp $pghome/share/recovery.conf.sample ./recovery.conf
sed -ir "s/#*restore_command.*/restore_command = 'cp \${pghome}\/archive\/%f %p'/" $pgdata/recovery.conf
7 恢复结果
test02存在，但是其中的数据被认为是未提交事务，表为空（最后一个xlog文件的内容全部遗失了）。
情况2
设置archive_timeout，整个数据目录被删除，归档timeout为60s，在test02表数据灌入之后，xlog自动切换并归档
(sed -ir "s/#*archive_timeout.*/archive_timeout = 60/" $pgdata/postgresql.conf)
5 数据目录被rm -rf掉了（模拟误操作）
rm -rf pg_root20/
6 归档恢复
cp -r pg_root21 pg_root20
修改pg_hba.conf阻止用户连接
cp $pghome/share/recovery.conf.sample ./recovery.conf
sed -ir "s/#*restore_command.*/restore_command = 'cp \${pghome}\/archive\/%f %p'/" $pgdata/recovery.conf
7 恢复结果
test02存在，数据也存在（由于归档设置了超时切换，最后一个xlog会被归档）。
情况3（重要）
设置archive_timeout，根据估计时间点尝试多次恢复，不能确定想恢复到具体哪个时间点，归档timeout为60s
(sed -ir "s/#*archive_timeout.*/archive_timeout = 60/" $pgdata/postgresql.conf)
5 数据目录被rm -rf掉了（模拟误操作）
rm -rf pg_root20/
6 归档恢复
cp -r pg_root21 pg_root20
修改pg_hba.conf阻止用户连接
cp $pghome/share/recovery.conf.sample ./recovery.conf
sed -ir "s/#*restore_command.*/restore_command = 'cp \${pghome}\/archive\/%f %p'/" $pgdata/recovery.conf
(1) recovery_target_time = ‘2017-4-1 10:09:47' （基础备份时间之前）
这里------------------------------------------------------------------------ -10:09:55----10:10:10-----10:10:48-----10:10:53------10:15:59----10:16:09-- ----|-----------|------------|------------|---basebackup---|--------|------ c test00-----i test00-----c test01-----i test01-----c test02-----i test02--
结果：
恢复时间定到了基础备份之前，所以这里会恢复到最早时间点：基础备份点。
log: recovery stopping before commit of transaction 1175, time 2017-04-01 10:15:59.597495+08
注意：无法恢复到基础备份之前的点，所以再做基础备份时，请保证数据一致性。
(2) recovery_target_time = ‘2017-4-1 10:10:00' （基础备份时间之前）
---------这里--------------------------------------------------------------- -10:09:55----10:10:10-----10:10:48-----10:10:53------10:15:59----10:16:09-- ----|-----------|------------|------------|---basebackup---|--------|------ c test00-----i test00-----c test01-----i test01-----c test02-----i test02--
结果：
恢复时间定到了基础备份之前，所以这里会恢复到最早时间点：基础备份点。
log: recovery stopping before commit of transaction 1175, time 2017-04-01 10:15:59.597495+08
注意：无法恢复到基础备份之前的点，所以再做基础备份时，请保证数据一致性。
(3) recovery_target_time = ‘2017-4-1 10:16:00'
-------------------------------------------------------------这里----------- -10:09:55----10:10:10-----10:10:48-----10:10:53------10:15:59----10:16:09-- ----|-----------|------------|------------|---basebackup---|--------|------ c test00-----i test00-----c test01-----i test01-----c test02-----i test02--
结果：
表test02存在，但没有数据。说明如果时间在基础备份点之后，可以恢复到任意时间点。恢复后会创建新时间线。
log: last completed transaction was at log time 2017-04-01 10:15:59.597495+08
(3.1) 在(3)的基础上继续进行恢复：recovery_target_time = ‘2017-4-1 10:17:00'
------------------------------------------------------------------------这里 -10:09:55----10:10:10-----10:10:48-----10:10:53------10:15:59----10:16:09-- ----|-----------|------------|------------|---basebackup---|--------|------ c test00-----i test00-----c test01-----i test01-----c test02-----i test02--
结果：
同3，这次恢复创建了一条新的时间线3，这条时间线上面进行恢复的话，数据库会去archive里面去找时间线2的xlog，但是归档目录中的日志应该都是时间线1的，所以会报错找不到xlog。
cp: cannot stat pathto/archive/00000002000000000000000a': no such file or directory
注意： 根据上述结论，请在每次恢复时都使用原始归档文件，即如果尝试再次恢复，请重新使用基础备份进行恢复，不要在前一次恢复的基础上继续进行，否则由于时间线切换，会找不到归档文件。
其他
压缩的归档日志
sed -ir "s/#*archive_command.*/archive_command = 'gzip -c %p > \${pghome}\/archive\/%f.gz'/" $pgdata/postgresql.conf
sed -ir "s/#*restore_command.*/restore_command = 'gzip -d -c \${pghome}\/archive\/%f.gz > %p'/" $pgdata/recovery.conf
1、recovery.conf（几个重要参数）
postgresql9.6手册（彭煜玮翻译）
restore_command (string)
用于获取 wal 文件系列的一个已归档段的本地 shell 命令。这个参数是归档恢复所必需的，但是对于流复制是可选的。在该字符串中的任何%f会被替换为从归档中获得的文件的名字，并且任何%p会被在服务器上的复制目标路径名替换（该路径名是相对于当前工作目录的，即集簇的数据目录）。任何%r会被包含上一个可用重启点的文件的名字所替换。在那些必须被保留用于使得一次恢复变成可重启的文件中，这个文件是其中最早的一个，因此这个信息可以被用来把归档截断为支持从当前恢复重启所需的最小值。%r通常只被温备配置（见section 26.2）所使用。要嵌入一个真正的%字符，需要写成%%。很重要的一点是，该命令只有在成功时才返回一个为零的退出状态。该命令将会被询问不存在于归档中的文件名，当这样被询问时它必须返回非零。
recovery_target_time (timestamp)
这个参数指定恢复将进入的时间戳。
recovery_target_xid (string)
这个参数指定恢复将进入的事务 id。记住虽然事务 id 是在事务开始时顺序分配的，但是事务可能以不同的数字顺序完成。那些在指定事务之前（也可以包括该事务）提交的事务将被恢复。精确的停止点也受到recovery_target_inclusive的影响。
recovery_target_timeline (string)
指定恢复到一个特定的时间线中。默认值是沿着基础备份建立时的当前时间线恢复。将这个参数设置为latest会恢复到该归档中能找到的最新的时间线，这在一个后备服务器中有用。除此之外，你只需要在复杂的重恢复情况下设置这个参数，在这种情况下你需要返回到一个状态，该状态本身是在一次时间点恢复之后到达的。相关讨论见section25.3.5
2、关于时间线
postgresql9.6手册（彭煜玮翻译）
将数据库恢复到一个之前的时间点的能力带来了一些复杂性，这和有关时间旅行和平行宇宙的科幻小说有些相似。例如，在数据库的最初历史中，假设你在周二晚上5：15时丢弃了一个关键表，但是一直到周三中午才意识到你的错误。不用苦恼，你取出你的备份，恢复到周二晚上5：14的时间点，并上线运行。在数据库宇宙的这个历史中，你从没有丢弃该表。但是假设你后来意识到这并非一个好主意，并且想回到最初历史中周三早上的某个时间。你没法这样做，在你的数据库在线运行期间，它重写了某些wal段文件，而这些文件本来可以将你引向你希望回到的时间。因此，为了避免出现这种状况，你需要将完成时间点恢复后生成的wal记录序列与初始数据库历史中产生的wal记录序列区分开来。
要解决这个问题，postgresql有一个时间线概念。无论何时当一次归档恢复完成，一个新的时间线被创建来标识恢复之后生成的wal记录序列。时间线id号是wal段文件名的一部分，因此一个新的时间线不会重写由之前的时间线生成的wal数据。实际上可以归档很多不同的时间线。虽然这可能看起来是一个无用的特性，但是它常常扮演救命稻草的角色。考虑到你不太确定需要恢复到哪个时间点的情况，你可能不得不做多次时间点恢复尝试和错误，直到最终找到从旧历史中分支出去的最佳位置。如果没有时间线，该处理将会很快生成一堆不可管理的混乱。而有了时间线，你可以恢复到任何之前的状态，包括早先被你放弃的时间线分支中的状态。
每次当一个新的时间线被创建，postgresql会创建一个“时间线历史”文件，它显示了新时间线是什么时候从哪个时间线分支出来的。系统在从一个包含多个时间线的归档中恢复时，这些历史文件对于允许系统选取正确的wal段文件非常必要。因此，和wal段文件相似，它们也要被归档到wal归档区域。历史文件是很小的文本文件，因此将它们无限期地保存起来的代价很小，而且也是很合适的（而段文件都很大）。如果你喜欢，你可以在一个历史文件中增加注释来记录如何和为什么要创建该时间线。当你由于试验的结果拥有了一大堆错综复杂的不同时间线时，这种注释将会特别有价值。
恢复的默认行为是沿着相同的时间线进行恢复，该时间线是基础备份创建时的当前时间线。如果你希望恢复到某个子女时间线（即，你希望回到在一次恢复尝试后产生的某个状态），你需要在recovery.conf中指定目标时间线id。你不能恢复到早于该基础备份之前分支出去的时间线。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。