How to fix Connection failure in PostgreSQL

Check if the PostgreSQL service is active and running.

On Linux/macOS:

# Check service status
sudo systemctl status postgresql

# If not running, start it
sudo systemctl start postgresql

On macOS with Homebrew:

brew services list
brew services start postgresql

On Windows:

# Check in Services application or use:
net start postgresql-x64-14

If the service won't start, check the PostgreSQL logs for startup errors. The log location is typically /var/log/postgresql/ on Linux or in the PostgreSQL data directory.

Verify the server is listening on the port you're connecting to.

# Check what port PostgreSQL is listening on
sudo netstat -tlnp | grep postgres

# Or with ss (newer systems)
sudo ss -tlnp | grep postgres

The output should show PostgreSQL listening on port 5432 (default) or your custom port. If nothing appears, PostgreSQL is not listening on TCP/IP.

If using a non-standard port, ensure your connection string includes it:

psql -h localhost -p 5433 -U username -d database

PostgreSQL must be configured to accept TCP/IP connections.

1. Find and edit the postgresql.conf file:

# Find the file location
sudo -u postgres psql -c "SHOW config_file"

# Edit it (commonly at /etc/postgresql/14/main/postgresql.conf)
sudo nano /etc/postgresql/14/main/postgresql.conf

2. Find the listen_addresses setting. It should be:

listen_addresses = '*'  # Listen on all interfaces

If it's set to localhost only, remote connections will fail. The * value allows connections from any network interface.

3. Save and restart PostgreSQL:

sudo systemctl restart postgresql

The pg_hba.conf file controls which hosts can connect. Verify your client IP is allowed.

1. Find and edit the file:

# Commonly at:
sudo nano /etc/postgresql/14/main/pg_hba.conf

2. Look for a line matching your connection type. For TCP/IP connections from localhost:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

3. For remote connections, you need an entry like:

# Allow from specific IP
host    all             all             192.168.1.100/32        md5

# Or allow from entire subnet
host    all             all             192.168.1.0/24          md5

4. Save and reload PostgreSQL:

sudo systemctl reload postgresql

Note: Lines are processed top-to-bottom, so place more specific entries before general ones.

Before troubleshooting PostgreSQL internals, test basic network connectivity:

# Test with telnet (if installed)
telnet localhost 5432

# Or with netcat
nc -zv localhost 5432

# For remote server
nc -zv 192.168.1.50 5432

If you get 'Connection refused' or timeout, the server isn't listening. If it connects, PostgreSQL is reachable but the client may have wrong credentials or pg_hba.conf issues.

Check firewall if connecting remotely:

# On Linux with ufw
sudo ufw allow 5432/tcp

# Or with iptables
sudo iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

Ensure your application is using correct connection parameters.

Common formats:

postgresql://username:password@localhost:5432/dbname
host=localhost port=5432 user=postgres password=secret dbname=mydb

Verify each parameter:
- host: Should be 'localhost' for local Unix socket, or IP/hostname for TCP
- port: Default is 5432; verify it matches your postgresql.conf
- user: Must be a valid PostgreSQL role
- password: Must match the user's password
- dbname: Database must exist

Test with psql directly:

psql -h localhost -p 5432 -U postgres -d postgres

If this works, your connection parameters are correct. If not, you've found the issue in one of the above steps.

If PostgreSQL was running but now rejects all connections, the max_connections limit may be reached.

# Connect as superuser (if you can)
sudo -u postgres psql

# View current setting
SHOW max_connections;

# View active connections
SELECT count(*) FROM pg_stat_activity;

# Terminate idle connections
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid <> pg_backend_pid() AND state = 'idle';

To increase the limit:
1. Edit postgresql.conf and change max_connections
2. Restart PostgreSQL: sudo systemctl restart postgresql

Note: Each connection uses system resources. Setting it too high can cause performance issues.