PostgreSQL Socket Failover Connectivity Auditor

Threat/Impact Level: HIGH | Downtime Risk: HIGH | Time to Fix: 15–45 mins depending on stack

TL;DR

• What broke: Your connection pool cached TCP sockets to the old primary. After failover, those sockets are dead — the OS hasn't torn them down yet, so the pool hands them to your app, which immediately fails on write.
• How to fix it: Enable pool_pre_ping, set TCP keepalives at the driver level, and configure your pool's connection_recycle interval to be shorter than your HA failover window.
• Shortcut: Use our Client-Side Sandbox above to auto-refactor this — paste your SQLAlchemy URI, PgBouncer config, or pg Node.js options and get a patched config instantly.

OperationalError: could not send data to server: Socket is not connected
SSL connection has been closed unexpectedly
server closed the connection unexpectedly

How to Fix It

Basic Fix — Enable Pre-Ping / Connection Validation

Most pools support a "test on borrow" or pre-ping mechanism. This adds one SELECT 1 round-trip before handing a connection to your app. It's not zero-cost, but it's the fastest path to correctness.

SQLAlchemy (Python):

 engine = create_engine(
     DATABASE_URL,
-    pool_pre_ping=False,
+    pool_pre_ping=True,
+    pool_recycle=300,          # recycle connections older than 5 min
+    pool_timeout=10,
+    connect_args={
+        "keepalives": 1,
+        "keepalives_idle": 10,
+        "keepalives_interval": 5,
+        "keepalives_count": 3,
+    }
 )

node-postgres (pg / pg-pool):

 const pool = new Pool({
   connectionString: process.env.DATABASE_URL,
+  idleTimeoutMillis: 10000,
+  connectionTimeoutMillis: 5000,
+  allowExitOnIdle: true,
 });
+
+// Attach error handler — without this, stale socket errors crash the process
+pool.on('error', (err, client) => {
+  console.error('Idle client error, removing from pool', err.message);
+});

asyncpg (Python async):

 pool = await asyncpg.create_pool(
     dsn=DATABASE_URL,
+    min_size=2,
+    max_size=10,
+    max_inactive_connection_lifetime=30.0,  # seconds — critical post-failover
+    command_timeout=10,
 )

Enterprise Best Practice — TCP Keepalives + Pool Health Checks + Retry Policy

Pre-ping alone is insufficient for high-throughput systems (it serializes connection checkout). The correct enterprise posture is:

1. TCP keepalives at the kernel level — detect dead connections without an application-layer round-trip.
2. Short pool recycle TTL — force connection renewal before your HA failover window.
3. Exponential backoff retry scoped only to transient network errors, not SQL errors.
4. PgBouncer in transaction mode (not session mode) — transaction mode re-establishes server connections per transaction, making it naturally resilient to failover.

PgBouncer pgbouncer.ini:

 [pgbouncer]
 pool_mode = session
+pool_mode = transaction
+
 server_idle_timeout = 600
+server_idle_timeout = 30
+server_lifetime = 300
+server_connect_timeout = 5
+tcp_keepalive = 1
+tcp_keepidle = 10
+tcp_keepintvl = 5
+tcp_keepcnt = 3
+query_wait_timeout = 10

JDBC / HikariCP (Java):

 HikariConfig config = new HikariConfig();
 config.setJdbcUrl(DB_URL);
+config.setConnectionTestQuery("SELECT 1");
+config.setKeepaliveTime(30000);          // 30s keepalive probe
+config.setMaxLifetime(180000);           // 3 min max connection lifetime
+config.setConnectionTimeout(10000);
+config.setIdleTimeout(20000);
+config.addDataSourceProperty("tcpKeepAlive", "true");
+config.addDataSourceProperty("socketTimeout", "15");

PostgreSQL postgresql.conf (server-side keepalive — defense in depth):

+tcp_keepalives_idle = 10
+tcp_keepalives_interval = 5
+tcp_keepalives_count = 3

💡 Tired of pasting proprietary infrastructure configs into public LLMs? Generic AI interfaces log your company's ARNs, connection strings, and private keys. StackEngine enforces client-side zero-trust boundaries—the sandbox above executes 100% locally in your browser. Drop your broken manifest above to redact secrets locally and synthesize deterministic patch drafts with your own API key.

(For CI/CD pipeline automation, install via npm i -g stackengine).

# .checkov/custom_checks/db_pool_pre_ping.py
# Check that SQLAlchemy engine configs include pool_pre_ping=True
# Integrate into: checkov -d . --external-checks-dir .checkov/custom_checks

# Simulate failover: drop all connections to DB port for 45 seconds
toxiproxy-cli toxic add postgres_proxy \
  --type timeout \
  --attribute timeout=45000 \
  -n failover_sim

# Run your integration test suite during this window
# Any test that fails with 'Socket is not connected' without retry = gap in your pool config

package kubernetes.admission

deny[msg] {
  input.request.kind.kind == "Deployment"
  container := input.request.object.spec.template.spec.containers[_]
  not container.livenessProbe
  msg := sprintf("Container '%v' missing livenessProbe — required for DB failover detection", [container.name])
}