Re: Patch for reserved connections for replication users

On 15-10-2013 14:34, Josh Berkus wrote:
> On 10/15/2013 07:36 AM, Robert Haas wrote:
>> On Tue, Oct 15, 2013 at 10:34 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>>> Josh said we should treat replication connections in a separate "pool"
>>> from normal database connections, right? So you withdraw your earlier
>>> objection to that?
>>
>> I don't think that's what he said. Here's what I was referring to:
>
> To clarify: I do, indeed, support the idea of treating replication
> connections as a pool outside of max_connections. Here's why:
>
+1. I've already faced the same problem Josh described. Even if it is
documented (as in max_wal_senders), it is not easy to figure out why you
can't connect (check 2 parameters and query 2 views). Also, the 'check
connections' task is so complicated in a monitoring tool.

Let's separate the replication connections in another pool (that is not
related to user connections). We already have the infrastructure to
limit replication connections (max_wal_senders), let's use it.

> FATAL: connection limit exceeded for non-superusers
>
> SHOW max_connections;
>
> 100
>
> SELECT COUNT(*) FROM pg_stat_activity;
>
> 94
>
> SHOW superuser_reserved_connections;
>
> 3
>
> ????
>
> ... search around quite a bit, eventually figure out that you have
> three replication connections open. We've already set up an illogical
> and hard-to-troubleshoot situation where replication connections do not
> appear in pg_stat_activity, yet they are counted against max_connections.
>
Another situation is when you can't run pg_basebackup because automated
routines got all of the available connections and the replication user
is not a superuser (even if replication user is a superuser, if some app
run as superuser there won't be slots available).

--
Euler Taveira Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento