How to fix Connection exception in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUM

Connection exceptions in PostgreSQL occur when the client fails to establish or maintain a TCP/IP connection to the database server. This can happen due to the server not running, network issues, firewall blocking, or incorrect connection parameters.

What this error means

A "Connection exception" in PostgreSQL is represented by SQLSTATE class 08 errors and indicates that the client cannot connect to the PostgreSQL server. This is distinct from authentication errors (SQLSTATE 28) - the connection itself is failing before credentials are even evaluated. Connection exceptions can occur at different stages: - **Connection initiation**: The TCP connection attempt is refused, times out, or encounters network errors - **Connection drop**: An established connection suddenly closes during communication - **DNS resolution**: Hostname cannot be resolved to an IP address - **Port issues**: The specified port is not listening or is blocked The most common SQLSTATE 08 codes you'll encounter are: - **08001**: sqlclient_unable_to_establish_sqlconnection - Cannot establish connection - **08003**: connection_does_not_exist - The connection was closed or never established - **08006**: connection_failure - Connection lost during command execution

How to fix "Connection exception"

1Verify PostgreSQL server is running

First, confirm the PostgreSQL daemon is actually running on the target system.

On Linux:

bash

sudo systemctl status postgresql
# or for specific versions like postgresql-15
sudo systemctl status postgresql-15

On macOS with Homebrew:

bash

brew services list | grep postgres

On Windows (PowerShell as Administrator):

powershell

Get-Service postgresql*

On Docker:

bash

docker ps | grep postgres

If PostgreSQL is not running, start it:

bash

# Linux
sudo systemctl start postgresql

# macOS
brew services start postgresql

# Windows - Open Services and start PostgreSQL service
# Docker
docker run --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=password postgres

2Check if PostgreSQL is listening on the correct port

PostgreSQL defaults to port 5432, but may be configured differently. Verify it's listening:

On Linux/macOS:

bash

# Check if port 5432 is listening
netstat -tlnp | grep postgres
# or
ss -tlnp | grep postgres
# or
lsof -i :5432

On Windows (PowerShell as Administrator):

powershell

netstat -ano | findstr :5432

Docker:

bash

docker port <container_name>

If no process is listening on 5432, either:
1. PostgreSQL is not running (refer to Step 1)
2. PostgreSQL is listening on a different port

To find the actual port, check the PostgreSQL configuration:

bash

# Linux - check postgresql.conf
grep "^port = " /etc/postgresql/*/main/postgresql.conf

# Docker - check startup logs
docker logs <container_name> | grep "listening on"

3Verify the hostname and IP address

Ensure your connection string uses the correct hostname and that it resolves properly.

Check DNS resolution:

bash

# Test hostname resolution
nslookup postgres.example.com
dig postgres.example.com
getent hosts postgres.example.com

# or use ping (works on most systems)
ping postgres.example.com
ping localhost
ping 127.0.0.1

Common connection string formats:

bash

# Standard format
postgresql://username:password@hostname:5432/database_name

# Examples:
postgresql://postgres:password@localhost:5432/mydb
postgresql://postgres:[email protected]:5432/mydb
postgresql://postgres:[email protected]:5432/mydb

Test local vs remote:
- Use localhost or 127.0.0.1 for connections from the same machine
- Use the actual hostname or IP for remote connections
- Avoid using localhost for remote connections - it won't resolve correctly

4Configure PostgreSQL to listen on all interfaces

By default, PostgreSQL only listens on localhost. For remote connections, configure it to listen on all interfaces.

Edit postgresql.conf:

bash

# Find postgresql.conf location
sudo -u postgres psql -c "SHOW config_file"

# Edit the file (example for Linux)
sudo nano /etc/postgresql/15/main/postgresql.conf

Find and modify the listen_addresses setting:

ini

# Default (localhost only):
listen_addresses = 'localhost'

# Change to listen on all interfaces:
listen_addresses = '*'

# Or listen on specific IP addresses:
listen_addresses = 'localhost,192.168.1.100'

Restart PostgreSQL for changes to take effect:

bash

sudo systemctl restart postgresql

# or on macOS
brew services restart postgresql

Verify the change:

bash

ss -tlnp | grep postgres
# Should show something like:
# LISTEN  0  244  0.0.0.0:5432  0.0.0.0:*  ...

5Check pg_hba.conf for authentication rules

The pg_hba.conf file controls which clients can connect. Incorrect rules will cause connection exceptions.

Locate pg_hba.conf:

bash

# Query the location
sudo -u postgres psql -c "SHOW hba_file"

# Usually at:
# Linux: /etc/postgresql/*/main/pg_hba.conf
# macOS: /usr/local/var/postgres/pg_hba.conf
# Windows: C:\Program Files\PostgreSQL\15\data\pg_hba.conf

Example pg_hba.conf rules:

bash

# TYPE  DATABASE  USER  ADDRESS      METHOD
local   all       all               trust
host    all       all   127.0.0.1/32 trust
host    all       all   ::1/128      trust
host    all       all   0.0.0.0/0    md5

Key settings:
- local: Unix domain socket connections
- host: TCP/IP connections
- ADDRESS can be hostname, CIDR notation (192.168.0.0/16), or 'all'
- METHOD: trust (no password), md5/password (with password), scram-sha-256, ident, etc.

To allow remote connections:

bash

host  all  all  0.0.0.0/0  scram-sha-256

