How to corrupt your database (and how to deal with data corruption)
summary
Turning off fsync=off yields a measured 2.5x speedup but an OS crash can “toast” the cluster; enabling synchronous_commit instead gives safer performance tradeoffs. File-system backups without the backup_label or using pg_upgrade --link can start two servers on the same files and silently corrupt data. Emergency tools like pg_resetwal -f or pg_surgery can make the server start but usually introduce more corruption. Recommended recovery path is stop, save disk images, dump and restore from verified logical and physical backups (use tools like pgBackRest).
AI-written summary · about
chapters (18)
- 0:00Introduction to data corruption
- 1:09What is database corruption
- 2:33Bad hardware and software causes
- 3:38Disabling fsync for performance
- 4:20File system backup mistakes
- 7:56Using pg_resetwal dangerously
- 9:44Corrupting with pg_upgrade
- 11:49Modifying catalogs directly
- 13:57First steps after corruption
- 14:59Backup strategies for recovery
- 16:49Index corruption explained
- 18:34Silent data corruption cases
- 20:20Corrupted blocks and checksums
- 21:49Handling toast corruption
- 25:21Crashes during queries
- 27:53Missing or empty files
- 29:39Key takeaways and prevention
- 30:46Q&A session
related talks
20mDebugging Data Corruption in PostgreSQL
51mDatabase in Distress: Testing and Repairing Different Types of Database Corruption
59mPostgreSQL Don'ts and Dos
32mPostgreSQL The Hard Way
56mPostgreSQL Backup Strategies
32mDebugging Data Corruption in PostgreSQL: A Systematic Approach
45mRecovering from a damaged cluster
50mPostgreSQL Backup and Recovery Methods