PostgreSQL PITR not working properly

Question

I am trying to restore a PostgreSQL database to a point in time.

When I am using only restore_command in recovery.conf then its working fine.

restore_command = 'cp /var/lib/pgsql/pg_log_archive/%f %p'

When I am using the recovery_target_time parameter, it is not restoring to the target time.

restore_command = 'cp /var/lib/pgsql/pg_log_archive/%f %p'
recovery_target_time='2018-06-05 06:43:00.0'

Below is the log file content:

2018-06-05 07:31:39.166 UTC [22512] LOG:  database system was interrupted; last known up at 2018-06-05 06:35:52 UTC
2018-06-05 07:31:39.664 UTC [22512] LOG:  starting point-in-time recovery to 2018-06-05 06:43:00+00
2018-06-05 07:31:39.671 UTC [22512] LOG:  restored log file "00000005.history" from archive
2018-06-05 07:31:39.769 UTC [22512] LOG:  restored log file "00000005000000020000008F" from archive
2018-06-05 07:31:39.816 UTC [22512] LOG:  redo starts at 2/8F000028
2018-06-05 07:31:39.817 UTC [22512] LOG:  consistent recovery state reached at 2/8F000130
2018-06-05 07:31:39.818 UTC [22510] LOG:  database system is ready to accept read only connections
2018-06-05 07:31:39.912 UTC [22512] LOG:  restored log file "000000050000000200000090" from archive
2018-06-05 07:31:39.996 UTC [22512] LOG:  recovery stopping before abort of transaction 9525, time 2018-06-05 06:45:02.088502+00
2018-06-05 07:31:39.996 UTC [22512] LOG:  recovery has paused

I am trying to restore the database instance to 06:43:00. Why is it recovering up to 06:45:02?

EDIT

In first scenario recovery.conf converted into recovery.done but this didn't happen in second scenario

What could be the reason of this?

score 5 · Accepted Answer · answered Jun 05 '18 at 09:01

You forgot to set

recovery_target_action = 'promote'

After point-in-time-recovery, recovery_target_action determines how PostgreSQL will proceed.

The default value is pause which means that PostgreSQL will do nothing and wait for you to tell it how to proceed.

To complete recovery, connect to the database and run

SELECT pg_wal_replay_resume();

It seems that there has been no database activity logged between 06:43:00 and 06:45:02. Observe that the log says recovery stopping before abort of transaction 9525.

PostgreSQL PITR not working properly

1 Answers1