Re: Repmgr + pgbouncer - Notification of master promotion to application level ...

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."