Kopia zapasowa i przywracanie SQLite: .backup, VACUUM INTO i Online API

Dlaczego nie wystarczy cp na pliku

Baza SQLite to jeden plik, więc kusi, żeby robić jej kopię zwykłym kopiowaniem pliku. Czasem to działa. Często nie.

Dwie rzeczy mogą pójść źle:

• Inne połączenie jest w trakcie zapisu, gdy kopiujesz. Plik docelowy zawiera wtedy transakcję zastosowaną w połowie i przy otwarciu okazuje się uszkodzony.
• Baza działa w trybie WAL (domyślnym w większości współczesnych aplikacji). Najnowsze zmiany leżą w osobnym pliku database.db-wal. Skopiujesz tylko główny plik i po cichu stracisz dane.

SQLite daje do tego właściwe narzędzia. Obsługują blokady, zawartość WAL i równoległe zapisy bez niespodzianek. Sięgaj po nie zamiast po cp.

Polecenie .backup

Najszybszy sposób na kopię zapasową bazy z CLI to polecenie z kropką .backup:

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

To zapisuje kompletną kopię app.db do backup.db. Działa nawet wtedy, gdy inne procesy czytają lub zapisują bazę: Backup API zakłada serię małych blokad zamiast jednej dużej, kopiuje strony przyrostowo i ponawia kopiowanie stron zmienionych w trakcie.

Wynik to w pełni użyteczna baza SQLite. Otwórz ją jak każdą inną:

sqlite3 backup.db
sqlite> .tables

Całość możesz też zrobić jednym poleceniem powłoki, i tak zwykle wyglądają zadania cron:

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

Jeden plik na wejściu, jeden na wyjściu. Bez rundy dump/restore i bez parsowania SQL: po prostu strony kopiowane na poziomie warstwy przechowywania.

VACUUM INTO dla skompaktowanej kopii

VACUUM INTO to pokrewne, ale inne narzędzie. Zapisuje świeżo zbudowaną kopię bazy do nowego pliku:

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;

Wynik to ta sama baza logiczna, ale przepisana od zera: każda strona ciasno upakowana, bez fragmentacji i bez wolnych stron po usuniętych wierszach. Dzięki temu plik kopii jest jak najmniejszy.

Kiedy co wybrać:

• .backup: rutynowe, częste kopie. Szybsze, dobrze współpracuje z równoległymi zapisami, wierne bajt po bajcie.
• VACUUM INTO: okresowe migawki, gdy chcesz też uporządkowany plik o minimalnym rozmiarze. Wolniejsze, bo przepisuje wszystko, i przez cały czas trzyma blokadę zapisu na źródle.

Oba tworzą poprawny plik .db, który możesz od razu otworzyć.

Online Backup API w kodzie aplikacji

W aplikacji nie wywołujesz sqlite3 z powłoki. Używasz Online Backup API udostępnianego przez sterownik. W module sqlite3 z biblioteki standardowej Pythona to Connection.backup:

import sqlite3

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

with dest:
    source.backup(dest)

source.close()
dest.close()

Metoda backup kopiuje strony z source do dest, podczas gdy inne połączenia dalej pracują. Możesz też przekazać pages=, aby kopiować w porcjach, i progress=, aby dostawać callback. To przydatne przy dużych bazach, gdy chcesz ograniczać tempo kopiowania albo pokazywać postęp.

Większość sterowników w innych językach udostępnia to samo API C (sqlite3_backup_init, _step, _finish) pod podobną nazwą. Schemat jest zawsze ten sam: otwórz źródło, otwórz cel, przechodź po stronach, zakończ.

Kopie zapasowe podczas pracy bazy

Tu SQLite po cichu błyszczy. Zarówno .backup, jak i Online Backup API są zaprojektowane do kopii na gorąco: baza źródłowa może być cały czas otwarta i aktywna.

Co się właściwie dzieje:

1. Kopia zakłada blokadę współdzieloną i zaczyna kopiować strony.
2. Jeśli zapisujący zmieni stronę, która nie została jeszcze skopiowana, kopia to zauważa i czyta ją ponownie.
3. Kopiowanie kończy się, gdy każda strona jest spójna.

