General support on postgres replication
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:
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-052023-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 s2023-11-22 03:39:58 EST [1276846]: user=,db=,app=,client=FATAL:
terminating walreceiver process due to administrator commandERROR: [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: 37ERROR: [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?
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
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.
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