Postgresql : Connection refused. Check that the hostname and port are correct and that the postma...

Learn postgresql : connection refused. check that the hostname and port are correct and that the postmaster is accepting tcp/ip connections with practical examples, diagrams, and best practices. Co...
Tags:
Categories:

Troubleshooting 'Connection refused' in PostgreSQL

Illustration of a broken network cable connecting to a database server, symbolizing connection refused error.

This article guides you through diagnosing and resolving the common 'Connection refused' error when connecting to a PostgreSQL database, covering network, configuration, and service status issues.

The 'Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections' error is one of the most frequent issues encountered when trying to connect to a PostgreSQL database. It indicates that your client application could not establish a connection with the PostgreSQL server. This usually points to a problem with network accessibility, server configuration, or the PostgreSQL service itself not running or not listening on the expected interface/port.

Understanding the Error Message

The error message provides crucial clues:

  • 'Connection refused': This is a low-level network error. It means the operating system on the client machine tried to establish a TCP connection to the specified IP address and port, but the server machine actively rejected it. This is different from a 'Connection timed out' error, which would imply no response at all.
  • 'Check that the hostname and port are correct': This suggests verifying the connection parameters used by your client application.
  • 'postmaster is accepting TCP/IP connections': This refers to the main PostgreSQL server process (often called postgres or postmaster). It implies checking if the server is running and configured to listen for network connections.
flowchart TD
    A[Client Application] -->|Attempt Connection| B{PostgreSQL Server Host:Port}
    B --> C{Is PostgreSQL Service Running?}
    C -- No --> D[Error: Service Not Running]
    C -- Yes --> E{Is PostgreSQL Listening on Host:Port?}
    E -- No --> F[Error: Configuration Issue (e.g., listen_addresses, port)]
    E -- Yes --> G{Is Firewall Blocking Connection?}
    G -- Yes --> H[Error: Firewall Blocked]
    G -- No --> I[Connection Successful!]

Troubleshooting flow for PostgreSQL 'Connection refused' error

Common Causes and Solutions

Let's break down the most common reasons for this error and how to resolve them.

1. PostgreSQL Service Not Running

This is the most straightforward cause. If the PostgreSQL server process isn't running, it can't accept connections.

# Check service status (Linux/systemd)
sudo systemctl status postgresql

# Start service (Linux/systemd)
sudo systemctl start postgresql

# Check service status (macOS/Homebrew)
brew services list
brew services start postgresql

Commands to check and start PostgreSQL service

2. Incorrect Hostname or Port

Your client application might be trying to connect to the wrong address or port.

Verify Client Connection String

Double-check the hostname (or IP address) and port specified in your application's connection string or configuration. The default PostgreSQL port is 5432.

Verify Server Listening Port

On the PostgreSQL server, check the postgresql.conf file for the port setting. Ensure it matches what your client is using.

# postgresql.conf
port = 5432

Example port setting in postgresql.conf

3. PostgreSQL Not Listening on Correct Interface (listen_addresses)

By default, PostgreSQL often listens only on localhost (127.0.0.1) for security reasons. If your client is on a different machine, the server needs to be configured to listen on its network interface.

Edit the postgresql.conf file and modify the listen_addresses parameter:

  • listen_addresses = 'localhost' (default, only accepts connections from the same machine)
  • listen_addresses = '*' (accepts connections from all available network interfaces)
  • listen_addresses = '192.168.1.100' (accepts connections only on a specific IP address)

After changing listen_addresses, you must restart the PostgreSQL service for the changes to take effect.

# postgresql.conf
listen_addresses = '*'

Configuring PostgreSQL to listen on all interfaces

4. Firewall Blocking the Connection

A firewall (either on the client or server machine) can prevent connections to the PostgreSQL port.

Server-side Firewall

Ensure that the PostgreSQL port (default 5432) is open on the server's firewall. Examples for common Linux firewalls:

  • UFW (Ubuntu/Debian): sudo ufw allow 5432/tcp sudo ufw enable (if not already enabled)

  • firewalld (CentOS/RHEL): sudo firewall-cmd --add-port=5432/tcp --permanent sudo firewall-cmd --reload

Client-side Firewall

Less common, but ensure your client machine's firewall isn't blocking outgoing connections to the PostgreSQL server's IP and port.

5. Incorrect pg_hba.conf Configuration

Even if the server is listening and the firewall is open, PostgreSQL uses pg_hba.conf (Host-Based Authentication) to control which hosts can connect and with what authentication method. If your client's IP address isn't permitted, the connection will be refused after the initial TCP handshake, but it can sometimes manifest similarly to a 'connection refused' at the application level.

Locate your pg_hba.conf file (often in the same directory as postgresql.conf). Add an entry that allows connections from your client's IP address. For example, to allow connections from any IP address using MD5 password authentication for all databases and users:

host all all 0.0.0.0/0 md5

Warning: 0.0.0.0/0 allows connections from any IP address and should only be used in development or highly secured environments. For production, specify exact IP addresses or subnets (e.g., 192.168.1.0/24).

After modifying pg_hba.conf, you need to reload PostgreSQL's configuration (a full restart is not always required, but often safer):

# Reload PostgreSQL configuration (Linux/systemd)
sudo systemctl reload postgresql

# Or restart (more robust)
sudo systemctl restart postgresql

Reloading or restarting PostgreSQL after pg_hba.conf changes

6. Network Connectivity Issues

Basic network problems can also cause this error. Use ping and telnet (or nc) to test connectivity.

# Test basic network reachability
ping <server_ip_address>

# Test if the port is open and listening (from client machine)
telnet <server_ip_address> 5432
# or using netcat (nc)
nc -vz <server_ip_address> 5432

Using ping and telnet/nc to test network connectivity and port status

If ping fails, there's a fundamental network issue. If ping succeeds but telnet or nc to port 5432 fails, it points to a firewall or the PostgreSQL server not listening on that port.

Conclusion

The 'Connection refused' error in PostgreSQL can be frustrating, but by systematically checking the server status, configuration files (postgresql.conf, pg_hba.conf), and network connectivity (including firewalls), you can pinpoint and resolve the issue. Always remember to restart or reload the PostgreSQL service after making configuration changes.