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

Harryalways317 · 2024-04-05T07:33:16Z

Harryalways317
Apr 5, 2024

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 · 2024-04-05T16:59:27Z

yosifkit
Apr 5, 2024
Maintainer

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).

0 replies

Harryalways317 · 2024-04-05T17:22:39Z

Harryalways317
Apr 5, 2024
Author

@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

0 replies

Siddh4194 · 2024-08-17T07:35:50Z

Siddh4194
Aug 17, 2024

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

0 replies

Harryalways317 · 2024-09-05T18:10:40Z

Harryalways317
Sep 5, 2024
Author

@Siddh4194 , tried bumping up postgres version?

0 replies

sohitkumar · 2024-09-13T05:15:02Z

sohitkumar
Sep 13, 2024

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

7440

1 reply

tianon Sep 13, 2024
Maintainer

#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).

salman0ansari · 2025-07-21T21:16:43Z

salman0ansari
Jul 21, 2025

what's causing this?

0 replies

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

Uh oh!

Uh oh!

Harryalways317 Apr 5, 2024

Replies: 6 comments · 1 reply

Uh oh!

Uh oh!

yosifkit Apr 5, 2024 Maintainer

Uh oh!

Uh oh!

Harryalways317 Apr 5, 2024 Author

Uh oh!

Siddh4194 Aug 17, 2024

Uh oh!

Harryalways317 Sep 5, 2024 Author

Uh oh!

sohitkumar Sep 13, 2024

Uh oh!

tianon Sep 13, 2024 Maintainer

Uh oh!

salman0ansari Jul 21, 2025

Harryalways317
Apr 5, 2024

Replies: 6 comments 1 reply

yosifkit
Apr 5, 2024
Maintainer

Harryalways317
Apr 5, 2024
Author

Siddh4194
Aug 17, 2024

Harryalways317
Sep 5, 2024
Author

sohitkumar
Sep 13, 2024

tianon Sep 13, 2024
Maintainer

salman0ansari
Jul 21, 2025