Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
We are considering a 3-node Postgres setup where 1-master and 2-standby with replication and master failover handled by repmgr and connection pooling with Pgbouncer.
Is there a way to let our application too automatically connect to the newly elected master? I dont see that happening with Pgbouncer or did I miss something? We always need our app to connect to the master db node.
What you might do is create an event notification script to be invoked upon the standby promotion, which would edit the pgbouncer databases list to point to the correct host, logging in to the pgbouncer server(s) remotely if necessary.
Alternatively, you might employ a floating IP address for the current primary and reassign it as appropriate, again using an event notification script.
In either case the application always connects to the same pgbouncer server(s), and the pooler forwards the connection to the proper primary.
