SQLite Online Backup API: резервное копирование SQLite без блокировок

Материал основан на разборе sqlite.org. Ниже — главное и практические шаги, которые можно быстро применить в работе.

Как работает SQLite Online Backup API

Исторически резервные копии баз данных SQLite создавались следующим методом:

Установить разделяемую блокировку (shared lock) на файл базы данных с помощью SQLite API — например, через инструмент командной строки.

Скопировать файл базы данных с помощью внешнего инструмента — например, утилиты cp в Unix или команды copy в DOS.

Снять разделяемую блокировку с файла базы данных, полученную на первом шаге.

Этот метод обычно работает быстро в большинстве случаев, но имеет и значительные недостатки.

Любые клиенты базы данных, желающие выполнить запись в файл во время создания резервной копии, вынуждены ждать снятия разделяемой блокировки.

Метод нельзя использовать для копирования данных в базы данных в памяти или из них.

Если во время копирования файла произойдёт сбой питания или сбой операционной системы, резервная копия может оказаться повреждённой после восстановления системы.

На практике это означает простую вещь: чем дольше вы держите shared lock, тем заметнее проседает работа приложения под записью. Для локального desktop-инструмента это может быть терпимо, а для фонового backup в мобильном приложении или встроенном сервисе уже становится заметной проблемой.

Если база используется активно, резервное копирование обычным копированием файла начинает конкурировать с пользовательскими действиями и делает поведение системы менее предсказуемым.

Online Backup API решает указанные проблемы, позволяя копировать содержимое одной базы данных в другую. Передача данных происходит инкрементально, что исключает необходимость блокировки исходной базы данных на весь период копирования. Блокировка требуется лишь на краткие моменты, когда осуществляется считывание данных, что даёт возможность другим пользователям продолжать работу с базой данных без значительных задержек.

По окончании резервного копирования целевая база данных становится точной копией исходной базы данных на момент начала процесса, фактически представляя собой её снимок (snapshot) базы данных.

Оставшаяся часть этой страницы содержит два примера на языке C, иллюстрирующих типичные варианты использования API, и их обсуждение. Чтение этих примеров не заменяет чтения документации по API.

Альтернативные методы резервного копирования SQLite

Online Backup API — это исходный метод создания резервных копий работающих баз данных SQLite. Другие, более современные методы достижения того же результата включают:

Команда VACUUM INTO создаёт дефрагментированную копию (VACUUM INTO) работающей базы данных SQLite в отдельный файл.

Программа sqlite3_rsync создаёт копию работающей базы данных SQLite на удалённой системе или с неё, используя SSH-соединение.

Выбор между этими подходами зависит от задачи, а не от “правильности” инструмента. Если вам нужна локальная резервная копия в отдельный файл и заодно полезно уменьшить фрагментацию, VACUUM INTO часто оказывается самым простым вариантом. Если резервная копия должна уходить на другой хост по сети, логично смотреть в сторону sqlite3_rsync.

А вот Online Backup API особенно удобен, когда резервное копирование нужно встроить прямо в код приложения: вы можете запускать его из своего C-кода, дозировать копирование страниц и встроить прогресс в интерфейс или в фоновый job runner. Для production это часто важнее, чем “красота” одного отдельного SQL-вызова.

Когда этот подход оправдан в реальном проекте

На практике я выбираю этот механизм в трёх сценариях. Первый сценарий — встроенное приложение или локальный сервис, где база данных используется постоянно, а резервную копию нужно снимать без долгой остановки записи. Второй сценарий — настольная программа, в которой важно дать пользователю кнопку “создать резервную копию сейчас” и при этом не замораживать интерфейс на всё время операции.

Третий сценарий — фоновая задача обслуживания, где резервное копирование должно идти небольшими порциями и не мешать основному рабочему потоку.

Есть и простой критерий выбора. Если вам нужен предсказуемый прикладной код с контролем прогресса, обработкой ошибок и возможностью встроить таймауты или ограничения по числу шагов, этот вариант подходит лучше, чем грубое копирование файла. Если же задача сводится к одноразовому обслуживанию небольшой локальной базы без параллельных записей, можно выбрать более простой путь.

Иными словами, смысл Online Backup API раскрывается там, где важно не только получить копию, но и сделать это аккуратно с точки зрения поведения приложения под нагрузкой.

Пример 1: резервная копия базы данных SQLite в памяти

