Postgres ERROR: SSL SYSCALL error: Connection timed out

Question

I wanted to develop an app with Python that would run on a Raspberry and be remote controlled. This app should control (switch on and off) 2 leds based on the current day, time and the user's indication. The app is to be executed when the Raspberry is booted (auto start) and should be run endlessly. For this purpose, a service is created under /usr/lib/systemd/system. The postgres (Heroku) databases were used for this purpose.

I have read several articles and used several suggestions, but unfortunately without much success. The problem with my app is that it crashes every 24 hours with the following error:

Light_system Error 1: SSL SYSCALL error: Connection timed out

Light_system Error 1: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

The raspberry is connected to the internet through a sim module (SIM7600E -H 4G HAT). But this is not supposed to be the problem, because I also connect the raspberry to the direct router, but get the same error again.

I am very grateful for any suggestions!

I have already tried the following steps:

• keepalive_kwargs = { "keepalives": 1, "keepalives_idle": 30, "keepalives_interval": 5, "keepalives_count": 5, }

• conn = psycopg2.connect(conn_string, **keepalive_kwargs)

• engine = sqlalchemy.create_engine(DATABASE_URL, pool_pre_ping=True)

Here is my code:

crud.py

    keepalive_kwargs = {
    "keepalives": 1,
    "keepalives_idle": 30,
    "keepalives_interval": 5,
    "keepalives_count": 5,
}

conn_string = "host, dbname, user, password"
conn = psycopg2.connect(conn_string, **keepalive_kwargs)

class CRUDSettings:
  

    @staticmethod   
    def read_from_db():
        try:
            cur = conn.cursor()
            cur.execute("SELECT * FROM time_settings_tabel")
            data = cur.fetchall()
            return data
        except Exception as e:
            with open("read_from_db.log", "a") as f:
                f.write(f"ERROR : {str(e)}")


        @staticmethod   
        def read_from_db_LED():
            try:
                cur = conn.cursor()
                cur.execute("SELECT * FROM LED_settings_tabel")
                data = cur.fetchall()
                return data
            except Exception as e:
                with open("read_from_db_1.log", "a") as f:
                    f.write(f"ERROR : {str(e)}")

settings = CRUDSettings()

database.py

DATABASE_URL = "URL for DB"
database = databases.Database(DATABASE_URL)
engine = sqlalchemy.create_engine(DATABASE_URL, pool_pre_ping=True)

SessionLocal = sessionmaker(autocommit=False, autoflush = False, bind = engine)

Base = declarative_base()

metadata = sqlalchemy.MetaData()

Base.metadata.create_all(bind=engine)

def get_db():
    Base.metadata.create_all(bind=engine)
    database = engine.connect()
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

app.py

  class Watersystem:
    async def run(self, app):
        try:
            while(app.state.isRunning):
            # Es werden die Datenbanken aktualisiert
                for row in crud.settings.read_from_db():
                    now = datetime.now()
                    current_time  = convert(now.hour, now.minute,now.second)
                    current_day= now.strftime("%A")
                    start_split = row[2].split(":")
                    end_split = row[3].split(":")
                    start = convert(int(start_split[0]), int(start_split[1]), int(start_split[2]))
                    end = convert(int(end_split[0]), int(end_split[1]), int(end_split[2]))
                    current_id = row[0]

                    await RestartButton_einlesen()
                    GPIO.output(18,GPIO.LOW)
                    GPIO.output(20,GPIO.LOW)
                    GPIO.output(21,GPIO.LOW)                                
                    while ((current_day == row[1].replace(" ","")) and (current_time  >= start) and (current_time  <= end) and (current_id == row[0])): 
                        print("START")
                        GPIO.output(18,GPIO.HIGH)
                        await RestartButton_einlesen()
                        for vent in crud.settings.read_from_db_LED():
                            if (vent[1] is True):
                                GPIO.output(20,GPIO.HIGH)                                                                            
                            else:
                                GPIO.output(20,GPIO.LOW)
                                                                                                                    
                            if(vent[2] is True):                                      
                                GPIO.output(21,GPIO.HIGH)                                              
                            else:                                       
                                GPIO.output(21,GPIO.LOW)
                                
                        now = datetime.now()
                        current_time  = convert(now.hour, now.minute,now.second)

                        if (current_day == row[1].replace(" ","") and current_time  >= end):
                            print("END")
                            GPIO.output(18,GPIO.LOW)
                            GPIO.output(20,GPIO.LOW)
                            GPIO.output(21,GPIO.LOW)
                            break
        
        except Exception as err:
            logger.error('FunctionName: %s',  err)
            await asyncio.sleep(5)
            os.system("sudo reboot")



watersystem = Watersystem()


async def RestartButton_einlesen():
    for rest in crud.settings.read_from_db_Restart():
        if(rest[0] is True):
            GPIO.output(18,GPIO.LOW)
            GPIO.output(20,GPIO.LOW)
            GPIO.output(21,GPIO.LOW)
            await asyncio.sleep(1)
            os.system("sudo reboot")

postgresql-13-main.log file:

LOG: starting PostgreSQL 13.9 (Raspbian 13.9-0+deb11u1) on arm-unknown-linux-gnueabihf, compiled by gcc (Raspbian 10.2.1-6+rpi1) 10.2.1 20210110, 32-bit

LOG: listening on IPv4 address "0.0.0.0", port 5432

LOG: listening on IPv6 address "::", port 5432

LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2023-02-21 15:45:22.409 CET [623] LOG: database system was interrupted; last known up at 2023-02-20 21:28:14 CET

LOG: database system was not properly shut down; automatic recovery in progress

LOG: redo starts at 0/15D1718

LOG: invalid record length at 0/15D1750: wanted 24, got
 
LOG: redo done at 0/15D1718

LOG: database system is ready to accept connections