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(dot)foerster(at)gmail(dot)com
> <mailto:paul(dot)foerster(at)gmail(dot)com>> wrote:
>
> Hi David,
>
> > On 11. Aug, 2020, at 17:12, David Gauthier
> <davegauthierpg(at)gmail(dot)com <mailto:davegauthierpg(at)gmail(dot)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.