General support on postgres replication

[Vijaykumar Patil]

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 

Please consider the environment before printing this email.


Classification: Internal

RE: General support on postgres replication

[Vijaykumar Patil]

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/2728
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/2800 on timeline 36
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, time 2023-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=16430 kB
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 
Sent: 23 November 2023 14:14
To: Vijaykumar Patil ; 
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 recognize the source of this email and 
know the content is safe.


On Wed, 2023-11-22 at 08:57 +, 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 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.

PAF with Pacemaker

[Vijaykumar Patil]

Hi Team ,

I have two postgres server one is primary and other one replica, I have setup 
replication and configured pacemaker and corosync.

But still I'm facing issue while creating resource. It is showing invalid 
parameters.

[root@scrbtrheldbaas001 heartbeat]# pcs status
Cluster name: pg_cluster
Cluster Summary:
  * Stack: corosync (Pacemaker is running)
  * Current DC: scrbtrheldbaas001 (version 2.1.6-8.el8-6fdc9deea29) - partition 
with quorum
  * Last updated: Thu Nov 30 19:04:29 2023 on scrbtrheldbaas001
  * Last change:  Thu Nov 30 13:41:53 2023 by root via cibadmin on 
scrbtrheldbaas002
  * 2 nodes configured
  * 2 resource instances configured

Node List:
  * Online: [ scrbtrheldbaas001 scrbtrheldbaas002 ]

Full List of Resources:
  * Clone Set: pgsqld-clone [pgsqld] (promotable):
* Stopped (invalid parameter): [ scrbtrheldbaas001 scrbtrheldbaas002 ]

Daemon Status:
  corosync: active/enabled
  pacemaker: active/enabled
  pcsd: active/enabled
[root@scrbtrheldbaas001 heartbeat]#


My postgres version is 15.3 but still  is searching recover.conf . please find 
below log.





Node 1 pacemaker log:-

[root@scrbtrheldbaas001 heartbeat]# journalctl -xe | grep pacemaker
Nov 30 18:58:32 scrbtrheldbaas001.crb.apmoller.net pacemaker-controld[69280]:  
notice: State transition S_IDLE -> S_POLICY_ENGINE
Nov 30 18:58:32 scrbtrheldbaas001.crb.apmoller.net pacemaker-schedulerd[69279]: 
 notice: Treating probe result 'invalid parameter' for pgsqld:0 on 
scrbtrheldbaas002 as 'not running'
Nov 30 18:58:32 scrbtrheldbaas001.crb.apmoller.net pacemaker-schedulerd[69279]: 
 notice: Treating probe result 'invalid parameter' for pgsqld:0 on 
scrbtrheldbaas002 as 'not running'
Nov 30 18:58:32 scrbtrheldbaas001.crb.apmoller.net pacemaker-schedulerd[69279]: 
 notice: Treating probe result 'invalid parameter' for pgsqld:0 on 
scrbtrheldbaas001 as 'not running'
Nov 30 18:58:32 scrbtrheldbaas001.crb.apmoller.net pacemaker-schedulerd[69279]: 
 notice: Treating probe result 'invalid parameter' for pgsqld:0 on 
scrbtrheldbaas001 as 'not running'
Nov 30 18:58:32 scrbtrheldbaas001.crb.apmoller.net pacemaker-schedulerd[69279]: 
 notice: Calculated transition 3, saving inputs in 
/var/lib/pacemaker/pengine/pe-input-87.bz2
Nov 30 18:58:32 scrbtrheldbaas001.crb.apmoller.net pacemaker-controld[69280]:  
notice: Transition 3 (Complete=0, Pending=0, Fired=0, Skipped=0, Incomplete=0, 
Source=/var/lib/pacemaker/pengine/pe-input-87.bz2): Complete
Nov 30 18:58:32 scrbtrheldbaas001.crb.apmoller.net pacemaker-controld[69280]:  
notice: State transition S_TRANSITION_ENGINE -> S_IDLE
[root@scrbtrheldbaas001 heartbeat]#