Reload the configuration after editing:

bash

sudo systemctl reload postgresql

# or send SIGHUP signal
sudo -u postgres pg_ctl reload -D /var/lib/postgresql/data

6Check firewall rules

Network firewalls can block PostgreSQL connections. Verify port 5432 is accessible.

On Linux with UFW:

bash

# Check current rules
sudo ufw status

# Allow PostgreSQL port (if needed)
sudo ufw allow 5432/tcp

# Allow from specific IP only (more secure)
sudo ufw allow from 192.168.1.100 to any port 5432

On Linux with firewalld:

bash

# Check allowed ports
sudo firewall-cmd --list-ports

# Allow PostgreSQL port
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

On Windows (PowerShell as Administrator):

powershell

# Check existing rules
Get-NetFirewallRule | Where-Object {$_.DisplayName -like "*PostgreSQL*"}

# Create new rule
New-NetFirewallRule -DisplayName "PostgreSQL" `
  -Direction Inbound -LocalPort 5432 -Protocol TCP -Action Allow

Cloud firewalls (AWS/Azure/GCP):
- Check security groups, network ACLs, or firewall rules
- Ensure inbound traffic on port 5432 is allowed from your client IP
- Consider using security group source restrictions instead of opening to 0.0.0.0/0

7Test the connection with psql

Use the psql command-line tool to test connectivity and get detailed error messages.

Basic connection test:

bash

# Connect to localhost (default)
psql -U postgres

# Connect to specific host and port
psql -h localhost -p 5432 -U postgres -d postgres

# Connect to remote server
psql -h db.example.com -p 5432 -U postgres -d mydb

When prompted for password, enter the database password.

Verbose connection output:

bash

# Show detailed connection diagnostics
psql -h localhost -p 5432 -U postgres -d postgres -v ON_ERROR_STOP=1

# Even more verbose (trace libpq messages)
PGSSLMODE=require psql -h localhost -p 5432 -U postgres

Connection string format alternative:

bash

# Using connection string
psql "postgresql://postgres@localhost:5432/postgres"

# With password in URL (not recommended for security)
psql "postgresql://postgres:password@localhost:5432/postgres"

Common error messages and meanings:
- could not connect to server: Connection refused - Server not running or wrong port
- could not translate host name "hostname" to address - DNS resolution failed
- is the server running on host - Server not listening on that address
- FATAL: no pg_hba.conf entry for host - Authentication rules don't allow connection

8Check connection limits

PostgreSQL has a limit on concurrent connections. If exceeded, new connections will fail.

Check current connection limit:

bash

# Connect with superuser
psql -U postgres

# Query the max_connections parameter
SHOW max_connections;

# Check current connection count
SELECT count(*) FROM pg_stat_activity;

Increase connection limit (if needed):

bash

# Edit postgresql.conf
sudo nano /etc/postgresql/15/main/postgresql.conf

Find and modify:

ini

# Default is usually 100
max_connections = 100

# Increase to allow more connections
max_connections = 200

Apply the change:

bash

sudo systemctl restart postgresql

Identify idle or long-running connections:

sql

-- Connect as superuser
psql -U postgres

-- View active connections
SELECT pid, usename, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle';

-- Kill long-running queries (be careful!)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE query_start < now() - interval '1 hour';

9Verify TCP/IP is enabled

Ensure TCP/IP connections are not disabled in PostgreSQL configuration.

Check the setting:

bash

# Connect to PostgreSQL
psql -U postgres

# Query the listen_addresses setting
SHOW listen_addresses;

The output should be something like:
- localhost - Only local connections
- * - All interfaces (recommended for servers)
- 192.168.1.100 - Specific IP address

If listen_addresses is empty or unset, TCP/IP may be disabled.

Also check for SSL/TLS requirements:

sql

-- Check SSL mode
SHOW ssl;

-- Check SSL certificate paths
SHOW ssl_cert_file;
SHOW ssl_key_file;

If SSL is required but your client doesn't support it, use the sslmode parameter:

bash

# Force non-SSL connection
psql "postgresql://localhost/postgres?sslmode=disable"

# Or set via environment
export PGSSLMODE=disable
psql

10Troubleshoot Docker PostgreSQL connections

If running PostgreSQL in Docker, special considerations apply.

Example docker-compose.yml:

yaml

version: '3.8'
services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_DB: mydb
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:

Start the container:

bash

docker-compose up -d

# Wait a few seconds for PostgreSQL to initialize
sleep 5

# Test connection from your machine
psql -h localhost -U postgres -d mydb

Connect from another Docker container:

bash

# Use the service name (postgres) as hostname, not localhost
psql -h postgres -U postgres -d mydb

Common Docker issues:
- Port not mapped: Add "5432:5432" to ports in docker-compose
- Container not started: Run docker-compose up -d or check docker logs postgres
- Wrong hostname: Use postgres (service name) inside Docker network, localhost from host machine
- Not enough time to initialize: Add healthcheck or wait script before connecting

Debugging Docker PostgreSQL:

bash

# Check container logs
docker-compose logs postgres

# Execute psql inside container
docker exec -it <container_name> psql -U postgres

# Check port mapping
docker port <container_name>

How to fix Connection exception in PostgreSQL

What this error means

Typical symptoms

Common causes

How to fix "Connection exception"

Advanced notes

Related errors

Official resources & further reading