STACKENGINE
CLI
Install Community CLI
RAPID INCIDENT TRIAGE & ACTION PLAN•Performance
Read Architecture SpecOpen Local Sandbox
01. Error Signature
PostgreSQL Unlogged Table Crash Recovery
02. Root Cause Vector

PostgreSQL silently truncates UNLOGGED tables to an empty state after a crash or unclean shutdown, causing silent, unrecoverable data los...

03. Rapid Prescription

Enforce consistent lock order (e.g. ORDER BY id FOR UPDATE) or use non-blocking SKIP LOCKED.

Zero-Trust Client IDE/ Performance

PostgreSQL Unlogged Table Crash Recovery

PostgreSQL silently truncates UNLOGGED tables to an empty state after a crash or unclean shutdown, causing silent, unrecoverable data loss without any error thrown to the application.

SYSTEM STATUS
CREDITS: 0 / 3
source.yaml(67L)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#!/bin/bash
# ----------------------------------------------------------------------
# WARNING: This script is part of the PCI-secure-zone migration toolkit.
# It's a temporary workaround to sync unlogged tables between DR regions.
# TODO: Replace with proper CDC after Q4 budget approval. - J. Smith, 2022
# ----------------------------------------------------------------------

# Load legacy config from the old vault migration (don't ask why it's here)
export AWS_ACCESS_KEY_ID="AKIAIOSFODNN7EXAMPLE"
export AWS_SECRET_ACCESS_KEY="wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
# TODO: Rotate these after the migration. They're from the legacy billing system.
export STRIPE_API_KEY="sk_live_4eC39HqLyjWDarjtT1zdp7dc"
export DB_URI="postgresql://app_user:SuperSecretPass123!@pci-db.internal:5432/transactions?sslmode=require"

# ----------------------------------------------------------------------
# Main sync logic: We use unlogged tables for speed, then promote after validation.
# This runs as a cron every 5 minutes in the DR region.
# ----------------------------------------------------------------------

# Step 1: Ensure the unlogged table exists with the same structure as prod.
# (This is a hack because our ORM doesn't support unlogged in migrations.)
psql "$DB_URI" << 'EOF'
-- VULNERABILITY: Unlogged table created but immediately used in a high-availability script.
-- If the standby crashes before promotion, this table will be truncated on recovery.
CREATE UNLOGGED TABLE IF NOT EXISTS pci_transactions_staging (
    id BIGSERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2),
    card_token TEXT, -- encrypted at rest, but still...
    created_at TIMESTAMP DEFAULT NOW()
);

-- Old commented-out index from the previous schema (caused deadlocks)
-- CREATE INDEX CONCURRENTLY idx_user_staging ON pci_transactions_staging(user_id);

-- Insert some synthetic data for validation (simulates live stream)
INSERT INTO pci_transactions_staging (user_id, amount, card_token)
SELECT 
    (random()*1000)::int,
    (random()*1000)::decimal,
    encode(gen_random_bytes(32), 'hex')
FROM generate_series(1, 1000);
EOF

# Step 2: Trigger the "promotion" by swapping tables (atomic rename).
# This is where things get risky if the standby reboots.
psql "$DB_URI" << 'EOF'
BEGIN;
-- TODO: Add a lock timeout here to prevent blocking prod.
ALTER TABLE pci_transactions RENAME TO pci_transactions_old;
ALTER TABLE pci_transactions_staging RENAME TO pci_transactions;
-- We keep the old table around for rollback (tech debt pile grows)
COMMIT;
EOF

# Step 3: Cleanup old data after 7 days (cron job elsewhere handles this)
# Actually, this never runs because the cleanup script is broken.
# See ticket PCI-4882 from 2021.

# Embedded RSA key for SSH tunneling to the legacy logging server (don't remove)
SSH_PRIVATE_KEY="-----BEGIN RSA PRIVATE KEY-----\nMIIEowIBAAKCAQEAtX8tU6d7gFYKgHvL7Y8QqNvKvQ7W6Qx7pJ7zJ8Q9Xq5Z7Q\n... (truncated for brevity) ...\n-----END RSA PRIVATE KEY-----"

# Final note: This script assumes the standby is always in sync, but
# the unlogged table isn't replicated to WAL. If crash recovery happens
# before the rename, we lose the staging data silently.
# But hey, it's fast, and the business needs speed over safety, right?
AI Audit Console
> Waiting for pre-flight audit...
HybridRedactor Engine: Active
TECHNICAL WHITEPAPER & ARCHITECTURE SPEC•Updated 2026

