PostgreSQL使用pg_xlogdump找到誤操作事務號

一般使用者在發生誤操作後，可能會非常模糊的記得一個大概操作的時間點，那麼通過這個線索，我們怎麼能定位到使用者誤操作的精準事務號呢？
從而使用PITR恢復到發生誤操作前的資料庫狀態。
我們可以使用審計日誌，找到精準的時間，假設你開了所有SQL的審計日誌(log_statement = `all`)。
然後找到精準的XID，需要使用pg_xlogdump。
一個例子：
使用者在某個時間點2015-09-21 10:10:00 +08發生了一筆誤操作，刪除了某個表tbl的一批資料。
首先我們可以在日誌中，根據使用者給的模糊時間，找到精準的時間。

將資料庫恢復到這之前的一個時間（最好是給出時間點的上一個檢查點之前的時間），並停止恢復。
假設檢查點時5分鐘會做一次的，那麼我們可以選擇5分鐘前的一個時間點。
這麼做是為了防止PITR到誤操作後，那就白搞了。

$vi $PGDATA/recovery.conf 
recovery_target_inclusive = false
restore_command = `cp /tmp/%f %p`
recovery_target_time = `2015-09-21 10:00:00 +08`   # 選擇了一個10分鐘前的時間
standby_mode = on
pause_at_recovery_target = true

資料庫起來之後，去查詢tbl對應的filenode，要用它來分析pg_xlog
digoal=# select oid from pg_class where relname=`tbl`;
   oid    
----------
 34874054
(1 row)
digoal=# select pg_relation_filenode(34874054);
 pg_relation_filenode 
----------------------
             37201015
(1 row)
digoal=# select pg_relation_filepath(34874054);
  pg_relation_filepath  
------------------------
 base/34873862/37201015
(1 row)

然後下載這個誤操作前的基礎備份，以及到誤操作時產生的所有XLOG。
000000040000006500000095
000000040000006500000096
000000040000006500000097

使用pg_xlogdump分析XLOG。
$pg_xlogdump -b 000000040000006500000097 000000040000006500000097 | less
找到誤操作的XID了，就是1485021，因為這筆事務日誌包含了大量的tbl表的delete，而且上一個事務結束的時間點2015-09-21 10:13:22.038262和使用者給的誤操作時間點吻合。
rmgr: Heap        len (rec/tot):     50/    82, tx:    1485020, lsn: 65/9760DDF8, prev 65/9760DDC0, bkp: 0000, desc: hot_update: rel 1663/13003/16387; tid 0/36 xmax 1485020 ; new tid 0/37 xmax 0
rmgr: Transaction len (rec/tot):     12/    44, tx:    1485020, lsn: 65/9760DE50, prev 65/9760DDF8, bkp: 0000, desc: commit: 2015-09-21 10:13:22.038262 CST

rmgr: Heap        len (rec/tot):     26/  8218, tx:    1485021, lsn: 65/9760DE80, prev 65/9760DE50, bkp: 1000, desc: delete: rel 1663/34873862/37201015; tid 0/1 KEYS_UPDATED 
        backup bkp #0; rel 1663/34873862/37201015; fork: main; block: 0; hole: offset: 184, length: 56
rmgr: Heap        len (rec/tot):     26/    58, tx:    1485021, lsn: 65/9760FEB8, prev 65/9760DE80, bkp: 0000, desc: delete: rel 1663/34873862/37201015; tid 0/2 KEYS_UPDATED 
rmgr: Heap        len (rec/tot):     26/    58, tx:    1485021, lsn: 65/9760FEF8, prev 65/9760FEB8, bkp: 0000, desc: delete: rel 1663/34873862/37201015; tid 0/3 KEYS_UPDATED 
rmgr: Heap        len (rec/tot):     26/    58, tx:    1485021, lsn: 65/9760FF38, prev 65/9760FEF8, bkp: 0000, desc: delete: rel 1663/34873862/37201015; tid 0/4 KEYS_UPDATED 
rmgr: Heap        len (rec/tot):     26/    58, tx:    1485021, lsn: 65/9760FF78, prev 65/9760FF38, bkp: 0000, desc: delete: rel 1663/34873862/37201015; tid 0/5 KEYS_UPDATED 
.....

解釋一下
desc: delete: rel 1663/34873862/37201015;
1663表示表空間的oid
34873862對應資料庫的oid
37201015對應table的filenode
contrib/pg_xlogdump/pg_xlogdump.c
                        printf("	backup bkp #%u; rel %u/%u/%u; fork: %s; block: %u; hole: offset: %u, length: %u
",
                                   bkpnum,
                                   bkpb.node.spcNode, bkpb.node.dbNode, bkpb.node.relNode,
                                   forkNames[bkpb.fork],
                                   bkpb.block, bkpb.hole_offset, bkpb.hole_length);
src/include/storage/relfilenode.h
typedef struct RelFileNode
{
        Oid                     spcNode;                /* tablespace */
        Oid                     dbNode;                 /* database */
        Oid                     relNode;                /* relation */
} RelFileNode;
src/include/access/xlog_internal.h
typedef struct BkpBlock
{
        RelFileNode node;                       /* relation containing block */
        ForkNumber      fork;                   /* fork within the relation */
        BlockNumber block;                      /* block number */
        uint16          hole_offset;    /* number of bytes before "hole" */
        uint16          hole_length;    /* number of bytes in "hole" */

        /* ACTUAL BLOCK DATA FOLLOWS AT END OF STRUCT */
} BkpBlock;

現在已經知道發生誤操作前的最後一筆結束事務的XID，需要把recovery.conf改為xid恢復。
recovery_target_inclusive = true
restore_command = `cp /tmp/%f %p`
recovery_target_xid = `1485020`
standby_mode = on
pause_at_recovery_target = true
重啟資料庫，現在已經恢復到真正的目標了。
可以匯出tbl表，恢復被刪除的資料。

[參考]
1. http://blog.163.com/digoal@126/blog/static/163877040201303082942271/
2. http://blog.163.com/digoal@126/blog/static/16387704020155199222755/