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 p...@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
