🖼️ See the drawing + lab 🏠 Course home 🌿 Branch on GitHub ✏️ View source

🖼️ आकृती आणि labThe drawing + lab पूर्ण पानावर उघडा ↗Open full page ↗

🧯 धडा 09 — Backups आणि recovery: आगीपासून सुरक्षित प्रत

📍 तुम्ही इथे आहात: 18 पैकी धडा 09 · मागे: lesson-08-migrations · पुढे: lesson-10-scaling

📦 या ब्रँचमध्ये काय आहे

धडे 01–08, आणि इतर सर्व वचनांपेक्षा जास्त टिकणारे वचन: दुसऱ्या इमारतीत खोलीची एक प्रत, आग लागण्याआधी शाळा ठरवते ते दोन आकडे (RPO, RTO), point-in-time recovery, आणि कोणालाच नको असलेली पण सर्वांना हवी असलेली restore ची रंगीत तालीम.

🧒 5 वर्षांच्या मुलाला समजावल्यासारखे

धडा 04 ने सिद्ध केले की transaction errors खोडते. चुकांसाठी ते काहीच करत नाही: WHERE शिवायचा DELETE FROM students अगदी व्यवस्थित commit होतो. आग, पूर, चोरीला गेलेले laptops किंवा रविवारी बंद पडणारी disk यांच्यासाठीही नाही.

म्हणून दप्तरदार दुसऱ्या इमारतीत एक आगीपासून सुरक्षित प्रत 🧯 ठेवतो: दर रात्री प्रत्येक नोंदवहीची photocopy (एक full backup), आणि दिवसभर लिहिल्या जाणाऱ्या प्रत्येक pencil-आणि-शाईच्या ओळीची प्रत (write-ahead log). दोन्ही असल्यास शाळा खोली कोणत्याही मिनिटाला होती तशी पुन्हा बांधू शकते — point-in-time recovery.

काहीही जळण्याआधी दोन आकडे ठरवले जातात:

RPO — आपण किती गमावू शकतो? "पाच मिनिटांचा log" किंवा "एका दिवसाचा dump" — यावरून तुम्ही किती वेळा copy करता ते ठरते.
RTO — खोली किती वेळ बंद राहू शकते? यावरून restore किती जलद हवा ते ठरते — आणि ते stopwatch ने, एका रंगीत तालमीत मोजले जाते.

कारण रेकॉर्ड रूमचा सर्वात जुना नियम आहे: कोणीही restore न केलेला backup म्हणजे नुसती अफवा.

🗺️ आकृती

flowchart LR
    room["🗄️ school.db"]
    copy["🧯 school.backup.db<br/>conn.backup() — the fireproof copy"]
    boom["💥 DELETE … WHERE id = 5<br/>Rohan is gone (a mistake, not an error)"]
    restore["♻️ copy the backup back<br/>SELECT to prove it — Rohan returns"]
    room -->|"1 nightly + the log all day"| copy
    room -->|"2"| boom -->|"3 restore, measured (RTO)"| restore
    n["RPO: how much may we lose · RTO: how long may we be shut · drill quarterly"]

❓ काय

Full backup: सुसंगत प्रत (sqlite3 .backup, pg_dump, snapshots). Incremental / log shipping: शेवटच्या full copy पासूनचा WAL, एखाद्या क्षणापर्यंत पोहोचण्यासाठी replay केलेला.
RPO (recovery point objective): स्वीकारार्ह data नुकसान. RTO (recovery time objective): स्वीकारार्ह downtime. दोन्ही business निर्णय आहेत; backup schedule आणि restore tooling त्यानुसार ठरतात.
3-2-1: तीन प्रती, दोन माध्यमे, एक दुसऱ्या ठिकाणी — त्याच disk वरचा backup म्हणजे backup नाही; त्याच इमारतीतला backup आगीपासून सुरक्षित नाही (AWS L17: RDS snapshots दुसऱ्या region मध्ये).
Restore ची चाचणी घ्या: scratch database मध्ये restore करा, checks चालवा (SELECT COUNT(*), एक माहीत असलेली row), वेळ मोजा. दर महिन्याला automate करा.
Replication म्हणजे backup नव्हे: replica DELETE सुद्धा इमानदारीने replicate करते (धडा 10). चुका उलटवते ते point-in-time recovery.
Backups encrypt करा; ते कोण वाचू शकते यावर नियंत्रण ठेवा — ते म्हणजेच data आहे.

