We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design Masterclass · Consistency Models
Free preview: the first 2 parts of this 8-part lesson.
Already bought the course? Sign in to read the whole lesson.
this lesson is part of
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design courseread the rest of this lesson in
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design coursein Consistency Models
This lesson is part of the full System Design Masterclass. Unlock it along with 769 other lessons for a one-time payment.
One-time payment by card or PayPal
Get Lifetime AccessOne-time payment: UPI, card or netbanking
Get Lifetime Access18 Foundation lessons are free, no payment needed. Try them now
It is easy and good
“It is really nice and good Please join the course”
At 3:47 PM on a Tuesday, someone runs DELETE FROM users without a WHERE clause. 2.3 million records vanish in 400 milliseconds. The application starts throwing 500 errors. Slack explodes.
PITR lets you restore the database to 3:46 PM, one minute before the disaster. You lose 60 seconds of data instead of everything. The difference between a 1-minute RPO and "restore last night's backup and lose a full day" is the difference between an incident and a career-ending event.
PostgreSQL, MySQL, Server, and every managed cloud database (RDS, Cloud SQL, Neon, Supabase) support PITR. It is your last line of defense against human error, application bugs, and ransomware.
PITR requires exactly two things running in parallel:
1. Periodic base backups. A full copy of the data files taken daily or weekly. PostgreSQL uses pg_basebackup, which copies every data file and records the WAL position (LSN) where the backup started. This is your starting point for any recovery.
2. Continuous WAL archiving. Every completed WAL segment (default 16 MB each) is copied to separate durable storage the moment it fills. PostgreSQL's archive_command or archive_library handles this. The WAL stream is your change log.
Recovery process:
recovery_target_time in postgresql.conf (or recovery.conf in older versions)The RPO depends on how frequently WAL segments are archived. With continuous archiving, the RPO is one WAL segment's worth of data, typically a few seconds to a few minutes depending on write volume. With WAL streaming , the RPO drops to near-zero.