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 below 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.

---

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."

---

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 configs into ChatGPT? Generic AI tools log your company's ARNs, DB strings, and private keys. StackEngine is a zero-backend, pure Client-Side WASM utility. Drop your failing config into the sandbox above. We redact your secrets locally in the browser and auto-generate the refactored code using your own API key.

---

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'.