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

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
>