Link to home
Get AccessLog in
Avatar of coder
coderFlag for Australia

asked on

postgres connection error

Hi Experts,

I get the following error for python application.

django.db.utils.OperationalError: FATAL:  remaining connection slots are reserved for non-replication superuser connections

Open in new window


I use dj_database_url.py for configuring database.   I am using Postgres as a database

I had set connection_max_age to 0 in the config. dj_database_url.py

Still, I get this error.

I had checked the entire source code by using grep there is no connection in the source code.

I getting this error from inside docker container.  the database is outside the docker container.

the connection string I use to connect is DATABASE=postgres://test:*****@172.17.0.1/test where 172.17.0.1 is docker gateway.

max_connection =100 in postgresql.conf which located inside /etc/postgresql/9.5/main/

Any help is greatly appreciated.
Avatar of David Favor
David Favor
Flag of United States of America image

Common reasons for this.

1) Incorrect config. You can attempt increasing max connections settings. Unlikely this is problem.

2) Client connections are breaking without correct connection disconnect/teardown. Most likely the problem.

Get into your PostgreSQL logs + look at how connections begin, then end. Look for ugly patterns of connection errors.

Tip: Sigh... Can't believe this should be mentioned... And many times this is the problem...

Either old OS code or old PostgreSQL code is being used.

Start with an OS running a recent Kernel, like Ubuntu Bionic, then install latest stable PostgreSQL.
Avatar of coder

ASKER

Hi David,

inside the postgresql log file I have the following errors.

2019-02-25 06:40:02 UTC [26515-1] ts@ts FATAL:  remaining connection slots are reserved for non-replication superuser connections
2019-02-25 06:40:02 UTC [26598-1] trove@trove FATAL:  remaining connection slots are reserved for non-replication superuser connections
2019-02-25 06:40:03 UTC [26606-1] trove@trove FATAL:  remaining connection slots are reserved for non-replication superuser connections

there are more than 2 docker images and 6 to 7 docker containers from images use this database.

Thanks for you response
Avatar of coder

ASKER

What I have to do to make it work inside the docker.  It is working fine under normal conditions without dockers or containers
Avatar of coder

ASKER

Host machine is ubuntu inside aws.
Describe your setup in a bit more detail.

Specifically if all Docker code is connecting to one database instance or each Docker container has it's own database instance running inside the container.
Also, "superuser connections" might give another clue.

This suggests however connections are being made, maybe theres some ACL mismatch between login credentials + actual database.

Maybe posting your Docker config file will help pinpoint any pitfalls.
Avatar of coder

ASKER

Hi David,

       What you said may be correct.  you had provided me with an excellent hint 'superuser'.   There was one python file with root as owner.  I changed the owner to www-data inside the docker container and restarted the docker container.  Let me wait till e.o.d and see tomorrow whether the same error occurs once again.

Thanks for your help.
Avatar of coder

ASKER

Hi David,

   Still I am getting this error.
    It is a single common Postgres database on the Ubuntu(AWS) host machine for all the Docker containers.  Now all the containers fail with this same error.

django.db.utils.OperationalError: FATAL:  remaining connection slots are reserved for non-replication superuser connections

Open in new window


I had changed the owner from root to www-data for all the folders and files inside the container.  still, I get this error.

max_connection =100 in postgresql.conf

I am not able to guess what is causing the issue?  Do you have any ideas for me to try?  Please let me know.

Thanks
Avatar of coder

ASKER

Hi David,

Do you have any ideas for me to try?  Please let me know.
Avatar of coder

ASKER

Hi David,

There are error's like on the postgres logs.

2019-03-03 23:24:03 UTC [21175-1] postgres@ntdl LOG:  could not receive data from client: Connection reset by peer
2019-03-03 23:26:56 UTC [21401-1] postgres@ntdl LOG:  could not receive data from client: Connection reset by peer
2019-03-03 23:26:56 UTC [21509-1] [unknown]@[unknown] LOG:  incomplete startup packet
2019-03-03 23:26:56 UTC [4383-2] LOG:  received fast shutdown request
2019-03-03 23:26:56 UTC [4383-3] LOG:  aborting any active transactions
2019-03-03 23:26:56 UTC [4388-2] LOG:  autovacuum launcher shutting down
2019-03-03 23:26:56 UTC [4385-1] LOG:  shutting down
2019-03-03 23:26:56 UTC [4385-2] LOG:  database system is shut down
2019-03-03 23:27:42 UTC [1462-1] LOG:  database system was shut down at 2019-03-03 23:26:56 UTC
2019-03-03 23:27:42 UTC [1462-2] LOG:  MultiXact member wraparound protections are now enabled
2019-03-03 23:27:42 UTC [1426-1] LOG:  database system is ready to accept connections
2019-03-03 23:27:42 UTC [1481-1] LOG:  autovacuum launcher started
2019-03-03 23:27:43 UTC [1526-1] [unknown]@[unknown] LOG:  incomplete startup packet
2019-03-03 23:28:44 UTC [3719-1] postgres@ntdl LOG:  could not receive data from client: Connection reset by peer
2019-03-04 00:07:13 UTC [4571-1] postgres@ntdl LOG:  could not receive data from client: Connection reset by peer
2019-03-04 03:21:03 UTC [18307-1] ts@ts FATAL:  remaining connection slots are reserved for non-replication superuser connections
2019-03-04 03:21:03 UTC [18344-1] ts@ts FATAL:  remaining connection slots are reserved for non-replication superuser connections

Open in new window


is the error "2019-03-03 23:24:03 UTC [21175-1] postgres@ntdl LOG:  could not receive data from client: Connection reset by peer"

is the root cause for "2019-03-03 23:24:03 UTC [21175-1] remaining connection slots are reserved for non-replication superuser connections
error

Please get back to me
ASKER CERTIFIED SOLUTION
Avatar of coder
coder
Flag of Australia image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of coder

ASKER

Thanks David for providing me suggestions