Thread: General support on postgres replication

Classification: Internal

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,
time2023-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 [1278503]: user=,db=,app=,client=LOG:  entering standby mode
ERROR: [037]: archive-get command requires option: pg1-path
       HINT: does this stanza exist?
ERROR: [037]: archive-get command requires option: pg1-path
       HINT: does this stanza exist?
2023-11-22 04:32:47 EST [1278503]: user=,db=,app=,client=LOG:  redo starts at 1/27000028
ERROR: [037]: archive-get command requires option: pg1-path
       HINT: does this stanza exist?
2023-11-22 04:32:47 EST [1278503]: user=,db=,app=,client=LOG:  consistent recovery state reached at 1/27000100
2023-11-22 04:32:47 EST [1278499]: user=,db=,app=,client=LOG:  database system is ready to accept read-only connections
ERROR: [037]: archive-get command requires option: pg1-path
       HINT: does this stanza exist?
2023-11-22 04:32:47 EST [1278509]: user=,db=,app=,client=LOG:  started streaming WAL from primary at 1/28000000 on
timeline36 
2023-11-22 04:33:02 EST [1278511]: user=[unknown],db=[unknown],app=[unknown],client=10.125.0.120LOG:  connection
received:host=10.125.0.120 port=48408 
2023-11-22 04:33:02 EST [1278511]: user=pensieve,db=postgres,app=[unknown],client=10.125.0.120LOG:  connection
authorized:user=pensieve database=postgres 
2023-11-22 04:33:31 EST [1278503]: user=,db=,app=,client=LOG:  recovery stopping before commit of transaction 25628,
time2023-11-22 04:33:31.454379-05 
2023-11-22 04:33:31 EST [1278503]: user=,db=,app=,client=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 [1278509]: 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 04:33:31 EST [1278503]: 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 04:33:31 EST [1278503]: user=,db=,app=,client=LOG:  archive recovery complete
2023-11-22 04:33:31 EST [1278501]: user=,db=,app=,client=LOG:  checkpoint starting: end-of-recovery immediate wait
2023-11-22 04:33:31 EST [1278501]: user=,db=,app=,client=LOG:  checkpoint complete: wrote 10 buffers (0.0%); 0 WAL
file(s)added, 0 removed, 0 recycled; writ 
e=0.026 s, sync=0.003 s, total=0.038 s; sync files=9, longest=0.002 s, average=0.001 s; distance=16430 kB,
estimate=16430kB 
2023-11-22 04:33:31 EST [1278499]: user=,db=,app=,client=LOG:  database system is ready to accept connections
Host key verification failed.
rsync: connection unexpectedly closed (0 bytes received so far) [sender]


Thanks
Vijay


Classification: Internal
-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: 23 November 2023 14:14
To: Vijaykumar Patil <vijaykumar.patil@maersk.com>; pgsql-general@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
recognizethe source of this email and know the content is safe. 


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

________________________________

The information contained in this message is privileged and intended only for the recipients named. If the reader is
nota representative of the intended recipient, any review, dissemination or copying of this message or the information
itcontains is prohibited. If you have received this message in error, please immediately notify the sender, and delete
theoriginal message and attachments. 

Maersk will as part of our communication and interaction with you collect and process your personal data. You can read
moreabout 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.

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