Sadserver Day 5: Troubleshooting a PostgreSQL Connection Issue

Today’s server story is about a failing database connection. A web application was trying to connect to PostgreSQL 13 on one of our servers, but the connection simply wouldn’t work. Let’s walk through the problem, the investigation, and the fix.

The Problem Statement

The application depends on PostgreSQL 13. For some reason, it could not connect to the database running on the server. This was one of those "it should work but doesn’t" moments.

---

Investigating the Issue

The first step was checking the PostgreSQL service:

systemctl status postgresql

The output showed that the service was active, but here’s the catch:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2025-08-28 17:38:39 UTC; 2s ago
    Process: 911 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 911 (code=exited, status=0/SUCCESS)
        CPU: 1ms

That’s a subtle hint. The service unit itself reports as active, but it doesn’t mean the actual PostgreSQL instance is running properly.

Looking further in the logs:

2023-11-25 18:04:05.918 UTC [613] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-11-25 18:04:05.919 UTC [613] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-11-25 18:04:05.931 UTC [614] LOG:  database system was shut down at 2023-11-25 17:30:15 UTC
2023-11-25 18:04:05.944 UTC [613] LOG:  database system is ready to accept connections
2023-11-25 18:05:08.006 UTC [613] LOG:  received fast shutdown request
2023-11-25 18:05:08.009 UTC [613] LOG:  aborting any active transactions
2023-11-25 18:05:08.011 UTC [613] LOG:  background worker "logical replication launcher" (PID 620) exited with exit code 1
2023-11-25 18:05:08.012 UTC [615] LOG:  shutting down
2023-11-25 18:05:08.031 UTC [613] LOG:  database system is shut down
2023-11-25 18:05:08.201 UTC [1454] LOG:  starting PostgreSQL 13.13 (Debian 13.13-0+deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

So PostgreSQL was starting, shutting down, and coming back up.

At this point, configuration files became the next place to look. I changed directory to etc → postgresql and basically just followed the path and landed at this location:

/etc/postgresql/13/main/

Inside that directory were the main files that control PostgreSQL’s behavior:

• postgresql.conf – general configuration

• pg_hba.conf – client authentication

• pg_ident.conf – user mapping

The one that stood out was pg_hba.conf, since it directly controls which clients can connect and how they authenticate.

---

Digging into pg_hba.conf

The pg_hba.conf file defines rules for access: which IPs, users, and databases are allowed, and whether they should be accepted, rejected, or required to authenticate.

Sure enough, inside the file there were lines with the reject keyword. That meant PostgreSQL was explicitly refusing connections that matched those rules.

# Database administrative login by Unix domain socket
local   all             postgres                                peer
host    all             all             all                     reject
host    all             all             all                     reject

---

The Fix

To resolve the issue:

1. Opened the file:

    vi /etc/postgresql/13/main/pg_hba.conf
   

2. Commented out the lines that contained reject.

3. Saved the file.

4. Restarted PostgreSQL to apply the changes:

    systemctl restart postgresql
   

Once restarted, the application successfully connected to the database.

---

Key Takeaways

• Don’t trust “active” at first glance. PostgreSQL or systemd can both say “running,” but it doesn’t guarantee the database is actually accepting connections from your app.

• Always inspect the authentication rules. The pg_hba.conf file is a common source of connection issues and is the first place I now check when a database refuses connections.

• Sometimes the database isn’t down. It’s just saying “no” because of how the rules are written. A simple check of the authentication configuration can save hours of guessing.

---