PostgreSQL recovers mistakenly deleted data

In Oracle, deleting a table or deleting a table record; There is a flashback feature that does not require a stop operation and can perfectly retrieve the record. There are, of course, some other recovery tools, such as ODU tools, Gdul tools. can retrieve data. PostgreSQL currently does not have a flashback feature. How to recover the data mistakenly deleted in the non-stop condition. Fortunately there is a full hot backup.

The method described in this article is to use a hot backup to perform data recovery on another server, and then import the formal environment, which does not affect database operations. This method is also applicable in Oracle recovery. Several conditions must be met

1. There is a complete base data file backup and archive file backup. So backup is important.
2. There is a server with the same postgres software installed

Example simulation explanation

The process simulates the mistake of deleting the table tbl_lottu_drop; follow-up dml/ddl operation; indicates that the official database is still working properly. A database Pitr recovery is based on another database. Recovers data from table Tbl_lottu_drop.

• Postgres201: On-line database server
• POSTGRES202: Operating Server

1. Create a valid backup

Postgres=#SelectPg_start_backup (Now ()::text); Pg_start_backup----------------- 0/F000060 (1row)[[email protected] ~]$ rsync-Acvz-L--exclude "Pg_xlog"--exclude "Pg_log" $PGDATA/data/backup/20180428Postgres=#SelectPg_stop_backup (); Notice:pg_stop_backup Complete, Allrequired WAL segments have been archived Pg_stop_backup---------------- 0/F000168 (1Row

2. Analog Error operation

2.1 Create a need to restore the object table Tbl_lottu_drop. and insert 1000 records. It also guarantees that data is written to disk from the data cache.

Lottu= Create TableTbl_lottu_drop (IDint);CREATE TABLELottu= Insert  intoTbl_lottu_dropSelectGenerate_series (1, +); INSERT 0  +Lottu=\c Lottu postgresyou is now connected to Database"Lottu" as User"Postgres".

2.2 This gets a time: used for subsequent database-based Pitr recovery (of course, only one approximate time can be remembered after the actual operation; Later there is a tutorial on how to get back to that point in time)

Lottu=#SelectNow (); now------------------------------- 2018-Geneva- -  -: -:31.617808+ ,(1row) Lottu=#Checkpoint;CHECKPOINTLottu=#SelectPg_xlogfile_name (Pg_switch_xlog ()); Pg_xlogfile_name-------------------------- 000000030000000000000010(1Row

2.3 Making a drop table

Lottu=droptable  tbl_lottu_drop; DROP TABLE

2.4 Follow up the DML/DDL operation; indicate that the official database is still working properly

Lottu=#Create TableTbl_lottu_log (IDint);CREATE TABLELottu=#Insert  intoTbl_lottu_logValues(1),(2);INSERT 0 2Lottu=#Checkpoint;CHECKPOINTLottu=#SelectPg_xlogfile_name (Pg_switch_xlog ()); Pg_xlogfile_name-------------------------- 000000030000000000000011(1Row

3. Restore operations

3.1 Copy the backup to the POSTGRES202 database

[Email protected] 20180428]$ cd/data/backup/20180428[postgres@Postgres201 20180428]$4drwx ------. Postgres postgres 4096 Apr 20:42 data[postgres@Postgres201 20180428]$ rsync-acvz-l data [email protected] :/data/postgres

3.2 Deleting unnecessary files

$PGDATA [postgres@Postgres202 data]$ rm backup_label.old Postmaster.pid tablespace_map.old

3.3 Restore Backup table space Soft links

[Email protected] data]$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]$01 postgres postgres Apr 23:12 16385

3.4 Copy the Wal log to the Pg_xlog directory on the Postgres202 database, and from which log to start the copy?

[Email protected] data]$ mkdir-p pg_xlog/archive_status[postgres@Postgres202 data]$0/0/  2018-04-28 20:42:152018-04-28 20:42:13.244358+08

View Backup_label; know that the 00000003000000000000000F starts to the Wal log that is being written.

[Email protected] pg_xlog]$65540-rw-------. 1 postgres postgres 16777216 Apr 20:42 00000003000000000000000F-rw-------. 1 postgres postgres      313 Apr 20:42 00000003000000000000000f.00000060. Backup-rw-------. 1 postgres Postgres 16777216 Apr 20:48 000000030000000000000010-rw-------. 1 postgres postgres 16777216 Apr 20:50 000000030000000000000011-rw-------. 1 postgres postgres 16777216 Apr 28 20:55 000000030000000000000012

3.5 Edit recovery.conf File

[Email protected] data]$= ' cp/data/arch/%f%p '            #  e.g. ' cp/mnt/server/archivedir/%f%p ' recovery_target_time = ' 2018-04-28 20:47:31.617808+08false= ' latest '

3.6 Start the database, and verify the data

