PostgreSQL networking troubleshooting with practical examples: a step-by-step implementation guide

Intro

PostgreSQL connectivity failures often masquerade as application bugs until you prove they are network problems. This hands-on guide shows how to isolate PostgreSQL network issues with a small, safe set of steps you can run on Linux, macOS, and Windows. You will collect the right facts, make reversible changes, verify each stage with concrete commands, and recover reliably when something goes wrong.

Constructed examples are used throughout. Replace hostnames, IPs, ports, and users with values from your environment.

What you will learn:

• How to inventory versions, topology, and critical file paths
• How to change PostgreSQL network settings safely
• How to test DNS, routing, ports, and firewalls without guesswork
• How to read PostgreSQL connection errors and confirm the fix
• How to handle common failure modes, roll back, and recover service

Version and Environment Inventory

Before changing anything, write down what you have so tests are repeatable.

Prerequisites:

• Shell access on the PostgreSQL server
• A client host for remote tests
• Permissions to read PostgreSQL configuration and logs
• Elevated privileges for firewall checks when required

Collect the facts:

1. PostgreSQL and client versions

• On the server:

postgres --version
psql --version

• On each client host:

psql --version

1. Confirm where PostgreSQL reads its config (from any working psql session or via local socket):

psql -d postgres -c "show config_file;"
psql -d postgres -c "show hba_file;"
psql -d postgres -c "show data_directory;"

1. Network interfaces and addresses

• Linux:

ip addr
ip route
hostname -I

• macOS:

ifconfig
route -n get default
ipconfig getifaddr en0   # or your interface

• Windows (PowerShell):

Get-NetIPAddress
Get-NetRoute -DestinationPrefix 0.0.0.0/0

1. Service status (examples vary by distro/install)

• Linux systemd:

systemctl status postgresql || systemctl status postgresql-15

• macOS Homebrew:

brew services list | grep -i postgres

• Windows (service name varies):

Get-Service | Where-Object {$_.Name -like "postgres*"}

1. Client environment (affects how psql connects)

• Linux/macOS:

printenv | grep -E '^PGHOST|^PGPORT|^PGUSER|^PGDATABASE'

• Windows PowerShell:

Get-ChildItem Env: PG*

1. Minimal topology map (example):

• Server: db1.internal.example.com, 10.10.20.15, port 5432
• App host: app1.internal.example.com, 10.10.20.50
• External bastion: bastion.example.com, 203.0.113.10

Safe Configuration Path

Change one thing at a time and confirm it worked before proceeding. Back up configuration files and keep a rollback plan ready.

Back up before edits (example paths):

# On the PostgreSQL server
cp /var/lib/pgsql/15/data/postgresql.conf /var/lib/pgsql/15/data/postgresql.conf.bak.$(date +%Y%m%d%H%M%S)
cp /var/lib/pgsql/15/data/pg_hba.conf /var/lib/pgsql/15/data/pg_hba.conf.bak.$(date +%Y%m%d%H%M%S)

Reload vs. restart:

• Reload applies many configuration changes without disconnecting sessions:

psql -d postgres -c "select pg_reload_conf();"

• Restart is required when you change listen_addresses, port, or ssl.

Recommended change sequence (narrow, measurable, and locally testable):

1. Prove local connectivity first.

psql -h 127.0.0.1 -p 5432 -U postgres -d postgres -c "select 1;"

Expected: a single row with 1.

1. Enable listening on the specific server IP (avoid 0.0.0.0 unless justified). In postgresql.conf:

listen_addresses = '127.0.0.1,10.10.20.15'
port = 5432

Restart PostgreSQL, then verify listening sockets (Linux):

ss -ltnp | grep 5432

1. Add the narrowest necessary pg_hba.conf entries for your client subnet or host (example allows user app from 10.10.20.0/24 using SCRAM):

# TYPE  DATABASE  USER  ADDRESS          METHOD
host    all       app   10.10.20.0/24    scram-sha-256

Reload and test from the app host. Broaden scope only if a visible, verified reason requires it.

Verification and Diagnostics

Use the checks below to isolate the failing layer. Stop when you find the cause.

Quick reference touchpoints (examples):

• Port: typically 5432. Verify with: psql -p 5432, ss -ltnp, Test-NetConnection.
• listen_addresses: usually 127.0.0.1, <serverIP>. Verify by checking show config_file; and inspecting the file.
• pg_hba.conf: must allow the client subnet/host. Verify with show hba_file; and check rule order.
• DNS A/AAAA: db1.internal -> 10.10.20.15. Verify with dig, nslookup, or getent hosts.
• Firewall: allow TCP 5432 to the server. Verify with ufw/firewalld/nftables/iptables/Windows Firewall.

Symptom-to-cause starting points:

