PostgreSQL Recovery Error operation

According to the German Song blog own Practice:

Locate the previous thing ID that you want to restore, and then roll back to that time:

Using Pg_xlogdump analysis 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; Onlinermgr:standby Len (rec/tot): 28/54, tx:0, lsn:0/063272d0, prev 0/06327260, desc:running_xact S Nextxid 1773 Latestcompletedxid 1771 Oldestrunningxid 1772;    1 xacts:1772rmgr:transaction len (rec/tot): 8/ tx:1772, lsn:0/06327308, prev 0/063272d0, desc:commit 2017-05-19 17:00:22.037554 cstrmgr:standby Len (re C/tot): 24/50, tx:0, lsn:0/06327330, prev 0/06327308, desc:running_xacts nextxid 1773 Latestcompletedx ID 1772 oldestrunningxid 1773rmgr:standby len (rec/tot): 24/50, tx:0, lsn:0/06327368, prev 0/06327 Desc:running_xacts Nextxid 1773 Latestcompletedxid 1772 oldestrUnningxid 1773rmgr:xlog Len (rec/tot): 80/106, tx:0, lsn:0/063273a0, prev 0/06327368, DESC:CHEC Kpoint_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; Onlinermgr:standby Len (rec/tot): 24/50, tx:0, lsn:0/06327410, prev 0/063273a0, desc:running_xact S Nextxid 1773 Latestcompletedxid 1772 oldestrunningxid 1773rmgr:standby len (rec/tot): 24/50, tx:0 , lsn:0/06327448, prev 0/06327410, desc:running_xacts nextxid 1773 Latestcompletedxid 1772 OldestRunningXid 1773RMGR:XL OG Len (rec/tot): 80/106, tx:0, lsn:0/06327480, prev 0/06327448, desc:checkpoint_online redo 0/6 327448; 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; Onlinermgr:standby Len (rec/tot): 24/50, tx:0, lsn:0/063274f0, prev 0/06327480, desc:running_xact S Nextxid 1773 Latestcompletedxid 1772 Oldestrunningxid 1773

recovery_target_inclusive = Truerestore_command = ' cp/tmp/%f%p ' recovery_target_xid = ' 1721 ' Standby_mode = Onpause_at_ Recovery_target = True

remember the approximate time and then find the corresponding checkpoint time to recover:If all SQL audit logs are turned on (log_statement = ' all '), first we can find the exact time in the log based on the fuzzy time given by the user. Restore the database to a time prior to this (preferably a time before the last checkpoint at the point in time), and stop the recovery. Assuming that the checkpoint is done in 5 minutes, then we can choose a point in time 5 minutes ago.

$vi $PGDATA/recovery.conf recovery_target_inclusive = Falserestore_command = ' cp/tmp/%f%p ' recovery_target_time = ' 2017 -05-19 10:00:00 +08 '   standby_mode = Onpause_at_recovery_target = True

PostgreSQL Recovery Error operation