Do all superuser processes count toward superuser_reserved_connections?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Do all superuser processes count toward superuser_reserved_connections?

jfinzel
I don't find a clear mention in the docs of superuser processes that are exempt from counting toward superuser_reserved_connections.  So I would think that it's possible that postgres autovac workers ought to count toward that.  Am I wrong about that?  I actually have the same question about pglogical replication background workers and manager, which also run as postgres.

Do I need to increase my limit to account for these workers, or are some or all of these processes ignored by superuser_reserved_connections?

Many thanks!
Jeremy
Reply | Threaded
Open this post in threaded view
|

Re: Do all superuser processes count toward superuser_reserved_connections?

Adrian Klaver-4
On 3/14/19 8:23 AM, Jeremy Finzel wrote:
> I don't find a clear mention in the docs of superuser processes that are
> exempt from counting toward superuser_reserved_connections.  So I would
> think that it's possible that postgres autovac workers ought to count
> toward that.  Am I wrong about that?  I actually have the same question

AFAICK autovacuum workers do not use the connections referred to above.
The details can be found here:

https://doxygen.postgresql.org/autovacuum_8c.html

> about pglogical replication background workers and manager, which also
> run as postgres.

But the actual connection can be by a different user:

https://www.postgresql.org/docs/11/logical-replication-security.html

>
> Do I need to increase my limit to account for these workers, or are some
> or all of these processes ignored by superuser_reserved_connections?

I am still learning about logical replication, but as I understand it
the process uses replication slots:

https://www.postgresql.org/docs/11/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT

So configuring for that involves:

https://www.postgresql.org/docs/11/logical-replication-config.html

 From the looks of it superuser_reserved_connections is not impacted.

>
> Many thanks!
> Jeremy


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Do all superuser processes count toward superuser_reserved_connections?

jfinzel
On Fri, Mar 15, 2019 at 9:48 AM Adrian Klaver <[hidden email]> wrote:
On 3/14/19 8:23 AM, Jeremy Finzel wrote:
> I don't find a clear mention in the docs of superuser processes that are
> exempt from counting toward superuser_reserved_connections.  So I would
> think that it's possible that postgres autovac workers ought to count
> toward that.  Am I wrong about that?  I actually have the same question

AFAICK autovacuum workers do not use the connections referred to above.
The details can be found here:

https://doxygen.postgresql.org/autovacuum_8c.html

Not sure I can really grok that and how it answers the question.  Are you saying if you have max_connections set to 10, you could theoretically have 20 autovac processes still?
 
> about pglogical replication background workers and manager, which also
> run as postgres.

But the actual connection can be by a different user:

https://www.postgresql.org/docs/11/logical-replication-security.html


But I am speaking of pglogical, which does require superuser, last I checked :).[hidden email]

It does use replication slots, but there are processes corresponding to each subscription.  I have some databases with dozens of them.

Thanks,
Jeremy
Reply | Threaded
Open this post in threaded view
|

Re: Do all superuser processes count toward superuser_reserved_connections?

Adrian Klaver-4
On 3/15/19 8:16 AM, Jeremy Finzel wrote:

> On Fri, Mar 15, 2019 at 9:48 AM Adrian Klaver <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     On 3/14/19 8:23 AM, Jeremy Finzel wrote:
>      > I don't find a clear mention in the docs of superuser processes
>     that are
>      > exempt from counting toward superuser_reserved_connections.  So I
>     would
>      > think that it's possible that postgres autovac workers ought to
>     count
>      > toward that.  Am I wrong about that?  I actually have the same
>     question
>
>     AFAICK autovacuum workers do not use the connections referred to above.
>     The details can be found here:
>
>     https://doxygen.postgresql.org/autovacuum_8c.html
>
>
> Not sure I can really grok that and how it answers the question.  Are
> you saying if you have max_connections set to 10, you could
> theoretically have 20 autovac processes still?

Yes:

https://www.postgresql.org/docs/11/routine-vacuuming.html#AUTOVACUUM

"There is no limit on how many workers might be in a single database,
but workers do try to avoid repeating work that has already been done by
other workers. Note that the number of running workers does not count
towards max_connections or superuser_reserved_connections limits."


>
>      > about pglogical replication background workers and manager, which
>     also
>      > run as postgres.
>
>     But the actual connection can be by a different user:
>
>     https://www.postgresql.org/docs/11/logical-replication-security.html
>
>
> But I am speaking of pglogical, which does require superuser, last I
> checked :).<mailto:[hidden email]>

Aah, my mistake I conflated pglogical with the built in logical
replication as the latter came from the former. Still:

https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/

2.1 Quick Setup

First the PostgreSQL server has to be properly configured to support
logical decoding:

wal_level = 'logical'
max_worker_processes = 10   # one per database needed on provider node
                             # one per node needed on subscriber node
max_replication_slots = 10  # one per node needed on provider node
max_wal_senders = 10        # one per node needed on provider node
shared_preload_libraries = 'pglogical'



>
> It does use replication slots, but there are processes corresponding to
> each subscription.  I have some databases with dozens of them.

A process does not necessarily equal a connection.

>
> Thanks,
> Jeremy


--
Adrian Klaver
[hidden email]