Re: The uselessness of pgbouncer PAUSE

Sergey,

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.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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

On 01/16/2014 01:29 PM, Sergey Konoplev wrote:
> 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.

That's far more complicated than just restarting pgbouncer, and doesn't
solve the problem I was trying to solve. Maybe I'll add something
later, but right now it's too limited to do anything with.

It would be nice to use PAUSE for reloading the configuration after, for
example, adding a new replica to the pool. However, that would take a
lot of experimentation to see if it even works.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 01/16/2014 01:29 PM, Sergey Konoplev wrote:
> Here is my notes for the PgBouncer based switchover process
>
> https://github.com/grayhemp/pgcookbook/blob/master/switching_to_another_server_with_pgbouncer.md

BTW, the Debian/Ubuntu PostgreSQL packages don't support these commands:

/etc/init.d/pgbouncer pause
/etc/init.d/pgbouncer reload

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Thu, Jan 16, 2014 at 1:55 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 01/16/2014 01:29 PM, Sergey Konoplev wrote:
> That's far more complicated than just restarting pgbouncer, and doesn't
> solve the problem I was trying to solve. Maybe I'll add something
> later, but right now it's too limited to do anything with.

Sure.

> It would be nice to use PAUSE for reloading the configuration after, for
> example, adding a new replica to the pool. However, that would take a
> lot of experimentation to see if it even works.

You just need to RELOAD if you added/changed something in the
[database] section of config. It will handle everything itself AFAIK.

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

On Thu, Jan 16, 2014 at 2:02 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 01/16/2014 01:29 PM, Sergey Konoplev wrote:
>> Here is my notes for the PgBouncer based switchover process
>>
>> https://github.com/grayhemp/pgcookbook/blob/master/switching_to_another_server_with_pgbouncer.md
>
> BTW, the Debian/Ubuntu PostgreSQL packages don't support these commands:
>
> /etc/init.d/pgbouncer pause
> /etc/init.d/pgbouncer reload

Yes, in this case you can PAUSE-RELOAD ... RESUME via console.

I probably need to add a note about it to the document. Thanks for the hint.

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

>> It would be nice to use PAUSE for reloading the configuration after, for
>> example, adding a new replica to the pool. However, that would take a
>> lot of experimentation to see if it even works.
>
> You just need to RELOAD if you added/changed something in the
> [database] section of config. It will handle everything itself AFAIK.

The real issue here is that PAUSE can hang indefinitely. What happens
if I do RELOAD without doing PAUSE?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Thu, Jan 16, 2014 at 2:22 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>> It would be nice to use PAUSE for reloading the configuration after, for
>>> example, adding a new replica to the pool. However, that would take a
>>> lot of experimentation to see if it even works.
>>
>> You just need to RELOAD if you added/changed something in the
>> [database] section of config. It will handle everything itself AFAIK.
>
> The real issue here is that PAUSE can hang indefinitely. What happens
> if I do RELOAD without doing PAUSE?

I meant just RELOAD without PAUSE in case of adding a new replica. It
will simply start working with new entries you added to the [database]
section and change the way it worked with the existing ones that were
changed.

ps. Returning to the PAUSE - it can be prevented from hanging
indefinitely, but, yes, as you noted, it might require a lot of
additional logic and experimenting.

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