4 ms·

How does pgbouncer make this process easy? Just because there are less connections to go to the final DB? I've also got a random question you might be able to a

• snuxoll 9y ago
  PgBouncer makes it easy to do a controlled failover without having to deal with floating IP's on your postgresql cluster.
• fake-name 9y ago
  I'd assume it's because you can just reconfigure to point pgbouncer to redirect everything to the secondary, rather then having to update all the applications using pgbouncer. It centralizes the configuration of which database is active.
  • scurvy 9y ago
    How much effort is required to reconfigure pgbouncer across hundreds of app containers vs moving a single IP? (not using pacemaker/corosync)
    • majidazimi 9y ago
      Zero. Update configuration in a central location. Push an update to configuration manager agent on each node (using a dead simple gossip protocol). The agent wakes up, pulls new configuration, writes it and reloads pgbouncer. Using an aggressive gossip protocol, the whole process shouldn't take more than 5 seconds on a large cluster, let alone couple of nodes.
      • scurvy 9y ago
        That's a fair amount of moving parts. What if one of the agents misses the update and keeps using the old server? What happens during that period of 5 seconds where the hosts are switching? It doesn't sound like an atomic, instant change process.
        • majidazimi 9y ago
          It's gossip. There is no precise gossip protocol. I've just mentioned one approach. It's not difficult to modify it such that each node gets one message multiple times, to ensure everyone will see the changes. Centrally configuration management systems like Zookeeper/etcd,... will also suffer from the same problem: message propagation takes time. Even with floating IP, a couple of requests may fail until routing tables converge. So there is no atomic configuration switch in either case anyway. The argument was if floating IP is easier than pure software approach to deploy, which is not the case. both of them are equally challenging. One of them requires experienced network engineers, the other one requires experienced distributed system designer. In case of failure the effort to switch from failed node is zero in both approaches.
• jeltz 9y ago
  One reason is that some applications do not properly support reconnecting to the database if the connection is lost. With pgbouncer that is not an issue. Another is to avoid having floating IPs or updating the DNS.
  • scurvy 9y ago
    There's nothing wrong with "floating" IP's as long as it's done properly.
• pjungwir 9y ago
  > are created functions replicated/updated to everything else as well? Yes. Note if those functions have an implementation compiled from C, you do need to install the .so on the standbys though.
  • morrbo 9y ago
    Thanks!
  • koffiezet 9y ago
    You need the .so's on the replica's simply for the database structure, but afaik, it's only the on-disk format that's being synchronized, functions are not executed on the replicas. I'm not even sure this would throw errors if the .so's would be missing - but it will give problems when a replica is promoted to the new master.
    • pjungwir 9y ago
      I agree: it's only because you need them when you fail over.