Playbook — n8n “crashed” execution storm from Postgres connection drops

Alert: N8nWorkflowPersistentlyFailing (Prometheus / prometheus-alerts-ai-triage.py) First diagnosed: 2026-06-30 (issue #2253, workflow wa-ai-to-inbox / swdqVHDAj4kTeSyV) Server: bms-4 (bms-4-n8n-1 main + bms-4-n8n-worker-{1,2,3}-1 + bms-4-n8n-postgres-1)

Symptom

The N8nWorkflowPersistentlyFailing alert fires for an n8n workflow (e.g. N errors/1h). The n8n executions list shows a run of consecutive crashed executions (status crashed, finished=false), typically with stoppedAt timestamps clustered into a few identical moments rather than spread evenly.

This pattern is NOT a workflow-logic failure — it is in-flight executions being orphaned when n8n loses (and re-establishes) its connection to its Postgres backing store. On each reconnect, n8n’s execution-recovery sweep marks every still-”running” execution as crashed.

How to recognise it (vs. a real workflow bug)

Run on the n8n host (bms-4). Get the API key by NAME only — never print it:

N8N_KEY=$(grep -m1 '^BMS4_N8N_API_KEY=' /opt/p24-infra/bms-4/.env | cut -d= -f2- | tr -d '"')
BASE="http://localhost:5678"
WF=swdqVHDAj4kTeSyV   # workflow id from the alert's workflow_id label

Status history — look for many crashed with batched stoppedAt:

curl -s "$BASE/api/v1/executions?workflowId=$WF&limit=30&includeData=false" \
  -H "X-N8N-API-KEY: $N8N_KEY" \
  | python3 -c 'import json,sys;[print(e["id"],e["status"],e["startedAt"],"->",e["stoppedAt"]) for e in json.load(sys.stdin)["data"]]'

Per-node trace of a crashed run — confirms the workflow did NOT fail on a node. A genuine orphan shows the first node(s) succeeding then nothing, with no node-level error:

curl -s "$BASE/api/v1/executions/<ID>?includeData=true" -H "X-N8N-API-KEY: $N8N_KEY" \
  | python3 -c 'import json,sys;d=json.load(sys.stdin);rd=d["data"]["resultData"];print("last:",rd.get("lastNodeExecuted"));print("err:",(rd.get("error") or {}).get("message","none"))'

Confirm the DB-drop root cause — n8n logs a recovery line per reconnect; the timestamps align with the crashed-batch stoppedAt clusters:
```
docker logs -t bms-4-n8n-1 --since 2026-06-30T17:00:00 2>&1 | grep -i 'connection recovered'
```
Rule out the usual suspects (all were ruled out in #2253):
- OOM: free -h (had 26 Gi free); docker inspect bms-4-n8n-1 --format '{{.State.OOMKilled}}'
- Connection exhaustion: docker exec bms-4-n8n-postgres-1 psql -U n8n -d n8n -tAc "SELECT count(*) FROM pg_stat_activity;" vs SHOW max_connections; (was 13/100)
- Postgres restart: docker ps -a --filter name=n8n-postgres (was up 3 days)
- The Postgres duplicate key … PK_b21ace2e… errors are recovery artifacts, not the cause.

If steps 1–3 match, this is the DB-connection-drop pattern — proceed to remediation. If a crashed run shows a real node-level error, this playbook does not apply — triage the node error instead.

Remediation

If the workflow is already healthy again (latest executions are success), no live action is needed — a full n8n stack recreate or the next clean reconnect clears the orphan backlog. Verify:

curl -s "$BASE/api/v1/executions?workflowId=$WF&limit=6&includeData=false" -H "X-N8N-API-KEY: $N8N_KEY" \
  | python3 -c 'import json,sys;[print(e["id"],e["status"],e["startedAt"]) for e in json.load(sys.stdin)["data"]]'

If executions are still crashing, recreate the n8n stack to reset the Postgres connection pool — but restart the workers STAGGERED, never all at once. A simultaneous cold start of all three workers makes them CPU-peg replaying the shared event-log/crashed backlog on the n8n_data volume; they stop serving /metrics and trip N8nWorkerDown (#5598, #5813, #5817). Drain the crashed backlog first (see §Draining the crashed backlog) so there is little to replay, then:
```
# PLAYBOOK: n8n-crashed-executions-db-connection-drop.md
cd /opt/p24-infra/bms-4
docker compose up -d --force-recreate n8n            # main first; wait for healthy
for w in 1 2 3; do
  docker compose up -d --force-recreate n8n-worker-$w
  sleep 45                                           # let each worker finish recovery before the next
done
```
Check for stranded messages (for WA pipelines: claim_wa_messages marks rows processing before a crash). The wa-processing-watchdog workflow auto-resets rows stuck processing >30 min to processingError; verify nothing is stranded:
```
# via Supabase REST (service key by NAME only) — counts per status
for s in new processing processingError; do
  curl -s "$SUPA_HB_URL/rest/v1/p24_whatsapp_messages?status=eq.$s&select=id" \
    -H "apikey: $SUPA_HB_KEY" -H "Authorization: Bearer $SUPA_HB_KEY" \
    -H "Prefer: count=exact" -H "Range: 0-0" -I 2>/dev/null | grep -i content-range
done
```
Rows reset to processingError are terminal (not re-claimed). If user-facing tickets were lost, requeue by hand: UPDATE p24_whatsapp_messages SET status='new' WHERE status='processingError' AND processing_started_at >= '<window-start>'.

Prevention

executionTimeout on at-risk scheduled workflows (added to wa-ai-to-inbox in #2253: settings.executionTimeout = 120). Bounds an orphaned/hung execution to 120 s instead of lingering until the next recovery sweep, and prevents overlap with the 5-min schedule. Choose a value well above the observed legit runtime (≤1.7 s for wa-ai-to-inbox) and below the schedule interval. Apply to live via the n8n API PUT (see .claude/commands/n8n.md), then re-export the snapshot.
If the n8n→Postgres drops become frequent (not the transient one-off seen in #2253), escalate as a capacity / DB-stability issue: inspect Postgres checkpoint duration, container resource limits, and the inter-container network, and consider raising the n8n DB connection-pool timeout.

Frequent-drop / recovery-storm root cause (#5817, 2026-08-06)

When the drops recur every ~1–2 h (14 in 24 h on bms-4) the signature is always Database ping failed (1/3): Database connection timed out → Database connection recovered 2–4 s later — the first keepalive ping times out and the retry succeeds. That is not a real connection loss; it is a brief stall while the ping cannot get a working DB connection. Three compounding causes, all confirmed on bms-4:

DB connection pool too small. n8n’s DB_POSTGRESDB_POOL_SIZE was unset → default 2 on the main instance and every worker. When both pool connections are momentarily busy (a prune DELETE, an insights aggregation, a slow query) the keepalive ping cannot acquire one within its timeout and logs ping failed. Fix: set DB_POSTGRESDB_POOL_SIZE explicitly (main 8, workers 4 — done in bms-4/docker-compose.yml, applies on next container restart).
Postgres untuned. Stock postgres:16.9-alpine runs shared_buffers=128MB against a 2 GB+ execution store. Timed checkpoints (checkpoint_timeout=300s) flush dirty pages in I/O bursts that briefly stall queries. Fix: command: tuning on n8n-postgres (shared_buffers 512MB, max_wal_size 2GB, maintenance_work_mem 256MB — done in compose, applies on next postgres recreate).
crashed backlog feeds itself. Each stall’s recovery sweep marks in-flight runs crashed; the backlog (9,981 rows / 3.2 GB execution_data on 2026-08-06) is then the fuel for the cold-start recovery storm that takes workers offline (#5598/#5813). Draining it breaks the loop.

Confirm each: pool size docker exec bms-4-n8n-1 printenv DB_POSTGRESDB_POOL_SIZE; shared_buffers docker exec bms-4-n8n-postgres-1 psql -U n8n -d n8n -tAc "SHOW shared_buffers;"; backlog … -tAc "SELECT status,count(*) FROM execution_entity GROUP BY 1;".

Observability gap (noted #5817): n8n-postgres has log_checkpoints=on but logging_collector=off and its stderr is not reaching docker logs — so checkpoint timing/duration is currently invisible. If deeper checkpoint analysis is needed, fix the container log path first.

Draining the crashed backlog

crashed executions are terminal orphans — they hold no recoverable work and are safe to delete. EXECUTIONS_DATA_PRUNE=true/MAX_AGE=168h only ages them out slowly (7 days), so after a storm the backlog must be drained by hand. Delete in batches (never one giant DELETE) — a single delete of thousands of large execution_data TOAST rows generates a WAL burst that itself stalls the ping. execution_data, execution_metadata, execution_annotations are ON DELETE CASCADE, so deleting execution_entity is sufficient.

# PLAYBOOK: n8n-crashed-executions-db-connection-drop.md
PSQL() { docker exec bms-4-n8n-postgres-1 psql -U n8n -d n8n -tAc "$1"; }
PSQL "SELECT count(*) FROM execution_entity WHERE status='crashed';"   # how many
for i in $(seq 1 20); do
  n=$(PSQL "WITH d AS (DELETE FROM execution_entity WHERE id IN (
        SELECT id FROM execution_entity WHERE status='crashed' ORDER BY id LIMIT 1000
      ) RETURNING 1) SELECT count(*) FROM d;" | tr -d '[:space:]')
  echo "batch $i: $n"; [ "$n" = "0" ] && break; sleep 1
done
# Reclaim freed space for reuse + refresh planner stats (plain VACUUM — no exclusive lock):
docker exec bms-4-n8n-postgres-1 psql -U n8n -d n8n -c "VACUUM (ANALYZE) execution_data;"
docker exec bms-4-n8n-postgres-1 psql -U n8n -d n8n -c "VACUUM (ANALYZE) execution_entity;"

Plain VACUUM marks the pages reusable but does not return disk to the OS (the file stays ~its peak size and is refilled by new executions). Only run VACUUM FULL execution_data if you must reclaim disk — it takes an exclusive lock that blocks n8n, so do it in a maintenance window with the stack paused. On 2026-08-06 the batched drain of 10,061 rows caused 0 new ping failures and DB size fell 3,246 MB → 2,208 MB (row-count fuel removed; residual is reusable free space).

Top backlog contributors are the highest-frequency scheduled workflows — bound them with executionTimeout per §Prevention so a single orphaned run cannot linger until the next sweep (2026-08-06 top: mezmo-alert-router 6,014, atrax/kravag-scheduled-fleet-updates 1,304, p24-content-orchestrator 749, wa-ai-to-inbox 747).

References

Issue #2253 — original diagnosis
Issue #5817 — frequent-drop / recovery-storm root cause: pool size, Postgres tuning, backlog drain
Migration 20260624_wa_claim_limit_and_watchdog_threshold.sql — prior pile-up hardening (claim limit 50, watchdog threshold 30 min)
docs/n8n-operations.md, docs/n8n-postgresql-operations.md

Audit Log — Log to infra_operations

After this operation completes, log it to the infra_operations audit table.

Python (Linux server — bms-4, vps-i1, vps-h1, or similar):

import sys
sys.path.insert(0, '/opt/p24-infra')
from scripts.lib.log_op import log_op
 
log_op(
    actor="claude",  # "radieu" for manual human ops, "claude" for agent
    op_type="restart",
    resource="n8n-bms4",
    result="success",  # "success" | "failed" | "skipped"
    detail="n8n crashed executions DB connection drop resolved — DB connection pool reset",
    env="bms-4",
    gh_issue=2730,
)

PowerShell (Windows dev machine):

$env:SUPABASE_URL = (Get-Content "C:\code_2026\p24-infra\.env.local" | Select-String "^SUPABASE_URL=").ToString().Split("=",2)[1].Trim()
$env:SUPABASE_SERVICE_KEY = (Get-Content "C:\code_2026\p24-infra\.env.local" | Select-String "^SUPABASE_SERVICE_KEY=").ToString().Split("=",2)[1].Trim()
python -c "
import os, sys
sys.path.insert(0, 'C:/code_2026/p24-infra')
from scripts.lib.log_op import log_op
log_op('claude', 'restart', 'n8n-bms4', 'success', 'n8n crashed executions DB connection drop resolved — DB connection pool reset', 'bms-4')
"
$env:SUPABASE_URL = ''; $env:SUPABASE_SERVICE_KEY = ''

p24-infra Docs

Explorer

n8n-crashed-executions-db-connection-drop