[ClusterLabs] PostgreSQL PAF failover issue

[Tiemen Ruiten]

Hello,

I setup a new 3-node PostgreSQL cluster with HA managed by PAF. Nodes are
named ph-sql-03, ph-sql-04, ph-sql-05. Archive mode is on and writing
archive files to an NFS share that's mounted on all nodes using pgBackRest.

What I did:
- Create a pacemaker cluster, cib.xml is attached.
- Set maintenance-mode=true in pacemaker
- Bring up ph-sql-03 with pg_ctl start
- Take a pg_basebackup on ph-sql-04 and ph-sql-05
- Create a recovery.conf on ph-sql-04 and ph-sql-05:

standby_mode = 'on'
primary_conninfo = 'user=replication password=XXXXXXXXXXXXXXXX
application_name=ph-sql-0x host=10.100.130.20 port=5432 sslmode=prefer
sslcompression=0 krbsrvname=postgres target_session_attrs=any'
recovery_target_timeline = 'latest'
restore_command = 'pgbackrest --stanza=pgdb2 archive-get %f "%p"'

- Bring up ph-sql-04 and ph-sql-05 and let recovery finish
- Set maintenance-mode=false in pacemaker
- Cluster is now running with ph-sql-03 as master and ph-sql-04/5 as slaves
At this point I tried a manual failover:
- pcs resource move --wait --master pgsql-ha ph-sql-04
Contrary to my expectations, pacemaker attempted to stop psqld on
ph-sql-03. This took longer than the configured timeout of 60s (checkpoint
hadn't completed yet) and the node was fenced. Then I ended up with
ph-sql-04 and ph-sql-05 both in slave mode and ph-sql-03 rebooting.

 Master: pgsql-ha
  Meta Attrs: notify=true
  Resource: pgsqld (class=ocf provider=heartbeat type=pgsqlms)
   Attributes: bindir=/usr/pgsql-11/bin pgdata=/var/lib/pgsql/11/data
recovery_template=/var/lib/pgsql/recovery.conf.pcmk
   Operations: demote interval=0s timeout=30s (pgsqld-demote-interval-0s)
               methods interval=0s timeout=5 (pgsqld-methods-interval-0s)
               monitor interval=15s role=Master timeout=10s
(pgsqld-monitor-interval-15s)
               monitor interval=16s role=Slave timeout=10s
(pgsqld-monitor-interval-16s)
               notify interval=0s timeout=60s (pgsqld-notify-interval-0s)
               promote interval=0s timeout=30s (pgsqld-promote-interval-0s)
               reload interval=0s timeout=20 (pgsqld-reload-interval-0s)
               start interval=0s timeout=60s (pgsqld-start-interval-0s)
               stop interval=0s timeout=60s (pgsqld-stop-interval-0s)

I understand I should at least increase the timeout of the stop operation
for psqld, though I'm not sure how much. Checkpoints can take up to 15
minutes to complete on this cluster. So is 20 minutes reasonable? Any other
operations I should increase the timeouts for?

Why didn't pacemaker elect and promote one of the other nodes?



-- 
Tiemen Ruiten
Infrastructure Engineer
R&D Media
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.clusterlabs.org/pipermail/users/attachments/20190614/01a90cab/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: cluster1.xml
Type: text/xml
Size: 24355 bytes
Desc: not available
URL: <https://lists.clusterlabs.org/pipermail/users/attachments/20190614/01a90cab/attachment-0001.xml>