Простой способ перевести django проект с MySQL(MariaDB) на PostgreSQL

Зачем?

После долгого тестирования MariaDB, после долгих сравнений всех преимуществ MariaDB перед MySQL... В один прекрасный день, я понял, что мне надоело перезагружать сервер с MySQL, каким бы он не был. Наверно все знают, что MySQL закрывает соединения, которые не используются в течении wait_timeout. Мне надоело, открывать сайт и видеть:

MySQL server has gone away

Я честно боролся с этим ибо не по-джедайски, но mysql все равно уходил... И было принято решение в пользу слоника, потому что он хороший и он никогда не уходит и расходует по-умолчанию всего лишь порядка 24Mb памяти.

Установка и настройка PostgreSQL

Существует множество руководств и т.п. мануалов по установке PostgreSQL. Напишу и я:

# apt-get install postgresql libpq-dev

Теперь надо отредактировать специальный файл, работающий по принципу файервола. Верхние строки выполняются первыми и переопределяют более нижние. Сделаем так чтобы для входа с локального хоста не требовался пароль ( пропишем trust) и создадим пользователя:

# nano /etc/postgresql/9.1/main/pg_hba.conf
host    all             all             127.0.0.1/32            trust

# service posgresql restart

Зайдем в базу данных под пользователем postgres:

# su postgres
# psql

Теперь мы находимся в консоли postgres, создадим пользователя (представлены разные варианты) и базу данных:

postgres=# CREATE ROLE username LOGIN PASSWORD 'secret' SUPERUSER; # суперпользователь
postgres=# CREATE ROLE username LOGIN PASSWORD 'secret' NOSUPERUSER INHERIT; # не суперпользователь, наследует свойства от групп
postgres=# CREATE ROLE username LOGIN PASSWORD 'secret' NOSUPERUSER NOCREATEROLE; # не суперпользователь, не может создавать пользователей

Создадим базу данных:

postgres=# CREATE DATABASE database WITH OWNER=username ENCODING='UTF8';

Выход из консоли posgres:

postgres=# \q

Настройки производительности и т.п. как всегда в файле:

nano /etc/postgresql/9.1/main/postgresql.conf

В Ubuntu не пришлось создавать кластер. Для тех кому придется создавать кластер:

/usr/lib/postgresql/9.1/bin/initdb --locale=ru_RU.utf8 --encoding=utf8 -D /var/lib/postgresql/

Миграция данных

Вначале я опробовал все стандартные способы, пытался сдампить данные вот так:

# mysqldump -uusername -psecret --no-create-db=TRUE --no-create-info=TRUE --compatible=postgresql my_db > my_db_dump.sql

При попытке залить в PostgreSQL базу данных он ругается, говорит неверные ключи. И вот отчаявшись нашелся способ "соскочить" с mysql.

Мигрировать данные можно с помощью утилиты py-mysql2pgsql, её репозиторий Утилита оказалась многофункциональной и очень интересной, поэтому я решил написать про нее эту статью.

Устанавливаем py-mysql2pgsql

Утилита требует установленных python-mysql и psycopg2

# pip install py-mysql2pgsql

Один раз запускаем чтобы создался конфигурационный файл:

# py-mysql2pgsql
No configuration file found.
A new file has been initialized at: mysql2pgsql.yml
Please review the configuration and retry...

Далее нужно подставить необходимые параметры для копирования одной базы данных в другую. Необходимо чтобы существовала приемная база данных в PostgreSQL. Связь с mysql базой осуществляется через unix socket, его можно посмотреть в nano /etc/mysql/my.cnf, нужно найти socket у меня это /var/run/mysqld/mysqld.sock, можно просто закомментировать, тогда соединение произойдет через TCP socket. Я выбрал соединяться через unix socket.

Отредактируем конфигурационный файл:

# nano mysql2pgsql.yml

# if tcp is chosen you can use compression
mysql:
 hostname: localhost
 # port: 3306
 socket: /var/run/mysqld/mysqld.sock
 username: username
 password: secret
 database: source_db
 compress: false
destination:
 # if file is given, output goes to file, else postgres
 file: 
 postgres:
  hostname: localhost
  port: 5432
  username: username
  password: secret
  database: destination_db

Если указываем destination file, то вывод пойдет в файл.

Далее запускаем:

py-mysql2pgsql -f mysql2pgsql.yml

Все данные скопированы!

Также можно снять дамп с помощью этой утилиты, укажем файл:

