Re: The uselessness of pgbouncer PAUSE

On Wed, Jan 15, 2014 at 4:08 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> I looked into this today, and it doesn't help at all. PAUSE will wait
> for all current queries to complete before switching over, which isn't
> tolerable in a failover situation. Unfortunately, there seems to be no
> command which says "kill running query connections but leave idle
> connections alone". Possible this is the idea behind SUSPEND, but if
> so, it doesn't work.

Even more, it does not make any sense in a failover situation, when
the master is down or unavailable. I was talking about a manual
switchover case when one can control what is going on on master.

And yes, we need to keep in mind long running transactions. I always
turn off all the cron jobs and other stuff that might cause them
before switching over, and always have this query

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE now() - xact_start > '3 seconds';

ready in case if something long appear after the pause. I think a
configuration parameter in HandyRep can be added to control how many
seconds it can wait for queries to complete after pausing PgBouncer.

Here is my notes for the PgBouncer based switchover process

https://github.com/grayhemp/pgcookbook/blob/master/switching_to_another_server_with_pgbouncer.md

Probably you will find there something else, that might be useful for you.

ps. And this is the correct version of byte lag measurement for <9.1 I
promised to send you

CREATE OR REPLACE FUNCTION hex_to_int(i_hex text, OUT o_dec integer)
RETURNS integer LANGUAGE 'plpgsql' IMMUTABLE STRICT AS $$
BEGIN
EXECUTE 'SELECT x''' || i_hex || '''::integer' INTO o_dec;
RETURN;
END $$;

SELECT
client_addr,
sent_offset - (
replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 ) AS byte_lag
FROM (
SELECT
client_addr,
hex_to_int(split_part(sent_location, '/', 1)) AS sent_xlog,
hex_to_int(split_part(replay_location, '/', 1)) AS replay_xlog,
hex_to_int(split_part(sent_location, '/', 2)) AS sent_offset,
hex_to_int(split_part(replay_location, '/', 2)) AS replay_offset
FROM pg_stat_replication
) AS s;

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com