PostgreSQL Network Partition Broken Pipe Fix

The Incident (What Does the Error Mean?)

Raw error output:

FATAL: could not send data to server: Broken pipe
SSL connection has been closed unexpectedly
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or after processing the request.

What actually happened: the TCP session was severed — most commonly by an AWS Security Group idle timeout (350s default), a GCP Cloud NAT mapping expiry, an on-prem firewall dropping stateful entries, or a brief AZ-level network event. The PostgreSQL backend process (postgres: user db 10.x.x.x) received a SIGTERM or simply lost the socket. Your application's connection object has no idea. It attempts to flush a query buffer, the kernel finds the socket dead, and raises SIGPIPE / EPIPE. Any transaction in flight is lost. No rollback is guaranteed to have reached the server.

The Attack Vector / Blast Radius

This is not a one-connection problem in production. Here's the cascade:

1. Connection pool poisoning. PgBouncer, HikariCP, SQLAlchemy pool, or pg pool in Node — all of them cache connections. After a partition event, every idle connection in the pool is stale. The next N requests (where N = pool size) will all fail with broken pipe before the pool's eviction logic kicks in — if it's configured at all.
2. Silent data loss. If the pipe breaks mid-COPY or mid-multi-statement transaction, the server may have executed partial work. Without synchronous_commit = on and proper client-side retry logic, you can have partial writes with no client-side error until the next flush.
3. Thundering herd on recovery. Once the network restores, all pool threads simultaneously detect dead connections and attempt reconnect + re-auth at the same instant. Under high concurrency this spikes pg_stat_activity past max_connections, triggering FATAL: sorry, too many clients already.
4. Monitoring blindspot. APM tools record the broken pipe as an application error, not a network event. Root cause gets misattributed to the app layer. The real fix — keepalives and pool validation — gets skipped.

Prevention in CI/CD

1. Checkov — Scan Terraform RDS/VPC for Missing Keepalive Params

Add a custom Checkov policy to flag any aws_db_instance or application config that omits keepalive settings:

# .checkov/custom_policies/pg_keepalive.yaml
metadata:
  name: "PostgreSQL connection must enable TCP keepalives"
  id: "CKV_CUSTOM_PG_001"
  severity: HIGH
definition:
  and:
    - cond_type: "connection"
      resource_types: ["aws_db_parameter_group"]
      attribute: "parameter.tcp_keepalives_idle"
      operator: "exists"

2. OPA/Conftest — Validate App Config at Deploy Time

package postgres.connection

deny[msg] {
    input.pool_pre_ping != true
    msg := "pool_pre_ping must be enabled to survive network partitions"
}

deny[msg] {
    input.pool_recycle > 300
    msg := sprintf("pool_recycle=%d exceeds 300s; connections will outlive firewall idle timeouts", [input.pool_recycle])
}

3. Integration Test — Simulate Partition in CI

Use tc (traffic control) or Toxiproxy in your pipeline to validate retry behavior:

# docker-compose.test.yml — inject Toxiproxy between app and Postgres
# Then in CI:
toxiproxy-cli toxic add pg-proxy --type=timeout --attribute=timeout=0 --downstream
sleep 5
toxiproxy-cli toxic remove pg-proxy
# Assert: app recovered without data loss, no unhandled broken pipe exceptions

4. Alert on `pg_stat_activity` Idle Connection Spike

-- Prometheus query via postgres_exporter
pg_stat_activity_count{state="idle"} > 0.8 * pg_settings_max_connections

This fires before you hit max_connections, giving ops time to drain the pool gracefully rather than reacting to a full outage.