Questions trying to im;le

[themactech]

No description provided.

[themactech]

Almost a year ago, I was looking at implementing automatic failover in a PostgreSQL cluster. I got repmgr automatic failover up and running first so we went with that instead of PAF, but now I have a Window to look at switching back to PAF if it gives us more features. Here is what I have so far (I deploy this via ansible playbooks):

• PostgreSQL Cluster of two nodes with streaming replication
• Pacemaker Cluster of two nodes with 2 VirtualIPs
  • First VirtualIP points to master for R/W operations
  • Second VirtualIP points to standby for read-only operations

We are stuck using PostgreSQL 9.6 for this project. I currently deploy in my test lab 2 Clusters of 2 nodes each (via ansible)

• Cluster 1: (in test lab but also what we deployed in production)

  • Debian 12
  • PostgreSQL 9.6
  • Pacemaker 2.15
    • pcsd component updated since pcsd that comes with Debian 12 errors when trying to move resources
  • repmgr automatic failover

• Cluster 2:

  • Debian 12
  • PostgreSQL 9.6
  • Pacemaker 2.15
    • pcsd component updated since pcsd that comes with Debian 12 errors when trying to move resources
  • PAF automatic failover

Currently (with repmgr), if the master fails, we get:

• Replaced the failover commands in repmgr.conf with a bash script that:

• Launches the PostgreSQL failover
• Launches Pacemaker failover of the first VirtualIP
• Checks if PostgreSQL cluster still has an online standby:
  • yes: Move 2nd VirtualIP to it
  • no: Leave 2nd VirtualIP on newly promoted node
• Send SNMP trap that a failover occurred so we can quickly put the failed node back online

We also run a 'sanity_check' script every 5 minutes that verifies everything is running where it should be and send SNMP traps if it isn't

We also wrote some SNMP extensions to gather some Pacemaker/PostgreSQL metrics every SNMP polling session

I have this working, but it only is one way, i.e., if the Master fails, everything toggles properly to the standby server, but it does not fail back to the master if the standby then fails. So we get alerted immediately after a failover so we can manually put the master back online (as a new standby). Would switching over to PAF give me a more robust failover mecanism?

Currently with PAF, I have this:

PCS status gives me:

Cluster name: MTL03VLTDB-CDR5V
Status of pacemakerd: 'Pacemaker is running' (last updated 2023-12-18 03:44:47 -05:00)
Cluster Summary:
  * Stack: corosync
  * Current DC: MTL03VLTDB-CDR52 (version 2.1.5-a3f44794f94) - partition with quorum
  * Last updated: Mon Dec 18 03:44:47 2023
  * Last change:  Mon Dec 18 03:44:41 2023 by hacluster via crmd on MTL03VLTDB-CDR51
  * 2 nodes configured
  * 5 resource instances configured

Node List:
  * Online: [ MTL03VLTDB-CDR51 MTL03VLTDB-CDR52 ]

Full List of Resources:
  * ClusterIP	(ocf:heartbeat:IPaddr2):	 Started MTL03VLTDB-CDR51
  * ClusterIP2	(ocf:heartbeat:IPaddr2):	 Started MTL03VLTDB-CDR52
  * vmfence	(stonith:fence_vmware_soap):	 Started MTL03VLTDB-CDR52
  * Clone Set: pgsqld-clone [pgsqld] (promotable):
    * Promoted: [ MTL03VLTDB-CDR51 ]
    * Unpromoted: [ MTL03VLTDB-CDR52 ]

Daemon Status:
  corosync: active/enabled
  pacemaker: active/enabled
  pcsd: active/enabled

But switching to the postgres user and getting cluster info gives me:

WARNING: node "MTL03VLTDB-CDR52" not found in "pg_stat_replication"
 ID | Name             | Role    | Status    | Upstream           | Location | Priority | Timeline | Connection string
----+------------------+---------+-----------+--------------------+----------+----------+----------+----------------------------------------------------------------------
 1  | MTL03VLTDB-CDR51 | primary | * running |                    | default  | 100      | 3        | host=MTL03VLTDB-CDR51-LL user=repmgr dbname=repmgr connect_timeout=2
 2  | MTL03VLTDB-CDR52 | standby |   running | ! MTL03VLTDB-CDR51 | default  | 100      | 1        | host=MTL03VLTDB-CDR52-LL user=repmgr dbname=repmgr connect_timeout=2

WARNING: following issues were detected
  - node "MTL03VLTDB-CDR52" (ID: 2) is not attached to its upstream node "MTL03VLTDB-CDR51" (ID: 1)

What steps would this point to that I have setup wrong?

I should also note we perform streaming replication on a second/dedicated network interface with the same DNS name as the main node but with -LL added, so for MTL03VLTDB-CDR52, it replicates over MTL03VLTDB-CDR52-LL

[ioguix]

Hi,

I'm not familiar with repmgr commands anymore (and I never been really comfortable with them anyway). I'm not sure what your cluster status command is really doing, neither what ! MTL03VLTDB-CDR51 means.

However, I can tell you the standby is not following the primary anymore. It seems stuck on timeline 1 where the primary is on timeline 3 (TL 3).

If you already use Pacemaker, I advise moving away from repmgr for the sake of simplicity and robustness. Either build a Shared Storage cluster if you have a clean, robust and replicated storage around, or use PAF if you want to keep PostgreSQL replication. What ever you chose, dealing with IP location will be much easier and safer (you can have split brain on IP as well...).

In either case, you don't need repmgr anymore. I would rather advice relying on pgbackrest or barman to deal with your PITR backups and fast standby resynchronisation.

PAF does not support auto-failback by design.

Lastly, make sure to use fencing and/or watchdog and/or SBD (poison pills).