Thread: Open source HA setup for postgres

Hi Sam,

There is a whole chapter about this topic in the official documentation

https://www.postgresql.org/docs/current/high-availability.html

Plus, there are products like pg_auto_failover by Citus (Microsoft) and the template Patroni.

Regards,

Holger

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:11.0pt; font-family:"Calibri",sans-serif;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;}div.WordSection1 {page:WordSection1;}

Hi,

 

We are wondering what is open-source tools being used to set up the HA. We are also looking for a way to pass the reads to all the HA nodes and writes to the primary.  One of the pressing criteria is the HA also should be able to handle the node being taken down during patching and should be able to fail back or recover seamlessly without any manual intervention.

 

This is the first time we are embarking on HA, so would like some advice and caveats to avoid.

 

Thanx

Sam

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

On 10/14/22 11:33, Tharmarajah, Sam wrote:

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:11.0pt; font-family:"Calibri",sans-serif;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;}div.WordSection1 {page:WordSection1;}

Hi,

 

We are wondering what is open-source tools being used to set up the HA. We are also looking for a way to pass the reads to all the HA nodes and writes to the primary.  One of the pressing criteria is the HA also should be able to handle the node being taken down during patching and should be able to fail back or recover seamlessly without any manual intervention.

 

This is the first time we are embarking on HA, so would like some advice and caveats to avoid.

What I'm currently looking at is this combo:
https://repmgr.org/ (for quorum voting, fail over and seamless fail back)
https://pgpool.net/mediawiki/index.php/Main_Page (for a common IP address)

On a switchover (whether planned or unplanned), repgmr reconfigures PgPool to use the New-primary's IP address.

On 10/14/22 11:33, Tharmarajah, Sam wrote:

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:11.0pt; font-family:"Calibri",sans-serif;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;}div.WordSection1 {page:WordSection1;}

Hi,

 

We are wondering what is open-source tools being used to set up the HA. We are also looking for a way to pass the reads to all the HA nodes and writes to the primary.  One of the pressing criteria is the HA also should be able to handle the node being taken down during patching and should be able to fail back or recover seamlessly without any manual intervention.

 

This is the first time we are embarking on HA, so would like some advice and caveats to avoid.

I meant PgBouncer, not PgPool.  No SELECT load balancing, though.

https://www.pgbouncer.org/