Миграция Zabbix с MySQL на PostgreSQL

Периодически замечал в iotop на своём домашнем компьютере, что самую высокую нагрузку по вводу-выводу создаёт MySQL. В интернете встречал мнения, что PostgreSQL по сравнению с MySQL более стабильно ведёт себя в условиях дефицита производительности дисковой подсистемы. Больше всего MySQL на этом компьютере нагружался Zabbix'ом, поэтому ради эксперимента решил попробовать перевести Zabbix на использование PostgreSQL.

Довольно долго я пытался экспортировать данные из MySQL при помощи штатного инструмента mysqldump в виде, пригодном для последующего экспорта в PostgreSQL. У этой утилиты имеется опция, позволяющая экспортировать в формате, совместимом с PostgreSQL. Несколько дополнительных опций, чтобы отключить директивы, отключить создание таблиц, добавить явные имена колонок в запросы INSERT, позволили получить результат с виду пригодный для импорта в PostgreSQL.

Тут, однако, возникла проблема. Данные таблиц в дампе шли в порядке, соответствюущем алфавитному порядку имён таблиц. Это вызывало проблемы с обработкой внешних ключей. Почему-то на тот момент я не догадался заглянуть в файл scheme.sql и создавал схему базы данных сразу с ограничениями и внешними ключами. Для решения этой проблемы я написал скрипт, который брал информацию о внешних ключах из базы данных information_schema и сортировал таблицы так, чтобы ссылающиеся таблицы шли после тех, на которые они ссылаются. Экспортировал данные я именно в этом порядке.

Однако когда проблема с внешними ключами была решена не самым простым путём, всплыло несколько досадных мелочей:

• в полученном дампе внутри одинарных кавычек двойные кавычки экранировались обратным слэшем, в то время как PostgreSQL принимал их без экранирования,
• одинарные кавычки в тех же строках тоже экранировались обратным слэшем, но PostgreSQL ждал, что они просто будут продублированы,
• двоичные данные сохранялись в виде закавыченной последовательности байтов, в то время как PostgreSQL ждал двоичные данные в виде шестнадцатеричных цифр с префиксом 0x.

Попытки воспользоваться sed'ом для исправления недостатков не привели к успеху и я решил попробовать pgloader.

После продолжительных мучений с документацией pgloader и PostgreSQL в поисках правильной обработки зависимостей внешних ключей, я наконец-то догадался заглянуть в файл schema.sql и поделил его на две части. После этого pgloader отработал без запинки. Получившимся рецептом миграции и спешу поделиться.

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

1. Установка пакетов

Установим СУБД, если она ещё не установлена:

# apt-get install postgresql

Установим пакет pgloader, с помощью которого будем переносить содержимое базы из MySQL в PostgreSQL:

# apt-get install pgloader

Доустановим пакет для работы веб-интерфейса с СУБД PostgreSQL:

# apt-get install php5-pgsql

Чтобы новый модуль можно было использовать из PHP, перезапустим php5-fpm:

# systemctl restart php5-fpm

2. Подготовка СУБД

Правим файл конфигурации аутентификации пользователей /etc/postgresql/9.4/pg_hba.conf, заменяя первую строку на вторую:

local   all             all                                     peer
local   all             all                                     md5

По умолчанию, при подключении к UNIX-сокету, PostgreSQL определяет учётную запись, под которой работает подключившийся процесс и автоматически создаёт подключение от имени одноимённого пользователя из СУБД. Пароль при этом не запрашивается. Меняя эту строчку, мы будем требовать у подключившегося процесса явным образом указать имя пользователя СУБД и его пароль.

После этого перезапустим сервер базы данных, чтобы новые настройки вступили в силу:

# systemctl restart postgresql

Теперь из сеанса пользователя root заходим под пользователем postgres:

# su - postgres

От имени пользователя postgres создаём пользователя базы данных с именем zabbix:

$ createuser -P zabbix

Ключ -P означает, что будет запрошен пароль нового пользователя.

От имени пользователя postgres создаём саму базу данных с именем zabbix, владеть которой будет только что созданный пользователь с именем zabbix:

$ createdb -E UTF-8 -O zabbix zabbix

Опция -E UTF-8 означает, что текстовая информация в базе данных будет храниться в кодировке UTF-8, а опция -O задаёт пользователя, который будет владельцем базы данных.

3. Подготовка к переносу данных

Теперь возьмём файл database/postgresql/schema.sql, имеющийся в дистрибутиве Zabbix и поделим его на две части. В первой части будут запросы, создающие таблицы (CREATE TABLE), а во второй - создающие внешние ключи и ограничения (ALTER TABLE). Назовём эти файлы schema1.sql и schema2.sql

Создадим файл zabbix.load, содержащий настройки для переноса данных:

LOAD DATABASE
  FROM mysql://zabbix:zabbix_password@localhost/zabbix
  INTO postgresql://zabbix:zabbix_password@localhost/zabbix

WITH include no drop,
     truncate,
     create no tables,
     create no indexes,
     no foreign keys,
     reset sequences,
     data only

SET maintenance_work_mem TO '128MB',
    work_mem to '12MB'

BEFORE LOAD EXECUTE schema1.sql

AFTER LOAD EXECUTE schema2.sql;

Подготовим пакет с Zabbix-сервером, работающим с PostgreSQL или подключим репозиторий с этим пакетом. Как это сделать - решайте сами.

4. Перенос данных

Перед переносом данных остановим Zabbix-сервер:

# systemctl stop zabbix-server

Закроем доступ к веб-интерфейсу Zabbix, остановив php5-fpm (можно просто запретить доступ к веб-интерфейсу Zabbix через настройки nginx):

# systemctl stop php5-fpm

Теперь приступим к собственно переносу данных (в текущем каталоге должны быть подготовленные ранее файлы schema1.sql, schema2.sql, zabbix.load):

$ pgload zabbix.load

Пока данные переносятся, удалим старый Zabbix-сервер для MySQL:

# dpkg -r zabbix-server-mysql

Установим Zabbix-сервер для PostgreSQL :

# dpkg -i zabbix-server-pgsql_2.4.5-1+jessie_amd64.deb

Теперь, если данные уже перенеслись, можно запускать новый Zabbix-сервер:

# systemctl start zabbix-server

Отредактируем файл /etc/zabbix/web/zabbix.conf.php, заменив первую строчку на вторую:

$DB['TYPE']     = 'MYSQL';
$DB['TYPE']     = 'POSTGRESQL';

Запускаем php5-fpm (или открываем доступ к веб-интерфейсу Zabbix через настройки nginx):

# systemctl start php5-fpm

5. Проверка результата

Заглядываем в журналы Zabbix-сервера /var/log/zabbix/zabbix_server.log и заходим в веб-интерфейс Zabbix, проверяя, что всё работает нормально.

Кстати, наблюдение за нагрузкой на дисковую подсистему по такому не совсем чёткому параметру как iowait, показало, что нагрузка действительно упала: