How to Fix PostgreSQL Error: Connection Refused

The "Connection refused" error in PostgreSQL occurs when a client application cannot establish a connection to the PostgreSQL server. This error indicates that the connection attempt reached the target host but was actively rejected, typically because PostgreSQL is not listening on the specified address or port.

Impact

This error prevents clients from accessing the database, effectively making your PostgreSQL server unavailable to applications. It can disrupt critical business operations, prevent data access, and halt application functionality. In production environments, this can lead to service outages and application failures.

Common Causes

1. PostgreSQL service is not running
2. PostgreSQL is listening on localhost only (not accepting remote connections)
3. Incorrect port configuration
4. Firewall blocking the connection
5. Incorrect host address in connection string
6. PostgreSQL is bound to the wrong network interface

Troubleshooting and Resolution Steps

1. Verify PostgreSQL service is running:

   sudo systemctl status postgresql
   

   If not running, start the service:

   sudo systemctl start postgresql
   

2. Check PostgreSQL is listening on the correct interface: Edit postgresql.conf:

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

   Ensure listen_addresses is set correctly:

   listen_addresses = '*'          # Listen on all interfaces
   # OR
   listen_addresses = '0.0.0.0'    # Listen on all IPv4 interfaces
   # OR
   listen_addresses = 'localhost,192.168.1.10'  # Specific interfaces
   

3. Verify the port configuration: In postgresql.conf, check:

   port = 5432
   

4. Check that pg_hba.conf allows connections from your client:

   sudo nano /etc/postgresql/15/main/pg_hba.conf
   

   Add appropriate entries:

   # Allow connections from specific IP
   host    all    all    192.168.1.0/24    scram-sha-256
   
   # Allow connections from all IPs (use with caution)
   host    all    all    0.0.0.0/0         scram-sha-256
   

5. Check firewall settings: For UFW:

   sudo ufw allow 5432/tcp
   sudo ufw status
   

   For firewalld:

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

6. Verify PostgreSQL is listening on the expected port:

   sudo netstat -tlnp | grep postgres
   # OR
   sudo ss -tlnp | grep postgres
   

7. Test connectivity from the client machine:

   telnet your-server-ip 5432
   # OR
   nc -zv your-server-ip 5432
   

8. Restart PostgreSQL after configuration changes:

   sudo systemctl restart postgresql
   

9. Check PostgreSQL logs for additional details:

   sudo tail -f /var/log/postgresql/postgresql-15-main.log
   

Additional Information

• Always use SSL/TLS for remote connections in production environments
• Consider using connection pooling (PgBouncer, pgpool-II) for better resource management
• Implement proper authentication methods (SCRAM-SHA-256 is recommended)
• Use VPN or SSH tunneling for secure remote database access
• Monitor connection attempts and failed connections regularly

Frequently Asked Questions

Q: How can I check if PostgreSQL is running?
A: Use sudo systemctl status postgresql on systemd-based systems, or ps aux | grep postgres to check if the process is running.

Q: What is the default PostgreSQL port?
A: PostgreSQL uses port 5432 by default for client connections.

Q: Can I change the PostgreSQL listening port?
A: Yes, modify the port parameter in postgresql.conf, then restart PostgreSQL. Remember to update firewall rules and client connection strings.

Q: Why does PostgreSQL refuse connections from remote hosts?
A: By default, PostgreSQL listens only on localhost. You must configure listen_addresses in postgresql.conf and add appropriate entries in pg_hba.conf to allow remote connections.

Q: How do I allow connections from a specific IP address?
A: Add an entry in pg_hba.conf like: host all all 192.168.1.100/32 scram-sha-256, then reload PostgreSQL configuration with SELECT pg_reload_conf();

Q: Should I set listen_addresses to '*' in production?
A: While convenient, it's more secure to specify exact IP addresses or interfaces. Use pg_hba.conf to control which clients can connect, and always use SSL/TLS encryption.