We help IT Professionals succeed at work.

postgres connection error

coder
coder asked
on
286 Views
Last Modified: 2019-03-19
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

David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

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
coderdeveloper

Author

Commented:
Host machine is ubuntu inside aws.
David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

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
CERTIFIED EXPERT
Distinguished Expert 2019

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:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
coderdeveloper

Author

Commented:
Thanks David for providing me suggestions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.