/* ** This function is used to load the contents of a database file on disk ** into the "main" database of open database connection pInMemory, or ** to save the current contents of the database opened by pInMemory into ** a database file on disk. pInMemory is probably an in-memory database, ** but this function will also work fine if it is not. ** ** Parameter zFilename points to a nul-terminated string containing the ** name of the database file on disk to load from or save to. If parameter ** isSave is non-zero, then the contents of the file zFilename are ** overwritten with the contents of the database opened by pInMemory. If ** parameter isSave is zero, then the contents of the database opened by ** pInMemory are replaced by data loaded from the file zFilename. ** ** If the operation is successful, SQLITE_OK is returned. Otherwise, if ** an error occurs, an SQLite error code is returned. */ int loadOrSaveDb( sqlite3 *pInMemory, const char *zFilename, int isSave){ int rc; /* Function return code */ sqlite3 *pFile; /* Database connection opened on zFilename */ sqlite3_backup *pBackup; /* Backup object used to copy data */ sqlite3 *pTo; /* Database to copy to (pFile or pInMemory) */ sqlite3 *pFrom; /* Database to copy from (pFile or pInMemory) */ /* Open the database file identified by zFilename. Exit early if this fails ** for any reason. */ rc = sqlite3_open(zFilename, &pFile); if( rc==SQLITE_OK ){ pFrom = (isSave ? pInMemory : pFile); pTo = (isSave ? pFile : pInMemory); pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main"); if( pBackup ){ (void)sqlite3_backup_step(pBackup, -1); (void)sqlite3_backup_finish(pBackup); } rc = sqlite3_errcode(pTo); } (void)sqlite3_close(pFile); return rc; }

Приведённая выше функция на языке C демонстрирует один из простейших и наиболее распространённых вариантов использования backup API: загрузку и сохранение содержимого базы данных в памяти в файл на диске. Именно с этого примера я рекомендую начинать знакомство с API, поскольку он наглядно показывает минимально необходимую последовательность вызовов. В данном примере backup API используется следующим образом:

Функция sqlite3_backup_init() вызывается для создания объекта sqlite3_backup, предназначенного для копирования данных между двумя базами данных — либо из файла в базу данных в памяти, либо наоборот.

Функция sqlite3_backup_step() вызывается с параметром -1 для копирования всей исходной базы данных в целевую за один проход.

Функция sqlite3_backup_finish() вызывается для освобождения ресурсов, выделенных функцией sqlite3_backup_init().

Обработка ошибок

Если ошибка возникает в любой из трёх основных процедур backup API, код ошибки и сообщение сохраняются в дескрипторе соединения с целевой базой данных. Кроме того, если функция sqlite3_backup_step() сталкивается с ошибкой, код ошибки возвращается как самим вызовом sqlite3_backup_step(), так и последующим вызовом sqlite3_backup_finish().

Таким образом, вызов sqlite3_backup_finish() не перезаписывает код ошибки, сохранённый в дескрипторе соединения с целевой базой данных функцией sqlite3_backup_step(). Эта особенность используется в примере кода для сокращения объёма необходимой обработки ошибок.

Возвращаемые значения вызовов sqlite3_backup_step() и sqlite3_backup_finish() игнорируются, а код ошибки, указывающий на успех или неудачу операции копирования, впоследствии извлекается из дескриптора соединения с целевой базой данных.

Возможные улучшения

Реализацию этой функции можно улучшить как минимум двумя способами:

Можно обработать ситуацию, когда не удаётся получить блокировку файла базы данных zFilename (ошибка SQLITE_BUSY).

Можно лучше обработать случаи, когда размеры страниц баз данных pInMemory и zFilename различаются.

Поскольку база данных zFilename является файлом на диске, к ней может обращаться внешний процесс. Это означает, что когда вызов sqlite3_backup_step() пытается прочитать данные из неё или записать данные в неё, он может не получить необходимую блокировку файла. Если это происходит, данная реализация завершится с ошибкой, немедленно вернув SQLITE_BUSY.

Решением было бы зарегистрировать колбэк-функцию обработчика занятости (busy handler) или тайм-аут для соединения с базой данных pFile с помощью sqlite3_busy_handler() или sqlite3_busy_timeout() сразу после его открытия.

Если sqlite3_backup_step() не удаётся немедленно получить необходимую блокировку, она использует любой зарегистрированный обработчик занятости или тайм-аут так же, как это делают sqlite3_step() или sqlite3_exec().

Как правило, не имеет значения, различаются ли размеры страниц исходной базы данных и базы данных назначения до того, как содержимое базы данных назначения будет перезаписано. Размер страницы базы данных назначения просто изменяется в ходе операции резервного копирования. Исключение составляет случай, когда база данных назначения оказывается базой данных в памяти.

В этом случае, если размеры страниц не совпадают в начале операции резервного копирования, операция завершается с ошибкой SQLITE_READONLY. К сожалению, это может произойти при загрузке образа базы данных из файла в базу данных в памяти с помощью функции loadOrSaveDb().

Однако если база данных в памяти pInMemory была только что открыта (и поэтому полностью пуста) до передачи в функцию loadOrSaveDb(), то всё ещё можно изменить её размер страницы с помощью команды SQLite PRAGMA page_size. Функция loadOrSaveDb() могла бы обнаруживать этот случай и пытаться установить размер страницы базы данных в памяти равным размеру страницы базы данных zFilename перед вызовом функций онлайн-API резервного копирования.

Пример 2: онлайн-резервное копирование работающей базы данных SQLite

/* ** Perform an online backup of database pDb to the database file named ** by zFilename. This function copies 5 database pages from pDb to ** zFilename, then unlocks pDb and sleeps for 250 ms, then repeats the ** process until the entire database is backed up. ** ** The third argument passed to this function must be a pointer to a progress ** function. After each set of 5 pages is backed up, the progress function ** is invoked with two integer parameters: the number of pages left to ** copy, and the total number of pages in the source file. ** ** If the backup process is successfully completed, SQLITE_OK is returned. ** Otherwise, if an error occurs, an SQLite error code is returned. */ int backupDb( sqlite3 *pDb, /* Database to back up */ const char *zFilename, /* Name of file to back up to */ void(*xProgress)(int, int) /* Progress function to invoke */ ){ int rc; sqlite3 *pFile; sqlite3_backup *pBackup; rc = sqlite3_open(zFilename, &pFile); if( rc==SQLITE_OK ){ pBackup = sqlite3_backup_init(pFile, "main", pDb, "main"); if( pBackup ){ do { rc = sqlite3_backup_step(pBackup, 5); xProgress( sqlite3_backup_remaining(pBackup), sqlite3_backup_pagecount(pBackup) ); if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){ sqlite3_sleep(250); } } while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ); (void)sqlite3_backup_finish(pBackup); } rc = sqlite3_errcode(pFile); } (void)sqlite3_close(pFile); return rc; }

