Обсуждение: recovery.conf not getting changed to recovery.done after PITR
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.
>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
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
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.confrestore_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,AmitOn 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
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@gmail.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,RajniOn 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.confrestore_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
Hi Amit,
Good to know that your problem is resolved.
Regards
Rajni
On Tue, 11 Sep 2018 at 7:29 pm amit tripathi <amittripathi1445@gmail.com> wrote:
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,AmitOn Fri, Sep 7, 2018 at 2:52 PM Rajni Baliyan <saan654@gmail.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,RajniOn 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.confrestore_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]
Thanks
Regards,
Rajni
0410472086
Regards,
Rajni
0410472086