Postgresql HA cluster
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 [cid:image001.png@01D9FB7D.85F88200] Jason Grammenos | Operations & Infrastructure Analyst Pronouns: he/him P: 613.232.7797 x1131 Toll-free: 866.545.3745 x1131 jason.gramme...@agilitypr.com<mailto:jason.gramme...@agilitypr.com> agilitypr.com<https://agilitypr.com/> Learn new PR tips from our free resources<https://www.agilitypr.com/resources/>.
RE: Postgresql HA cluster
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 [cid:image001.png@01D9FDA9.6A6562F0] Jason Grammenos | Operations & Infrastructure Analyst Pronouns: he/him P: 613.232.7797 x1131 Toll-free: 866.545.3745 x1131 jason.gramme...@agilitypr.com<mailto:jason.gramme...@agilitypr.com> agilitypr.com<https://agilitypr.com/> Learn new PR tips from our free resources<https://www.agilitypr.com/resources/>. From: Олег Самойлов Sent: Thursday, October 12, 2023 11:08 AM To: Jason Grammenos ; pgsql-general@lists.postgresql.org Subject: Re: Postgresql HA cluster For Postgresql HA cluster the most popular solution is streaming replication. There is an option how implement this. Web programmer approach is using haproxy+consul+patrony. The "old schoool" is using Pacemaker, all in one bottle and well tested. If you interesting how implement in Pacemaker, you may look at my project of testbed that continuously test different HA clusters by random failures. I don't see such for haproxy+consul+patrony. https://github.com/domclick/tuchanka 11.10.2023, 21:02, "Jason Grammenos" mailto:jason.gramme...@agilitypr.com>>: 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 [cid:image001.png@01D9FDA9.6A6562F0] Jason Grammenos | Operations & Infrastructure Analyst Pronouns: he/him P: 613.232.7797 x1131 Toll-free: 866.545.3745 x1131 jason.gramme...@agilitypr.com<mailto:jason.gramme...@agilitypr.com> agilitypr.com<https://agilitypr.com/> Learn new PR tips from our free resources<https://www.agilitypr.com/resources/>.
RE: Postgresql HA cluster
Hello Jehan-Guillaume de Rorthais Perhaps you could help me understand a few things better about how primary-standby works in postgresql with streaming replication. If you have 2 PostgreSQL nodes hooked up to a Load balancer (haproxy), and you move take node1 out of load balancing, you now have connections on node1 and connections on node2, as the Load balancer drains the connections off node1 and over to node2. How does PostgreSQL handle this scenario when there are writes happening on both nodes? If instead you have 2 PostgreSQL nodes behind pacemaker (controlling a floating ip), what happens when you initiate a failover and move the floating ip? You want the connections to drain off node1 and move to node2. Again in this scenario, both nodes would be sustaining writes at some point in time. How does the write only replica get changed out of write mode during this failover? How does the primary node get switched to read only after the connection drain is complete? Overall, I am trying to understand what it looks like operationally to run a 2 node postgresql "cluster" and how patching both nodes would work (and mentally comparing it to what I currently do with mysql). You recommended that primary-standby could be sufficient and is much simpler, so I am simply trying to wrap my head around what exactly running it would look like. If primary standby is simple enough to failover, patch , reboot, maintain, etc. Then you could be correct that master->master may not be needed. Thank You Jason Jason Grammenos | Operations & Infrastructure Analyst Pronouns: he/him P: 613.232.7797 x1131 Toll-free: 866.545.3745 x1131 jason.gramme...@agilitypr.com agilitypr.com Learn new PR tips from our free resources. -Original Message- From: Jehan-Guillaume de Rorthais Sent: Friday, October 13, 2023 9:10 AM To: Jason Grammenos Cc: pgsql-general@lists.postgresql.org Subject: Re: Postgresql HA cluster On Fri, 13 Oct 2023 12:02:53 +0000 Jason Grammenos wrote: > Thank you for the feedback, > > I have used pacemaker for other purposes previously so am a little > familiar with it. So you might be familiar with shared-storage cluster, that are the simpler one you could deploy (baring you have a reliable HA storage available). But it's not a multi-primary cluster. > It appears that in this scenario pacemaker is being used to manage a > floating ip as well as deal with split brain scenarios. There's also two different resource agents dealing with PostgreSQL itself: pgsql and PAF. Both handle multi-status differently from the administration point of view. > What isn’t clear is how effective master-> master replication is being > accomplished. There's no master-master in PostgreSQL core. There's few external solutions out there though, but double check you real needs, the real primary-standby capacity to answer you needs, and the various constraints M-M imply before rushing there. > 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. It depend if this is a "controlled failover" (aka. "switchover") or a real failover triggered by some failure. If this is a controlled failover, you can hook back your old primary as a standby with no trouble. PAF even handle this for you. Moreover, even with a failure scenario, there's some solutions around to quickly fix your old primary data and get it back in production quickly as a standby (pg_rewind, PITR/pgbackrest, etc). You just have to plan for failure and write you procedures accordingly to get the cluster back on feet quickly after a failover. > 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. This doesn't exist as PostgreSQL has no multi-primary solution in core. You can do rolling upgrade, but you'll have to pause the production during the switchover between the primary and the standby. > 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 will not. > It would seem that something like `pgpool-ii` or `pgEdge` would be > required, but with my limited knowledge it is uncle