🤔 का

कारण इतर प्रत्येक धडा खोली अस्तित्वात आहे असे गृहीत धरतो. Durability (धडा 01) crashes पासून वाचवते, rm, ransomware, वाईट migration किंवा चांगल्या हेतूने केलेल्या DELETE पासून नाही. "आमच्याकडे backups आहेत" याला काही अर्थ आहे याचा एकमेव पुरावा म्हणजे restore ची रंगीत तालीम.

🔧 कसे (या repo मध्ये)

db/demo.py मधील backup() conn.backup() ने प्रत बनवते, Rohan ला delete करते, मग backup परत copy करते आणि तो परत आला हे सिद्ध करते. stopwatch तुम्ही जोडायचे आहे.

🧪 करून पाहा — रंगीत तालीम

python3 db/demo.py backup
python3 - <<'EOF'
import sqlite3, shutil, time, os
src = sqlite3.connect("db/school.db"); dst = sqlite3.connect("db/drill.db"); src.backup(dst); dst.close()   # 1) the copy
before = src.execute("SELECT COUNT(*) FROM grades").fetchone()[0]
src.execute("DELETE FROM grades"); src.commit()                                                              # 2) the mistake
t = time.perf_counter(); src.close(); shutil.copy("db/drill.db", "db/school.db")                             # 3) the restore
after = sqlite3.connect("db/school.db").execute("SELECT COUNT(*) FROM grades").fetchone()[0]
print(f"grades before {before}, after restore {after}, RTO {(time.perf_counter()-t)*1000:.1f} ms"); os.remove("db/drill.db")
EOF

✅ तपासा — तुम्हाला काय दिसायला हवे

backup आधी Rohan शिवायची विद्यार्थ्यांची यादी छापतो, मग पुन्हा त्याच्यासह. तुमची रंगीत तालीम grades before 10, after restore 10 आणि milliseconds मध्ये एक RTO छापते — तुम्ही restore गृहीत धरण्याऐवजी तो मोजला.

🏁 तुम्ही आत्ताच काय सिद्ध केले

transaction उलटवू शकत नाही अशी चूक तुम्ही चाचणी घेतलेल्या प्रतीने उलटवली — आणि तुम्हाला तुमचा RPO (एक प्रत आधी) आणि RTO (stopwatch) माहीत आहे.

⚠️ नेहमीच्या चुका

खोलीच्याच disk वर किंवा त्याच इमारतीत backups
कधीच restore न करणे — "चालला" असे वाटलेला backup जो रिकामा होता, हरवलेल्या key ने encrypt होता, किंवा तीन schemas जुना होता
replicas म्हणजे backups असे मानणे — ते चूक milliseconds मध्ये replicate करतात
RPO/RTO incident नंतर ठरवणे
outage मध्ये कोणालाच वाचता न येणारे backups (credentials नेमक्या बंद पडलेल्या गोष्टीत)

🏭 प्रत्यक्ष वापरात हे का महत्त्वाचे: आपल्या database च्या सर्वात वाईट दिवसातून वाचलेल्या companies म्हणजे ज्यांच्या calendar वर restore ची रंगीत तालीम होती. Managed services (RDS, धडा 10) copy करणे automate करतात; रंगीत तालीम आणि ते दोन आकडे अजूनही तुमचेच आहेत.

⏭️ पुढे

जास्त विद्यार्थी, जास्त clerks, जास्त प्रश्न: scaling — pools, caches, replicas, partitions, आणि teams प्रत्यक्षात पाळतात तो क्रम.

git checkout lesson-10-scaling

🧯 Lesson 09 — Backups & recovery: the fireproof copy

📍 You are here: Lesson 09 of 18 · Previous: lesson-08-migrations · Next: lesson-10-scaling

📦 What's in this branch

Lessons 01–08, plus the promise that outlives every other: a copy of the room in another building, the two numbers the school agrees on before the fire (RPO, RTO), point-in-time recovery, and the restore drill nobody wants and everybody needs.

🧒 Explain like I'm 5

Lesson 04 proved a transaction erases errors. It does nothing for mistakes: DELETE FROM students without a WHERE commits perfectly. Nor for fires, floods, stolen laptops or a disk that dies on a Sunday.

