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

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 scale_data5 ( section NUMERIC NOT NULL, id1 NUMERIC NOT NULL, id2 NUMERIC NOT NULL );
>
> Regards,
> Amit
>
> On Thu, Sep 6, 2018 at 9:03 PM Jerry Sievers <gsievers19(at)comcast(dot)net>
> wrote:
>
>> amit tripathi <amittripathi1445(at)gmail(dot)com> writes:
>>
>> > Hi All,
>> >
>> > Need help on below issue.
>> >
>> >>In recovery.conf file if we give recovery_target_time as 2018-09-05
>> > 09:54:21 (without EST) and restart the postgresql, recovery.conf is
>> > not getting changed to recovery.done. Although PITR is successful and
>> > only till given time records/tables are restored. But database goes
>> > in recovery mode and i am unable to enter more records.
>>
>> Show us your recovery.conf after masking anything sensitive.
>>
>> You probably are paused at a recovery target and if trying to promote
>> the pitr cluster at this point, need to un-pause replication else it
>> will not respond to the promote.
>>
>> HTH
>>
>> >
>> >>In the second case if i add EST and restart the postgresql,
>> > recovery.conf is getting changed to recovery.done but everything is
>> > getting restored. I mean tables/records added after 2018-09-05
>> > 09:54:21 are also restored.
>> >
>> > Other information-Doing it on Linux host. Timezone in postgresql.conf
>> > is US/Eastern.
>> >
>> >
>> > Regards,
>> > Amit
>> >
>> >
>>
>> --
>> Jerry Sievers
>> Postgres DBA/Development Consulting
>> e: postgres(dot)consulting(at)comcast(dot)net
>> p: 312.241.7800
>>
>