Прога на Qt + БД SQLite: как сделать бекап из своего софта?

Зачем такие сложности, когда можно просто копировать файл базы по таймеру (или вообще по крону)

Или даже делать это в онлайн режиме через https://www.sqlite.org/backup.html

А про QtSQL, если не предполагается использование других бэкэндов, лчучше вообще забыть

А как быть с тем, что будут обращения к БД на запись во время копирования? Или оно это «само разрулит и ничего не поломает»?

А как быть с тем, что будут обращения к БД на запись во время копирования?

Если делать копированием файла, то придется блокировать доступ на это время. Backup API, насколько я понимаю, не требует этого

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

Ну, собственно, проблема.

Backup API, насколько я понимаю, не требует этого

Хм. Вообще, судя по беглому гуглению, возможно эту штуку можно будет использовать совместно с Qtшными объектами для работы с БД. Придётся читать документацию, эх.

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

Или менять СУБД. Совет хороший, конечно, понимаю.

А про QtSQL, если не предполагается использование других бэкэндов, лчучше вообще забыть

Это почему?

Что ты хранишь в базе? Т.е. будет ли база постоянно увеличиваться или нет? Если да и последует разрастание базы до десятков а то и сотен мегабайт, то лучше возьми тот же PosgreSQL/MySQL/MongoDB или еще что-нибудь, потому что получишь проблемы в виде тормозов при большом размере базы Sqlite. При этом у тех баз, что я написал, есть инструменты резервного копирования, не требующие прерывать работу с базой.

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

Хм. Вообще, судя по беглому гуглению, возможно эту штуку можно будет использовать совместно с Qtшными объектами для работы с БД.

Можно. Но нужно убедиться, что QtSql и код, использующий sqlite напрямую, слинкованы с одной и той же библиотекой (ну или хотя бы одной и той же версией). На это можно напороться, используя бинарные сборки Qt от разработчиков

Потому что это ненужный слой абстракции, мешающий полноценно использовать API sqlite (использование двух API сразу некрасиво и создает проблему, описанную выше), создающий гемор с деплойментом плагина для qtsql (или статической линковкой этого плагина), при этом заточенный на клиент-серверные базы. Ну и б-гомерзкие QVariant’ы в API, через которые надо гонять данные.

Если да и последует разрастание базы до десятков а то и сотен мегабайт, то лучше возьми тот же PosgreSQL/MySQL/MongoDB или еще что-нибудь, потому что получишь проблемы в виде тормозов при большом размере базы Sqlite.

+1. Но если база раздувается не из-за количества строк, стобцов и таблиц, а из-за хранения в ней больших блобов, то можно хранить их во внешних файлах.

При этом у тех баз, что я написал, есть инструменты резервного копирования, не требующие прерывать работу с базой.

У sqlite тоже есть

Что ты хранишь в базе?

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

лучше возьми тот же PosgreSQL/MySQL/MongoDB

Да вот пока вместе с моей программой ещё одну софтину тащить не хочется.

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

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

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

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

В таком случае в базе нужно хранить строго метаданные

Это не дичь. Читай сообщение дальше, осознаешь.

Всмысле, видеофайлы просто валить рядом, а в БДшке оставить пути к ним?

На сколько точны сведения, что SQLite будет тупить при разрастании БДшки? Давно гуглил эту тему, но жалоб вроде не было.

c 00 до 00:05 каждый день профилактика :) И копируй этот файлик…

Всмысле, видеофайлы просто валить рядом

у меня на наколенной поделке есть каталог, где файлики (картинки, пдфки и т.д. с именем = id в базе) и храняться…

:)

Все норм… Но у меня не гигабайты и поделка однопользовательская…

Если снаружи в кроне, то sqlite3 foo.db ‘.backup bar.db’, это онлайн-бэкап, не особо тормозящий основное приложение.

Ещё начиная с sqlite 3.27 имеем VACUUM INTO, можно его выполнить как из внешнего шелла по крону, так и из приложения.

Ещё есть собственно backup api https://www.sqlite.org/backup.html

https://www.sqlite.org/backup.html

Глупости. SQLite с большими базами справляется не хуже постгреса и уж тем более не хуже всяких монго. Алгоритмы там плюс-минус такие же. Никаких тормозов, обусловленных SQLite нет и быть не может. Инструменты резервного копирования у SQLite тоже есть.

Единственная причина, по которой SQLite может быть хуже, это параллельная работа на запись множества транзакций в одной таблице. Там версионная БД вроде постгреса будет выигрывать.

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

Имей в виду, что в SQLite ограничение в 2 ГБ на один блоб. При хранении видео можно в него упереться, будет неудобно обходить.

Если у тебя уже используется Qt то думаю с красотой все нормально. С деплоем в виде разделяемых библиотек у меня проблем на винде и линуксе нету.

