1

I need to setup a Highly available Postgresql Cluster. So I found Pacemaker and DRBD with pgpool load balancer are the best options.

So the architecture will look like this.

enter image description here

As of my knowledge, DRBD is for Block level replication (works on storage) and Pacemaker is Service Cluster (works on service level). So which is the best one to use and best HA solution?

And another question is Im using AWS EC2, So how can I manage VIP (I mean if the primary goes down how the PGpool will switch the VIP to Standby also its and in AWS)?

  • If you are on AWS, I'd suggest looking into Spilo – dezso Sep 12 '17 at 19:03
  • Although I am not a full expert with PostgreSQL, I did write up something 5.5 years ago about PostgreSQL and DRBD using, not Pacemaker, but ucarp for VIP movement : dba.stackexchange.com/questions/14201/… – RolandoMySQLDBA Sep 12 '17 at 19:03
  • @RolandoMySQLDBA - Yes, I read this already, ok, lets ignore VIPs. Is this possible to setup the cluster with pacemaker+DRDB+corosync and if the master goes down the slave will act as a master and when the old master came up then it should act as a slave (without reinitiating the replication). Is this possible ? – Bhuvanesh Sep 12 '17 at 19:06
  • Have you looked at BDR? – Vérace Sep 12 '17 at 20:04
3

Usually, you use them both. DRBD to replicate the data synchronously, and Pacemaker to manage where the services become active. Pacemaker will control promoting/demoting DRBD, and starting your database and virtual IP address.

Pacemaker uses resource-agents to manage cluster services. One of those, is a resource-agent for moving a secondary IP between AWS nodes: https://github.com/ClusterLabs/resource-agents/blob/master/heartbeat/awsvip

Hope that leads you down the right path.

  • If my master and slave is in different subnet then I guess it's not possible to attach vip. – Bhuvanesh Sep 13 '17 at 2:36
2

Shared storage is a seriously bad idea with postgres unless you have total trust in your fencing.

It looks like DRBD is single-master, so it's probably OK.

It's absolutely vital that you ensure that the non-master's postgres is completely down, and there's zero overlap in which two postgres instances could be sharing the same data directory. Otherwise massive data corruption will occur. PostgreSQL is not a shared-storage database.

Get your STONITH and fencing right.

Personally, I'd recommend using PostgreSQL's replication, rather than block device replication, but I'm a PostgreSQL developer so that's hardly surprising.

  • Thanks for the answer, but I guess DRBD is not a shared storage, it's like a disk mirroring. – Bhuvanesh Sep 13 '17 at 1:21
  • OK, so it's single-master storage, that helps. My mistake, I thought DRBD accepted writes from multiple nodes. – Craig Ringer Sep 13 '17 at 1:23
  • 1
    It can be either Master/Slave (default) or Master/Master, but the latter requires STONITH/fencing and some form of application locking to avoid corruption. – Matt Kereczman Sep 15 '17 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.