pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedrecovery.conf not getting changed to recovery.done after PITR
6+ messages / 3 participants
[nested] [flat]
* recovery.conf not getting changed to recovery.done after PITR
@ 2018-09-06 05:13 amit tripathi <amittripathi1445@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: amit tripathi @ 2018-09-06 05:13 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: recovery.conf not getting changed to recovery.done after PITR
@ 2018-09-06 15:32 Jerry Sievers <gsievers19@comcast.net>
parent: amit tripathi <amittripathi1445@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jerry Sievers @ 2018-09-06 15:32 UTC (permalink / raw)
To: amit tripathi <amittripathi1445@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: recovery.conf not getting changed to recovery.done after PITR
@ 2018-09-07 08:26 amit tripathi <amittripathi1445@gmail.com>
parent: Jerry Sievers <gsievers19@comcast.net>
0 siblings, 1 reply; 6+ messages in thread
From: amit tripathi @ 2018-09-07 08:26 UTC (permalink / raw)
To: gsievers19@comcast.net; +Cc: pgsql-admin
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@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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: recovery.conf not getting changed to recovery.done after PITR
@ 2018-09-07 09:22 Rajni Baliyan <saan654@gmail.com>
parent: amit tripathi <amittripathi1445@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Rajni Baliyan @ 2018-09-07 09:22 UTC (permalink / raw)
To: amittripathi1445@gmail.com; +Cc: pgsql-admin
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 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@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
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: recovery.conf not getting changed to recovery.done after PITR
@ 2018-09-11 09:32 amit tripathi <amittripathi1445@gmail.com>
parent: Rajni Baliyan <saan654@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: amit tripathi @ 2018-09-11 09:32 UTC (permalink / raw)
To: saan654@gmail.com; +Cc: Jerry Sievers <gsievers19@comcast.net>; pgsql-admin
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,
> 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 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
>>
>>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: recovery.conf not getting changed to recovery.done after PITR
@ 2018-09-11 10:26 Rajni Baliyan <saan654@gmail.com>
parent: amit tripathi <amittripathi1445@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Rajni Baliyan @ 2018-09-11 10:26 UTC (permalink / raw)
To: amit tripathi <amittripathi1445@gmail.com>; +Cc: Jerry Sievers <gsievers19@comcast.net>; pgsql-admin
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,
> 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,
>> 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 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]
>>>
>>> --
Thanks
Regards,
Rajni
0410472086
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-09-11 10:26 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-06 05:13 recovery.conf not getting changed to recovery.done after PITR amit tripathi <amittripathi1445@gmail.com>
2018-09-06 15:32 ` Jerry Sievers <gsievers19@comcast.net>
2018-09-07 08:26 ` amit tripathi <amittripathi1445@gmail.com>
2018-09-07 09:22 ` Rajni Baliyan <saan654@gmail.com>
2018-09-11 09:32 ` amit tripathi <amittripathi1445@gmail.com>
2018-09-11 10:26 ` Rajni Baliyan <saan654@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox