Still there is one area, where many of the documentation point it out as an excercise for the reader: when doing the switchover, so the former standby becomes the new master – how do we inform all database users?
One solution is to have PgBouncer or PgPool running on a third server (as „running on one of the database servers“ will take PgBouncer / PgPool down when that server goes down), and have clients connect to the database via pgbouncer / pgpool
The nice things:
- all the additional powers of those tools – i.e. pooling connections: read the linked websites for details and more powers
- no heckling with network infrastructure (IP, DNS)
- at least pgbouncer is also available on windows
The not-so-nice things:
- neading another server (that also should be kept redundand, giving an additional level to the switchover-challenge as in „switching over to the backup pgbouncer/pgpool)
- having another tool to configure and maintain
- authorization and encryption become more challenging through pgbouncer/pgpool being an involved third party
The second idea is to deal with the switchover on IP level: the new master can take over the IP-address of the old master
Nice about this: Clients can connect to the old IP-address
- make sure the old master is really taken of the network or switched to a third IP-address (so no quick testing with "pg_ctl stop" on the master)
- is the DBA allowed to change IP addresses (network infrastructure may be handled by a totally different department / company)
- how quick and reliable are the updates of the IP-MAC-caches in switches / routers, especially in WAN environments
- hassle with database-monitoring software which checks on both roles (and be it simply PgAdmin refreshing the "SELECT * FROM repl_status")
- are the used backup solutions provable happy with that kind of IP-changes?
Third option is doing the switchover using the dns-server: have a DNS-name-alias „actualmasterdb“ for the active server, have that alias point to the actual master server – hence the name :)
- is the DBA qualified to reconfigure the DNS-server ( DNS-server may be running on a different operating system / or be an appliance)
- how to script the reconfiguration of the DNS-Server
- is the DBA allowed to reconfigure the DNS-server (network infrastructure in other department / company)
- how to update all DNS-caches accross the network (especially on the clients, intensly looking at you, windows DNS name cache)
- can database monitoring cope with the changes?
- double check that backup knows whom it is backuping
The three presented solutions make you pay their – and especially those „network infrastructure in different deparment“ may be a prohibitive situation.
libpq to the rescue: since 2000-10-17, the option of a pg_service.conf file is within libpq.
To use it, use the following receipt:
- create a pg_service.conf file
# comment: ncc1701 is master at stardate 2011-03-06
- put that pg_service.conf on a network file share
- point the environment variable PGSYSCONFDIR to that directory (on windows setenv may come in handy), alternatively point PGSERVICEFILE to the pg_service.conf
- connect to your active database via a connection parameter of „service=myactivedatabase“ in any libpq application
- on switchover, simply change the pg_service.conf file so that „host“ points to the promoted standby. Have the clients build up a new connection (as the old connection will be broke anyway, since the old master is gone)
Challenges of this solution:
- not every libpq-application supports entering service=servicename for its connection parameters (that's a limit of those applcations connection handlers, not of libpq!); allthough, there is allways hope that expand_dbname is non-zero and you can enter „service=servicename“ for the database-name and libpq will parse it correctly
- solutions have to be found what to do if the file-share goes down (have a local copy of the pg_service.conf, how to update that ...) But in practice most enterprises come to a halt anyway when strategic .ppt-files - also on fileshares - are no longer available; so somebody else has allready taken care of the fileshare high availability.