Обсуждение: 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 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 );

Regards,

Amit

On Thu, Sep 6, 2018 at 9:03 PM Jerry Sievers <gsievers19@comcast.net> wrote:

amit tripathi <amittripathi1445@gmail.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.consulting@comcast.net
p: 312.241.7800

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@gmail.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 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