What's Actually Happening

When you get a PostgreSQL connection refused error, the client cannot connect to the PostgreSQL server. This typically means PostgreSQL isn't running, isn't listening on the expected port, or isn't configured to accept connections from your client.

The Error You'll See

```bash $ psql -U postgres psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory

# Or for TCP connection: psql: error: connection to server at "127.0.0.1", port 5432 failed: Connection refused ```

Why This Happens

  1. 1.PostgreSQL not running - Server stopped or crashed
  2. 2.Wrong port - PostgreSQL on different port
  3. 3.Wrong host - Not listening on expected interface
  4. 4.pg_hba.conf blocking - Client authentication rejected
  5. 5.listen_addresses - Not configured for remote connections
  6. 6.Firewall - Port blocked
  7. 7.Socket missing - Unix socket file deleted

Step 1: Check PostgreSQL Service

bash
# Check if PostgreSQL is running
systemctl status postgresql
# Or:
systemctl status postgresql@14-main

If not running:

bash
systemctl start postgresql
systemctl enable postgresql

Step 2: Check PostgreSQL Process

bash
ps aux | grep postgres

Should show multiple processes:

bash
postgres  1234  0.0  0.5  12345  6789 ?  Ss   10:00   0:00 /usr/lib/postgresql/14/bin/postgres
postgres  1235  0.0  0.1  12345  1234 ?  Ss   10:00   0:00 postgres: checkpointer
postgres  1236  0.0  0.1  12345  1234 ?  Ss   10:00   0:00 postgres: background writer

Step 3: Check Listening Port

bash
# Check PostgreSQL port
ss -tlnp | grep postgres
netstat -tlnp | grep 5432

Output:

bash
tcp  LISTEN  0  128  127.0.0.1:5432  0.0.0.0:*  users:(("postgres",pid=1234,fd=3))

Step 4: Check listen_addresses

```bash # Show current setting sudo -u postgres psql -c "SHOW listen_addresses;"

# Check config file grep "^listen_addresses" /etc/postgresql/14/main/postgresql.conf ```

Default is usually:

bash
listen_addresses = 'localhost'

For remote connections:

```bash sudo vim /etc/postgresql/14/main/postgresql.conf

# Change to: listen_addresses = '*' # Or specific IPs: listen_addresses = 'localhost, 192.168.1.100' ```

Restart PostgreSQL:

bash
systemctl restart postgresql

Step 5: Check Port Configuration

bash
grep "^port" /etc/postgresql/14/main/postgresql.conf

Shows:

bash
port = 5432

Connect to specific port:

bash
psql -U postgres -p 5433

Step 6: Check pg_hba.conf

Client authentication is controlled by pg_hba.conf:

bash
cat /etc/postgresql/14/main/pg_hba.conf

Look for entries allowing your connection:

bash
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Add entry for remote connections:

bash
host    all             all             192.168.1.0/24          md5

Reload after changes:

bash
systemctl reload postgresql
# Or:
sudo -u postgres psql -c "SELECT pg_reload_conf();"

Step 7: Test Local Connection

```bash # Connect via Unix socket sudo -u postgres psql

# Connect via TCP psql -h 127.0.0.1 -U postgres ```

Step 8: Check Unix Socket

bash
ls -la /var/run/postgresql/

Should show socket file:

bash
-rw-rw---- 1 postgres postgres 5 Apr 3 10:00 14-main.pid
srwxrwxrwx 1 postgres postgres 0 Apr 3 10:00 .s.PGSQL.5432

If missing, PostgreSQL isn't running properly.

Step 9: Check Firewall

```bash # Check if port blocked sudo iptables -L -n | grep 5432

# Allow PostgreSQL port sudo ufw allow 5432/tcp # Or: sudo firewall-cmd --add-port=5432/tcp --permanent sudo firewall-cmd --reload ```

Step 10: Check PostgreSQL Logs

bash
# Check logs
tail -f /var/log/postgresql/postgresql-14-main.log

Look for errors:

bash
FATAL:  no pg_hba.conf entry for host "192.168.1.100", user "postgres", database "postgres"
FATAL:  password authentication failed for user "postgres"

Verify the Fix

```bash # Test connection psql -U postgres -c "SELECT version();"

# Test remote connection psql -h postgres-server-ip -U postgres -c "SELECT 1;"

# Test from application # Should connect without errors ```

Prevention Tips

```bash # Enable PostgreSQL to start on boot systemctl enable postgresql

# Monitor PostgreSQL health sudo -u postgres pg_isready

# Set up alerts # Prometheus: pg_up == 0 ```