首页 > 代码库 > PostgreSQL恢复误操作

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

 

PostgreSQL恢复误操作