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@xxxxxxxxx
<mailto:paul.foerster@xxxxxxxxx>> wrote:
Hi David,
> On 11. Aug, 2020, at 17:12, David Gauthier
<davegauthierpg@xxxxxxxxx <mailto:davegauthierpg@xxxxxxxxx>> 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.