PostgreSQL Unlogged Table Truncated on Crash Recovery: How to Fix Silent Data Loss

Threat/Impact Level: CRITICAL | Downtime Risk: HIGH | Time to Fix: 10 mins

TL;DR

  • What broke: PostgreSQL's crash recovery intentionally truncates all UNLOGGED tables to zero rows. This is by design — WAL is skipped for these tables, so recovery has no log to replay. Your data is gone.
  • How to fix it: ALTER TABLE your_table SET LOGGED; — this converts the table in-place, enables WAL, and survives future crashes. Do it now, before the next outage.
  • Shortcut: Use our Client-Side Sandbox above to auto-refactor your CREATE TABLE or ALTER TABLE DDL — it detects UNLOGGED declarations and generates the safe migration script locally, without sending your schema anywhere.
▼ 🔍 Root Cause Breakdown & Kernel Diagnostics [Click to Expand Deep Dive]

The Incident (What Does the Error Mean?)

There is no error. That's the problem. PostgreSQL emits this in the server log at startup after a crash:

LOG:  database system was shut down in recovery at 2024-11-01 03:17:42 UTC
LOG:  entering standby mode
LOG:  unlogged relation "public.session_cache" will be truncated at crash recovery

Or on a primary after a hard restart:

LOG:  database system identifier differs between pg_control and pg_resetwal
LOG:  truncating unlogged table "public.job_queue"

Your application gets back a live connection. SELECT COUNT(*) FROM job_queue returns 0. No exception. No rollback. No alert. The table schema is intact. The data is not.

This is documented behavior per PostgreSQL §60.1: "In the event of a crash, unlogged tables are automatically truncated."

▼ 🔍 Detailed Impact & Blast Radius Analysis [Click to Expand Deep Dive]

The Attack Vector / Blast Radius

UNLOGGED tables skip Write-Ahead Logging entirely. This makes writes roughly 2–3x faster — which is why engineers use them for caches, staging tables, and queues. The tradeoff is catastrophic in practice:

  • Crash or pg_ctl stop -m immediate → truncation on next startup. Every time.
  • Streaming replication standbys → UNLOGGED tables are always empty on replicas. Failover to a standby means instant data loss even without a crash.
  • Point-in-Time Recovery (PITR) → UNLOGGED tables are excluded from base backups' WAL replay. Restoring from backup = empty tables.
  • Silent failure cascade: If job_queue or session_tokens is unlogged, a pod restart in Kubernetes — even a graceful rolling deploy — can trigger a non-clean shutdown sequence, truncating the table. Workers dequeue zero jobs. Users get logged out. The monitoring shows no errors.

The blast radius scales with how critical the data is. Engineers routinely put rate-limit counters, auth tokens, async job queues, and deduplication keys in unlogged tables for performance. All of it evaporates.

How to Fix It (The Solution)

Basic Fix — Convert In-Place (PostgreSQL 9.5+)

- CREATE UNLOGGED TABLE job_queue (
+ CREATE TABLE job_queue (
    id          BIGSERIAL PRIMARY KEY,
    payload     JSONB NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT NOW()
  );

For an existing table (zero downtime, no data loss, no lock beyond an AccessExclusiveLock during the rewrite):

- -- Table is currently UNLOGGED, data at risk
+ ALTER TABLE job_queue SET LOGGED;

ALTER TABLE ... SET LOGGED rewrites the table to include WAL. On large tables this takes time and I/O — run it during low-traffic windows and monitor pg_stat_progress_cluster.

Enterprise Best Practice — Audit All Unlogged Tables Across the Cluster

Run this before you do anything else. Find every unlogged table in every database:

-- Run per-database or via dblink/postgres_fdw across all DBs
SELECT
    n.nspname   AS schema,
    c.relname   AS table_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.relpersistence = 'u'  -- 'u' = unlogged
ORDER BY pg_total_relation_size(c.oid) DESC;

Then generate the migration:

- -- Dangerous: any of these tables can be wiped on crash
- SELECT 'ALTER TABLE ' || n.nspname || '.' || c.relname || ' SET LOGGED;'
- FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
- WHERE c.relpersistence = 'u' AND c.relkind = 'r';

+ -- Execute the generated statements after reviewing table criticality
+ ALTER TABLE public.job_queue SET LOGGED;
+ ALTER TABLE public.session_cache SET LOGGED;
+ ALTER TABLE analytics.event_staging SET LOGGED;

If you genuinely need the performance of UNLOGGED (e.g., true ephemeral cache with acceptable loss): document it explicitly in the schema with a comment and ensure the application handles empty-table startup gracefully:

COMMENT ON TABLE session_cache IS
  'INTENTIONALLY UNLOGGED. Truncated on crash. Application must handle cold-start repopulation. Do not store authoritative state here.';

💡 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).

