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

[amit tripathi <amittripathi1445@xxxxxxxxx>]

Hi Rajni,

Thanks for pointing it out.

I tried after adding recovery_target_action = promote in recovery.conf and it seems to be working fine.

Regards,

Amit 

On Fri, Sep 7, 2018 at 2:52 PM Rajni Baliyan <saan654@xxxxxxxxx> wrote:

Hello Amit,

Recovery.conf changes to recovery.done once you stop the recovery on replica and promote it as standalone. It is clear from your logs that replica is still in recovery  i.e replication is still active.

If you touch the recovery.conf then it will rename to recovery.done i.e no replication in place and both the servers are standalone.

What is your requirement here, PIT recovery or promoting replica as master?

Regards,

Rajni

On Fri, Sep 7, 2018 at 6:24 PM amit tripathi <amittripathi1445@xxxxxxxxx> wrote:

Hi Jerry,

Thanks for responding.

I am using following two parameters in recovery.conf

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

postgresql.conf has following configurations. 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'

Adding the logs details as well.

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