Резервное копирование и восстановление SQLite: .backup, VACUUM INTO и Online API

Почему нельзя просто cp файла

База SQLite — это один файл, и от этого так и тянет сделать бэкап обычным копированием. Иногда срабатывает. Чаще — нет.

Сломаться может в двух местах:

• Другое соединение пишет посреди транзакции, когда вы копируете. На выходе — файл с наполовину применённой транзакцией, битый при открытии.
• База в режиме WAL (а это дефолт для большинства современных приложений). Свежие изменения лежат в отдельном файле database.db-wal. Скопировали только основной файл — и тихо потеряли часть данных.

В SQLite для этого есть нормальные инструменты. Они правильно обрабатывают блокировки, WAL и параллельную запись. Берите их вместо cp.

Команда .backup

Самый быстрый способ снять бэкап из CLI — dot-команда .backup:

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

Эта команда пишет полноценную копию app.db в backup.db. Работает даже под активным чтением и записью со стороны других процессов: backup 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 — периодические снимки, когда заодно нужен аккуратный, минимально возможный по размеру файл. Медленнее, потому что переписывает всё, и берёт write-лок на исходник на всё время операции.

В обоих случаях получается готовый .db-файл, который можно открывать сразу.

Online backup API из приложения

В коде приложения никто не запускает sqlite3 через шелл. Вы используете online backup 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=, чтобы получать колбэк — пригодится на больших базах, где хочется ограничивать скорость или показывать прогресс.

В большинстве драйверов в других языках доступен тот же C API (sqlite3_backup_init, _step, _finish) под похожим именем. Форма всегда одна: открыть исходник, открыть приёмник, проитерировать страницы, закрыть.

Бэкап под живой нагрузкой

Вот тут SQLite тихо хорош. И .backup, и online backup API заточены под hot-бэкапы — исходная база может быть открыта и работать всё время операции.

Что происходит на самом деле:

1. Бэкап берёт shared-лок и начинает копировать страницы.
2. Если писатель меняет страницу, которую ещё не успели скопировать, бэкап это замечает и перечитывает её.
3. Копия завершается, когда каждая страница согласована.

Не нужно ни останавливать приложение, ни выгонять соединения, ни планировать downtime. На нагруженной базе бэкапу может потребоваться лишний цикл-другой, чтобы сойтись, но он сойдётся. На выходе — целостный снимок на конкретный момент времени.

Один момент: если вы в 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:

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

Она перезаписывает содержимое подключённой базы содержимым backup.db. Под капотом — тот же online backup API, только в обратную сторону.

.dump — это про другое

В старых руководствах часто встречается .dump. Это не бэкап в том же смысле — он выдаёт SQL-текст с CREATE и INSERT:

sqlite3 app.db .dump > app.sql

Чтобы восстановить — проигрываете этот SQL:

sqlite3 new.db < app.sql

Полезно, когда нужно перейти между версиями SQLite, отдиффить схему в git или перевезти данные в другой движок. Это медленнее, объёмнее и более «лоссовый» вариант, чем .backup (для пользовательских коллаций, генерируемых столбцов и некоторых прагм нужны дополнительные предосторожности). Для настоящего бэкапа рабочей базы используйте .backup или VACUUM INTO.

Адекватный регламент бэкапов

Для большинства приложений хорошо работает такая схема:

• Регулярный .backup по расписанию — раз в час, раз в сутки, как позволяет ваш RPO. Дёшево, быстро, под нагрузкой.
• Раз в неделю — 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-выражения: режим журнала, уровень синхронизации, размер кэша, контроль внешних ключей. На следующей странице пройдёмся по тем, что стоит знать.