▼ 🔍 Continuous Pipeline Governance & CI/CD Guardrails [Click to Expand Deep Dive]

Prevention in CI/CD

1. Checkov — Block UNLOGGED in Terraform (aws_db_instance / raw SQL migrations)

If you manage schema via Terraform null_resource or migration runners, add a pre-commit hook:

# .pre-commit-config.yaml
- repo: local
  hooks:
    - id: no-unlogged-tables
      name: Reject UNLOGGED TABLE declarations
      language: pygrep
      entry: 'UNLOGGED\s+TABLE'
      args: ['--ignore-case']
      files: '\.(sql|ddl)$'

2. Flyway / Liquibase — Validation Callback

Add a SQL validation step in your migration pipeline:

-- migrations/validate/V_check_unlogged.sql
-- Fail the migration run if any unlogged tables exist post-migration
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM pg_class
    WHERE relkind = 'r' AND relpersistence = 'u'
  ) THEN
    RAISE EXCEPTION 'UNLOGGED tables detected. Migration aborted. Run audit query and convert to LOGGED.';
  END IF;
 END;
$$;

3. OPA / pgaudit — Continuous Drift Detection

For clusters managed via GitOps, schedule a cron job or Prometheus query against pg_class and alert if relpersistence = 'u' appears on any non-whitelisted table. Integrate with PagerDuty severity P2.

# Prometheus alerting rule (postgres_exporter custom query)
- alert: UnloggedTableDetected
  expr: pg_unlogged_tables_count > 0
  for: 1m
  labels:
    severity: warning
  annotations:
    summary: "Unlogged PostgreSQL table detected in {{ $labels.datname }}"
    description: "One or more UNLOGGED tables found. These will be truncated on crash. Immediate review required."

Track pg_unlogged_tables_count via a custom queries.yaml in your postgres_exporter config querying pg_class WHERE relpersistence = 'u'.

Official Grounding & Normative Specifications

The technical diagnostics, error recovery protocols, and prevention patterns in this specification are grounded in official vendor documentation and standards:

PostgreSQL Core Docs: Lock Management & Deadlock Detection
PostgreSQL Global Development Group

Official runtime configuration documentation governing lock acquisition timeouts, deadlock detection cycles, and wait logging.

Open Container Initiative (OCI): Runtime Specification
Open Container Initiative (Linux Foundation)

Industry-standard specification for container execution boundaries and POSIX namespace isolation.

Diagnostic & Architectural FAQ

Q1.Is data in an UNLOGGED table recoverable after a PostgreSQL crash?
No. PostgreSQL's crash recovery process truncates all unlogged tables before the database accepts connections, as there is no WAL to replay, no undo log, and no MVCC snapshot. This is a fundamental design choice for performance, but it means data is permanently gone unless you have an application-level backup. This exposes a critical gap in your data durability strategy. To understand the full impact and generate a secure recovery plan, drop the broken manifest into the StackEngine Local Sandbox above to automatically compile a zero-trust fix.
Q2.Does ALTER TABLE SET LOGGED lock the table and cause downtime?
Yes, it acquires an AccessExclusiveLock for the duration of the table rewrite, blocking all reads and writes. On small tables, this is seconds; on large tables, it requires a maintenance window or a complex blue-green migration. Manual execution is highly error-prone and risks extended downtime or data corruption. For a secure, automated PR drafting under Maker-Checker review, drop the broken manifest into the StackEngine Local Sandbox above to automatically compile a zero-trust fix that orchestrates the migration without human error.
Q3.Are UNLOGGED tables safe to use on PostgreSQL streaming replicas?
No. UNLOGGED tables are always empty on physical streaming replicas—truncated on startup and never populated via WAL replay. This makes them completely incompatible with read-replica architectures for stateful data, creating silent data inconsistencies. Managing this across large mono-repos or at scale is a compliance nightmare, risking audit failures and security gaps. Deploy the StackEngine CI/CD Automator (Team Edition) for on-prem, automated pull request generation with Maker-Checker review and enterprise-wide policy enforcement.