Переключение с MySQL на PostgreSQL - советы, трюки и gotchas?

Ответ 1

Просто прошел через это сам, ну, я все равно...

• Текст, чувствительный к регистру
• Отсутствие INSERT IGNORE и REPLACE
• Явное кастинг необходимо почти везде
• Нет обратных ссылок
• LOAD DATA INFILE (COPY близок, но недостаточно близко)
• Измените autoincrement на SERIAL
• Хотя в MySQL нет плохой формы, в Postgres, INNER JOIN без предложения ON не может быть, используйте CROSS JOIN или тому подобное
• COUNT(*) может быть сумасшедшим медленным
• Базы данных кодируются наборами символов, а не таблицами
• У вас может быть несколько баз данных с несколькими схемами (у MySQL действительно есть только одна база данных и несколько схем)
• Разделение отличается
• MySQL interval против Postgres interval (для временных интервалов)
• Неявное переименование столбцов, Postgres требует AS
• Невозможно обновить несколько таблиц одновременно в Postgres
• Функции Postgres являются мощными. Таким образом, нет CALL proc();; перепишите proc() как функцию и SELECT proc();.

Ответ 2

Это будет масштабная задача, так как вам придется протестировать всю вашу базу кода - каждый запрос в любом месте для

• Синтаксис
• Правильное поведение (т.е. возвращает те же результаты)
• Производительность. есть ли какие-либо регрессии/улучшения производительности, и можете ли вы их обработать?
• Обработка ошибок - они не ведут себя одинаково в условиях ошибки, возможно, ваш код полагался на определенные коды ошибок.

Оперативно вам нужно будет посмотреть:

• Резервное копирование/восстановление
• Использование дискового пространства
• Использование памяти
• Одноразовая миграция данных - может быть большой/требующей много времени задачей.
• Откат плана, если он не работает
• Мониторинг - как вы контролируете свой MySQL, и могут ли эти методы быть адаптированы.
• (если это необходимо) - репликация

Перед рассмотрением такого шага вам обязательно придется выполнять большие испытания производительности.

Эти расходы делают переход к другой базе данных слишком дорогим для большинства нетривиальных приложений. Внимательно рассмотрите преимущества ОЧЕНЬ против огромных затрат на все вышеперечисленное.

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

Ответ 3

Вы можете попробовать PostgreSQL gotchas, который содержит наиболее распространенные проблемы. Как правило, документация PostgreSQL тоже хороша, так что держите ее под подушкой.

Кроме того, Преобразование из MySQL в PostgreSQL в wiki pgsql.

Ответ 4

Я нашел этот script, который будет подключаться к вашей базе данных MySQL и вашей базе данных PostgreSQL и просто передавать содержимое. Это работало как прелесть для меня.

https://github.com/philipsoutham/py-mysql2pgsql

Установлен

$ pip install py-mysql2pgsql

Run

$ py-mysql2pgsql

в любой папке, и он создаст для вас файл настроек шаблона (mysql2pgsql.yml), который вы можете редактировать и вводить данные своей базы данных.

Мне пришлось установить argparse для его работы.

$ pip install argparse

Когда ваши данные в базе данных заполняются, просто запустите его снова

$ py-mysql2pgsql

в той же папке, что и файл настроек, и wham, вы закончили. Он ничего не печатал на экране, но после этого моя база данных была полностью скопирована.

Ответ 5

Перед преобразованием установите MySQL в соответствии с ANSI-строгостью, запустив сервер с помощью: --transaction-isolation = SERIALIZABLE --sql-mode = ANSI

Убедитесь, что вы не используете таблицы MyIsam.

MySQL допускает много конверсий, которых он не должен; pg потребует отбрасывания.

Ваши сохраненные процедуры, функции и триггеры должны быть переписаны. pg дает вам выбор языков для них, но вы должны установить языки; это не так удобно, как MySQL.

pg разрешает только в столбцах списка выбора, которые находятся в группе или являются агрегатами; MySQL будет обманывать, выбрав первое значение в группе, если вы это сделаете.

MySQL добавляет кучу расширений: оператор не равных может быть !=, как в C, он позволяет '& &' как синоним "и", "||" для 'или' и т.д. В частности, pg использует '||' для обозначения привязки строк.

В принципе, pg довольно строго ANSI, MySQL - нет. Я бы настоятельно рекомендовал, чтобы ваш MySQL был как можно более строго соблюдающим ANSI, прежде чем перейти на pg, а затем проверить любые предупреждения при запуске приложений.

Ответ 6

Помимо перемещения структуры базы данных, где вы не можете избежать ручных настроек...

Самый надежный метод передачи данных (таблица за таблицей при условии, что структуры одинаковы):

mysql --default-character-set=utf8 -e "SELECT * FROM mytable" > mytable.txt

psql
\copy mytable from '/path/to/mytable.txt' WITH NULL AS 'NULL';

Пробовал каждый другой подход недавно (например, mysqldump с множеством опций + sed и т.д.), но ничего не сработало так хорошо, как это.

Этот подход также допускает некоторую гибкость при изменении структуры на этом пути - просто напишите соответствующий SELECT.