Глупости

Теоретически то алгоритмы плюс-минус такие же. Но.

Вот я сделал сравнительные замеры, чтобы обсуждение в ДА-НЕТ не скатывалось.

Дано. Две базы PostgreSQL и SQLite. Созданы через функционал Qt SQL. Вот запрос на создание таблиц:

const QString createTablesQuery =
    "CREATE TABLE IF NOT EXISTS test_table ("
    "id SERIAL PRIMARY KEY,"
    "t0 TEXT NOT NULL,"
    "t1 TEXT NOT NULL,"
    "t2 TEXT NOT NULL,"
    "v0 INTEGER NOT NULL,"
    "v1 INTEGER NOT NULL,"
    "v2 INTEGER NOT NULL,"
    "v3 INTEGER NOT NULL,"
    "v4 INTEGER NOT NULL,"
    "v5 INTEGER NOT NULL,"
    "v6 INTEGER NOT NULL,"
    "v7 INTEGER NOT NULL,"
    "v8 INTEGER NOT NULL,"
    "v9 INTEGER NOT NULL,"
    "v10 INTEGER NOT NULL,"
    "v11 INTEGER NOT NULL,"
    "v12 INTEGER NOT NULL,"
    "v13 INTEGER NOT NULL,"
    "v14 INTEGER NOT NULL"
    ")";

Заполнил случайными числами. Там где строки, значения получались из случайных чисел с плавающей точкой путем конвертирования случайного числа в строку.

Диск у меня SSD если что.

Вставил в таблицу в каждой базе 10000000 записей и замерял время вставки каждой записи. Так вот при росте базы роста времени вставки нет ни у SQLite (~2-4мс) ни у PostgreSQL(0-1мс).

А вот далее идет выборка и тут уже интереснее.

Запрос:

const QString selectQuery = "SELECT * FROM test_table where t0 = ''" /*здесь ставлю разные значения*/;

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

Вот результаты:

Данные значения получены с первого запуска и последующие запуски дают примерно такие цифры. Никаких средних я не находил.

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

в SQLite ограничение в 2 ГБ на один блоб. При хранении видео можно в него упереться

Мемуары планируешь издавать?

Если да и последует разрастание базы до десятков а то и сотен мегабайт, то лучше возьми тот же PosgreSQL/MySQL/MongoDB или еще что-нибудь, потому что получишь проблемы в виде тормозов при большом размере базы Sqlite

Пхах. При «разростании до сотен мегабайт» можно хранить данные в текстовом файле и при запуске загружать их целиком в память.

Интересное исследование, спасибо.

При «разростании до сотен мегабайт» можно хранить данные в текстовом файле и при запуске загружать их целиком в память.

В нашем мире текстовые редакторы адекватно не могут в текстовые файлы > 5 МБ, а ты про такое.

Единственная причина, по которой SQLite может быть хуже, это параллельная работа на запись множества транзакций в одной таблице. Там версионная БД вроде постгреса будет выигрывать

Извольте не согласиться. BDB прекрасно вытягивает множество параллельных записей в разные строки таблицы. Многоверсионные СУБД созданы совсем не для решения этой проблемы — они решают только проблему одновременного чтения и записи, или даже скорее так: проблему длительных сложных чтений с требованиями к целостности на фоне безостановочных записей. Если чтения короткие и записи короткие, то MVCC не нужно.

К слову, один из таких сценариев «долгого чтения во время записей» — это таки обсуждаемый бэкап, хоронящий содержимое базы в прошлом.

В нашем мире текстовые редакторы адекватно не могут в текстовые файлы > 5 МБ, а ты про такое

Проблема текстовых редакторов заключается во-первых в индексировании переносов строк, а во-вторых в определении высоты строки после переносов. Особенно если текст не моноширинный. А поскольку в тексте еще и может быть юникод, то длину строки нельзя посчитать просто по количеству символов. Отдельные редакторы делают ситуацию сильно хуже, применяя UTF-8 — это худший формат для внутреннего представления. Потом сидят жонглируют байтиками поочередно.

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

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

Прикольный бенч, но, к сожалению, все эти проблемы SQLite решаются ручным checkpoint-ом, желательно с закрытием старых читающих курсоров. Либо отключением WAL.

Ну и я бы заметил, что все-таки масштаб не тот, особенно учитывая SSD (очень маленький). Скорее всего большую часть времени постгрес здесь занимается не относящимися к обработке данных занятиями, вроде парсинга SQL или ввода-вывода в сокет.

В общем, сделал пока через sqlite3 backup api. Только БД, в которую делается бекап, пришлось открывать через функции sqlite3, поскольку при использовании Qt-шного объекта оно падало на sqlite3_backup_init.

В поставку Qt входят исходники sqlite3, так что я просто собрал из них статическую библиотеку, с которой и линкую проект.