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

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(at)gmail(dot)com> 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(at)gmail(dot)com>
> 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 EDT2018-09-07 03:35:59.593 EDT [8264] LOG: starting point-in-time recovery to 2018-09-07 03:25:46-042018-09-07 03:35:59.682 EDT [8264] LOG: restored log file "000000010000000000000003" from archive2018-09-07 03:35:59.722 EDT [8264] LOG: redo starts at 0/30000282018-09-07 03:35:59.725 EDT [8264] LOG: consistent recovery state reached at 0/30001302018-09-07 03:35:59.725 EDT [8262] LOG: database system is ready to accept read only connections2018-09-07 03:36:00.058 EDT [8264] LOG: restored log file "000000010000000000000004" from archive2018-09-07 03:36:00.097 EDT [8264] LOG: recovery stopping before commit of transaction 562, time 2018-09-07 03:26:17.435255-042018-09-07 03:36:00.097 EDT [8264] LOG: recovery has paused2018-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 transaction2018-09-07 03:36:54.138 EDT [8288] STATEMENT: CREATE TABLE
>>
>>