2

I'm testing the pgbouncer "pause" feature to minimize database downtime when a database restart is required.

For this test, I have a remote psql session connected to my database through pgbouncer. Pgbouncer SHOW CLIENTS shows my remote session as Active even with no queries running. The same client shows Idle according to pg_stat_activity. When I issue the PAUSE command, it hangs until I close my remote session. Shouldn't PAUSE work without closing my connection?

Improve this question

1 Answer 1

Reset to default
4

At least with pool_mode=session, it looks like a 'PAUSE' command will succeed only if the other client(s) have not yet issued any queries in their sessions. If the other client(s) have already issued any queries, the 'PAUSE;' command hangs as you describe.

This basically makes sense from PgBouncer's point of view, as it doesn't need to give a real connection to a client before the client issues any queries, but once it does a 'PAUSE' must wait for that client to finish its session.

Improve this answer
2
  • 1
    Just tested and the behavior is exactly as your described. As long as I did not issue a query after connecting, I was able to pause without issue. Should I expect most client connections to be short lived, so the PAUSE feature can be used when executing a quick restart? Guessing the answer depends on the application.
    – Mike A
    Aug 5, 2014 at 1:08
  • 1
    Ideally, client connections are short-lived, though any large job (e.g. a pg_dump) will be a reasonable exception. We use a cron job to find and kill backends which have been hanging out '<IDLE> in transaction' for an extended period (and then ideally track down the offending application code and fix the problem there), and perhaps there will be a supported way to do this from inside Postgres in the near future, see this proposed patch: postgresql.org/message-id/[email protected] .
    – Josh Kupershmidt
    Aug 5, 2014 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.