Thread: database aliasing options ?
Hi:
version 11.5 on linux.
Our IT dept has configured our PG DB as a "High Availability" database. It has a primary and backup server (disks too). Normally both are running but if one goes down, the other is still available for use, effectively keeping the DB up while the failed server is being repaired.
My question has to do with creating and using a DB alias for DB connection code. Of course the idea would be to toggle which server the alias is pointing to based on the state of the servers. The idea is to manage which server is being used OUTSIDE of the DB connection code by changing the alias. The code always uses the alias.
Years ago, working for a different company, they had DB aliases, so I'm assuming this is still possible (please tell if if it's not !). If this is possible, where should I point our IT guys as far as instructions on how to enable this or, if there are multiple options, what are a couple of the simplest/best? Also, what options are there for the automated toggling of which server the alias is pointing to if/when the one it's pointing to comes down ?
Thanks for any replies !
David Gauthier <davegauthierpg@gmail.com> writes:
> Our IT dept has configured our PG DB as a "High Availability" database. It
> has a primary and backup server (disks too). Normally both are running but
> if one goes down, the other is still available for use, effectively keeping
> the DB up while the failed server is being repaired.
> My question has to do with creating and using a DB alias for DB connection
> code. Of course the idea would be to toggle which server the alias is
> pointing to based on the state of the servers. The idea is to manage which
> server is being used OUTSIDE of the DB connection code by changing the
> alias. The code always uses the alias.
Some people do this by changing the DNS entry for the server.
Another thought is that maybe you don't have to do anything dynamic,
if you set up the clients with host lists not just single hostnames
(that is, "-h host1,host2" or the equivalent). That should work for
any libpq-based client.
regards, tom lane
On 2020-11-09 10:49:52 -0500, Tom Lane wrote:
> David Gauthier <davegauthierpg@gmail.com> writes:
> > Our IT dept has configured our PG DB as a "High Availability" database. It
> > has a primary and backup server (disks too). Normally both are running but
> > if one goes down, the other is still available for use, effectively keeping
> > the DB up while the failed server is being repaired.
>
> > My question has to do with creating and using a DB alias for DB connection
> > code. Of course the idea would be to toggle which server the alias is
> > pointing to based on the state of the servers. The idea is to manage which
> > server is being used OUTSIDE of the DB connection code by changing the
> > alias. The code always uses the alias.
>
> Some people do this by changing the DNS entry for the server.
Another common method is to have a "floating" ip-address. That address
is always bound to the interface on the active node, so clients
can just connect to that address and don't need to care which node it
represents. Cluster resource managers like Pacemaker usually support
this. For Patroni there is a handy little tool called vip-manager.
Compared to DNS this has the advantage that latency is usually shorter.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"