Nie musisz zatrzymywać aplikacji, rozłączać połączeń ani planować przestoju. Na obciążonej bazie kopia może potrzebować kilku dodatkowych cykli, zanim się ustabilizuje, ale w końcu się ustabilizuje. Plik docelowy przedstawia spójną migawkę z jednego momentu.

Jedna rzecz, o której warto wiedzieć: w trybie WAL od czasu do czasu uruchamiaj PRAGMA wal_checkpoint(TRUNCATE);, żeby plik WAL nie rósł bez końca. Sama kopia poprawnie obsługuje WAL, to po prostu ogólna higiena pracy z WAL.

Przywracanie z kopii zapasowej

Przywracanie bazy SQLite jest wyjątkowo nudne i o to właśnie chodzi. Plik kopii jest bazą. Aby go użyć, po prostu go otwórz:

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

Aby przywrócić kopię w miejsce działającej bazy, na przykład po utracie danych, bezpieczna kolejność wygląda tak:

1. Zatrzymaj każdy proces, który ma bazę otwartą.
2. Usuń istniejące pliki app.db, app.db-wal i app.db-shm. Pozostałe pliki WAL/SHM ze starej bazy zmylą SQLite, gdy trafią w parę z przywróconym plikiem głównym.
3. Skopiuj kopię na miejsce: cp backup.db app.db.
4. Uruchom ponownie aplikację.

Pliki -wal i -shm mają znaczenie. Jeśli pominiesz krok 2, SQLite może spróbować nałożyć nieaktualny WAL na przywrócony plik główny i dostaniesz uszkodzenie albo dziwnie wymieszane dane.

W CLI jest też polecenie .restore, lustrzane odbicie .backup:

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

Nadpisuje ono zawartość połączonej bazy zawartością backup.db. Używa tego samego Online Backup API, tylko w odwrotnym kierunku.

.dump to inne narzędzie

W starszych poradnikach zobaczysz odwołania do .dump. To nie jest kopia zapasowa w tym samym sensie: tworzy plik tekstowy SQL z instrukcjami CREATE i INSERT:

sqlite3 app.db .dump > app.sql

Aby przywrócić dane, odtwarzasz ten SQL:

sqlite3 new.db < app.sql

Przydaje się to przy migracji między wersjami SQLite, porównywaniu schematów w git albo przenoszeniu danych do innego silnika bazy. Jest wolniejsze, większe i mniej wierne niż .backup (własne collation, kolumny generowane i niektóre pragmy mogą wymagać dodatkowej uwagi). Do prawdziwej kopii działającej bazy wybieraj .backup lub VACUUM INTO.

Rozsądny harmonogram kopii zapasowych

W większości aplikacji dobrze sprawdza się takie połączenie:

• Zaplanowane uruchamianie .backup co godzinę, codziennie albo tak często, jak wymaga tego twoja tolerancja na utratę danych. Tanie, szybkie, na gorąco.
• Cotygodniowe VACUUM INTO do osobnej ścieżki. Wyłapuje rozbieżności, daje skompaktowaną migawkę i sprawdza inną ścieżkę kodu.
• Polityka retencji: trzymaj ostatnie N kopii dziennych i ostatnie M tygodniowych. Bazy SQLite dobrze się kompresują, więc warto potem zrobić gzip backup.db.
• Od czasu do czasu przywróć jedną kopię i wykonaj na niej kilka zapytań. Nieprzetestowana kopia zapasowa to nadzieja, a nie kopia zapasowa.

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

# Co tydzień:
sqlite3 /var/lib/app/app.db "VACUUM INTO '/var/backups/app-weekly-$(date +%F).db'"

Oba polecenia można bezpiecznie uruchamiać, gdy aplikacja obsługuje żądania.

Dalej: ustawienia PRAGMA

Kopie zapasowe to jedna sprawa operacyjna, a dostrajanie zachowania w czasie działania to druga. SQLite udostępnia swoje pokrętła przez instrukcje PRAGMA: tryb dziennika, poziom synchronizacji, rozmiar pamięci podręcznej, egzekwowanie kluczy obcych. Następna strona omawia te, które warto znać.