How to fix Server closed the connection unexpectedly in PostgreSQL

# Default log location (may vary by installation)
tail -100 /var/log/postgresql/postgresql.log

# Or check the log_directory setting in postgresql.conf
sudo -u postgres psql -c "SHOW log_directory;"

# Edit postgresql.conf
sudo nano /etc/postgresql/14/main/postgresql.conf

# Add or modify these settings
tcp_keepalives_idle = 60        # Send keepalive after 60 seconds idle
tcp_keepalives_interval = 10    # Send keepalive every 10 seconds
tcp_keepalives_count = 5        # Give up after 5 failed keepalives

sudo systemctl reload postgresql
# or
sudo -u postgres psql -c "SELECT pg_reload_conf();"

-- View current timeout settings
SHOW statement_timeout;
SHOW idle_in_transaction_session_timeout;
SHOW tcp_keepalives_idle;

-- Set via postgresql.conf or per-session
SET statement_timeout = '5min';           -- Query max duration
SET idle_in_transaction_session_timeout = '10min';  -- Max time in idle transaction

-- Or set globally in postgresql.conf
statement_timeout = 300000        # milliseconds
idle_in_transaction_session_timeout = 600000

# Install PgBouncer
sudo apt-get install pgbouncer

# Edit /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 100
default_pool_size = 25
server_idle_timeout = 600

# Start the service
sudo systemctl start pgbouncer

# Check current configuration
sudo -u postgres psql -c "SHOW listen_addresses;"

# If connecting remotely, edit postgresql.conf
sudo nano /etc/postgresql/14/main/postgresql.conf

# Set to listen on all interfaces (or specific IP)
listen_addresses = '*'    # All interfaces
# listen_addresses = '127.0.0.1,192.168.1.100'  # Specific IPs

# Then reload
sudo systemctl reload postgresql

// Node.js example with node-postgres
const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
});

async function queryWithRetry(sql, params, maxRetries = 3) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      const client = await pool.connect();
      try {
        return await client.query(sql, params);
      } finally {
        client.release();
      }
    } catch (err) {
      if (attempt === maxRetries - 1) throw err;
      const delay = Math.pow(2, attempt) * 100; // exponential backoff
      await new Promise(resolve => setTimeout(resolve, delay));
    }
  }
}

# Check if firewall is blocking connections
sudo ufw status
sudo firewall-cmd --list-all

# Ensure PostgreSQL port (5432) is allowed
sudo ufw allow 5432
# or
sudo firewall-cmd --add-port=5432/tcp --permanent

# Capture network traffic to diagnose connection issues
sudo tcpdump -i any -n 'port 5432' -w postgresql.pcap

# Analyze with Wireshark or similar tool
wireshark postgresql.pcap