PostgreSQL Connection Refused Error: Comprehensive Analysis and Solutions

Error Nature Analysis

When encountering org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections error, this typically indicates connection establishment failure rather than authentication issues. This error is unrelated to pg_hba.conf file configuration since it occurs during connection attempt phase, before reaching authentication steps.

Primary Investigation Directions

Based on the error message, focus on checking the following aspects:

PostgreSQL Service Status

First verify if PostgreSQL service is running. Use the following command:

ps -f -u postgres

This command should list running PostgreSQL processes. If no output appears, the service is not started and needs to be initiated using systemctl start postgresql or equivalent command.

TCP/IP Connection Listening Configuration

Check if PostgreSQL is configured to accept TCP/IP connections. In the postgresql.conf file:

# Check listen_addresses configuration
listen_addresses = 'localhost'  # Listen only to local connections
# or
listen_addresses = '*'          # Listen to all network interfaces

If listen_addresses is set to localhost or specific IP addresses, ensure the target address in connection requests matches accordingly.

IPv4 and IPv6 Compatibility

Some operating system versions may have IPv4 and IPv6 connection compatibility issues:

• If PostgreSQL only listens on IPv4 (127.0.0.1) while clients attempt connections via IPv6 (::1), connection will fail
• Conversely, ensure listening configuration matches connection method

Port Configuration Check

Confirm that the port PostgreSQL actually listens on matches the port used in connection requests:

# Use lsof to check listening ports
sudo lsof -n -u postgres | grep LISTEN

# Or use netstat
sudo netstat -ltnp | grep postgres

The default port is 5432, but it might be modified to other ports. Ensure the port number in connection string matches the actual listening port.

Firewall and Network Policies

Although local loopback connections are typically not restricted by firewalls, exceptions might exist in certain configurations:

• Check if iptables rules block loopback connections
• For remote connections, inspect network firewall policies
• In containerized environments, ensure inter-container network connectivity

Diagnostic Process Example

Below is a complete diagnostic workflow:

# 1. Check service status
ps -f -u postgres

# 2. Check listening configuration
sudo lsof -n -u postgres | grep LISTEN
# Expected output example:
# postgres 1234 postgres 5u IPv4 0x12345678 0t0 TCP localhost:5432 (LISTEN)

# 3. Test local connection
psql -h localhost -p 5432 -U username -d database_name

# 4. If failed, check configuration files
cat /etc/postgresql/*/main/postgresql.conf | grep listen_addresses
cat /etc/postgresql/*/main/postgresql.conf | grep port

Container Environment Special Considerations

In Docker environments, connection issues might involve:

• Port mapping configuration: Ensure -p 5432:5432 is correctly set
• Container network: Only containers in the same network can communicate
• Connection address: Use container names instead of localhost within containers

Configuration Optimization Recommendations

To avoid connection problems, consider the following configuration optimizations:

# postgresql.conf key configurations
listen_addresses = 'localhost'      # Production environment recommends specific IPs
port = 5432                        # Explicitly specify port
max_connections = 100              # Adjust according to requirements

Conclusion

PostgreSQL connection refused errors typically stem from basic connection configuration issues. Through systematic troubleshooting processes—from service status, listening configuration, network compatibility to port settings—the root cause can be quickly identified. In containerized deployment scenarios, special attention should be paid to network configuration and port mapping correctness. Mastering these diagnostic methods significantly improves problem resolution efficiency.