• Timeout connecting: routing issue or firewall drop. First check: nc -vz host 5432, traceroute/tracert.
• Connection refused: not listening or blocked locally. First check: ss -ltnp, firewall status.
• FATAL: no pg_hba.conf entry: missing or wrong pg_hba rule. First check: rule order and matching CIDR.
• FATAL: password authentication failed: wrong creds or role. First check: test with a known-good user.
• SSL errors or FATAL about ssl/hostssl: TLS requirement mismatch. First check: hostssl vs host, client sslmode.
• Wrong database server: DNS or hosts file misroute. First check: dig +short, getent hosts.

DNS and name resolution

Confirm that the hostname used by clients resolves to the expected IP.

• Linux:

getent hosts db1.internal.example.com

• Cross-platform with dig (install if needed):

dig +short db1.internal.example.com A

• Windows (PowerShell):

Resolve-DnsName db1.internal.example.com

Expected: the IP of your PostgreSQL server. If it is wrong or returns multiple IPs, test by IP while you fix records. If split-horizon DNS exists, test from each network segment.

Reachability and routing

• ICMP reachability (may be blocked; failure is informative but not definitive):

ping -c 3 10.10.20.15     # Linux/macOS
Test-Connection 10.10.20.15 -Count 3   # Windows

• Routing path:

traceroute 10.10.20.15    # Linux/macOS (install if needed)
tracert 10.10.20.15       # Windows

• Which route will be used (Linux):

ip route get 10.10.20.15

If the route targets the wrong interface or gateway, correct routes or use the correct interface IP in listen_addresses.

Is PostgreSQL listening?

Check the server for a TCP listener on the expected interface and port.

• Linux:

ss -ltnp | grep 5432
# or
lsof -iTCP:5432 -sTCP:LISTEN

• macOS:

lsof -n -iTCP:5432 -sTCP:LISTEN

• Windows (PowerShell):

Get-NetTCPConnection -LocalPort 5432 -State Listen

Expected: entries bound to 127.0.0.1 and your server IP. If only 127.0.0.1 appears, remote clients will time out or be refused.

Firewall checks

Local host firewall:

• Ubuntu/Debian with UFW:

sudo ufw status verbose

• firewalld (RHEL family):

sudo firewall-cmd --list-all
sudo firewall-cmd --list-ports

• nftables (generic):

sudo nft list ruleset | grep -i 5432 -n

• iptables (legacy):

sudo iptables -S | grep 5432

• Windows Defender Firewall:

netsh advfirewall firewall show rule name=all | findstr 5432

If inbound 5432 is blocked on the server, add an allow rule for TCP 5432 limited to necessary source addresses. Prefer allow-from-specific-subnet over open-to-world.

Port connectivity from the client

• Cross-platform with netcat (install if needed):

nc -vz db1.internal.example.com 5432

Expected: succeeded/open. The failure reason (timeout/refused) guides next steps.

• Windows PowerShell:

Test-NetConnection -ComputerName db1.internal.example.com -Port 5432 -InformationLevel Detailed

Expected: TcpTestSucceeded: True. If False, note NoRoute, TimedOut, or Refused.

• Telnet can also indicate open vs. closed/refused:

telnet db1.internal.example.com 5432

PostgreSQL authentication and HBA

From the client, attempt a minimal query:

psql "host=db1.internal.example.com port=5432 user=app dbname=postgres" -c "select version();"

Common messages and what they mean:

• FATAL: no pg_hba.conf entry for host X, user Y, database Z
• Cause: missing or mismatched pg_hba rule. Fix the rule and reload.
• FATAL: password authentication failed for user "app"
• Cause: wrong password or user does not exist.
• psql: could not connect to server: Connection timed out
• Cause: routing or firewall issue.
• psql: could not connect to server: Connection refused
• Cause: no listener on that IP:port or blocked locally.

Rule order matters: the first matching rule wins. Place specific allow rules above broader rules.

Constructed example allowing SSL from a subnet and local socket access:

# Allow local socket connections for admin
local   all         postgres                peer
# Allow SSL connections from app subnet with SCRAM
hostssl all         app     10.10.20.0/24   scram-sha-256
# Optional: read-only from a single host
hostssl reporting   ro_user 10.10.20.55/32  scram-sha-256

Reload after edits:

psql -d postgres -c "select pg_reload_conf();"

TLS/SSL mode alignment

If clients require TLS but the server is not configured for it, you will see SSL-related errors. Test explicitly:

psql "host=db1.internal.example.com user=app dbname=postgres sslmode=require" -c "show ssl;"

Expected when TLS is active: ssl = on.

To enable TLS on the server (example):

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file  = 'server.key'

Use hostssl entries in pg_hba.conf for relevant clients. Restart is required after enabling ssl.

Logs and packet inspection

PostgreSQL logs often state the reason for rejections. Locate logs via:

psql -d postgres -c "show log_destination;"
psql -d postgres -c "show logging_collector;"
psql -d postgres -c "show log_directory;"

Inspect the most recent log file for authentication or connection errors.

Packet capture (advanced; restrict scope/time and avoid capturing credentials):

sudo tcpdump -ni eth0 host 10.10.20.50 and port 5432 -c 20 -w /tmp/pg_5432.cap

