postgres connection error

coder
coder used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
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.
coderdeveloper

Author

Commented:
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
coderdeveloper

Author

Commented:
What I have to do to make it work inside the docker.  It is working fine under normal conditions without dockers or containers
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

coderdeveloper

Author

Commented:
Host machine is ubuntu inside aws.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
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.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
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.
coderdeveloper

Author

Commented:
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.
coderdeveloper

Author

Commented:
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
coderdeveloper

Author

Commented:
Hi David,

Do you have any ideas for me to try?  Please let me know.
coderdeveloper

Author

Commented:
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
developer
Commented:
Hi David,

        I had opened botocore library several times for logging purpose and this made the application fail and return.  And the docker, started another instance as it fails every time, making several instances of applications and database connections.    this caused Postgres database connection error.  I am able to solve with the help of my colleague.  Thanks for your help and suggestions.
coderdeveloper

Author

Commented:
Thanks David for providing me suggestions

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial