Redundant Database, Pgsql ?

[Brendan Kearney]

let me preface all of this by saying i dont have PostgreSQL running in 
any fashion, but have come across footprints that are standing in a 
Production environment.

are you running a single primary R/W node, with multiple secondary R/O 
nodes?  from what i have seen/heard, PG does not really have a well 
documented and currently support n-way multi-primary R/W load balancing 
mechanism.  that said, some effort did exist but may no longer be supported.

i have seen a HA footprint of PG setup with HAProxy, PostgreSQL, ETCd 
and Patroni.  HAProxy handles the Virtual IP (VIP) and can be setup as 
HA with VRRP running between the HAProxy nodes.  ETCd is setup with 3 
nodes, all monitoring the "active" status of the PostgreSQL nodes via 
Patroni.  Patroni runs on each PG node, watching the status of the PG 
instances for failures.  PG runs with one node in the "active" state, 
and replicates to the secondary nodes running in the "standby" state.

when the primary node encounters an issue, Patroni idenitifies that the 
node is no longer able to process, and ETCd records the updated status.  
HAProxy polls ETCd and is notified of the event, and marks the 
previously "active" member as down or in standby state.  by election 
process, the standby nodes promote a node to the active state, and the 
Patroni -> ETCd -> HAProxy chain picks up the new active node.

it requires a bit of supporting infrastructure to get it all working, 
but you wind up with a highly-available, fault tolerant PostgreSQL 
footprint with automatic failover.  the caveat is that you only have a 
single R/W instance at any one point.  this could be a performance 
bottleneck in high volume environments.

some links that may shed light on what and how:

https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
https://patroni.readthedocs.io/en/latest/
https://www.percona.com/blog/postgresql-ha-with-patroni-your-turn-to-test-failure-scenarios/
https://arctype.com/blog/postgres-patroni/

i do have a n-way multi-primary MariaDB footprint running with HAProxy, 
MariaDB and Galera running.  Each MariaDB instance is R/W and can take a 
write event, and then replicate the event to the other cluster members.  
the VIP on HAProxy for port 3306 has all three cluster members load 
balanced using least connections.  on the same VIP, but using a 
different port (3316, 3326, 3336) i have a backend for each of the 
individual cluster members, so i can isolate and troubleshoot each node 
separately.

in the PostgreSQL footprints i have come across, a similar setup using 
other ports has been used for access to the R/O nodes in the cluster.  
this can allow for queries, instead of writes, and reporting functionality.

best of luck,

brendan kearney

On 2/21/23 4:02 AM, Paul Kudla (SCOM.CA Internet Services Inc.) wrote:
>
> yes that seems to be the approach
>
> i setup a dns entry and pointed to 3 servers
>
> it does work round robin (ie from main, secondary etc) but that is ok
>
> at least it is working when i take the main server offline for 
> maintenance !
>
>
>
>
> Happy Tuesday !!!
> Thanks - paul
>
> Paul Kudla
>
>
> Scom.ca Internet Services <http://www.scom.ca>
> 004-1009 Byron Street South
> Whitby, Ontario - Canada
> L1N 4S3
>
> Toronto 416.642.7266
> Main 1.866.411.7266
> Fax 1.888.892.7266
> Email paul at scom.ca
>
> On 2023-02-19 12:56 p.m., Benny Pedersen wrote:
>>
>> Paul Kudla skrev den 2023-02-19 16:01:
>>> May I please get some guidance on what to add to talk to another
>>> postgresql server (i have 6 replicated servers so would probably want
>>> a couple worst case issue)
>>
>> change host=localhost to host=some-other-hostname-with-multiple-ips :)
>>
>> then dovecot with timeout and test next server ip
>>
>> there might be more to it, but i think this is how to do it
>>