RE: Postgresql HA cluster - Mailing list pgsql-general

Thank you for the feedback,

I have used pacemaker for other purposes previously so am a little familiar with it. It appears that in this scenario pacemaker is being used to manage a floating ip as well as deal with split brain scenarios. What isn’t clear is how effective master-> master replication is being accomplished. Postgresql streaming replication to the best of my limited knowledge only replicates in one direction, from the active to the standby servers. The issue this presents to me is that once you failover from the active to the standby (or one of the standby’s depending on how many you have) none of the data written on the standby is replicated back to the formerly active server.

Let us say that I have only 2 postgresql servers (absolute minimum number) and I want to patch server A. Ideally, I would use a load balancer (or other failover mechanism like pacemaker) and repoint the floating ip to server B. Now traffic would “drain” down off server A, and slowly (or rapidly) move to B. During the move some clients would still be writing to A and some clients would be writing to B. Once they have all moved to B, server A would then be patched. Then the load balancer would be used to repoint the floating ip again back to A, and the process would repeat, with traffic moving back to A. Just like in the first half of the failover some traffic would exist on both hosts as the failover progresses. Once completed all the traffic would be back on A.

In the above scenario, I do not understand how streaming replication would handle the part of the scenario when there are clients writing to A and B. It would seem that something like `pgpool-ii` or `pgEdge` would be required, but with my limited knowledge it is unclear if or which would be appropriate.

Regards

Jason

Jason Grammenos | Operations & Infrastructure Analyst  
Pronouns: he/him
P: 613.232.7797 x1131
Toll-free: 866.545.3745 x1131
jason.grammenos@agilitypr.com
agilitypr.com

Learn new PR tips from our free resources.

Hello,

 

I am new to PostgreSQL and having to migrate a Cassandra cluster to PostgreSQL.

Have a reasonable amount of experience with MySQL and use Master -> Master MySQL replication quite successfully.

 

I am trying to understand what configuration/option to pick for PostgreSQL that will provide the same or similar level of operational easy as the Cassandra cluster. What I mean by that is : Backups, Patching (rebooting nodes to patch)

With Cassandra any single node can be patched and rebooting without incurring downtime. This is also true with MySQL master->master replication, which we have fronted by a HAproxy reverse proxy (we can fail over between the two MySQL node, draining the connections as we do so and then patching and reboot).

 

I have found the following documentation:

https://www.postgresql.org/docs/current/different-replication-solutions.html

 

and have heard of “Traktor”, “pgEdge” and some other third-party tools.

My difficulty is that with very little knowledge of PostgreSQL I am having a hard time sorting through the various options and determining which are potential candidates for the kind of setup I am looking for (something approximating MySQL master->master replication).

 

Any advice or resources would be much appreciated.

Regards

Jason Grammenos

 

Jason Grammenos | Operations & Infrastructure Analyst  
Pronouns: he/him
P: 613.232.7797 x1131
Toll-free: 866.545.3745 x1131
jason.grammenos@agilitypr.com
agilitypr.com

Learn new PR tips from our free resources.