recovery.conf not getting changed to recovery.done after PITR

Question

In recovery.conf, if we give recovery_target_time as 2018-09-07 03:25:46 (without EST) and restart PostgreSQL, recovery.conf is not getting changed to recovery.done.

Yet PITR is successful and records/tables are restored only till given time. But then the database remains in recovery mode and I am unable to modify data.

In the second case, if I add EST and restart PostgreSQL, recovery.conf is getting changed to recovery.done, but everything is getting restored. I mean tables/records added after 2018-09-07 03:25:46 are also restored.

Other information: doing it on a Linux host. Timezone in postgresql.conf is US/Eastern

postgresql.conf has the following settings configured. Other options are commented out.

listen_addresses = '*'
port = 5432
max_connections = 100
shared_buffers = 128MB
dynamic_shared_memory_type = posix
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'
log_destination = 'stderr'
logging_collector =on
log_timezone = 'US/Eastern'
datestyle = 'iso, mdy'
timezone ='US/Eastern'
lc_messages = 'en_US.UTF-8'
lc_monetary ='en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

`recovery.conf' has the following two options enabled:

restore_command = 'cp /archive/%f %p'
recovery_target_time = '2018-09-07 03:25:46'


2018-09-07 03:35:57.745 EDT [8264] LOG:  database system was interrupted; last known up at 2018-09-07 03:23:31 EDT
2018-09-07 03:35:59.593 EDT [8264] LOG:  starting point-in-time recovery to 2018-09-07 03:25:46-04
2018-09-07 03:35:59.682 EDT [8264] LOG:  restored log file "000000010000000000000003" from archive
2018-09-07 03:35:59.722 EDT [8264] LOG:  redo starts at 0/3000028
2018-09-07 03:35:59.725 EDT [8264] LOG:  consistent recovery state reached at 0/3000130
2018-09-07 03:35:59.725 EDT [8262] LOG:  database system is ready to accept read only connections
2018-09-07 03:36:00.058 EDT [8264] LOG:  restored log file "000000010000000000000004" from archive
2018-09-07 03:36:00.097 EDT [8264] LOG:  recovery stopping before commit of transaction 562, time 2018-09-07 03:26:17.435255-04
2018-09-07 03:36:00.097 EDT [8264] LOG:  recovery has paused
2018-09-07 03:36:00.097 EDT [8264] HINT:  Execute pg_wal_replay_resume() to continue.
2018-09-07 03:36:54.138 EDT [8288] ERROR:  cannot execute CREATE TABLE in a read-only transaction
2018-09-07 03:36:54.138 EDT [8288] STATEMENT:  CREATE TABLE scale_data5 ( section NUMERIC NOT NULL, id1 NUMERIC NOT NULL, id2 NUMERIC NOT NULL );

Answer 1

As you can see, recovery pauses after reaching the target.

This is because you have hot_standby = on and you left recovery_target_action at its default value pause.

You have to add the following in recovery.conf:

recovery_target_action = promote

Alternatively, you can connect to the recovering server and complete recovery manually:

SELECT pg_wal_replay_resume();

To solve the mystery why adding the time zone makes a difference, compare the time zone in the log (EDT) to the time zone you used (EST).

Your database is running in Eastern Daylight Time, which is offset -4 hours from UTC, while Eastern Standard Time is offset -5 hours.

So what happened is that by using “EST” you actually specified a recovery target time corresponding to 2018-09-07 04:25:46 EDT, which was past the end of WAL, so PostgreSQL did a complete recovery.