How is PG replication typically used to create a High Availability (HA) config ?

From: David Gauthier <davegauthierpg(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: How is PG replication typically used to create a High Availability (HA) config ?
Date: 2020-08-11 15:12:46
Message-ID: CAMBRECD3EYs1AvkXPZ10gR0q4eiF-oZTqzikyw4xv6d4nrCJsA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi:

Our IT dept has created what they call a High Availability DB for our PG DB
(9.6.7 on linux). If the primary fails, they promise to promote the backup
to be the new primary but leave it at that. But from the perspective of
the app, I'm left with....
1) detecting an SQL error is a DB connectivity problem.
2) Attempt to reconnect. If fails, try connecting to the backup server
(assume it's the new primary ?)
3) If that works, then test to see if it is indeed a primary (try a write
statement) ?
4) Somehow remember that the backup server is the one to connect to as the
primary for future connections.
5) When the original primary server is fixed and brought back on-line, and
they promote it to being the new primary, then I guess my write statements
will fail because I'm still connecting to the backup. So go figure that
out and reset a pointer of sorts to the new primary ?

Seems like a lot of work for an app and that it should be more seamless.
But maybe I'm wrong.

I was hoping for something like a server alias that the IT team toggles as
needed between the servers so that I always know what to connect to. The
best solution would be something completely seamless... my app isn't even
aware that the primary went down, the backup was promoted, and that I'm
actually running on a different server. Is something like that possible ?

Thanks for any replies !

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Paul Förster 2020-08-11 15:47:01 Re: How is PG replication typically used to create a High Availability (HA) config ?
Previous Message Adrian Klaver 2020-08-11 13:39:10 Re: Implement a new data type