On 15/06/2017 05:27, Andreas Kretschmer wrote:
Am 15.06.2017 um 01:18 schrieb Martin Goodson:
I'm just wondering how people may have implemented this. Do people
setup pgbouncer nodes on the database servers themselves, on
application servers, in the middle tier between the application and
database, and so forth, or some combination of the three? I can think
of some advantages or drawbacks for each. Or do people find that
repmgr works better with other tools to handle the promotion
notification outside the database cluster?
Basically I'm new to this, and I'm wondering how folks have handled
this issue. I'm basically looking for the community's wisdom :)
Usually we recommend to install pgbouncer on the app-servers.
If you have full control of the application you can try to integrate
the logic into the application (provide a list of servers, the new
pg10-version of libpg is working similar in this way:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=274bb2b3857cc987cfa21d14775cae9b0dababa5
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=721f7bd3cbccaf8c07cad2707826b83f84694832
)
Regards, Andreas
Hello.
Unfortunately, no control of the application layer.
Very interesting feedback so far. Thanks, everyone!
The issues I think I would have with pgbouncer at the application level
is ...
1) What if an application server is down when pgbouncer tries to update
where the database IP is pointing to? When it is brought back into
service could that create a split-brain scenario if it's still pointing
to the old master? Since the only STONITH here is 'I've told you to talk
to server X instead of server Y' what happens if somebody doesn't get
that message and the old master is still up and about (e.g. the failover
was caused by a transient error such as a power issue or network issue
that the 'old master' was able to quickly recover from and come back
online)? :)
2) We use a non-trivial number of application servers. Could that create
'failover lag' due to the amount of time it takes to notify all the
pgbouncers to stop, change IP, and restart?
3) Since we use a non-trivial number of application servers, the
administrative overhead of pushing all user password changes up to each
pgbouncer could be a bit of a pain (candidate for an automation tool
like ansible, perhaps?)
How do people deal with that?
Regards,
Martin.
--
Martin Goodson
"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