Функция из предыдущего примера копирует всю исходную базу данных за один вызов sqlite3_backup_step(). Это требует удержания блокировки чтения на файле исходной базы данных на протяжении всей операции, что не позволяет другим пользователям выполнять запись в неё. Кроме того, на протяжении всего копирования удерживается мьютекс, связанный с базой данных pInMemory, что не позволяет другим потокам использовать её.

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

Функция на языке C в данном разделе, предназначенная для вызова фоновым потоком или процессом, избегает описанных проблем, используя следующий подход:

Вызывается функция sqlite3_backup_init() для создания объекта sqlite3_backup, копирующего данные из базы данных pDb в файл резервной копии, идентифицированный параметром zFilename.

Вызывается функция sqlite3_backup_step() с параметром 5 для копирования пяти страниц базы данных pDb в резервную базу данных (файл zFilename).

Если в базе данных pDb ещё остались страницы для копирования, функция засыпает на 250 миллисекунд (используя sqlite3_sleep()), а затем возвращается к предыдущему шагу.

Вызывается функция sqlite3_backup_finish() для освобождения ресурсов, выделенных функцией sqlite3_backup_init().

Блокировка файлов и соединений с базой данных

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

Если другой поток или процесс выполняет запись в исходную базу данных, пока данная функция находится в режиме сна, SQLite обнаруживает это и обычно перезапускает процесс резервного копирования при следующем вызове sqlite3_backup_step().

Из этого правила есть одно исключение: если исходная база данных не является базой данных в памяти, и запись выполняется из того же процесса, что и операция резервного копирования, и использует тот же дескриптор базы данных (pDb), то база данных назначения (открытая с использованием соединения pFile) автоматически обновляется вместе с исходной.

После этого процесс резервного копирования может быть продолжен после возврата из вызова sqlite3_sleep() так, как если бы ничего не произошло.

Независимо от того, перезапускается ли процесс резервного копирования в результате записей в исходную базу данных в ходе оперативного резервного копирования (online backup), пользователь может быть уверен, что по завершении операции резервная база данных содержит согласованный и актуальный снимок (snapshot) базы данных оригинала. Однако необходимо учитывать два важных момента:

Записи в исходную базу данных в памяти, а также записи в исходную базу данных на основе файлов, выполняемые внешним процессом или потоком с использованием соединения, отличного от pDb, обходятся значительно дороже, чем записи с использованием pDb — поскольку в первых двух случаях вся операция резервного копирования должна быть перезапущена.

Если процесс резервного копирования перезапускается достаточно часто, он может никогда не завершиться, и функция backupDb() может никогда не вернуть управление.

backup_remaining() и backup_pagecount()

Функция backupDb() использует функции sqlite3_backup_remaining() и sqlite3_backup_pagecount() для отображения прогресса через пользовательский колбэк xProgress(). Функция sqlite3_backup_remaining() возвращает количество страниц, оставшихся для копирования, а sqlite3_backup_pagecount() возвращает общее количество страниц в исходной базе данных (в данном случае — базе данных, открытой через pDb). Таким образом, процент завершения процесса можно вычислить по формуле:

