2

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)?

Improve this question
4
  • If you are on AWS, I'd suggest looking into Spilo
    – András Váczi
    Commented Sep 12, 2017 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
    Commented Sep 12, 2017 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 ?
    – TheDataGuy
    Commented Sep 12, 2017 at 19:06
  • Have you looked at BDR?
    – Vérace
    Commented Sep 12, 2017 at 20:04

2 Answers 2

Reset to default
4

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.

Improve this answer
1
  • If my master and slave is in different subnet then I guess it's not possible to attach vip.
    – TheDataGuy
    Commented Sep 13, 2017 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.

Improve this answer
3
  • Thanks for the answer, but I guess DRBD is not a shared storage, it's like a disk mirroring.
    – TheDataGuy
    Commented Sep 13, 2017 at 1:21
  • OK, so it's single-master storage, that helps. My mistake, I thought DRBD accepted writes from multiple nodes.
    – Craig Ringer
    Commented Sep 13, 2017 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
    Commented Sep 15, 2017 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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