Node 2 pacemkaer log:-

13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemakerd[1112600]:  notice: 
Stopping pacemaker-fenced
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemaker-fenced[1112602]:  
notice: Caught 'Terminated' signal
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemakerd[1112600]:  
notice: Stopping pacemaker-based
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemaker-based[1112601]:  
notice: Caught 'Terminated' signal
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemaker-based[1112601]:  
notice: Disconnected from Corosync
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemaker-based[1112601]:  
notice: Disconnected from Corosync
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemakerd[1112600]:  
notice: Shutdown complete
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net systemd[1]: 
pacemaker.service: Succeeded.
-- The unit pacemaker.service has successfully entered the 'dead' state.
-- Subject: Unit pacemaker.service has finished shutting down
-- Unit pacemaker.service has finished shutting down.
-- Subject: Unit pacemaker.service has finished start-up
-- Unit pacemaker.service has finished starting up.
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemakerd[1114127]:  
notice: Additional logging available in /var/log/pacemaker/pacemaker.log
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemakerd[1114127]:  
notice: Starting Pacemaker 2.1.6-8.el8
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemakerd[1114127]:  
notice: Pacemaker daemon successfully started and accepting connections
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemaker-based[1114128]:  
notice: Starting Pacemaker CIB manager
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemaker-execd[1114130]:  
notice: Starting Pacemaker local executor
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemaker-execd[1114130]:  
notice: Pacemaker local executor successfully started and accepting connections
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemaker-execd[1114130]:  
notice: OCF resource agent search path is /usr/lib/ocf/resource.d
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemaker-fenced[1114129]:  
notice: Additional logging available in /var/log/pacemaker/pacemaker.log
Nov 30 13:43:28 scrbtrheldbaas002.crb.apmoller.net pacemaker-fenced[1114129]:  
n

unable to resgiter witnes node in repmgr

[Vijaykumar Patil]

Happy new year to everyone !!


Can any one help me on this below issue as we are not able to register witness 
node in repmgr , getting below error.

postgres@adsoazdbaodb04[TEST]-/home/postgres: repmgr -f 
/u01/app/admin/Data/repmgr.conf witness register -h scrbtrheldbaas001
INFO: connecting to witness node "adsoazdbaodb04" (ID: 4)
DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr 
host=adsoazdbaodb04 fallback_application_name=repmgr options=-csearch_path="
INFO: connecting to primary node
DEBUG: connecting to: "user=repmgr connect_timeout=2 dbname=repmgr 
host=scrbtrheldbaas001 fallback_application_name=repmgr options=-csearch_path="
ERROR: witness node cannot be in the same cluster as the primary node
DETAIL: database system identifiers on primary node and provided witness node 
match (7317528477531093832)
HINT: the witness node must be created on a separate read/write node


postgres@adsoazdbaodb04[TEST]-/home/postgres: cat 
/u01/app/admin/Data/repmgr.conf
#cluster='pg_cluster'
node_id=4
node_name=adsoazdbaodb04
conninfo='host=adsoazdbaodb04 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/u01/app/admin/Data/pg_da'
failover=automatic
promote_command='repmgr standby promote -f /u01/app/admin/Data/repmgr.conf 
--log-to-file'
follow_command='repmgr standby follow -f /u01/app/admin/Data/repmgr.conf 
--log-to-file --upstream-node-id=%n'
pg_bindir='/u01/app/admin/Postgresql/15.3/bin'
user='repmgr'
log_level = 'DEBUG'
log_file = '/u01/app/admin/Data/repmgr.log'
monitoring_history = 'true'
primary_visibility_consensus = 'true'

Thanks & Regards
Vijaykumar
Database Operations
[cid:image001.png@01DA3E32.D50A9B60]
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 

Please consider the environment before printing this email.


Classification: Internal