Completion = 100% * (pagecount() - remaining()) / pagecount()

Функции sqlite3_backup_remaining() и sqlite3_backup_pagecount() возвращают значения, сохранённые предыдущим вызовом sqlite3_backup_step(), и не выполняют фактическую проверку файла исходной базы данных.

Это означает, что если другой поток или процесс выполнит запись в исходную базу данных после того, как вызов sqlite3_backup_step() вернёт управление, но до того, как будут использованы значения, возвращаемые sqlite3_backup_remaining() и sqlite3_backup_pagecount(), эти значения могут оказаться технически некорректными. Как правило, это не является проблемой.

С инженерной точки зрения это важно для построения корректного progress bar. Значения remaining() и pagecount() хорошо подходят для приблизительного отображения хода операции, но не стоит трактовать их как абсолютно точный источник телеметрии в условиях параллельной записи.

Если вы строите мониторинг фоновых backup-задач, разумнее показывать их как “оценку прогресса”, а не как строгую бухгалтерию байтов или страниц. В противном случае пользователь увидит скачки процентов и решит, что backup “завис”, хотя на самом деле SQLite просто пересчитывает снимок после новых записей.

Типичные ошибки при работе с Online Backup API

Работа с backup API выглядит прямолинейно, однако на практике встречается несколько устойчивых ошибок, которые стоит знать заранее.

Отсутствие обработчика занятости для файловых баз данных. Если к файлу zFilename обращается внешний процесс, вызов sqlite3_backup_step() может немедленно вернуть SQLITE_BUSY вместо того, чтобы подождать. Регистрация sqlite3_busy_timeout() на соединении pFile сразу после его открытия решает эту проблему.

Несовпадение размеров страниц при загрузке в базу данных в памяти. Если целевая база данных в памяти уже содержит данные с другим размером страницы, операция завершится с ошибкой SQLITE_READONLY. Чтобы избежать этого, нужно устанавливать PRAGMA page_size до начала копирования, пока база данных в памяти ещё пуста.

Бесконечный перезапуск при активной записи. Если в исходную базу данных в памяти или в файловую базу данных через стороннее соединение непрерывно поступают записи, процесс резервного копирования будет перезапускаться снова и снова. В таких сценариях стоит предусмотреть ограничение на количество попыток или временной тайм-аут для функции backupDb().

Игнорирование кода ошибки после sqlite3_backup_finish(). Поскольку sqlite3_backup_finish() не перезаписывает код ошибки, установленный sqlite3_backup_step(), итоговый результат операции нужно проверять через sqlite3_errcode() на соединении с целевой базой данных — именно так, как это сделано в обоих примерах выше.

Я бы добавил сюда ещё один практический критерий проверки: после внедрения backup API полезно один раз прогнать сценарий восстановления на тестовой копии. Если команда умеет делать резервную копию, но не проверяла, что копия действительно открывается и проходит элементарный PRAGMA integrity_check, то схема резервного копирования остаётся недоведённой.

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

Часто задаваемые вопросы по SQLite Backup API

Чем Online Backup API отличается от простого копирования файла базы данных?

При простом копировании файла необходимо удерживать разделяемую блокировку на всё время операции, что блокирует запись для других клиентов. Online Backup API копирует данные инкрементально, удерживая блокировку только на короткие промежутки фактического чтения, и позволяет другим пользователям продолжать работу с базой данных.

Что происходит, если в исходную базу данных записывают данные во время резервного копирования?

SQLite обнаруживает изменения и обычно перезапускает процесс резервного копирования при следующем вызове sqlite3_backup_step(). Исключение — запись через тот же дескриптор pDb в файловую базу данных: в этом случае целевая база данных обновляется автоматически без перезапуска.

Почему при загрузке файла в базу данных в памяти может возникнуть ошибка SQLITE_READONLY?

Это происходит, когда размер страницы файловой базы данных не совпадает с размером страницы уже инициализированной базы данных в памяти. Чтобы избежать ошибки, нужно установить PRAGMA page_size до начала копирования, пока база данных в памяти ещё пуста.

Как вычислить процент завершения резервного копирования?

Используйте формулу: 100% * (pagecount() - remaining()) / pagecount(), где pagecount() — это sqlite3_backup_pagecount(), а remaining() — sqlite3_backup_remaining(). Оба значения отражают состояние на момент последнего вызова sqlite3_backup_step().

Когда лучше использовать VACUUM INTO вместо Online Backup API?

VACUUM INTO удобен, когда нужно получить дефрагментированную копию базы данных в отдельный файл за один вызов. Online Backup API предпочтителен, когда требуется инкрементальное копирование с минимальным влиянием на работу других клиентов или когда нужно копировать данные в базу данных в памяти и из неё.