PostgreSQL 恢复误删数据的操作
在Oracle中；删除表或者误删表记录；有个闪回特性，不需要停机操作，可以完美找回记录。当然也有一些其他的恢复工具；例如odu工具，gdul工具。都可以找回数据。而PostgreSQL目前没有闪回特性。如何在不停机情况下恢复误删数据。还好是有完整的热备份。
本文描述的方法是：利用热备份在另一台服务器进行数据恢复；再导入正式环境；这样不影响数据库操作。这方法也适用在Oracle恢复。必须满足几个条件
1、有完整的基础数据文件备份和归档文件备份.所以备份是很重要的。
2、有一台装好同款Postgres软件的服务器
实例模拟讲解
过程模拟误删表tbl_lottu_drop后；后续进行dml/ddl操作；表明正式数据库还是进行正常工作。在另外一台数据库基于数据库PITR恢复。恢复表tbl_lottu_drop的数据。
1、创建一个有效的备份
Postgres201：线上数据库服务器 Postgres202：操作服务器 postgres=#selectpg_start_backup(now()::text); pg_start_backup ----------------- 0/F000060 (1row) [postgres@Postgres201~]$rsync-acvz-L--exclude"pg_xlog"--exclude"pg_log"$PGDATA/data/backup/20180428 postgres=#selectpg_stop_backup(); NOTICE:pg_stop_backupcomplete,allrequiredWALsegmentshavebeenarchived pg_stop_backup ---------------- 0/F000168 (1row)
2、模拟误操作
2.1创建一个需要恢复对象表tbl_lottu_drop。并插入1000记录。也保证数据从数据缓存写入磁盘中。
lottu=>createtabletbl_lottu_drop(idint); CREATETABLE lottu=>insertintotbl_lottu_dropselectgenerate_series(1,1000); INSERT01000 lottu=>\clottupostgres Youarenowconnectedtodatabase"lottu"asuser"postgres".
2.2这个获取一个时间：用于后面基于数据库PITR恢复(当然现实操作后只能记住一个大概的时间；还往往是不准；可能记住的时间是误操作之后。后面有讲解如何获取需要恢复到那个时间点)
lottu=#selectnow(); now ------------------------------- 2018-04-2820:47:31.617808+08 (1row) lottu=#checkpoint; CHECKPOINT lottu=#selectpg_xlogfile_name(pg_switch_xlog()); pg_xlogfile_name -------------------------- 000000030000000000000010 (1row)
2.3进行drop表
lottu=#droptabletbl_lottu_drop; DROPTABLE
2.4后续进行dml/ddl操作；表明正式数据库还是进行正常工作
lottu=#createtabletbl_lottu_log(idint); CREATETABLE lottu=#insertintotbl_lottu_logvalues(1),(2); INSERT02 lottu=#checkpoint; CHECKPOINT lottu=#selectpg_xlogfile_name(pg_switch_xlog()); pg_xlogfile_name -------------------------- 000000030000000000000011 (1row)
3、恢复操作
3.1将备份拷贝到Postgres202数据库上
[postgres@Postgres20120180428]$cd/data/backup/20180428 [postgres@Postgres20120180428]$ll total4 drwx------.18postgrespostgres4096Apr2820:42data [postgres@Postgres20120180428]$rsync-acvz-Ldatapostgres@192.168.1.202:/data/postgres
3.2删除不必要的文件
[postgres@Postgres202data]$cd$PGDATA [postgres@Postgres202data]$rmbackup_label.oldpostmaster.pidtablespace_map.old
3.3还原备份表空间软链接
[postgres@Postgres202data]$cattablespace_map 16385/data/pg_data/lottu [postgres@Postgres202data]$mkdir-p/data/pg_data [postgres@Postgres202data]$cdpg_tblspc/ [postgres@Postgres202pg_tblspc]$mv16385//data/pg_data/lottu [postgres@Postgres202pg_tblspc]$ln-s/data/pg_data/lottu./16385 [postgres@Postgres202pg_tblspc]$ll total0 lrwxrwxrwx.1postgrespostgres19Apr2823:1216385->/data/pg_data/lottu
3.4将wal日志拷贝到Postgres202数据库上pg_xlog目录下；从哪个日志开始拷贝？
[postgres@Postgres202data]$mkdir-ppg_xlog/archive_status [postgres@Postgres202data]$catbackup_label STARTWALLOCATION:0/F000060(file00000003000000000000000F) CHECKPOINTLOCATION:0/F000098 BACKUPMETHOD:pg_start_backup BACKUPFROM:master STARTTIME:2018-04-2820:42:15CST LABEL:2018-04-2820:42:13.244358+08
查看backup_label；知道00000003000000000000000F开始到正在写入的wal日志。
[postgres@Postgres202pg_xlog]$ll total65540 -rw-------.1postgrespostgres16777216Apr2820:4200000003000000000000000F -rw-------.1postgrespostgres313Apr2820:4200000003000000000000000F.00000060.backup -rw-------.1postgrespostgres16777216Apr2820:48000000030000000000000010 -rw-------.1postgrespostgres16777216Apr2820:50000000030000000000000011 -rw-------.1postgrespostgres16777216Apr2820:55000000030000000000000012
3.5编辑recovery.conf文件
[postgres@Postgres202data]$virecovery.conf restore_command='cp/data/arch/%f%p'#e.g.'cp/mnt/server/archivedir/%f%p' recovery_target_time='2018-04-2820:47:31.617808+08' recovery_target_inclusive=false recovery_target_timeline='latest'
3.6启动数据库；并验证数据
[postgres@Postgres202data]$pg_start serverstarting [postgres@Postgres202data]$ps-ef|greppostgres root10981083022:32pts/000:00:00su-postgres postgres10991098022:32pts/000:00:00-bash root12101195022:55pts/100:00:00su-postgres postgres12111210022:55pts/100:00:00-bash postgres14421123:16pts/000:00:00/opt/pgsql96/bin/postgres postgres14501442023:16?00:00:00postgres:checkpointerprocess postgres14511442023:16?00:00:00postgres:writerprocess postgres14591442023:16?00:00:00postgres:walwriterprocess postgres14601442023:16?00:00:00postgres:autovacuumlauncherprocess postgres14611442023:16?00:00:00postgres:archiverprocesslastwas00000005.history postgres14621442023:16?00:00:00postgres:statscollectorprocess postgres14641099023:16pts/000:00:00ps-ef postgres14651099023:16pts/000:00:00greppostgres [postgres@Postgres202data]$psql psql(9.6.0) Type"help"forhelp. postgres=#\clottulottu Youarenowconnectedtodatabase"lottu"asuser"lottu". lottu=>\dt Listofrelations Schema|Name|Type|Owner --------+----------------+-------+------- public|pitr_test|table|lottu public|tbl_lottu_drop|table|lottu lottu=>selectcount(1)fromtbl_lottu_drop; count ------- 1000 (1row)
从这看数据是恢复了；copy到线上数据库操作略。
延伸点
下面讲解下如何找到误操作的时间。即recovery_target_time='2018-04-2820:47:31.617808+08'的时间点。上文是前面已经获取的；
1.用pg_xlogdump解析这段日志。
[postgres@Postgres201pg_xlog]$pg_xlogdump-b00000003000000000000000F000000030000000000000012>lottu.log pg_xlogdump:FATAL:errorinWALrecordat0/12000648:invalidrecordlengthat0/12000680:wanted24,got0
2.从lottu.log中可以找到这段日志
rmgr:Transactionlen(rec/tot):8/34,tx:1689,lsn:0/100244A0,prev0/10024460,desc:COMMIT2018-04-2820:45:49.736013CST rmgr:Standbylen(rec/tot):24/50,tx:0,lsn:0/100244C8,prev0/100244A0,desc:RUNNING_XACTSnextXid1690latestCompletedXid1689oldestRunningXid1690 rmgr:Heaplen(rec/tot):3/3130,tx:1690,lsn:0/10024500,prev0/100244C8,desc:INSERToff9 blkref#0:rel16385/16386/2619forkmainblk15(FPW);hole:offset:60,length:5116 rmgr:Btreelen(rec/tot):2/7793,tx:1690,lsn:0/10025140,prev0/10024500,desc:INSERT_LEAFoff385 blkref#0:rel16385/16386/2696forkmainblk1(FPW);hole:offset:1564,length:452 rmgr:Heaplen(rec/tot):2/184,tx:1690,lsn:0/10026FD0,prev0/10025140,desc:INPLACEoff16 blkref#0:rel16385/16386/1259forkmainblk0 rmgr:Transactionlen(rec/tot):88/114,tx:1690,lsn:0/10027088,prev0/10026FD0,desc:COMMIT2018-04-2820:46:37.718442CST;invalmsgs:catcache49catcache45catcache44relcache32784 rmgr:Standbylen(rec/tot):24/50,tx:0,lsn:0/10027100,prev0/10027088,desc:RUNNING_XACTSnextXid1691latestCompletedXid1690oldestRunningXid1691 rmgr:Standbylen(rec/tot):24/50,tx:0,lsn:0/10027138,prev0/10027100,desc:RUNNING_XACTSnextXid1691latestCompletedXid1690oldestRunningXid1691 rmgr:XLOGlen(rec/tot):80/106,tx:0,lsn:0/10027170,prev0/10027138,desc:CHECKPOINT_ONLINEredo0/10027138;tli3;prevtli3;fpwtrue;xid0:1691;oid40976;multi1;offset0;oldestxid1668inDB1;oldestmulti1inDB1;oldest/newestcommittimestampxid:0/0;oldestrunningxid1691;online rmgr:Standbylen(rec/tot):24/50,tx:0,lsn:0/100271E0,prev0/10027170,desc:RUNNING_XACTSnextXid1691latestCompletedXid1690oldestRunningXid1691 rmgr:Standbylen(rec/tot):24/50,tx:0,lsn:0/10027218,prev0/100271E0,desc:RUNNING_XACTSnextXid1691latestCompletedXid1690oldestRunningXid1691 rmgr:XLOGlen(rec/tot):80/106,tx:0,lsn:0/10027250,prev0/10027218,desc:CHECKPOINT_ONLINEredo0/10027218;tli3;prevtli3;fpwtrue;xid0:1691;oid40976;multi1;offset0;oldestxid1668inDB1;oldestmulti1inDB1;oldest/newestcommittimestampxid:0/0;oldestrunningxid1691;online rmgr:XLOGlen(rec/tot):0/24,tx:0,lsn:0/100272C0,prev0/10027250,desc:SWITCH rmgr:Standbylen(rec/tot):24/50,tx:0,lsn:0/11000028,prev0/100272C0,desc:RUNNING_XACTSnextXid1691latestCompletedXid1690oldestRunningXid1691 rmgr:Standbylen(rec/tot):16/42,tx:1691,lsn:0/11000060,prev0/11000028,desc:LOCKxid1691db16386rel32784 rmgr:Heaplen(rec/tot):8/2963,tx:1691,lsn:0/11000090,prev0/11000060,desc:DELETEoff16KEYS_UPDATED blkref#0:rel16385/16386/1247forkmainblk8(FPW);hole:offset:88,length:5288
根据“32784”日志可以看到是表tbl_lottu_drop在2018-04-2820:46:37.718442插入1000条记录(所以恢复时间点选2018-04-2820: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"createtabletest00(idintprimarykey,infotext)" SatApr110:09:55CST2017 date;psql-c"insertintotest00values(generate_series(1,50000),repeat(md5(random()::text),1000))" SatApr110:10:10CST2017 date;psql-c"createtabletest01(idintprimarykey,infotext)" SatApr110:10:48CST2017 date;psql-c"insertintotest01values(generate_series(1,50000),repeat(md5(random()::text),1000))" SatApr110:10:53CST2017
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-lbasebackup21
4数据库上继续进行业务操作（模拟在基础备份后，业务继续下发，然后发生故障）
date;psql-c"createtabletest02(idintprimarykey,infotext)" SatApr110:15:59CST2017 date;psql-c"insertintotest02values(generate_series(1,100000),repeat(md5(random()::text),1000))" SatApr110:16:09CST2017
时间轴（第三行的缩写代表CreateInsert）
-10:09:55----10:10:10-----10:10:48-----10:10:53------10:15:59----10:16:09-- ----|-----------|------------|------------|-------------|-----------|------ Ctest00-----Itest00-----Ctest01-----Itest01-----Ctest01-----Itest01--
情况1
没有设置archive_timeout，整个数据目录被删除
5数据目录被rm-rf掉了（模拟误操作）
rm-rfpg_root20/
6归档恢复
cp-rpg_root21pg_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-rfpg_root20/
6归档恢复
cp-rpg_root21pg_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-rfpg_root20/
6归档恢复
cp-rpg_root21pg_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-110:09:47'（基础备份时间之前）
这里------------------------------------------------------------------------ -10:09:55----10:10:10-----10:10:48-----10:10:53------10:15:59----10:16:09-- ----|-----------|------------|------------|---basebackup---|--------|------ Ctest00-----Itest00-----Ctest01-----Itest01-----Ctest02-----Itest02--
结果：
恢复时间定到了基础备份之前，所以这里会恢复到最早时间点：基础备份点。
LOG:recoverystoppingbeforecommitoftransaction1175,time2017-04-0110:15:59.597495+08
注意：无法恢复到基础备份之前的点，所以再做基础备份时，请保证数据一致性。
(2)recovery_target_time=‘2017-4-110:10:00'（基础备份时间之前）
---------这里--------------------------------------------------------------- -10:09:55----10:10:10-----10:10:48-----10:10:53------10:15:59----10:16:09-- ----|-----------|------------|------------|---basebackup---|--------|------ Ctest00-----Itest00-----Ctest01-----Itest01-----Ctest02-----Itest02--
结果：
恢复时间定到了基础备份之前，所以这里会恢复到最早时间点：基础备份点。
LOG:recoverystoppingbeforecommitoftransaction1175,time2017-04-0110:15:59.597495+08
注意：无法恢复到基础备份之前的点，所以再做基础备份时，请保证数据一致性。
(3)recovery_target_time=‘2017-4-110:16:00'
-------------------------------------------------------------这里----------- -10:09:55----10:10:10-----10:10:48-----10:10:53------10:15:59----10:16:09-- ----|-----------|------------|------------|---basebackup---|--------|------ Ctest00-----Itest00-----Ctest01-----Itest01-----Ctest02-----Itest02--
结果：
表test02存在，但没有数据。说明如果时间在基础备份点之后，可以恢复到任意时间点。恢复后会创建新时间线。
LOG:lastcompletedtransactionwasatlogtime2017-04-0110:15:59.597495+08
(3.1)在(3)的基础上继续进行恢复：recovery_target_time=‘2017-4-110:17:00'
------------------------------------------------------------------------这里 -10:09:55----10:10:10-----10:10:48-----10:10:53------10:15:59----10:16:09-- ----|-----------|------------|------------|---basebackup---|--------|------ Ctest00-----Itest00-----Ctest01-----Itest01-----Ctest02-----Itest02--
结果：
同3，这次恢复创建了一条新的时间线3，这条时间线上面进行恢复的话，数据库会去archive里面去找时间线2的xlog，但是归档目录中的日志应该都是时间线1的，所以会报错找不到xlog。
cp:cannotstatpathto/archive/00000002000000000000000A':Nosuchfileordirectory
注意：根据上述结论，请在每次恢复时都使用原始归档文件，即如果尝试再次恢复，请重新使用基础备份进行恢复，不要在前一次恢复的基础上继续进行，否则由于时间线切换，会找不到归档文件。
其他
压缩的归档日志
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通常只被温备配置（见Section26.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。你不能恢复到早于该基础备份之前分支出去的时间线。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。