Expected: SYN/SYN-ACK/ACK handshake for successful TCP establishment. A stream of SYNs with no replies indicates filtering along the path.

Failure Modes and Recovery

This section pairs common issues with concrete recovery and rollback.

• Server listens only on 127.0.0.1
• Symptom: remote connection refused or timed out; ss shows 127.0.0.1:5432 only.
• Fix: set listen_addresses to include the server IP, restart, and verify.
• Verify:
• ss -ltnp | grep 5432
• nc -vz 10.10.20.15 5432
• Rollback: restore postgresql.conf backup and restart if needed.

• Wrong or missing pg_hba.conf rule
• Symptom: FATAL: no pg_hba.conf entry...
• Fix: add an explicit host or hostssl rule for the user and client subnet; place above broader rules.
• Verify: reload and reconnect.
• Rollback: revert pg_hba.conf from backup and reload.

• Local firewall drops inbound 5432
• Symptom: nc or Test-NetConnection shows timeout; server listens correctly.
• Fix (UFW example): sudo ufw allow from 10.10.20.0/24 to any port 5432 proto tcp.
• Verify: firewall status reflects the rule; client can connect.
• Rollback: sudo ufw delete allow from 10.10.20.0/24 to any port 5432 proto tcp.

• DNS points to the wrong host
• Symptom: psql connects but version() or data looks unexpected.
• Fix: correct DNS; temporarily connect by IP or pin via hosts file until DNS propagates.
• Verify: dig +short shows intended IP; psql connects to the expected server.
• Rollback: remove temporary hosts file entries once fixed.

• TLS mismatch
• Symptom: client uses sslmode=require but server has ssl=off, or hostssl in HBA while ssl=off.
• Fix: enable ssl on the server and use hostssl rules, or align clients to policy (disable/allow/require).
• Verify: show ssl; returns on; connection succeeds.
• Rollback: revert config and restart; adjust HBA back to non-SSL rules if necessary.

• Port collision on 5432
• Symptom: PostgreSQL fails to start; lsof shows another process on 5432.
• Fix: stop the conflicting service or move PostgreSQL to a new port, e.g. port = 5433.
• Verify: ss shows 5433 listening; clients updated to use -p 5433.
• Rollback: restore to 5432 when the conflict is removed.

• NAT hairpin not supported
• Symptom: inside-LAN clients cannot connect using the public IP, but external clients can.
• Fix: use the internal IP for internal clients or configure hairpin NAT on the router.
• Verify: internal clients connect via the LAN IP.

• Path MTU or intermittent loss
• Symptom: small queries work; larger ones hang.
• Fix (Linux/macOS):

ping -M do -s 1472 10.10.20.15

• Reduce MTU on the interface or correct the network path; coordinate with network admins.
• Verify: large queries complete; packet loss cleared.

Expected Results and How to Verify End-to-End

After fixes, confirm end-to-end behavior:

1. Local to server by loopback and interface IPs:

psql -h 127.0.0.1 -U postgres -d postgres -c "select 1;"
psql -h 10.10.20.15 -U postgres -d postgres -c "select 1;"

Both should return 1 promptly.

1. Remote client basic TCP open:

nc -vz db1.internal.example.com 5432

Should report open.

1. Remote client authenticated query with explicit sslmode as policy requires:

psql "postgresql://[email protected]:5432/postgres?sslmode=require" \
  -c "select current_user, inet_server_addr(), inet_server_port();"

Expected: current_user is app; inet_server_addr() matches the intended server IP; inet_server_port() matches the configured port.

1. If you changed listen_addresses, port, or ssl, restart and verify with:

pg_isready -h db1.internal.example.com -p 5432 -t 5

Expected: accepting connections.

Operations Checklist

Use this compact checklist during incidents and for routine changes.

• Record context: server and client hostnames, IPs, PostgreSQL versions
• Confirm DNS resolves to the expected IP from each client network
• Prove local server connectivity via 127.0.0.1 and interface IP
• Check server is listening on intended interface and port (ss/lsof)
• Test client TCP reachability (nc or Test-NetConnection)
• Inspect and correct pg_hba.conf rules and order; reload
• Align TLS: server ssl=on if hostssl is used; clients set sslmode appropriately
• Review host firewalls and allow only required sources to TCP 5432
• Verify end-to-end with psql: run a minimal query and confirm server identity
• If changing configs: back up files, change one item, reload or restart as required, verify, and document
• If something breaks: restore from backups, restart if needed, and retest from local to remote in order

Conclusion

PostgreSQL connectivity issues become straightforward when you narrow the problem to one layer at a time and verify each step. Start locally, expand outward, and change configurations in small, reversible increments. Keep DNS unambiguous, limit firewalls to what is necessary, order pg_hba rules precisely, and align TLS expectations. With the inventory, diagnostics, recovery steps, and checklist in this guide, you can resolve most PostgreSQL networking problems quickly and safely and have clear evidence when deeper network changes are required.