Fix PostgreSQL Connection Timeout

What's Actually Happening

Applications cannot connect to PostgreSQL server within the expected timeout period. Connection attempts hang and eventually fail.

The Error You'll See

Connection timeout:

```bash $ psql -h db-server -U myuser -d mydb

psql: error: connection to server at "db-server" (10.0.0.1), port 5432 failed: Connection timed out ```

Application error:

java.sql.SQLException: Connection timed out
org.postgresql.util.PSQLException: Connection to localhost:5432 refused

Python error:

psycopg2.OperationalError: could not connect to server: Connection timed out

Why This Happens

1. 1.PostgreSQL not running - Server process stopped
2. 2.Firewall blocking - Port 5432 blocked
3. 3.Wrong host/port - Incorrect connection parameters
4. 4.Network latency - Slow network connection
5. 5.Max connections - Server reached connection limit
6. 6.DNS resolution - Hostname not resolving

Step 1: Check PostgreSQL Server Status

```bash # Check PostgreSQL service: systemctl status postgresql

# Or specific version: systemctl status postgresql@14-main

# Check process: ps aux | grep postgres

# Check listening port: ss -tlnp | grep 5432 netstat -tlnp | grep 5432

# Check multiple instances: pg_lsclusters

# Start if not running: systemctl start postgresql ```

Step 2: Test Local Connection

```bash # Test local connection: psql -U postgres -h localhost

# Test with socket: psql -U postgres

# Test with full parameters: psql -h 127.0.0.1 -p 5432 -U postgres -d postgres

# Check connection works: psql -U postgres -c "SELECT version();"

# Test with timeout: psql "host=localhost connect_timeout=5" ```

Step 3: Check Network Connectivity

```bash # Test port connectivity: nc -zv db-server 5432

# Test with telnet: telnet db-server 5432

# Test with timeout: timeout 5 bash -c 'cat < /dev/null > /dev/tcp/db-server/5432'

# Check routing: traceroute db-server

# Check DNS resolution: nslookup db-server dig db-server

# Ping test: ping -c 3 db-server ```

Step 4: Check PostgreSQL Configuration

```bash # Check listen_addresses: psql -U postgres -c "SHOW listen_addresses"

# For remote connections, should be '*' or specific IP: # In postgresql.conf: listen_addresses = '*'

# Or specific: listen_addresses = 'localhost, 10.0.0.1'

# Check port: psql -U postgres -c "SHOW port"

# View config file: cat /etc/postgresql/14/main/postgresql.conf | grep -E "^listen|^port"

# After changes, restart: systemctl restart postgresql ```

Step 5: Check pg_hba.conf for Remote Access

```bash # View pg_hba.conf: cat /etc/postgresql/14/main/pg_hba.conf

# Add entry for remote hosts: # TYPE DATABASE USER ADDRESS METHOD host all all 10.0.0.0/8 scram-sha-256 host all all 0.0.0.0/0 scram-sha-256

# For specific database: host mydb myuser 192.168.1.0/24 scram-sha-256

# Reload config: systemctl reload postgresql # Or: psql -U postgres -c "SELECT pg_reload_conf()" ```

Step 6: Check Firewall Rules

```bash # Check iptables: iptables -L -n | grep 5432

# Allow PostgreSQL: iptables -I INPUT -p tcp --dport 5432 -j ACCEPT

# Firewalld: firewall-cmd --add-port=5432/tcp --permanent firewall-cmd --reload

# UFW: ufw allow 5432/tcp

# Check cloud security groups: # AWS: Security group inbound rules # Azure: Network security group # GCP: Firewall rules

# Test from client: nc -zv db-server 5432 ```

Step 7: Check Connection Limits

```bash # Check max_connections: psql -U postgres -c "SHOW max_connections"

# Check current connections: psql -U postgres -c "SELECT count(*) FROM pg_stat_activity"

# Check connection details: psql -U postgres -c "SELECT pid, usename, application_name, client_addr, state FROM pg_stat_activity"

# Kill idle connections: psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND pid <> pg_backend_pid()"

# Increase max_connections: # In postgresql.conf: max_connections = 200

# Or use connection pooler like pgbouncer ```

Step 8: Check Connection Timeout Settings

```bash # Check statement_timeout: psql -U postgres -c "SHOW statement_timeout"

# Check lock_timeout: psql -U postgres -c "SHOW lock_timeout"

# Set connection timeout in connection string: psql "host=db-server connect_timeout=10"

# In application connection string: # postgresql://user:pass@host:5432/db?connect_timeout=10

# Set in postgresql.conf: statement_timeout = 30000 # 30 seconds lock_timeout = 10000 # 10 seconds

# Reload: psql -U postgres -c "SELECT pg_reload_conf()" ```

Step 9: Check PostgreSQL Logs

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

# Or: journalctl -u postgresql -f

# Look for connection errors: grep -i "connection" /var/log/postgresql/*.log grep -i "timeout" /var/log/postgresql/*.log

# Enable detailed logging: # In postgresql.conf: log_connections = on log_disconnections = on log_line_prefix = '%t [%p] %u@%d '

# Check log directory: ls -la /var/log/postgresql/ ```

Step 10: Verify and Monitor

```bash # Create verification script: cat << 'EOF' > /usr/local/bin/check-pg-connection.sh #!/bin/bash

echo "=== PostgreSQL Status ===" systemctl status postgresql | head -5

echo "" echo "=== Listening Ports ===" ss -tlnp | grep 5432

echo "" echo "=== Connection Test ===" psql -U postgres -c "SELECT version();" -t

echo "" echo "=== Connection Count ===" psql -U postgres -c "SELECT count(*) FROM pg_stat_activity" -t

echo "" echo "=== Max Connections ===" psql -U postgres -c "SHOW max_connections" -t

echo "" echo "=== Listen Addresses ===" psql -U postgres -c "SHOW listen_addresses" -t EOF

chmod +x /usr/local/bin/check-pg-connection.sh

# Run check: /usr/local/bin/check-pg-connection.sh

# Monitor connections: watch -n 1 'psql -U postgres -c "SELECT count(*) FROM pg_stat_activity"' ```

PostgreSQL Connection Timeout Checklist

Verify the Fix

```bash # After fixing connection timeout

# 1. Check service running systemctl status postgresql // Active: active (running)

# 2. Test local connection psql -U postgres -c "SELECT 1" // Returns 1

# 3. Test remote connection psql -h db-server -U postgres // Connected

# 4. Check listening ss -tlnp | grep 5432 // Port 5432 listening

# 5. Check firewall iptables -L | grep 5432 // ACCEPT rule exists

# 6. Monitor connections psql -U postgres -c "SELECT count(*) FROM pg_stat_activity" // Within limits ```

Related Issues

• [Fix PostgreSQL Authentication Failed](/articles/fix-postgresql-authentication-failed)
• [Fix PostgreSQL Permission Denied](/articles/fix-postgresql-permission-denied)
• [Fix PostgreSQL Database Not Found](/articles/fix-postgresql-database-not-found)