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.
LVL 1
coderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
coderAuthor 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
coderAuthor 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 Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

coderAuthor Commented:
Host machine is ubuntu inside aws.
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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 FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
coderAuthor 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.
coderAuthor 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
coderAuthor Commented:
Hi David,

Do you have any ideas for me to try?  Please let me know.
coderAuthor 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
coderAuthor 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
coderAuthor Commented:
Thanks David for providing me suggestions
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.