Unlogged Table Data Loss After Crash

Scenario

A team converted several staging tables to UNLOGGED for faster bulk imports (no WAL overhead). After a power outage, PostgreSQL performs crash recovery. All UNLOGGED tables are automatically truncated to empty — by design. The team loses 3 days of import data that hadn’t been promoted to regular tables yet. They didn’t know UNLOGGED tables don’t survive crashes.

How to Identify

Conditions:

• pg_class.relpersistence = 'u' for tables that should have durable data
• Tables are empty after a crash or unexpected restart
• Data was being staged in UNLOGGED tables before processing
• No backup of UNLOGGED table data (UNLOGGED tables are not included in WAL-based backups)

Analysis Steps

-- Find all UNLOGGED tables in the database
SELECT schemaname, tablename, tableowner,
       pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables t
JOIN pg_class c ON c.relname = t.tablename AND c.relnamespace::regnamespace::text = t.schemaname
WHERE c.relpersistence = 'u'   -- 'u' = UNLOGGED
ORDER BY pg_relation_size(schemaname||'.'||tablename) DESC;

-- Check persistence type for all tables:
SELECT relname, relpersistence,
    CASE relpersistence
        WHEN 'p' THEN 'permanent (logged)'
        WHEN 'u' THEN 'UNLOGGED - truncated on crash!'
        WHEN 't' THEN 'temporary - gone after session'
    END AS persistence_type
FROM pg_class
WHERE relkind = 'r'
  AND relnamespace NOT IN (SELECT oid FROM pg_namespace WHERE nspname IN ('pg_catalog','information_schema'))
ORDER BY relpersistence, relname;

-- Check if the table is part of logical replication (UNLOGGED tables cannot be replicated):
SELECT tablename FROM pg_publication_tables WHERE tablename IN (
    SELECT relname FROM pg_class WHERE relpersistence = 'u'
);
-- Any match = UNLOGGED table in a publication (replication won't work for it)

Pitfalls

• UNLOGGED tables are automatically truncated on crash recovery — this is by PostgreSQL design and cannot be prevented. Data in UNLOGGED tables is not durable.
• pg_dump does NOT include data from UNLOGGED tables — the dump captures the schema but exports zero rows. This is another silent data loss risk.
• UNLOGGED tables cannot be part of logical replication publications.
• The performance benefit of UNLOGGED is real (no WAL overhead) but limited to truly disposable data: caches, scratch space, session state, or data that can be reloaded from an external source.
• Converting from UNLOGGED to regular: ALTER TABLE foo SET LOGGED — rewrites the entire table with WAL enabled. Data is preserved during this conversion.

Resolution Approach

Convert critical staging tables from UNLOGGED to regular (logged). Use UNLOGGED only for genuinely disposable data. For bulk imports that need speed without data loss: use synchronous_commit = off at the session level instead — much safer than UNLOGGED.