UNDELETE data FROM table;
summary
Postgres rows marked deleted remain on disk and can often be recovered by reading page images or forcing a table scan that ignores visibility. Practical recipes: use pg_dirtyread to return deleted tuples (specify column list, handles xmin/xmax and dropped columns), or extract full-page images from WAL with pg_waldump --save-fpi (dump many FPI files, cat into a new base file, create table copy, then pg_dirtyread). Autovacuum removes candidates after ~50 rows + 20% changed, toast and concurrent updates complicate recovery. Works reliably in clean, low-concurrency cases.
AI-written summary · about
chapters (10)
- 0:00Introduction to talk
- 1:02What is UNDELETE data FROM table
- 2:49MVCC and deleted rows
- 6:00Introducing PG dirty read
- 11:44Handling TOAST and limitations
- 14:41Autovacuum and data loss
- 16:09Recovering from WAL files
- 23:46Practical recovery steps
- 25:12Summary and best practices
- 26:17Q&A session
related talks
56mI Dropped My Database! Now What? - A Dive Into PostgreSQL Backup Using pgBackRest
48mInside Postgres: why VACUUM matters
27mMaintaining Large Tables in PostgreSQL
50mSolving PostgreSQL Wicked Problems
50mWe have multiple concurrent versions of this title trying to understand MVCC
55mUnderstanding Autovacuum
20mDebugging Data Corruption in PostgreSQL
33mPostgres Storytelling: Support in the Darkest Hour