Avatar of coder
coder
Flag 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.
PostgreSQLDatabasesDockerPython

Avatar of undefined
Last Comment
coder

8/22/2022 - Mon
David Favor

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.
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
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
coder

ASKER
Host machine is ubuntu inside aws.
David Favor

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 Favor

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
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
coder

ASKER
Hi David,

Do you have any ideas for me to try?  Please let me know.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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
coder

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
coder

ASKER
Thanks David for providing me suggestions