Python - psycopg2 giving error after execution

Question

I am getting this error when executing my code in Python.

Here is my Python - DataBaseHelper.py:

import psycopg2
#class
class DataBaseHelper:
    database = "testdata";user = "test";password = "pass123"; host = "mtest.75tyey.us-east-1.rds.amazonaws.com"

    #create and return the connection of database
    def getConection(self):
        self.conn = psycopg2.connect(database=self.database, user = self.user, password = self.password, host = self.host, port = "5432")
        return self.conn

Then I am importing this file and using in another python file - MyScript.py:

import sys
import uuid
from DBHelper import DataBaseHelper
from ExecutionLogHelper import ExecutionLogHelper
from GotUtility import GotUtility


class MyScript:
   def __init__(self,):
        self.con = DataBaseHelper().getConection()
        self.logHelper = ExecutionLogHelper()
        self.uuid = self.logHelper.Get_TEST_Run_Id()

When I run my code concurrently, it gives me this error:

psycopg2.errors.AdminShutdown: terminating connection due to administrator command
SSL connection has been closed unexpectedly

I am not able to understand why am I getting this error. When I run the Python program again, it works. And I checked the Postgres server is in running, no restart, no signal to shutdown. This keeps on happening every few hours for me.

I have a Ubuntu server. I login, create multiple screens and then run my code concurrently. — dang, Apr 17 '20 at 09:19
I noticed you're using an AWS RDS endpoint; how did you check for restart/ signal to shutdown events? — Ionut Ticus, Apr 22 '20 at 20:10
@snakecharmerb the connections are open for over 24 hours sometimes. — dang, Apr 26 '20 at 05:26
Did you check this answer? https://stackoverflow.com/a/57455876/1412564 — Uri, Apr 26 '20 at 05:32
It's not impossible that a firewall kills connections after they have been idle for a certain time; there's also a postgres `idle_in_transaction_session_timeout` which might be set. In general I wouldn't keep connections open indefinitely. Consider using a [connection pool](https://www.psycopg.org/docs/pool.html?highlight=pool#psycopg2-pool-connections-pooling) to manage their lifecycles. — snakecharmerb, Apr 26 '20 at 07:36

Rajat Singh · Answer 1 · 2020-07-06T19:20:02.823

This is happening because psycopg2 is try to connect to AWS Postgresql over SSL and failing to do so.

Try connecting with sslmode = disable

def getConection(self):
    self.conn = psycopg2.connect(database=self.database,
                                 user = self.user,
                                 password = self.password,
                                 host = self.host,
                                 port = "5432",
                                 sslmode="disable")
    return self.conn

Method 1 will not work if your AWS Postgresql is configured to force a ssl connection ie. parameter rds.force_ssl = 1. If you enable set rds.force_ssl all non-SSL connections are refused. In that case try connecting using something like this:

$ psql -h testpg.cdhmuqifdpib.us-east-1.rds.amazonaws.com -p 5432 "dbname=testpg user=testuser sslrootcert=rds-ca-2015-root.pem sslmode=verify-full"

For more on how to connect to AWS RDS over ssl using various drivers : AWS RDS SSL.

score -1 · Answer 2 · edited Nov 02 '22 at 13:13

After a little digging, I found a few answers. According to this link:

This error message comes from intervention by a program external to Postgres: http://www.postgresql.org/message-id/4564.1284559661@sss.pgh.pa.us

To elaborate, this link says:

If user stop postgresql server with "service postgresql stop" or if any SIGINT has been called on the postgresql PID then this error will occur.

Solution: Since your code is running concurrently, multiple transactions at the same time, at the same row could be causing this error. So you've got to make sure that that doesn't happen... Look here for more details:

When you update rows in a transaction, these rows are locked until the transaction is committed.

If you are unable to do that, I suggest you enable query logging and look to see if something odd is in it.

@dang -- This might help: https://stackoverflow.com/questions/28950212/how-do-i-use-psycopg2s-loggingconnection — xilpex, Apr 20 '20 at 17:37

Python - psycopg2 giving error after execution

2 Answers2