PostgreSQL恢复误操作

根据德歌的博客自己实践一下：

 

找到要恢复的前一个事物ID，然后回滚到那个时候：

使用pg_xlogdump分析XLOG

-bash-4.1$ pg_xlogdump 000000020000000000000006|less

rmgr: XLOG        len (rec/tot):     80/   106, tx:          0, lsn: 0/06327260, prev 0/06327228, desc: CHECKPOINT_ONLINE redo 0/6327228; tli 2; prev tli 2; fpw true; xid 0/1773; oid 24577; multi 1; offset 0; oldest xid 1740 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 1772; online
rmgr: Standby     len (rec/tot):     28/    54, tx:          0, lsn: 0/063272D0, prev 0/06327260, desc: RUNNING_XACTS nextXid 1773 latestCompletedXid 1771 oldestRunningXid 1772; 1 xacts: 1772
rmgr: Transaction len (rec/tot):      8/    34, tx:       1772, lsn: 0/06327308, prev 0/063272D0, desc: COMMIT 2017-05-19 17:00:22.037554 CST
rmgr: Standby     len (rec/tot):     24/    50, tx:          0, lsn: 0/06327330, prev 0/06327308, desc: RUNNING_XACTS nextXid 1773 latestCompletedXid 1772 oldestRunningXid 1773
rmgr: Standby     len (rec/tot):     24/    50, tx:          0, lsn: 0/06327368, prev 0/06327330, desc: RUNNING_XACTS nextXid 1773 latestCompletedXid 1772 oldestRunningXid 1773
rmgr: XLOG        len (rec/tot):     80/   106, tx:          0, lsn: 0/063273A0, prev 0/06327368, desc: CHECKPOINT_ONLINE redo 0/6327368; tli 2; prev tli 2; fpw true; xid 0/1773; oid 24577; multi 1; offset 0; oldest xid 1740 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 1773; online
rmgr: Standby     len (rec/tot):     24/    50, tx:          0, lsn: 0/06327410, prev 0/063273A0, desc: RUNNING_XACTS nextXid 1773 latestCompletedXid 1772 oldestRunningXid 1773
rmgr: Standby     len (rec/tot):     24/    50, tx:          0, lsn: 0/06327448, prev 0/06327410, desc: RUNNING_XACTS nextXid 1773 latestCompletedXid 1772 oldestRunningXid 1773
rmgr: XLOG        len (rec/tot):     80/   106, tx:          0, lsn: 0/06327480, prev 0/06327448, desc: CHECKPOINT_ONLINE redo 0/6327448; tli 2; prev tli 2; fpw true; xid 0/1773; oid 24577; multi 1; offset 0; oldest xid 1740 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 1773; online
rmgr: Standby     len (rec/tot):     24/    50, tx:          0, lsn: 0/063274F0, prev 0/06327480, desc: RUNNING_XACTS nextXid 1773 latestCompletedXid 1772 oldestRunningXid 1773

 

$vi $PGDATA/recovery.conf 

recovery_target_inclusive = true
restore_command = 'cp /tmp/%f %p'
recovery_target_xid = '1721'
standby_mode = on
pause_at_recovery_target = true

 

记住大概时间，然后找到对应的checkpoint时间来进行恢复：

如果打开了所有SQL的审计日志(log_statement = 'all')，首先我们可以在日志中，根据用户给的模糊时间，找到精准的时间。

 

将数据库恢复到这之前的一个时间（最好是给出时间点的上一个检查点之前的时间），并停止恢复。

假设检查点时5分钟会做一次的，那么我们可以选择5分钟前的一个时间点。

$vi $PGDATA/recovery.conf 
recovery_target_inclusive = false
restore_command = 'cp /tmp/%f %p'
recovery_target_time = '2017-05-19 10:00:00 +08'   
standby_mode = on
pause_at_recovery_target = true