# nano mysql2pgsql.yml

# if tcp is chosen you can use compression
mysql:
 hostname: localhost
 # port: 3306
 socket: /var/run/mysqld/mysqld.sock
 username: username
 password: secret
 database: source_db
 compress: false
destination:
 # if file is given, output goes to file, else postgres
 file: pg_dump.sql
 postgres:
  hostname: localhost
  port: 5432
  username: username
  password: secret
  database: destination_db

Запустим, чтобы создать дамп:

py-mysql2pgsql -v -f mysql2pgsql.yml

Загрузим дамп в PostgreSQL:

psql -U pg_username -W pg_pass -d destination_db -f pg_dump.sql

Все данные скопированы!

Использование в Django приложении:

В виртуальное окружение нужно установить psycopg2:

# source env/bin/activate
pip install psycopg2

В settings.py настройках базы данных надо изменить:

'ENGINE': 'django.db.backends.postgresql_psycopg2',

Еще у PostgreSQL есть одна особенность. Он не любит пускать процессы работающие под например www-data с логином username. Создайте пользователя www-data или отключите проверку паролей.

Сложности и переписывание запросов после миграции

Как выяснилось не существует полной совместимости можду MySQL и PostgreSQL. Кто-то скажет "спасибо, кэп!". Признаюсь я знал, что это так, но не предполагал всей полноты проблемы, потому и хочу предупредить.

Например нельзя сделать так:

SELECT `mt`.* FROM `my_table` `mt` WHERE TRUE GROUP BY `mt`.`somefield`;

Вначале слон не поддреживает апострофы (обратные одинарные кавычки).

SELECT mt.* FROM my_table mt WHERE TRUE GROUP BY mt.somefield;

А теперь он будет ругаться, что все поля которые не входят в группировку должны быть задействованы в функциях аггрегирования. А mysql просто строки сливает вместе и все. Решение этой проблемы специальная функция аггрегирования:

SELECT (array_agg(mt.id))[1] as id, mt.somefield, (array_agg(mt.somefield2))[1] as somefield2 ... FROM my_table mt WHERE TRUE GROUP BY mt.somefield;

Кроме этого есть много проблем, но они все решаются. Так что смотрите. Кстати django orm нормально работает с PostgreSQL. Мной ошибок не выявлено, только в raw запросах. Правда при этом невелируются все преимущества PostgreSQL перед MySQL. Например функции авторизации.

Удалить mariadb:

sudo apt-get purge mariadb*

Удалить mysql:

sudo apt-get purge mysql*

Писсимистическая (читать: реалистическая) нота

Пока не знаю правильно ли я сделал. Пока все работает. Есть сложности с запросами. Но это временно. У меня нет 100% уверености, что PostgreSQL будет стабильнее и лучше. Только время покажет. Для моих задач должен был подходить и mysql, но он сука постоянно уходил.

Полезности

Так делается дамп (или сжатый дамп):

# pg_dump -h 127.0.0.1 -p 5432 -U username -F p database > pg_dump.sql
# pg_dump -h 127.0.0.1 -p 5432 -U username -F p database | gzip > pg_dump.sql.gz
# pg_dump -h 127.0.0.1 -p 5432 -U username -F t database | gzip > pg_dump.sql.tar.gz

Так дамп заливается в базу, база должна быть пустая.

# psql -Uusername -Wsecret -d database < pg_dump.sql
# gunzip pg_dump.sql.gz | psql -Uusername -Wsecret -d database
# tar -xOf pg_dump.sql.tar.gz | psql -Uusername -Wsecret -d database

Скрипт для создания резервной копии по крону

$ mkdir /scripts
$ touch /scripts/pg_dump.sh
$ chmod +x /scripts/pg_dump.sh
$ nano /scripts/pg_dump.sh

#!/bin/bash

DB="db_name"
USER="db_user"
PASSWORD="db_password"
PREFIX="pgsql_db"
DESTINATION="/tmp/"

/usr/bin/find $DESTINATION -name "*.gz" -ctime +30 -delete
PGPASSWORD=$PASSWORD /usr/bin/pg_dump -h 127.0.0.1 -p 5432 -U $USER -F p $DB | gzip > $DESTINATION/$PREFIX\_`date +%Y-%m-%d`.sql.gz

Если статья вам понравилась щелкните по рекламе. Спасибо за внимание и за щелчки $)

Переключаемся с mysql на postgresql