4 ms·
This works wonderfully. If you have any long running queries, though, the PAUSE won't pause until they have finished. I love pgbouncer, it is such a great tool
by fullstop 5y ago
This works wonderfully. If you have any long running queries, though, the PAUSE won't pause until they have finished.
I love pgbouncer, it is such a great tool.
- fierro 5y agothis is interesting. So you have a weakest link kind of problem here.
- fullstop 5y agoNot really, new connections will block as it's pausing. But you won't be able to shut down Postgres until those long queries complete. Perhaps I was not super clear, but what I'm trying to say is that PAUSE is not instantaneous.
- fierro 5y agoyeah what I'm saying is that you can only pause as fast as your slowest currently initiated query. So if you have a diverse set of query patterns, you could be waiting for a really small percentage of small queries to wrap up.
- fullstop 5y agoI understand now. Yes, this is true and should be taken into consideration during upgrades. Most of our queries are fast, and the longer ones hit a read-only secondary instance. Cleanup jobs, though, can take hours to complete.
- nijave 5y agoYou can temporarily reduce query timeout to a smaller setting as part of the automated failover. The long running transactions will fail but you can minimize the window where you can't talk to postgres