pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedGeneral support on postgres replication
5+ messages / 3 participants
[nested] [flat]
* General support on postgres replication
@ 2023-11-22 08:57 Vijaykumar Patil <vijaykumar.patil@maersk.com>
0 siblings, 2 replies; 5+ messages in thread
From: Vijaykumar Patil @ 2023-11-22 08:57 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi Team,
Need some support for below issue .
I have created streaming replication with two nodes .
One is primary and 2nd one is standby but after doing any DML or DDL operation on primary Walreceiver process is terminated on standby and standby database is opening in read write mode.
Below are the error details .
2023-11-22 03:39:58 EST [1275942]: user=,db=,app=,client=LOG: recovery stopping before commit of transaction 25627, time 2023-11-22 03:39:58.013764-05
2023-11-22 03:39:58 EST [1275942]: user=,db=,app=,client=LOG: redo done at 1/260059E0 system usage: CPU: user: 0.02 s, system: 0.04 s, elapsed: 5197.90 s
2023-11-22 03:39:58 EST [1276846]: user=,db=,app=,client=FATAL: terminating walreceiver process due to administrator command
ERROR: [037]: archive-get command requires option: pg1-path
HINT: does this stanza exist?
2023-11-22 03:39:58 EST [1275942]: user=,db=,app=,client=LOG: selected new timeline ID: 37
ERROR: [037]: archive-get command requires option: pg1-path
HINT: does this stanza exist?
2023-11-22 03:39:58 EST [1275942]: user=,db=,app=,client=LOG: archive recovery complete
2023-11-22 03:39:58 EST [1275940]: user=,db=,app=,client=LOG: checkpoint starting: end-of-recovery immediate wait
2023-11-22 03:39:58 EST [1275940]: user=,db=,app=,client=LOG: checkpoint complete: wrote 6 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.022 s, sync=0.003 s, total=0.033 s; sync files=6, longest=0.002 s, average=0.001 s; distance=22 kB, estimate=34772 kB
2023-11-22 03:39:58 EST [1275938]: user=,db=,app=,client=LOG: database system is ready to accept connections
Thanks & Regards
Vijaykumar
Database Operations
[cid:image001.png@01DA1D50.11779610]
Maersk Global Service Centre, Pune.
________________________________
The information contained in this message is privileged and intended only for the recipients named. If the reader is not a representative of the intended recipient, any review, dissemination or copying of this message or the information it contains is prohibited. If you have received this message in error, please immediately notify the sender, and delete the original message and attachments.
Maersk will as part of our communication and interaction with you collect and process your personal data. You can read more about Maersk's collection and processing of your personal data and your rights as a data subject in our privacy policy <https://www.maersk.com/front-page-requirements/privacy-policy;
Please consider the environment before printing this email.
Classification: Internal
Attachments:
image001.png (image/png, 4.0K) download
view image
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: General support on postgres replication
@ 2023-11-22 16:22 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Vijaykumar Patil <vijaykumar.patil@maersk.com>
1 sibling, 0 replies; 5+ messages in thread
From: Ron Johnson @ 2023-11-22 16:22 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Wed, Nov 22, 2023 at 11:17 AM Vijaykumar Patil <
vijaykumar.patil@maersk.com> wrote:
> Hi Team,
>
>
>
> Need some support for below issue .
>
>
>
> I have created streaming replication with two nodes .
>
>
>
> One is primary and 2nd one is standby but after doing any DML or DDL
> operation on primary Walreceiver process is terminated on standby and
> standby database is opening in read write mode.
>
>
>
> Below are the error details .
>
>
>
> 2023-11-22 03:39:58 EST [1275942]: user=,db=,app=,client=LOG: recovery
> stopping before commit of transaction 25627, time 2023-11-22
> 03:39:58.013764-05
>
> 2023-11-22 03:39:58 EST [1275942]: user=,db=,app=,client=LOG: redo done
> at 1/260059E0 system usage: CPU: user: 0.02 s, system: 0.04 s, elapsed:
> 5197.90 s
>
> 2023-11-22 03:39:58 EST [1276846]: user=,db=,app=,client=FATAL:
> terminating walreceiver process due to administrator command
>
> ERROR: [037]: archive-get command requires option: pg1-path
>
> HINT: does this stanza exist?
>
> 2023-11-22 03:39:58 EST [1275942]: user=,db=,app=,client=LOG: selected
> new timeline ID: 37
>
> ERROR: [037]: archive-get command requires option: pg1-path
>
> HINT: does this stanza exist?
>
What have you done about fixing that quite explicit ERROR message, and its
attendant HINT?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: General support on postgres replication
@ 2023-11-23 08:43 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Vijaykumar Patil <vijaykumar.patil@maersk.com>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2023-11-23 08:43 UTC (permalink / raw)
To: Vijaykumar Patil <vijaykumar.patil@maersk.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Wed, 2023-11-22 at 08:57 +0000, Vijaykumar Patil wrote:
> I have created streaming replication with two nodes .
>
> One is primary and 2nd one is standby but after doing any DML or DDL operation on
> primary Walreceiver process is terminated on standby and standby database is
> opening in read write mode.
>
> Below are the error details .
>
> 2023-11-22 03:39:58 EST [1275942]: user=,db=,app=,client=LOG: recovery stopping before commit of transaction 25627, time 2023-11-22 03:39:58.013764-05
> 2023-11-22 03:39:58 EST [1275942]: user=,db=,app=,client=LOG: redo done at 1/260059E0 system usage: CPU: user: 0.02 s, system: 0.04 s, elapsed: 5197.90 s
> 2023-11-22 03:39:58 EST [1276846]: user=,db=,app=,client=FATAL: terminating walreceiver process due to administrator command
> ERROR: [037]: archive-get command requires option: pg1-path
> HINT: does this stanza exist?
It looks like you have a bad "restore_command" configured.
It would be intereseting to see the log lines before the ones you are showing.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: General support on postgres replication
@ 2023-11-23 09:32 Vijaykumar Patil <vijaykumar.patil@maersk.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Vijaykumar Patil @ 2023-11-23 09:32 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi Laurenz,
Please find the log details :-
2023-11-22 04:32:47 EST +AFs-1278503+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: entering standby mode
ERROR: +AFs-037+AF0-: archive-get command requires option: pg1-path
HINT: does this stanza exist?
ERROR: +AFs-037+AF0-: archive-get command requires option: pg1-path
HINT: does this stanza exist?
2023-11-22 04:32:47 EST +AFs-1278503+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: redo starts at 1/27000028
ERROR: +AFs-037+AF0-: archive-get command requires option: pg1-path
HINT: does this stanza exist?
2023-11-22 04:32:47 EST +AFs-1278503+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: consistent recovery state reached at 1/27000100
2023-11-22 04:32:47 EST +AFs-1278499+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: database system is ready to accept read-only connections
ERROR: +AFs-037+AF0-: archive-get command requires option: pg1-path
HINT: does this stanza exist?
2023-11-22 04:32:47 EST +AFs-1278509+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: started streaming WAL from primary at 1/28000000 on timeline 36
2023-11-22 04:33:02 EST +AFs-1278511+AF0-: user+AD0AWw-unknown+AF0-,db+AD0AWw-unknown+AF0-,app+AD0AWw-unknown+AF0-,client+AD0-10.125.0.120LOG: connection received: host+AD0-10.125.0.120 port+AD0-48408
2023-11-22 04:33:02 EST +AFs-1278511+AF0-: user+AD0-pensieve,db+AD0-postgres,app+AD0AWw-unknown+AF0-,client+AD0-10.125.0.120LOG: connection authorized: user+AD0-pensieve database+AD0-postgres
2023-11-22 04:33:31 EST +AFs-1278503+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: recovery stopping before commit of transaction 25628, time 2023-11-22 04:33:31.454379-05
2023-11-22 04:33:31 EST +AFs-1278503+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: redo done at 1/2800BBB0 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 43.73 s
2023-11-22 04:33:31 EST +AFs-1278509+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-FATAL: terminating walreceiver process due to administrator command
ERROR: +AFs-037+AF0-: archive-get command requires option: pg1-path
HINT: does this stanza exist?
2023-11-22 04:33:31 EST +AFs-1278503+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: selected new timeline ID: 37
ERROR: +AFs-037+AF0-: archive-get command requires option: pg1-path
HINT: does this stanza exist?
2023-11-22 04:33:31 EST +AFs-1278503+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: archive recovery complete
2023-11-22 04:33:31 EST +AFs-1278501+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: checkpoint starting: end-of-recovery immediate wait
2023-11-22 04:33:31 EST +AFs-1278501+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: checkpoint complete: wrote 10 buffers (0.0+ACU-)+ADs- 0 WAL file(s) added, 0 removed, 0 recycled+ADs- writ
e+AD0-0.026 s, sync+AD0-0.003 s, total+AD0-0.038 s+ADs- sync files+AD0-9, longest+AD0-0.002 s, average+AD0-0.001 s+ADs- distance+AD0-16430 kB, estimate+AD0-16430 kB
2023-11-22 04:33:31 EST +AFs-1278499+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: database system is ready to accept connections
Host key verification failed.
rsync: connection unexpectedly closed (0 bytes received so far) +AFs-sender+AF0-
Thanks
Vijay
Classification: Internal
-----Original Message-----
From: Laurenz Albe +ADw-laurenz.albe+AEA-cybertec.at+AD4-
Sent: 23 November 2023 14:14
To: Vijaykumar Patil +ADw-vijaykumar.patil+AEA-maersk.com+AD4AOw- pgsql-general+AEA-lists.postgresql.org
Subject: Re: General support on postgres replication
This message was sent from outside of your organization. Please do not click links or open attachments unless you recognize the source of this email and know the content is safe.
On Wed, 2023-11-22 at 08:57 +-0000, Vijaykumar Patil wrote:
+AD4- I have created streaming replication with two nodes .
+AD4-
+AD4- One is primary and 2nd one is standby but after doing any DML or DDL
+AD4- operation on primary Walreceiver process is terminated on standby and
+AD4- standby database is opening in read write mode.
+AD4-
+AD4- Below are the error details .
+AD4-
+AD4- 2023-11-22 03:39:58 EST +AFs-1275942+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG:
+AD4- recovery stopping before commit of transaction 25627, time 2023-11-22
+AD4- 03:39:58.013764-05
+AD4- 2023-11-22 03:39:58 EST +AFs-1275942+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-LOG: redo
+AD4- done at 1/260059E0 system usage: CPU: user: 0.02 s, system: 0.04 s,
+AD4- elapsed: 5197.90 s
+AD4- 2023-11-22 03:39:58 EST +AFs-1276846+AF0-: user+AD0-,db+AD0-,app+AD0-,client+AD0-FATAL:
+AD4- terminating walreceiver process due to administrator command
+AD4- ERROR: +AFs-037+AF0-: archive-get command requires option: pg1-path
+AD4- HINT: does this stanza exist?
It looks like you have a bad +ACI-restore+AF8-command+ACI- configured.
It would be intereseting to see the log lines before the ones you are showing.
Yours,
Laurenz Albe
+AF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXw-
The information contained in this message is privileged and intended only for the recipients named. If the reader is not a representative of the intended recipient, any review, dissemination or copying of this message or the information it contains is prohibited. If you have received this message in error, please immediately notify the sender, and delete the original message and attachments.
Maersk will as part of our communication and interaction with you collect and process your personal data. You can read more about Maersk+IBk-s collection and processing of your personal data and your rights as a data subject in our privacy policy +ADw-https://www.maersk.com/front-page-requirements/privacy-policy+AD4-
Please consider the environment before printing this email.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: General support on postgres replication
@ 2023-11-23 11:53 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Vijaykumar Patil <vijaykumar.patil@maersk.com>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2023-11-23 11:53 UTC (permalink / raw)
To: Vijaykumar Patil <vijaykumar.patil@maersk.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Thu, 2023-11-23 at 09:32 +0000, Vijaykumar Patil wrote:
> LOG: entering standby mode
> LOG: consistent recovery state reached at 1/27000100
> LOG: database system is ready to accept read-only connections
> LOG: started streaming WAL from primary at 1/28000000 on timeline 36
> LOG: recovery stopping before commit of transaction 25628, time 2023-11-22 04:33:31.454379-05
> LOG: redo done at 1/2800BBB0 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 43.73 s
> FATAL: terminating walreceiver process due to administrator command
> LOG: selected new timeline ID: 37
> LOG: archive recovery complete
When you built your standby, you accidentally set (or copied via pg_basebackup)
an option "recovery_target_*", so recovery stopped at that point, and the standby
server was promoted.
Don't set any of these parameters on the standby server.
This is certainly one of the major problems introduced by commit 2dedf4d9a8:
If you ever recovered a database, you may end up having recovery parameters
set in your configuration file. You don't notice them until you build a
standby server, which will then get into trouble.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-11-23 11:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-22 08:57 General support on postgres replication Vijaykumar Patil <vijaykumar.patil@maersk.com>
2023-11-22 16:22 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-11-23 08:43 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-11-23 09:32 ` Vijaykumar Patil <vijaykumar.patil@maersk.com>
2023-11-23 11:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
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