Find unlogged tables before a crash empties them
An UNLOGGED table skips the WAL, so PostgreSQL truncates it during crash recovery rather than serve contents it cannot vouch for. Inventory them, separate the ones you can regenerate from the ones you cannot, and convert the rest back to logged.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
UNLOGGED buys write throughput by not writing WAL. The price is written into the recovery path: after an unclean shutdown PostgreSQL truncates every unlogged relation, because without WAL it has no way to establish that their contents are consistent. The same absence means they never reach a physical standby, so a replica is not a second copy of that data either.
Meridian sped up a high-write events table by creating it UNLOGGED — a sound decision for scratch space. Over two years, two neighbouring tables created the same way quietly grew into the system of record. Nothing is wrong today. The next unclean restart returns both of them empty, and no backup or replica holds the rows.
In plain English
PostgreSQL normally records every change twice: once in the write-ahead log, and once in the table itself. The log is what lets it rebuild a consistent picture after a crash. An UNLOGGED table opts out of the log, which genuinely is faster — but it means that after a crash PostgreSQL has no way to know whether that table's contents are intact. Rather than hand you data it cannot vouch for, it empties the table. So unlogged does not mean 'a bit less safe'; it means 'this table is empty after any unclean shutdown, by design'. It also never reaches a replica. That is a fine trade for a cache or a staging area, and a disaster for anything you cannot rebuild from somewhere else.
Full runbook for this incident
- The full identify checklist — the exact signals that tell you it's this incident
- Every diagnostic query; PostgreSQL 18 output is attached only to the steps we captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
More in this category
Other WAL & replication runbooks
Neighbouring incidents that share the same diagnostic surface.