גיבוי ושחזור ב-SQLite: .backup, VACUUM INTO וה-API המקוון

למה אי אפשר פשוט לעשות cp לקובץ

מסד נתונים של SQLite הוא קובץ יחיד, ולכן מפתה לגבות אותו בהעתקת קובץ רגילה. לפעמים זה עובד. הרבה פעמים לא.

שני דברים יכולים להשתבש:

• חיבור אחר נמצא באמצע כתיבה כשאתם מעתיקים. קובץ היעד מקבל טרנזקציה שהוחלה רק בחלקה, והוא פגום כשפותחים אותו.
• מסד הנתונים נמצא במצב WAL (ברירת המחדל ברוב האפליקציות המודרניות). שינויים אחרונים יושבים בקובץ נפרד, database.db-wal. העתקתם רק את הקובץ הראשי? איבדתם נתונים בשקט.

SQLite נותנת לכם כלים מתאימים לזה. הם מטפלים בנעילות, בתוכן ה-WAL ובכותבים מקבילים בלי הפתעות. השתמשו בהם במקום ב-cp.

הפקודה .backup

הדרך המהירה ביותר לגבות מסד נתונים מה-CLI היא פקודת הנקודה .backup:

sqlite3 app.db
sqlite> .backup backup.db
sqlite> .quit

זה כותב עותק מלא של app.db ל-backup.db. זה עובד גם אם תהליכים אחרים קוראים או כותבים למסד הנתונים: ה-API לגיבוי לוקח סדרה של נעילות קטנות במקום נעילה אחת גדולה, מעתיק דפים בהדרגה, ומעתיק שוב דפים שהשתנו במהלך ההעתקה.

הפלט הוא מסד נתונים של SQLite שאפשר להשתמש בו במלואו. פתחו אותו כמו כל מסד נתונים אחר:

sqlite3 backup.db
sqlite> .tables

אפשר גם לעשות את כל זה בפקודת shell אחת, וככה נראות רוב משימות ה-cron בסוף:

sqlite3 app.db ".backup '/var/backups/app-$(date +%Y%m%d).db'"

קובץ אחד נכנס, קובץ אחד יוצא. בלי סבב של dump ו-restore, בלי פענוח SQL: רק דפים שמועתקים בשכבת האחסון.

VACUUM INTO לעותק דחוס

VACUUM INTO הוא כלי קרוב אבל שונה. הוא כותב לקובץ חדש עותק של מסד הנתונים שנבנה מחדש:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('first'), ('second'), ('third');
DELETE FROM notes WHERE id = 2;

VACUUM INTO 'notes-backup.db';

SELECT 'wrote backup' AS status;

התוצאה היא אותו מסד נתונים לוגי, אבל כתוב מחדש מאפס: כל דף ארוז בצפיפות, בלי פרגמנטציה, בלי דפים פנויים ששרדו משורות שנמחקו. כך קובץ הגיבוי קטן ככל האפשר.

מתי לבחור במה:

• .backup: גיבויים שוטפים ותכופים. מהיר יותר, מסתדר טוב עם כותבים מקבילים, נאמן ברמת הבייט.
• VACUUM INTO: תמונות מצב תקופתיות שבהן אתם רוצים גם קובץ מסודר בגודל מינימלי. איטי יותר כי הוא כותב הכול מחדש, והוא לוקח נעילת כתיבה על המקור לכל משך הפעולה.

שניהם מפיקים קובץ .db תקין שאפשר לפתוח מיד.

ה-API לגיבוי מקוון מתוך קוד של אפליקציה

בתוך אפליקציה, לא קוראים ל-sqlite3 דרך ה-shell. משתמשים ב-API לגיבוי מקוון שהדרייבר שלכם חושף. בספרייה הסטנדרטית של Python, sqlite3, זה Connection.backup:

import sqlite3

source = sqlite3.connect("app.db")
dest = sqlite3.connect("backup.db")

with dest:
    source.backup(dest)

source.close()
dest.close()

המתודה backup מעתיקה דפים מ-source אל dest בזמן שחיבורים אחרים ממשיכים לעבוד. אפשר גם להעביר pages= כדי להעתיק במנות ו-progress= כדי לקבל callback: שימושי למסדי נתונים גדולים שבהם רוצים להאט את ההעתקה או להציג התקדמות.

רוב הדרייברים בשפות אחרות חושפים את אותו API של C (sqlite3_backup_init, _step, _finish) בשם דומה. המבנה תמיד זהה: פותחים מקור, פותחים יעד, עוברים על הדפים, מסיימים.

גיבויים בזמן שמסד הנתונים בשימוש

כאן SQLite זוהרת בשקט. גם .backup וגם ה-API לגיבוי מקוון תוכננו לגיבויים חמים: מסד הנתונים המקורי יכול להיות פתוח ופעיל כל הזמן.

מה קורה בפועל:

