Периодически замечал в iotop на своём домашнем компьютере, что самую высокую нагрузку по вводу-выводу создаёт MySQL. В интернете встречал мнения, что PostgreSQL по сравнению с MySQL более стабильно ведёт себя в условиях дефицита производительности дисковой подсистемы. Больше всего MySQL на этом компьютере нагружался Zabbix'ом, поэтому ради эксперимента решил попробовать перевести Zabbix на использование PostgreSQL.
Довольно долго я пытался экспортировать данные из MySQL при помощи штатного инструмента mysqldump в виде, пригодном для последующего экспорта в PostgreSQL. У этой утилиты имеется опция, позволяющая экспортировать в формате, совместимом с PostgreSQL. Несколько дополнительных опций, чтобы отключить директивы, отключить создание таблиц, добавить явные имена колонок в запросы INSERT, позволили получить результат с виду пригодный для импорта в PostgreSQL.
Тут, однако, возникла проблема. Данные таблиц в дампе шли в порядке, соответствюущем алфавитному порядку имён таблиц. Это вызывало проблемы с обработкой внешних ключей. Почему-то на тот момент я не догадался заглянуть в файл scheme.sql и создавал схему базы данных сразу с ограничениями и внешними ключами. Для решения этой проблемы я написал скрипт, который брал информацию о внешних ключах из базы данных information_schema и сортировал таблицы так, чтобы ссылающиеся таблицы шли после тех, на которые они ссылаются. Экспортировал данные я именно в этом порядке.
Однако когда проблема с внешними ключами была решена не самым простым путём, всплыло несколько досадных мелочей:
Попытки воспользоваться sed'ом для исправления недостатков не привели к успеху и я решил попробовать pgloader.
После продолжительных мучений с документацией pgloader и PostgreSQL в поисках правильной обработки зависимостей внешних ключей, я наконец-то догадался заглянуть в файл schema.sql и поделил его на две части. После этого pgloader отработал без запинки. Получившимся рецептом миграции и спешу поделиться.
Стоит отметить, что такой прямолинейный способ миграции не подойдёт, если база данных очень большая или на диске нет места для второй копии базы данных. В этом случае нужно думать о поэтапной миграции, так чтобы минимизировать простой системы мониторинга и суметь справиться с недостатком места на дисках.
Установим СУБД, если она ещё не установлена:
# apt-get install postgresql
Установим пакет pgloader, с помощью которого будем переносить содержимое базы из MySQL в PostgreSQL:
# apt-get install pgloader
Доустановим пакет для работы веб-интерфейса с СУБД PostgreSQL:
# apt-get install php5-pgsql
Чтобы новый модуль можно было использовать из PHP, перезапустим php5-fpm:
# systemctl restart php5-fpm
Правим файл конфигурации аутентификации пользователей /etc/postgresql/9.4/pg_hba.conf, заменяя первую строку на вторую:
local all all peerlocal 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 задаёт пользователя, который будет владельцем базы данных.
Теперь возьмём файл 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 или подключим репозиторий с этим пакетом. Как это сделать - решайте сами.
Перед переносом данных остановим 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
Заглядываем в журналы Zabbix-сервера /var/log/zabbix/zabbix_server.log и заходим в веб-интерфейс Zabbix, проверяя, что всё работает нормально.
Кстати, наблюдение за нагрузкой на дисковую подсистему по такому не совсем чёткому параметру как iowait, показало, что нагрузка действительно упала:
