В последнее время клиенты нашей компании все чаще обращаются с просьбой осуществить переход с MySQL на PostgreSQL. Действительно, активное развитие PostgreSQL дает повод разработчикам задуматься о смене СУБД. Зачастую при этом встает вопрос о переносе больших объемов данных, которыми «обросла» существующая база под MySQL.
Мы сами являемся активными пользователями Redmine и разработчиками плагинов для него, потому и выбрали эту CRM для иллюстрации процесса миграции.
Ниже описывается один из способов миграции с БД MySQL на PostgreSQL 9.5 для Redmine на ОС CentOS 6.
Итак, начнём:
1. Для начала устанавливаем репозиторий PostgreSQL 9.5
rpm -ihv https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-2.noarch.rpm
2. Устанавливаем сам PostgreSQL 9.5.
yum install postgresql95-server postgresql95-contrib postgresql95-devel
service postgresql-9.5 initdb ru_RU.utf8
chkconfig postgresql-9.5 on
alternatives --install /usr/bin/psql psql /usr/pgsql-9.5/bin/psql 5000
alternatives --install /usr/bin/pg_dump pg_dump /usr/pgsql-9.5/bin/pg_dump 5000
alternatives --install /usr/bin/pg_config pg_config /usr/pgsql-9.5/bin/pg_config 5000
alternatives --install /usr/bin/createdb createdb /usr/pgsql-9.5/bin/createdb 5000
alternatives --install /usr/bin/createuser createuser /usr/pgsql-9.5/bin/createuser 5000
alternatives --install /usr/bin/dropdb dropdb /usr/pgsql-9.5/bin/dropdb 5000
alternatives --install /usr/bin/dropuser dropuser /usr/pgsql-9.5/bin/dropuser 5000
alternatives --install /usr/bin/pg_dumpall pg_dumpall /usr/pgsql-9.5/bin/pg_dumpall 5000
alternatives --install /usr/bin/createuser createuser /usr/pgsql-9.5/bin/createuser 5000
3. Делаем тюнинг PostgreSQL с помощью pgtune.leopard.in.ua, добавляем откорректированные настройки в конец файла.
4. Настраиваем авторизацию PostgreSQL таким образом, чтобы авторизоваться могли только с localhost по сети или через сокет, а root мог ходить под пользователем «postgres» без пароля.
pg_hba.conf:
local all postgres ident map=supervisor
host all all 127.0.0.1/32 md5
pg_ident.conf:
supervisor postgres postgres
supervisor root postgres
5. Создаем пользователя и БД «redmine» в PostgreSQL:
createuser --username=postgres --no-superuser --no-createdb --no-createrole --encrypted redmine
createdb --username=postgres --encoding=utf-8 --template=template0 --owner=redmine redmine
psql --username=postgres --dbname=postgres --command="ALTER USER \"redmine\" WITH ENCRYPTED PASSWORD 'yourgreatpassword'"
6. Прописываем данные авторизации PostgreSQL, комментируем авторизацию MySQL в redmine/config/database.yml:
# обратите внимание, перед настройками два пробела (!)
production:
adapter: postgresql
database: redmine
host: localhost
username: redmine
password: "yourgreatpassword"
7. Устанавливаем инструмент для миграции данных.
Мой выбор остановился на py-mysql2pgsql. Инструменты типа yaml_db не дали эффекта — Redmine упорно выдавал ошибку при запуске миграций.
Настраиваем py-mysql2pgsql.
Устанавливаем:
yum install python-pip python-devel
pip install py-mysql2pgsql
Делаем первый запуск, создаем шаблон файла конфигурации:
py-mysql2pgsql
Редактируем py-mysql2pgsql.yml, вносим данные авторизации MySQL и новой базы PostgreSQL.
Выполняем миграцию данных. Для базы данных размером 1 Гб на среднем сервере этот процесс занял порядка 15 минут.
py-mysql2pgsql -v -f mysql2pgsql.yml
Вот и все!
Задавайте любые вопросы, пишите ваши рекомендации. Буду рад обсудить в комментариях.
P.S. Подобным образом мы выполнили миграцию базы данных Zabbix размером 200 Гб с MySQL на PostgreSQL. Работает!
Комментарии (10)
spartan
05.05.2016 13:26+2А в чем конкретно преимущества postgresql над mysql для редмайна?
Sleuthhound
05.05.2016 19:36-2По моему — абсолютно никакого, сомневаюсь, что у кого-то база mysql для redmine больше 2-3 Гб.
У одного моего знакомого, который обслуживает БД, есть база на mysql в 10 Тб и ничего, живут и работают и никуда мигрировать не собираются.
stalkerg
05.05.2016 23:20+3А причём тут размер БД? Postgres хорош и на маленьких БД.
Sleuthhound
06.05.2016 07:36-1Как правила на Pg мигрируют с платного Oracle, покрайней мере мой знакомый осуществляет такие миграции и в принципе Pg позиционируется как замена Oracle и размер БД как раз имеет значения.
Попробуйте мигрировать с Oracle на MySQL базу 100Тб, мой знакомый делал миграцию именно на Pg и не потому что на MySQL было невозможно, а потому, что MySQL умрет на таком объеме данных.
bizzonaru
05.05.2016 13:58А как идет миграци индексов, тригеров и прочее? Зачастую индексы при нагрузке кастомизируешь под особенности mysql. Просто перелить данные можно и обычным скриптом.
BasilioCat
05.05.2016 14:06Не менее интересно услышать преимущества PostgreSQL для zabbix. В версиях 2.x заббикс активно апдейтил таблицы (в первую очередь items), что в комплекте с версионностью строк в PostgreSQL приводит к постоянному ваккуумированию. Структуру таблиц изменили в 3.0 (или даже в 2.4), но рекомендуемая БД для Заббикса по-прежнему MySQL.
PostgreSQL содержит весьма интересные возможности (особенно 9.4+), но обычное ПО редко их использует.
alexvl
12.05.2016 10:28Дело в том, что уже начиная с 2.2 количество аптейтов значитильно снизилось. Практически вся конфигурация кешируется в памяти и в апдейтах нет необходимости. У разработчиков Zabbix нет предпочтений по выбору базы данных, смело используйте то, что лучше знаете: MySQL или PostgreSQL. PostgreSQL отличный выбор.
Документация устарела, исправим!
Obramko
05.05.2016 22:43-1pip install py-mysql2pgsql
Тогда уж
make install, чего мелочиться.
Используйтеvirtualenv.eirnym
06.05.2016 15:24В случае с virtualenv, вы выполните ту же команду с pip.
По моим наблюдениям, сейчас очень большая часть программистов и администраторов устанавливают в virtualenv, если это не нужно в системе в целом, а если нужно, то через пакетный менеджер системы.
chemtech
Может быть опишите основные причины, которые, по вашему мнению, подталкивают на миграцию с MySQL на PostgreSQL? Можно добавить в начало статьи.