How to fix Could not receive data from client: Connection reset by peer in PostgreSQL

Review the PostgreSQL server logs to identify when and why connections are being reset:

tail -f /var/log/postgresql/postgresql.log

Look for the frequency and timing of "Connection reset by peer" messages. If they appear every 5 minutes, it's likely a firewall or load balancer timeout.

PostgreSQL has three TCP keepalive parameters that detect broken connections. Add these to your postgresql.conf file:

tcp_keepalives_idle = 240        # 4 minutes
tcp_keepalives_interval = 30     # 30 seconds
tcp_keepalives_count = 5         # 5 probes

These settings cause the server to probe idle connections every 4 minutes. If the client doesn't respond after 5 failed probes (150 seconds later), the connection is considered dead.

Reload the configuration:

sudo systemctl reload postgresql

Install and configure PgBouncer to manage database connections efficiently:

sudo apt-get install pgbouncer

Edit /etc/pgbouncer/pgbouncer.ini:

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5

Then update your application to connect to localhost:6432 instead of the PostgreSQL port directly. PgBouncer will manage keepalive and connection reuse automatically.

Configure your database driver or ORM to use a reasonable connection timeout. For example, with psycopg2 in Python:

import psycopg2
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="user",
    password="password",
    connect_timeout=5
)

For Node.js with pg:

const { Client } = require("pg");
const client = new Client({
  host: "localhost",
  database: "mydb",
  user: "user",
  password: "password",
  connectionTimeoutMillis: 5000,
  idleTimeoutMillis: 30000,
});

Without a timeout, your app may hang for 30+ seconds on network failures.

Work with your network/DevOps team to verify:

1. Firewall idle timeout is longer than your TCP keepalive interval (ideally > 10 minutes)
2. Load balancer or proxy is not aggressively closing idle connections
3. VIP health checks are not probing port 5432 every few seconds

If a VIP monitor is checking the port, coordinate with network ops to use a separate health check port or implement a dedicated health check endpoint.

For PostgreSQL 9.6+, set a server-side idle timeout to clean up abandoned sessions:

ALTER SYSTEM SET idle_session_timeout = '15min';

Reload:

sudo systemctl reload postgresql

This will automatically close sessions idle for more than 15 minutes, preventing resource exhaustion from abandoned connections.