Postgres Auto Dropping the DB Randomly - database "template0" is not currently accepting connections

[Harryalways317]

Hi, I'm facing this error from multiple days, it happens on postgres inside docker.

ERROR: database "template0" is not currently accepting connections
FATAL: database "template0" can only be updated by autovacuum worker processes

It happens mostly after a hour,

the below is the service inside docker compose

  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres
    ports:
      - "5432:5432"
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
      - database_data:/var/lib/postgresql/data

init.sql

-- Create the EC2 Fleet Instance Management table
CREATE TABLE IF NOT EXISTS ec2_fleet_instance_management (
    fleet_id VARCHAR PRIMARY KEY,
    instance_id VARCHAR,
    state VARCHAR,
    requested_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    fleet_group VARCHAR,
    instance_detail JSONB,
    instance_status VARCHAR,
    system_status VARCHAR,
    llm_status VARCHAR,
    public_ip_address VARCHAR
);

-- Create the Request History table
CREATE TABLE IF NOT EXISTS request_history (
    request_id VARCHAR PRIMARY KEY,
    instance_handled VARCHAR,
    request_details JSONB,
    response_details JSONB,
    requested_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    request_type VARCHAR,
    instance_detail JSONB,
    status_code VARCHAR,
    public_ip_address VARCHAR
);

-- Indexes for the EC2 Fleet Instance Management table for quicker search
CREATE INDEX IF NOT EXISTS idx_ec2_fleet_instance_management_on_fleet_group ON ec2_fleet_instance_management (fleet_group);
CREATE INDEX IF NOT EXISTS idx_ec2_fleet_instance_management_on_state ON ec2_fleet_instance_management (state);

-- Indexes for the Request History table for quicker search
CREATE INDEX IF NOT EXISTS idx_request_history_on_request_type ON request_history (request_type);
CREATE INDEX IF NOT EXISTS idx_request_history_on_requested_at ON request_history (requested_at);

the connection im making

def get_db_connection():
    conn = psycopg2.connect(
        dbname="postgres",
        user="postgres",
        password="*****",
        host="db",
        port="5432"
    )
    return conn

Any where i'm doing it wrong?

[yosifkit]

Unless you require access from another machine, then remove the ports: config from your docker-compose.yml. If the database host is accessible on the internet, then anyone can attempt to connect. Which is what I would guess is happening; there isn't anything in the container that would auto drop tables or databases. If you do need other hosts to access it, then choose an extremely strong password and setup something like AWS security groups to limit network access (local solutions like firewalld and iptables will not work since docker works with/around those to make the port exposed when you use ports).

[Harryalways317]

@yosifkit yes i understood that, but the issue is the ports is for the local docker container to access, and the instance is under vpc, so no outer people can access it,

the issue is, after some time, the error i mentioned (database "template0" is not currently accepting connections), is coming up and then the dropping is happening, to confirm this, i tried multiple other times with same config, same error is happening,

but other pg under same docker where i access tables with prisma, its not happening, so i was guessing there is issue in my config or the way i'm accessing

[Siddh4194]

Im having the same problem on the postgres
"development": {
"username": "postgres",
"password": "********",
"database": "psm_dev",
"host": "127.0.0.1",
"dialect": "postgres",
"logging": false
}

This is my userName and credentials to connect with the database

this errors I got

[Harryalways317]

@Siddh4194 , tried bumping up postgres version?

[sohitkumar]

hi @Harryalways317 , have you find out the solution for this ?

[tianon]

#1222 (comment)

If the database host is accessible on the internet, then anyone can attempt to connect. Which is what I would guess is happening; there isn't anything in the container that would auto drop tables or databases. If you do need other hosts to access it, then choose an extremely strong password and setup something like AWS security groups to limit network access (local solutions like firewalld and iptables will not work since docker works with/around those to make the port exposed when you use ports).