Programming, electronics, lifestyle
Последний месяц я на работе в основном занимаюсь миграцией Grafana. Одна из задач в связи с этим – перенести данные из Grafana 7.5.9 использующую SQLite в Grafana 9.2.0 с PostgreSQL. Оказалось перенести базу не так просто, как я рассчитывал и читал в статьях других авторов. Что до официальной документации то, она отсутствует, есть разве что о миграции из PostgreSQL в MySQL.
Решение которое первым попалось:
Вкратце оно предлагает следующий подход:
Однако как я не пытался это сделать, у меня pgloader падал со всевозможными ошибками в середине процесса рандомно и частично мигрировав какую-то часть данных. Полагаю это связано с тем, что находится в базе SQLite (есть ли неэкранированные последовательности итд), если ничего сверхъестественного нет, то все может пройти успешно.
Я попробовал разные настройки pgloader лучший результат у меня был при использовании следующей конструкции (так pgloader не вылетал по памяти):
load database
from sqlite://<ABSOLUTE_PATH>/grafana.db
into postgresql://<DBUSER>:<DBPASS>@<DBURL>:<DBPORT>/<DBNAME>
with data only, reset sequences
set work_mem to '16MB', maintenance_work_mem to '512 MB';
Однако запустить так, чтобы корректно отработал pgloader у меня так и не получилось, и я понял, что нужен другой подход. Первой идеей, которая приходила на ум было - копировать дашборды и источники данных из интерфейса. К сожалению, это было нереально. Во-первых, по причине того, что только дашбордов ≈1300 штук, а во-вторых имелось большое количество других сущностей вроде бекапов дашбордов, папок с правами и пользователями, аннотаций, алертов итд.
Далее я попробовал мигрировать данные через конвертацию в csv файлы, однако было много неэкранированных последовательностей, которые не давали нормально отделять поля друг от друга.
Источники:
В скоре я наткнулся на статью, предлагающую использование скрипта на Golang.
Но опять, скрипт прерывался ошибкой. Я попробовал использовать готовый docker образ, собрать программу из исходников, но безуспешно.
Однако я решил изучить, что делает программа и исправить. Посмотрев исходный код, я понял что ничего сложного нет, это программа просто выполняет поиск по регуляркам, и что-то правит.
Я плохо пишу на Golang, и для решения конвертации бд, где возможно постоянно нужно делать правки в код я решил использовать интерпретируемый язык - Python. И действие за действием перенести и исправить программу на Golang.
Ниже я приведу код программы по блокам и прокомментирую процесс.
Сразу оговорюсь, что не считаю свой скрипт и это решение готовым решением для миграции. Поэтому я подаю это как конструктор, который в случае неработоспособности можно самостоятельно допилить напильником под свои нужды.
#! /usr/bin/env python3
import re, time
srcfile = "./grafana.sql"
resfile = "./resfile.sql"
print("\033[1m#\n# This program is for preparing SQL converted from SQLite to PostgreSQL")
print("# Source file: " + srcfile)
print("# Result file: " + resfile)
print("#\033[0;0m\n")
last_time = int(time.time())
def howlong():
global last_time
_now = int(time.time())
_dif = _now - last_time
last_time = _now
return " (%ss)\n" % (_dif)
doings = 23
Это заголовочная часть скрипта. В ней вы можете видеть определение двух стандартных библиотек необходимых для обработки данных. Приветственный вывод, а также определение функции которая позволит нам подсчитать время выполнения действий.
Переменная doings – общее количество операций, для того, чтобы понимать сколько еще действий осталось.
with open(srcfile, 'r') as f:
print("> 1/%d Uploading the source file to the memory" % (doings))
content = f.read()
print("✅ Done" + howlong())
Я писал программу не супер-эффективно с построчным чтением файла, а целиком читал файл в память, и затем уже работал. Думаю что для большинство кейсов с Grafana это подойдет. Тк даже при достаточно забитой данными Grafana SQL-дамп весит 1.3GB.
В случае необходимости переписать программу, не вижу необходимости обрабатывать многострочные sql-инструкции, тк все, с чем работает программа - это инструкции INSERT. Которые сохраняются с помощью sqlite3 всегда построчно.
print("> 2/%d Deleting all statements except 'INSERT INTO' " % (doings))
match = re.compile("(?m)[\r\n]*^(?!INSERT INTO.*)(.*)$", re.IGNORECASE)
content = match.sub('', content)
print("✅ Done" + howlong())
Закономерный шаг - удаляем все строки, не начинающиеся на INSERT INTO.
print("> 3/%d Removing inserts to 'sqlite_sequence', 'user_auth'" % (doings))
match = re.compile("(?m)[\r\n]*^INSERT INTO (sqlite_sequence|user_auth) VALUES.*;$", re.IGNORECASE)
content = match.sub('', content)
print("✅ Done" + howlong())
Также удаляем данные для таблиц sqlite_sequence и для user_auth.
print("> 4/%d Fix char conversion (char -> chr)" % (doings))
match = re.compile("char\(10\)\)", re.IGNORECASE)
content = match.sub('chr(10))', content)
print("✅ Done" + howlong())
Заменяем конструкцию char(10) на chr(10) в sql-функция replace в соответствии с совместимостью с Postgres.
Далее я предлагаю составить список таблиц, содержащих байтовые или булевы поля. Для этого посмотреть второй пункт по миграции БД ниже в статье. Для Grafana 9.2.0 у меня получился следующий список:
bool byte tablename
[+] [-] alert
[+] [-] alert_configuration
[-] [-] alert_instance
[+] [-] alert_notification
[-] [-] alert_notification_state
[-] [-] alert_rule
[-] [-] alert_rule_version
[+] [-] api_key
[-] [-] annotation
[-] [-] annotation_tag
[-] [-] api_key
[-] [-] builtin_role
[+] [-] dashboard
[-] [-] dashboard_acl
[+] [+] dashboard_snapshot
[-] [-] dashboard_tag
[-] [-] dashboard_version
[+] [+] data_keys
[+] [-] data_source
[-] [-] kv_store
[+] [-] migration_log
[-] [-] org
[-] [-] org_user
[-] [-] permission
[-] [-] playlist
[-] [-] playlist_item
[+] [-] plugin_setting
[-] [-] preferences
[+] [-] role
[-] [-] secrets
[-] [-] server_lock
[-] [-] short_url
[-] [-] sqlite_sequence
[-] [-] star
[-] [-] tag
[-] [-] team
[+] [-] team_member
[+] [-] user
[-] [-] user_auth
[+] [-] user_auth_token
[-] [-] user_role
Также, чтобы не делать лишней работы предлагаю запустить следующий скрипт для того, что увидеть в какие вообще таблицы мы записываем данные:
cat grafana.sql | grep "INSERT INTO" | cut -d ' ' -f 3 | uniq | sort
Перейдем к следующей части программы:
print("> 5/%d Fixing HEX syntax for 'dashboard_snapshot'" % (doings))
match = re.compile("(?m)(?<=^INSERT INTO dashboard_snapshot VALUES\()(.*)(,X\')([a-fA-F0-9]+\')", re.IGNORECASE)
content = match.sub(r"\1,'\\x\3", content)
print("✅ Done" + howlong())
print("> 6/%d Fixing HEX syntax for 'data_keys'" % (doings))
match = re.compile("(?m)(?<=^INSERT INTO data_keys VALUES\()(.*)(,X\')([a-fA-F0-9]+\')", re.IGNORECASE)
content = match.sub(r"\1,'\\x\3", content)
print("✅ Done" + howlong())
Некоторые поля, а также байтовые поля, SQLite оборачивает в поля вида X'[a-fA-F0-9]'. Нам же нужно в тех таблицах, в которых нужно записать байтовое значение, необходимо поменять синтаксис на совместимый с PostgreSQL – '\x[a-fA-F0-9]'. Пользуясь составленным списком вверху, а также списком таблиц в которые мы записываем данные у меня получились таблицы – dashboard_snapshot и data_keys в которые у меня записываются данные, а также в которых содержатся байтовые поля.
Далее все остальные батовые поля вида X'[a-fA-F0-9]' нужно было в текстовым виде записать в PosgtreSQL. Я попробовал сделать это в коде
# def hexdecode(hexmatch):
# _hexmatch = hexmatch.group(1)
# _hexstring = bytes.fromhex(_hexmatch[2:-1])
# _hexstring = str(_hexstring)[1:] # delete 'b'
# return _hexstring
Протестировать перекодирование можно вот так:
# не является кодом основного скрипта
import re
content = "X'48656c6c6f20476f7068657221'"
match = re.compile("(?m)X\'([a-fA-F0-9]+)\'", re.IGNORECASE)
def hexdecode(hexmatch):
_hexstring = hexmatch.group(1)
_hexstring = bytes.fromhex(_hexstring)
_hexstring = _hexstring.decode("ascii")
_hexstring = "'%s'" % (_hexstring)
return _hexstring
content = match.sub(hexdecode, content)
print(content)
Однако декодирование на уровне Python плохая идея, тк в дальнейшем это ломает экранирование спец символов служащих для разделения полей друг от друга. Полагаю не случайно SQLite эти поля перевел в HEX. И я нашел другое решение.
def nodecode(hexmatch):
_hexmatch = hexmatch.group(1)[2:] # cut X
_hexmatch = ",convert_from(decode(" + _hexmatch + ",'hex'),'utf-8')"
return _hexmatch
print("> 7/%d Wrapping HEX data" % (doings))
match = re.compile("(,X\'[a-fA-F0-9]*\')")
content = match.sub(nodecode, content)
print("✅ Done" + howlong())
Здесь я меняю поля вида X'[a-fA-F0-9]', на convert_from(decode([a-fA-F0-9],'hex'),'utf-8'). convert_from и decode это встроенные функции PosgreSQL, с помощью которых я могу на лету расшифровать HEX поле и записать его в базу, не делая это в SQL-файле.
Источники:
Далее нам нужно составить структуру данных в которых мы обозначим порядковые номера булевых полей в таблицах (для этого мы и готовили список выше).
bool_pos = {
"alert": [13],
"alert_configuration": [5],
"alert_notification": [8, 10, 11],
"api_key": [11],
"dashboard": [14, 15, 17],
"dashboard_snapshot": [7],
"data_keys": [2],
"data_source": [11, 14, 18, 20],
"migration_log": [4],
"plugin_setting": [4, 5],
"role": [11],
"team_member": [7],
"user": [11, 12, 18, 19],
"user_auth_token": [7],
}
Далее я написал конечный автомат с помощью которого могу парсить строку отделять поля друг от друга
def list_conv(_match):
l = []
state = "empty" # "empty", "null", "digit", "string", "sqlfunc"
word = ""
quotes = 0
for i in range(len(_match)):
match _match[i]:
case '\'':
if state == "empty":
state = "string"
elif state == "string":
if _match[i-1] != '\\':
state = "empty"
word += _match[i]
case ',':
if state == "empty" or state == "digit" or state == "null":
l.append(word)
word = ""
state = "empty"
else:
word += _match[i]
case '(':
if state == "sqlfunc":
if _match[i-1] != '\\':
quotes += 1
word += _match[i]
case ')':
if state == "sqlfunc":
if _match[i-1] != '\\':
quotes -= 1
if quotes == 0:
state = "empty"
word += _match[i]
case _:
if state == "empty":
if _match[i].isdigit():
state = "digit"
elif _match[i].isalpha():
if _match[i] == "N":
state = "null"
else:
state = "sqlfunc"
word += _match[i]
l.append(word)
return l
А также функцию для конвертации булевых полей в соответствующий для PostgreSQL формат.
def bool_conv(_table, _match):
splited = list_conv(_match.group(0))
global bool_pos
res = ""
for i in bool_pos[_table]:
match splited[i-1]:
case '0':
splited[i-1] = "FALSE"
case '1':
splited[i-1] = "TRUE"
case 'NULL':
splited[i-1] = "NULL"
case 'null':
splited[i-1] = "NULL"
case _:
print("Error: This value should be boolean!")
print("Position: %s, value: %s" % (i,splited[i-1]))
print("Table: %s, data: %s" % (_table,splited))
exit(1)
return ','.join(splited)
Дело в том, что SQLite на местах где TRUE - выдает нам 1, где FALSE – 0. При попытке записать ноль и единицу в булево поле в PostgreSQL будет ошибка.
i = 7 # doings before
for table in bool_pos:
i += 1
print("> %d/%d Fixing boolean values for '%s'" % (i, doings, table))
match = re.compile("(?<=INSERT INTO %s VALUES\().*(?=\);)" % (table), re.IGNORECASE)
content = match.sub(lambda match: bool_conv(table, match), content)
print("✅ Done" + howlong())
И собственно перебор всех строк и приведения в порядок значений булевых полей.
print("> 22/%d Setting quotes above tables name" % (doings))
match = re.compile("(?<=INSERT INTO )([\w]+)(?= VALUES\(.*\);)", re.IGNORECASE)
content = match.sub(r'"\1"', content)
print("✅ Done" + howlong())
Предпоследнее действие – взятие в двойные кавычки названия таблиц, опять же продиктовано синтаксисом PostgreSQL.
print("> %d/%d Saving result to %s" % (doings, doings, resfile))
with open(resfile, 'w') as f2:
f2.write(content)
print("🎉 Done" + howlong())
Ну и финальное действие – сохраняем полученный результат в resfile.sql.
Скрипт целиком вы можете скачать здесь.
Обновляем текущую Grafana до необходимой версии, не меняя используемой базы данных SQLite. Перезагружаем Grafana, ждём чтобы она запустилась, смотрим логи, проверяем работу через WebUI.
Переключаем базу на PostgreSQL, запускаем Grafana, ждем пока создастся вся схема БД. Используя графический интерфейс БД или другой копируем схему в файл с набором SQL инструкций (если необходимо удаляем INESERTs, CHANGE и другие инструкций отвечающие за наполнение БД).
Выключаем Grafana и конвертируем SQLite БД в набор SQL инструкций.
# если отсутствует, установите `sqlite3`
sqlite3 grafana.db .dump > grafana.sql
Наверное, многие задались вопросом зачем мы сделали файл со схемой БД, когда полученный sql-файл содержит инструкции по созданию схемы. Дело в том, что схема в частности тип полей между SQLite и PostgreSQL немного отличается.
Запускаем скрипт для исправления SQL-файла
./grafana_sql_migrator.py
Очищаем полностью схему БД, удаляя все таблицы и содержимое.
Создаем всю схему БД из подготовленного на 2м шаге файла со схемой:
cat ./grafana_schema.sql | psql postgresql://<DBUSER>:<DBPASS>@<DBURL>:<DBPORT>/<DBNAME>
Заполняем БД данными:
cat ./resfile.sql | psql postgresql://<DBUSER>:<DBPASS>@<DBURL>:<DBPORT>/<DBNAME> | grep -v "INSERT 0 1"
grep -v "INSERT 0 1"позволит отфильтровать вывод от успешных операций, и отображать только возможные ошибки.
Подготавливаем файл для сброса последовательностей уникальных ключей:
В целом подходов два:
Открыть исходный файл grafana.sql и в его конце найти инструкции по сбросу индексов.
...
INSERT INTO sqlite_sequence VALUES('org',1);
INSERT INTO sqlite_sequence VALUES('org_user',557);
INSERT INTO sqlite_sequence VALUES('preferences',61);
INSERT INTO sqlite_sequence VALUES('dashboard_tag',32188);
...
И увеличив на единицу каждый, подготовить соответствующие инструкции, например:
...
alter sequence migration_log_id_seq restart with 458;
...
Зайти PostgreSQL и запросить какие максимальные индексы находятся сейчас в таблицах, например:
select max(id) from dashboard;
А затем также подготовить набор инструкций для назначения последовательностей.
Посмотреть название последовательностей, полей для primary key предлагаю в файле со схемой подготовленной в пункте 2.
Я предлагаю комбинировать и эти два способа, чтобы убедиться, что все корректно.
Забыл сказать, что так как скрипт удаляет данные для таблицы user_auth, мы должны сбросить счетчик user_auth_id_seq на 1. В чем мы собственно убедимся, если будем использовать второй подход.
В итоге должен получиться примерно подобный файл reset_sequence.sql:
-- reset_sequence.sql
alter sequence migration_log_id_seq restart with 458;
alter sequence user_id_seq1 restart with 566;
alter sequence dashboard_id_seq1 restart with 1740;
alter sequence dashboard_provisioning_id_seq1 restart with 1;
alter sequence data_source_id_seq1 restart with 232;
alter sequence api_key_id_seq1 restart with 12;
alter sequence dashboard_version_id_seq restart with 1300583;
alter sequence dashboard_acl_id_seq restart with 6;
alter sequence login_attempt_id_seq1 restart with 1;
alter sequence org_id_seq restart with 2;
alter sequence org_user_id_seq restart with 566;
alter sequence preferences_id_seq restart with 64;
alter sequence dashboard_tag_id_seq restart with 32470;
alter sequence plugin_setting_id_seq restart with 4;
alter sequence annotation_id_seq restart with 186463;
alter sequence tag_id_seq restart with 16;
alter sequence user_auth_id_seq restart with 1;
alter sequence star_id_seq restart with 1009;
alter sequence team_id_seq restart with 3;
alter sequence team_member_id_seq restart with 4;
alter sequence alert_id_seq restart with 660;
alter sequence dashboard_snapshot_id_seq restart with 3067;
alter sequence alert_notification_id_seq restart with 24;
alter sequence alert_notification_state_id_seq restart with 886;
alter sequence playlist_id_seq restart with 3;
alter sequence playlist_item_id_seq restart with 6;
alter sequence temp_user_id_seq1 restart with 1;
alter sequence alert_rule_tag_id_seq restart with 1;
alter sequence annotation_tag_id_seq restart with 57;
alter sequence server_lock_id_seq restart with 3;
alter sequence user_auth_token_id_seq restart with 4497;
alter sequence short_url_id_seq restart with 851;
alter sequence alert_rule_id_seq restart with 305;
alter sequence alert_rule_version_id_seq restart with 305;
alter sequence alert_configuration_id_seq restart with 2;
alter sequence role_id_seq restart with 7;
alter sequence user_role_id_seq restart with 4;
alter sequence permission_id_seq restart with 5269;
alter sequence builtin_role_id_seq restart with 4;
alter sequence secrets_id_seq restart with 117;
alter sequence kv_store_id_seq restart with 4;
Ну и собственно после исполняем набор этих инструкций в СУБД. Я советую это делать в графическом интерфейсе для работы с СУБД. Но можно и аналогично через утилиту psql
Включаем Grafana, смотрим логи, проверяем работы WebUI. Миграция завершена.
Прикольно было бы использовать xargs для многопоточной записи данных в базу, но у меня не хватило времени заставить это работать и я забросил идею. Наработки у меня следующие:
# printf %s\\n {0..99} | xargs -n 1 -P 8 script-to-run.sh input/ output/
cat ./resfile.sql | tr \\n \\0 | xargs -0 -P7 psql postgresql://<DBUSER>:<DBPASS>@<DBURL>:<DBPORT>/<DBNAME> | grep -v "INSERT 0 1"
Источники:
Сначала я пытался мигрировать данные из Grafana версии 7.5.9 в 9.2.0. Несмотря на то, что схемы отличались немного (в новой версии имелись дополнительные поля в таблицах, что не вызывало проблем при INSERT). Проблема крылась в том, что в Grafana предусмотрен внутренний механизм обновления схемы через таблицу migration_log. И если версия схемы БД отличается от данных в этой таблице, скорее всего будет ошибка при запуске, например:
lvl=eror msg=“Executing migration failed” logger=migrator id=“copy data_source v1 to v2” error=“pq: column “account_id” does not exist”
Источник: bug with upgrade from 6.0.2 to 6.1.3 or 6.1.4 : column account_id does not exist #16720
Поэтому всегда сначала обновляем Grafana до нужно версии на той же базе данных, а лишь затем делаем миграцию данных в другую СУБД.