How PostgreSQL Replay Uncommitted Data During Crash Recovery
- When PostgreSQL crashes, everything in memory is lost but the important files on disk survive ā WAL files (transaction history), data files (actual tables), CLOG (who committed or not) andĀ
pg_controlĀ (database status file). - When PostgreSQL restarts, theĀ PostmasterĀ (the main process) first readsĀ
pg_controlĀ file ā if it sees the database was not shut down cleanly, it understands a crash happened and starts theĀ Startup ProcessĀ to fix the database. - TheĀ Startup ProcessĀ readsĀ
pg_controlĀ to find the last checkpoint position (think of it as the last known good save point) and goes to that exact position in the WAL files to start reading from there. - TheĀ Startup ProcessĀ reads WAL files one record at a time ā for each record, it finds the related data page, loads it into memory (Shared Buffers), applies the change to that page in memory and marks it as dirty (modified but not yet written to disk).
- TheĀ Startup ProcessĀ applies every single WAL record to memory pages ā it does not care whether the transaction was committed or not ā both committed and uncommitted changes are loaded into Shared Buffers because the WAL replay never checks CLOG at this stage.
- Before applying any change to a page, theĀ Startup ProcessĀ checks if that page was already updated before the crash (by checkingĀ
pd_lsnĀ on the page) ā if the page is already up to date it simply skips that WAL record and moves to the next one ā this makes replay safe to run multiple times. - When theĀ Startup ProcessĀ sees aĀ
COMMITĀ orĀABORTĀ record in the WAL, it immediately updates the CLOG file ā marking that transaction asĀCOMMITTEDĀ orĀABORTEDĀ so PostgreSQL knows the final outcome of that transaction. - When theĀ Startup ProcessĀ reaches the end of WAL files (the crash point), any transaction that had noĀ
COMMITĀ orĀABORTĀ record is considered as never completed ā the Startup ProcessĀ explicitly marks all those transactions asĀABORTEDĀ in CLOG right away before allowing any user to connect ā this is why you always seeĀABORTEDĀ and neverĀIN_PROGRESSĀ after a crash recovery. - Once WAL replay is done, a final checkpoint runs āĀ BGWriterĀ andĀ CheckpointerĀ take all the dirty pages sitting in Shared Buffers (including pages with uncommitted data) and write them permanently to the actual database files on disk ā thenĀ
pg_controlĀ is updated to say the database is healthy and users are allowed to connect. - Even though uncommitted data physically exists in the database files on disk, no user will ever see it ā because CLOG says those transactions areĀ
ABORTED, MVCC automatically hides those rows from every query ā and laterĀ VACUUMĀ comes along and physically cleans up those dead rows from the pages and frees up the space.
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you
Rajasekhar Amudala