So the archivist keeps a fireproof copy 🧯 in another building: every night a photocopy of every register (a full backup), and all day long a copy of every pencil-and-ink line as it is written (the write-ahead log). With both, the school can rebuild the room as it was at any minute — point-in-time recovery.

Two numbers are agreed before anything burns:

RPO — how much may we lose? "Five minutes of log" or "one day of dump" — it decides how often you copy.
RTO — how long may the room be shut? It decides how fast the restore must be — and it is measured with a stopwatch, in a drill.

Because the oldest rule of the record room is: a backup nobody has restored is a rumour.

🗺️ Diagram

flowchart LR
    room["🗄️ school.db"]
    copy["🧯 school.backup.db<br/>conn.backup() — the fireproof copy"]
    boom["💥 DELETE … WHERE id = 5<br/>Rohan is gone (a mistake, not an error)"]
    restore["♻️ copy the backup back<br/>SELECT to prove it — Rohan returns"]
    room -->|"1 nightly + the log all day"| copy
    room -->|"2"| boom -->|"3 restore, measured (RTO)"| restore
    n["RPO: how much may we lose · RTO: how long may we be shut · drill quarterly"]

❓ What

Full backup: a consistent copy (sqlite3 .backup, pg_dump, snapshots). Incremental / log shipping: the WAL since the last full copy, replayed to reach a point in time.
RPO (recovery point objective): acceptable data loss. RTO (recovery time objective): acceptable downtime. Both are business decisions; the backup schedule and the restore tooling follow.
3-2-1: three copies, two media, one off-site — a backup on the same disk is not a backup; a backup in the same building is not fire- proof (AWS L17: RDS snapshots to another region).
Test the restore: restore into a scratch database, run checks (SELECT COUNT(*), a known row), time it. Automate it monthly.
Replication is not backup: a replica faithfully replicates the DELETE (lesson 10). Point-in-time recovery is what undoes mistakes.
Encrypt backups; control who can read them — they are the data.

🤔 Why

Because every other lesson assumes the room exists. Durability (lesson 01) protects against crashes, not against rm, ransomware, a bad migration or a well-meaning DELETE. The restore drill is the only proof that "we have backups" means anything.

🔧 How (in this repo)

backup() in db/demo.py makes a copy with conn.backup(), deletes Rohan, then copies the backup back and proves he returned. The stopwatch is yours to add.

🧪 Try it — the drill

python3 db/demo.py backup
python3 - <<'EOF'
import sqlite3, shutil, time, os
src = sqlite3.connect("db/school.db"); dst = sqlite3.connect("db/drill.db"); src.backup(dst); dst.close()   # 1) the copy
before = src.execute("SELECT COUNT(*) FROM grades").fetchone()[0]
src.execute("DELETE FROM grades"); src.commit()                                                              # 2) the mistake
t = time.perf_counter(); src.close(); shutil.copy("db/drill.db", "db/school.db")                             # 3) the restore
after = sqlite3.connect("db/school.db").execute("SELECT COUNT(*) FROM grades").fetchone()[0]
print(f"grades before {before}, after restore {after}, RTO {(time.perf_counter()-t)*1000:.1f} ms"); os.remove("db/drill.db")
EOF

✅ Verify — what you should see

backup prints the student list without Rohan, then with him again. Your drill prints grades before 10, after restore 10 and an RTO in milliseconds — you measured a restore instead of assuming one.

🏁 What you just proved

A mistake that a transaction cannot undo was undone by a copy you had tested — and you know your RPO (one copy ago) and RTO (the stopwatch).

⚠️ Common mistakes

backups on the same disk or the same building as the room
never restoring — the backup that "worked" but was empty, encrypted with a lost key, or three schemas old
believing replicas are backups — they replicate the mistake in milliseconds
agreeing RPO/RTO after the incident
backups nobody can read in an outage (credentials in the thing that is down)

🏭 Why this matters in production: the companies that survived their worst database day are the ones with a restore drill on the calendar. Managed services (RDS, lesson 10) automate the copying; the drill and the two numbers are still yours.

⏭️ Next

More students, more clerks, more questions: scaling — pools, caches, replicas, partitions, and the order teams actually follow.

git checkout lesson-10-scaling

← Previousmigrations Next →scaling

This page is the lesson's README from the lesson-09-backups-recovery branch, shown here so the whole School stays on one site. Code files open on GitHub at the same branch.