Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] FATAL: remaining connection slots are reserved for non-replicationsuperuser connections

[GENERAL] FATAL: remaining connection slots are reserved for non-replicationsuperuser connections

From
Patrick B
Date:
Hi guys,

I get these messages at least once a day in my Prod environment: 
FATAL:  remaining connection slots are reserved for non-replication superuser connections

I do not have a DB pooler and my max_connections is 200. However, max connections for my PHP Application is 120.

My server has 128GB and SSD 10K iops disks (Amazon EBS).


Can you guys please outlines me the steps to troubleshoot this?

Interesting is that I didn't see any IO/CPU limitation on my server.

I'm currently running a Postgres 9.2 - one master and one slave streaming replication.


Thanks

Patrick

Re: [GENERAL] FATAL: remaining connection slots are reserved fornon-replication superuser connections

From
Steve Crawford
Date:
On Tue, Feb 7, 2017 at 6:52 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
Hi guys,

I get these messages at least once a day in my Prod environment: 
FATAL:  remaining connection slots are reserved for non-replication superuser connections

I do not have a DB pooler and my max_connections is 200. However, max connections for my PHP Application is 120.

My server has 128GB and SSD 10K iops disks (Amazon EBS).


Can you guys please outlines me the steps to troubleshoot this?

Interesting is that I didn't see any IO/CPU limitation on my server.

I'm currently running a Postgres 9.2 - one master and one slave streaming replication.


Thanks

Patrick


Something is using too many connections.

I may be wrong but I'm unaware of a limit on connections from PHP except when you are using persistent connections. Since each PHP script is it's own process, it can create one or more connections. I'd check to be sure that every PHP script you have is, indeed, using pg_pconnect and not pg_connect. That missing "p" could be hard to spot. I'm assuming, of course, that you are sure that your PHP script are the only things that can connect - no scripts, backups, etc. are consuming connections.

But generally I'd advise using pg_bouncer or a similar pooler which can deal with a mix of connections from persistent and non-persistent connections from one or multiple hosts.

Cheers,
Steve

Re: [GENERAL] FATAL: remaining connection slots are reserved fornon-replication superuser connections

From
Tatsuo Ishii
Date:
> Something is using too many connections.
>
> I may be wrong but I'm unaware of a limit on connections from PHP except
> when you are using persistent connections. Since each PHP script is it's
> own process, it can create one or more connections. I'd check to be sure
> that every PHP script you have is, indeed, using pg_pconnect and not
> pg_connect. That missing "p" could be hard to spot. I'm assuming, of
> course, that you are sure that your PHP script are the only things that can
> connect - no scripts, backups, etc. are consuming connections.

You can disable persistent connection feature of pg_pconnect by
tweaking php.ini.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Re: [GENERAL] FATAL: remaining connection slots are reserved fornon-replication superuser connections

From
Patrick B
Date:
2017-02-08 16:27 GMT+13:00 Tatsuo Ishii <ishii@sraoss.co.jp>:
> Something is using too many connections.
>
> I may be wrong but I'm unaware of a limit on connections from PHP except
> when you are using persistent connections. Since each PHP script is it's
> own process, it can create one or more connections. I'd check to be sure
> that every PHP script you have is, indeed, using pg_pconnect and not
> pg_connect. That missing "p" could be hard to spot. I'm assuming, of
> course, that you are sure that your PHP script are the only things that can
> connect - no scripts, backups, etc. are consuming connections.

You can disable persistent connection feature of pg_pconnect by
tweaking php.ini.


@Steven, yes, my developer said we are using persistent connections.

However, he checked and he is using pg_connect instead of pg_pconnect.

Patrick