Hi, On Fri, 14 Jun 2019 12:27:12 +0200 Tiemen Ruiten <[email protected]> wrote: > 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
This is not required. Just build your PgSQL replication, shut down the instances, then add the PAF resource to the cluster. But it's not very important here. > - 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"' Sounds fine. > - 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. Indeed. PostgreSQL doesn't support hot-demote. It has to be shut downed and started as a standby. > This took longer than the configured timeout of 60s (checkpoint > hadn't completed yet) and the node was fenced. 60s of checkpoint during a maintenance window? That's important indeed. I would command doing a manual checkpoint before triggering the move/switchover. > 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? 20 minutes is not reasonable for HA. 2 minutes is for manual procedure. Timeout are here so the cluster knows how to react during unexpected failure. Not during maintenance. As I wrote, just add a manual checkpoint in your switchover procedure before the actual move. > Any other operations I should increase the timeouts for? > > Why didn't pacemaker elect and promote one of the other nodes? Do you have logs of all nodes during this time period? _______________________________________________ Manage your subscription: https://lists.clusterlabs.org/mailman/listinfo/users ClusterLabs home: https://www.clusterlabs.org/