[Email protected] data]$Pg_startserver starting[postgres@Postgres202 data]$ Ps-ef |grep postgresroot1098 1083 0 22:32 pts/0 00:00:00 Su-Postgrespostgres1099 1098 0 22:32 pts/0 00:00:00-Bashroot1210 1195 0 22:55 pts/1 00:00:00 Su-Postgrespostgres1211 1210 0 22:55 PTS/1 00:00:00-Bashpostgres1442 1 1 23:16 pts/0 00:00:00/opt/pgsql96/bin/Postgrespostgres1450 1442 0 23:16? 00:00:00 Postgres:checkpointerProcessPostgres1451 1442 0 23:16? 00:00:00 Postgres:writerProcessPostgres1459 1442 0 23:16? 00:00:00 Postgres:wal WriterProcessPostgres1460 1442 0 23:16? 00:00:00 postgres:autovacuum LauncherProcessPostgres1461 1442 0 23:16? 00:00:00 Postgres:archiverProcessLast is 00000005. Historypostgres1462 1442 0 23:16? 00:00:00 postgres:stats CollectorProcessPostgres1464 1099 0 23:16 pts/0 00:00:00 PS-Efpostgres1465 1099 0 23:16 pts/0 00:00:00grep postgres[postgres@Postgres202 data]$Psqlpsql (9.6.0) Type" Help"  forHelp.postgres=#\c Lottu LottuConnected 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)

From this view the data is restored; Copy to online database operation is slightly.

Extension point

The following explains how to find the time of the misoperation. That is Recovery_target_time = ' 2018-04-28 20:47:31.617808+08 ' point in time. The above is obtained in the previous;

1. Parse the log with Pg_xlogdump.

[Email protected] pg_xlog]$ pg_xlogdump-b 00000003000000000000000F 000000030000000000000012 > lottu.logpg_ Xlogdump:fatal:  in the WAL record at 0/12000648:invalid record length at 0/12000680:wanted, got 0

2. This log can be found from the Lottu.log

Rmgr:transaction Len (Rec/tot): 8/ tx:1689, lsn:0/100244a0, prev 0/10024460, Desc:commit 2018-04-28 20:45:49.736013Cstrmgr:standby Len (rec/tot): 24/50, tx:0, Lsn:0/100244c8, prev 0/100244a0, desc:running_xacts Nextxid1690Latestcompletedxid 1689 Oldestrunningxid 1690rmgr:heap Len (rec/tot): 3/3130, tx:1690, lsn:0/10024500, prev 0/100244c8, desc:insert off 9Blkref#0:rel 16385/16386/2619 Fork Main Blk (FPW); hole:offset:60, length:5116rmgr:btree Len (rec/tot): 2/7793, tx:1690, lsn:0/10025140, prev 0/10024500, desc:insert_leaf off 385Blkref#0:rel 16385/16386/2696 Fork Main Blk 1 (FPW); hole:offset:1564, length:452rmgr:heap Len (rec/tot): 2/184, tx:1690, lsn:0/10026fd0, prev 0/10025140, desc:inplace off 16Blkref#0:rel 16385/16386/1259 Fork main BLK 0Rmgr:transaction Len (rec/tot): 88/114, tx:1690, lsn:0/10027088, prev 0/10026fd0, Desc:commit2018-04-28 20:46:37.718442CST; Inval msgs:catcache catcache Catcache relcache 32784Rmgr:standby Len (rec/tot): 24/50, tx:0, lsn:0/10027100, prev 0/10027088, desc:running_xacts nextxid 1691 Latestcompletedxi D 1690 Oldestrunningxid 1691Rmgr:standby Len (rec/tot): 24/50, tx:0, lsn:0/10027138, prev 0/10027100, desc:running_xacts nextxid 1691 Latestcompletedxi D 1690 Oldestrunningxid 1691Rmgr: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; Fpwtrue; XID 0:1691; OID 40976; Multi 1; Offset 0; Oldest XID 1668inchDB 1; Oldest multi 1inchDB 1; Oldest/newest commit timestamp xid:0/0; Oldest running XID 1691; Onlinermgr:standby Len (rec/tot): 24/50, tx:0, lsn:0/100271e0, prev 0/10027170, desc:running_xacts nextxid 1691 Latestcompletedxi D 1690 Oldestrunningxid 1691Rmgr:standby Len (rec/tot): 24/50, tx:0, lsn:0/10027218, prev 0/100271e0, desc:running_xacts nextxid 1691 Latestcompletedxi D 1690 Oldestrunningxid 1691Rmgr: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; Fpwtrue; XID 0:1691; OID 40976; Multi 1; Offset 0; Oldest XID 1668inchDB 1; Oldest multi 1inchDB 1; Oldest/newest commit timestamp xid:0/0; Oldest running XID 1691; Onlinermgr:xlog Len (rec/tot): 0/24, tx:0, lsn:0/100272c0, prev 0/10027250, desc:SWITCHRmgr:standby Len (rec/tot): 24/50, tx:0, lsn:0/11000028, prev 0/100272c0, desc:running_xacts nextxid 1691 Latestcompletedxi D 1690 Oldestrunningxid 1691Rmgr:standby Len (rec/tot): 16/42, tx:1691, lsn:0/11000060, prev 0/11000028, Desc:lock XID 1691 db 16386 rel32784rmgr:heap Len (rec/tot): 8/ 2963, tx:1691, lsn:0/11000090, prev 0/11000060, desc:delete off 16keys_updated blkref#0:rel 16385/16386/1247 Fork Main Blk 8 (FPW); hole:offset:88, length:5288

According to the "32784" Log you can see that the table Tbl_lottu_drop in 2018-04-28 20:46:37.718442 insert 1000 records (so the recovery time point is selected 2018-04-28 20:47:31.617808+ 08 is not a problem); that is, it is also in the transaction ID 1690 operation. And the transaction ID is 1691 for the delete operation.

So the above recovery.conf can also be rewritten as:

Restore_command = ' cp/data/arch/%f%p '            #  e.g. ' cp/mnt/server/archivedir/%f%p 'recovery_target_ XID = ' 1690false    = ' latest '

PostgreSQL recovers mistakenly deleted data