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

On 8/11/20 2:57 PM, David Gauthier wrote:
> Thanks for the response Paul :-)
> 
> Our code is actually perl which uses DBI which has functions to ping a 
> DB on a specific server and connect to it.
> But my question was more along the lines  of whether or not the onus to 
> do this sort of thing typically lies with the app or something outside 
> which is orchestrating the HA cfg.
> 
> On Tue, Aug 11, 2020 at 11:46 AM Paul Förster <paul.foerster@gmail.com 
> <mailto:paul.foerster@gmail.com>> wrote:
> 
>     Hi David,
> 
>      > On 11. Aug, 2020, at 17:12, David Gauthier
>     <davegauthierpg@gmail.com <mailto:davegauthierpg@gmail.com>> wrote:
>      >
>      > 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 !
> 
>     If you're a developer, you can take a look at:
>     https://www.postgresql.org/docs/current/libpq.html
> 
>     If your application uses libpq, then you can have it connect to the
>     primary database, no matter on what host the primary and the replica
>     currently is.
> 
>     Look at: https://jdbc.postgresql.org/documentation/head/connect.html
> 
>     You can especially do something like:
> 
>     jdbc:postgresql://host-a:5432,host-b:5433/postgres?targetServerType=master
> 
>     You can always download the current driver from:
>     https://jdbc.postgresql.org
> 
>     Does that help?
> 
>     Cheers,
>     Paul
> 
If it's not seamless to the app, it isn't HA.