Resolving PostgreSQL Connection Refused on Port 5432 & Monitoring Replication Lag

Understanding the Error: Connection Refused on Port 5432

When managing a database infrastructure, encountering a 'connection refused' error on PostgreSQL port 5432 is a rite of passage. Whether you are setting up postgresql replication lag monitoring, trying to connect a BI tool, or deploying a Java application, this connectivity blocker is incredibly common.

The error manifests in various ways depending on your client:

• Command Line (psql): psql: error: connection to server at "192.168.1.100", port 5432 failed: Connection refused
• DBeaver: dbeaver connection refused postgresql or dbeaver postgresql connection refused
• Java Applications (Spring Boot/Hibernate): org postgresql util psqlexception connection to localhost 5432 refused
• Node.js/Python: postgresql econnrefused
• Monitoring Tools: failed to retrieve postgresql server_version_num connection refused

At its core, a 'connection refused' message means the client successfully reached the server's IP address, but the server actively rejected the connection attempt on that specific port. This is distinctly different from a timeout, which usually indicates the packets are being dropped by a network appliance or firewall before even reaching the operating system.

Root Causes of PostgreSQL Connection Refused

Before diving into the fixes, it is crucial to understand why PostgreSQL rejects connections:

1. The Service is Down: The most obvious but often overlooked reason. If the PostgreSQL daemon is not running, nothing is listening on port 5432.
2. Localhost Binding: By default, PostgreSQL is configured for maximum security. It only listens on localhost (127.0.0.1). If you attempt a postgresql remote connection refused will be the guaranteed result.
3. Client Authentication Rejection: Even if PostgreSQL is listening on the network interface, the pg_hba.conf (Host-Based Authentication) file acts as an internal firewall, dictating which users can connect from which IP addresses.
4. OS Firewall Blocking: Windows Defender, ufw on Ubuntu, or firewalld on RHEL might be blocking port 5432.

Step 1: Verify the PostgreSQL Service Status

Before touching any configuration files, ensure the database engine is actually running. If you are getting org postgresql util psqlexception connection to localhost 5432 refused, the service might have crashed.

On Linux (systemd):

sudo systemctl status postgresql

If it's inactive, start it:

sudo systemctl start postgresql

On Windows (PowerShell), if you are troubleshooting postgresql connection refused port 5432 windows:

Get-Service -Name postgresql*

If the status is not 'Running', start it via the Services.msc snap-in or using Start-Service.

Step 2: Configure PostgreSQL to Accept TCP/IP Connections

To make postgresql accept tcp ip connections on port 5432 from remote machines, you must edit the main configuration file, postgresql.conf.

1. Locate your postgresql.conf file. The location varies by OS and version:

   • Ubuntu/Debian: /etc/postgresql/14/main/postgresql.conf
   • CentOS/RHEL: /var/lib/pgsql/14/data/postgresql.conf
   • Windows: C:\Program Files\PostgreSQL\14\data\postgresql.conf

2. Open the file in a text editor (requires admin/root privileges) and search for the listen_addresses parameter.

3. By default, it looks like this: #listen_addresses = 'localhost'

4. Change it to listen on all interfaces (or specify a comma-separated list of specific IP addresses): listen_addresses = '*'

5. Ensure the port is correctly set: port = 5432

Save the file. Note: Changes to listen_addresses require a full service restart, not just a configuration reload.

Step 3: Update Client Authentication (pg_hba.conf)

If you fixed listen_addresses but are now getting a fatal error regarding pg_hba.conf (often seen as postgresql port 5432 failed connection refused transitioning into an authentication failure), you need to tell PostgreSQL to trust incoming connections from your specific IP.

1. Open pg_hba.conf (located in the same directory as postgresql.conf).

2. Scroll to the bottom of the file to the IPv4 local connections section.

3. Add a rule for your remote client. To allow all IPs (not recommended for production, but good for testing): host all all 0.0.0.0/0 scram-sha-256

   To allow a specific subnet (e.g., your office VPN): host all all 192.168.1.0/24 scram-sha-256

4. Reload the PostgreSQL configuration to apply changes without restarting the database:

   sudo systemctl reload postgresql
   

   Or via psql: SELECT pg_reload_conf();

Step 4: Resolve Firewall Issues

If the configuration is correct but connection refused connect postgresql persists, the OS firewall is the culprit.

For Ubuntu/UFW:

sudo ufw allow 5432/tcp

For CentOS/Firewalld:

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

For Windows Defender Firewall: Open an elevated PowerShell prompt to fix postgresql connection refused port 5432 windows:

New-NetFirewallRule -DisplayName "Allow PostgreSQL 5432" -Direction Inbound -LocalPort 5432 -Protocol TCP -Action Allow

Deep Dive: PostgreSQL Replication Lag Monitoring

Once you have resolved the connectivity issues (connection refused postgresql 5432), you can properly set up monitoring for your high-availability cluster. Replication lag is a critical metric for read-replicas. If the replication lag in postgresql grows too large, your read-heavy applications will serve stale data, and in the event of a primary failure, failover will result in unacceptable data loss.

Understanding Replication in PostgreSQL

PostgreSQL uses Write-Ahead Logging (WAL) for replication. The primary server writes changes to WAL files, and the standby server (replica) reads these files and applies the changes. The difference in time or data volume between what the primary has written and what the standby has applied is the 'lag'.

Querying Replication Lag in Bytes

The most precise way to measure lag is in bytes. Run this query on the Primary server. It leverages the pg_stat_replication view, which requires a successful connection (hence why fixing the connection refused error was paramount).

SELECT
    client_addr AS replica_ip,
    usename AS replication_user,
    state,
    sync_state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replication_lag_bytes
FROM
    pg_stat_replication;

This query compares the current WAL Log Sequence Number (LSN) on the primary with the LSN that has been successfully replayed by the standby.

Calculating PostgreSQL Replication Lag in Seconds

While bytes are accurate, humans and alerting systems usually prefer time-based metrics. To measure postgresql replication lag in seconds, it is best to query the Standby server directly.

Execute the following on the replica:

SELECT
    CASE
        WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
        THEN 0
        ELSE EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))
    END AS replication_lag_in_seconds;

How this works:

1. It checks if the receive LSN matches the replay LSN. If they are identical, the replica is fully caught up, and lag is 0.
2. If they differ, it calculates the time difference between the current clock time (now()) and the timestamp of the last transaction that was replayed (pg_last_xact_replay_timestamp()).

Setting Up Automated Monitoring

To effectively monitor replication, you should integrate these queries into a monitoring stack like Prometheus, Datadog, or Zabbix.

For Prometheus, the postgres_exporter automatically exposes these metrics. However, the exporter must be able to connect to the database. If the exporter logs show failed to retrieve postgresql server_version_num connection refused, you need to ensure the exporter's IP is allowed in pg_hba.conf and that listen_addresses is configured correctly, referencing the troubleshooting steps in the first half of this guide.

When setting up alerts, consider your business requirements. A replication lag in seconds exceeding 30 seconds might trigger a warning, while exceeding 5 minutes could trigger a critical page to the on-call SRE. Always ensure your primary and standby servers have their system clocks synchronized via NTP/Chrony, as clock drift will heavily skew time-based lag calculations.