1. הגיבוי לוקח נעילה משותפת ומתחיל להעתיק דפים.
2. אם כותב משנה דף שעוד לא הועתק, הגיבוי שם לב וקורא אותו שוב.
3. ההעתקה מסתיימת כשכל הדפים עקביים.

לא צריך לעצור את האפליקציה, לנתק חיבורים או לתזמן זמן השבתה. במסד נתונים עמוס הגיבוי עשוי לקחת כמה סבבים נוספים עד שיתייצב, אבל הוא יתייצב. קובץ היעד שתקבלו מייצג תמונת מצב עקבית של נקודת זמן אחת.

דבר אחד שכדאי לדעת: אם אתם משתמשים במצב WAL, הריצו מדי פעם PRAGMA wal_checkpoint(TRUNCATE); כדי שקובץ ה-WAL לא יגדל בלי גבול. הגיבוי עצמו מטפל נכון ב-WAL: זו רק היגיינת WAL כללית.

שחזור מגיבוי

שחזור מסד נתונים של SQLite משעמם במיוחד, וזה בדיוק העניין. קובץ הגיבוי הוא מסד נתונים. כדי להשתמש בו, פשוט פתחו אותו:

sqlite3 backup.db
sqlite> SELECT COUNT(*) FROM notes;

כדי לשחזר על גבי מסד נתונים חי, למשל כדי להתאושש מאובדן נתונים, הרצף הבטוח הוא:

1. עצרו כל תהליך שפתח את מסד הנתונים.
2. מחקו את הקבצים הקיימים app.db, app.db-wal ו-app.db-shm. קבצי WAL/SHM שנשארו ממסד הנתונים הישן יבלבלו את SQLite כשיוצמדו לקובץ הראשי המשוחזר.
3. העתיקו את הגיבוי למקומו: cp backup.db app.db.
4. הפעילו מחדש את האפליקציה.

הקבצים -wal ו--shm חשובים. אם תדלגו על שלב 2, SQLite עלולה לנסות להחיל WAL ישן על הקובץ הראשי המשוחזר, ותקבלו פגיעה בקובץ או נתונים מעורבבים באופן מוזר.

מתוך ה-CLI יש גם פקודת .restore, תמונת המראה של .backup:

sqlite3 app.db
sqlite> .restore backup.db
sqlite> .quit

זה דורס את התוכן של מסד הנתונים המחובר בתוכן של backup.db. זה משתמש באותו API לגיבוי מקוון, בכיוון ההפוך.

.dump הוא כלי אחר

תראו אזכורים של .dump במדריכים ישנים. זה לא גיבוי באותו מובן: הוא מפיק קובץ טקסט של SQL עם פקודות CREATE ו-INSERT:

sqlite3 app.db .dump > app.sql

כדי לשחזר, מריצים שוב את ה-SQL:

sqlite3 new.db < app.sql

זה שימושי למעבר בין גרסאות של SQLite, להשוואת סכמות ב-git, או להעברת נתונים למנוע מסד נתונים אחר. זה איטי יותר, גדול יותר ומאבד יותר מידע מ-.backup (collations מותאמים, עמודות מחושבות וחלק מה-pragmas עשויים לדרוש תשומת לב נוספת). לגיבוי אמיתי של מסד נתונים פעיל, העדיפו .backup או VACUUM INTO.

שגרת גיבוי הגיונית

לרוב האפליקציות, השילוב הזה עובד טוב:

• הרצה מתוזמנת של .backup: כל שעה, כל יום, לפי כמה נתונים אתם מוכנים להפסיד. זול, מהיר, חם.
• VACUUM INTO שבועי לנתיב נפרד. תופס סטיות, נותן תמונת מצב דחוסה, ומפעיל נתיב קוד אחר.
• מדיניות שמירה: שמרו את N הגיבויים היומיים האחרונים ואת M השבועיים האחרונים. מסדי נתונים של SQLite נדחסים היטב, כך ששווה להריץ gzip backup.db אחר כך.
• מדי פעם שחזרו אחד מהם והריצו עליו כמה שאילתות. גיבוי שלא נבדק הוא תקווה, לא גיבוי.

# יומי, ב-cron:
sqlite3 /var/lib/app/app.db ".backup '/var/backups/app-$(date +%F).db'"
gzip "/var/backups/app-$(date +%F).db"

# שבועי:
sqlite3 /var/lib/app/app.db "VACUUM INTO '/var/backups/app-weekly-$(date +%F).db'"

שתי הפקודות בטוחות להרצה בזמן שהאפליקציה משרתת בקשות.

הבא בתור: הגדרות PRAGMA

גיבויים הם עניין תפעולי אחד; כוונון ההתנהגות בזמן ריצה הוא עניין אחר. SQLite חושפת את הכפתורים שלה דרך פקודות PRAGMA: מצב journal, רמת synchronous, גודל מטמון, אכיפת מפתחות זרים. העמוד הבא עובר על אלה ששווה להכיר.