Что нужно знать при миграции с MySQL на PostgreSQL?
В продолжение статьи о теории и практике миграции хранилищ данных на PostgreSQL, мы поговорим о проблемах, с которыми вы можете столкнуться при переезде с распространенной СУБД MySQL. Дабы не утомлять всех лишней риторикой, сегодняшний рассказ будет более тезисный и проблемно-ориентированный.Все нижепредставленное является перечнем типовых ошибок дизайна и эксплуатации MySQL, которые могут повлиять на процесс адаптации схемы, переработки кода и переноса данных. Наличие всех этих мелочей в разнообразных коварных сочетаниях является одной из причин, по которой существующие «универсальные» инструменты вряд ли справятся конкретно с вашей базой.
Именно поэтому в предыдущей статье я рекомендовал не тратить время на поиск серебрянной пули и написать что-нибудь свое «на коленке», что действительно работает. Данная статья призвана облегчить написание такого инструмента, указывая на потенциальные изъяны, в наличии которых вы может сравнительно быстро убедиться.
Перейдем к делу.
Значения по-умолчанию
MySQL печально известен своим стремлением упрощать и облегчать труд разработчика. Как правило, на старте эксплуатации выключен strict режим, который ответственно сообщает об отсутсвии корректных значений по-умолчанию при вставке данных в таблицы.
Иными словами, у вас может быть объявлена колонка в таблице без параметра DEFAULT. При вставке в таблицу, вы забудете указать эту колонку, но MySQL не растеряется и вставит что-нибудь «дефолтное» за вас. Как правило, это 0, пустая строка или аналогичное «нулевое» значение соответствующего типа данных. PostgreSQL всегда выбрасывает constraint violation, поэтому будьте готовы исправлять соответствующие ситуации в коде приложения.
Отдельно хочется отметить любовь MySQL вставлять неадекватные значения по-умолчанию в поля типа DATE / DATETIME / TIMESTAMP. Неаккуратная работа с такими полями может привести к наличию строк типа »0000–00–00» или »00:00:00 0000–00–00» в вашей базе. Понятное дело, PostgreSQL такое не пропустит. Приходится либо «фиксить» данные в исходной БД, либо, при импорте, заменять их принудительно на NULL и соответствующим образом исправлять код приложения и структуру соответствующей таблицы.
Строгость constraints, STRICT MODE
В продолжение темы о значениях по-умолчанию и ограничениях, MySQL также довольно лояльно относится к их нарушению. Например, у вас объявлено поле типа VARCHAR2(255). MySQL имеет нездоровую тенденцию делать автоматический truncate строки, если она не помещается в указанную размерность. PostgreSQL будет ругаться.
В разрезе данной проблемы, стоит также отметить, что некорректные последовательности байт в строках указанной кодировки MySQL будет молча вырезать при выключенном STRICT режиме. PostgreSQL всегда ругается на некорректные byte sequences. Будьте готовы дополнительно санитировать входные данные при подготовке кода приложения к работе с PostgreSQL.
Ну и просто заранее проверьте, сделав backup предварительно, что вся ваша база MySQL консистентно пребывает в одной кодировке, и никто из программистов не создал через phpMyAdmin таблицу с полем VARCHAR2 в какой-нибудь swedish локали или нечто подобное.
Транзакции и autocommit
Программист обычно боится использовать транзакции, либо, в силу своей некомпетентности, считает их «тяжелыми» и «медленными». По факту, исполнение запросов без транзакции невозможно. Поэтому, для таких специалистов, MySQL услужливо работает в режиме AUTOCOMMIT, который скрывает всю порочную практику транзакционной обработки «под капотом». В PostgreSQL вам, скорей всего, придется симулировать такой подход к эксплуатации базы данных включением специального флага (SET AUTOCOMMIT).
Отдельный интерес представляет возможность MySQL использовать внутри транзакции движки таблиц, не поддерживающие транзакционную обработку данных. Здесь можно лишь процитировать документацию («If you were not deliberately mixing transactional and nontransactional tables within the transaction, the most likely cause for this message is that a table you thought was transactional actually is not.») и молча апплодировать.
Если это возможно, постарайтесь систематизировать движки до миграции. Если нет, переезжайте с использованием autocommit либо старайтесь обеспечить хотя бы номинальную транзакционную обработку для таких ситуаций. Хуже уже точно не будет.
Свой собственный абзац заслуживает движок таблиц под названием Memory. Особенность этого движка заключается в том, что он не персистирует данные на диск, а хранит целиком в памяти. Количество памяти фиксировано и задается настройкой, которую менять можно только путем перезапуска сервера БД. С эксплуатацией этого движка связано два нюанса: таблица, вышедшая за предел допустимого размера, моментально «свопится» на диск, карета превращается в тыкву, иногда вместе с базой данных (если движок Memory использовался для оптимизации производительности); как правило, в такие таблицы кладутся данные, считающиеся временными, которые не жалко и потерять. После первой потери выясняется, что данные, оказывается-то, были mission critical, и терять их нельзя. А архитектура БД и приложения уже прочно «подсажена» на эти таблицы, в результате чего все начинают на них молиться, а любая штатная перезагрузка базы превращается в специфический ритуал с конвертированием таблиц в персистируемый на диск формат, преобразованием обратно в Memory и подобными шаманствами.
В общем и целом, PostgreSQL просто сам по себе более адекватен в плане менеджмента памяти и, при условии наличия достаточного ее количества, выделенного под вашу базу, будет прекрасно держать такую таблицу в «оперативке» и отдавать ряды по запросам от приложения. Если же все-таки workload на таблицы очень большой, памяти не хватает или банально нет времени перерабатывать приложение под персистируемые таблицы, можете сэмулировать поведение Memory таблиц, создавая аналогичные в PostgreSQL с ключевым словом UNLOGGED (CREATE UNLOGGED TABLE…). Цитируя документацию:
If specified, the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log (see Chapter 29), which makes them considerably faster than ordinary tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown. The contents of an unlogged table are also not replicated to standby servers. Any indexes created on an unlogged table are automatically unlogged as well.
Экономия на спичках
Многие архитекторы MySQL часто забывающие о таких крайне важных вещах, как правильная растановка primary keys и поддержание корректности реляционных взаимоотношений, тем не менее, считают своим долгом очень точно указать разрядность и знак чисел, складываемых в тот или иной integer. MySQL предлагает очень большое разнообразие типов данных (int, smallint, tinyint и проч.), для каждого из которых можно указать предельное количество знаков и отсутствие/наличие отрицательных значений. Например, TINYINT будет вмещать значения от -128 до 127, а UNSIGNED TINYINT — от 0 до 255.
О чем вышеупомянутый архитектор никогда не подумает, так это о будущем. Рано или поздно в такое поле приедет значение, не попадающее в указанные рамки, и будет урезано MySQL до ближайшего корректного значения, при отключенном по-умолчанию strict режиме. Я неоднократно наблюдал базы, где из-за таких глупостей объективно важные и реальные цифры показателей эффективности «срезались» до предельно допустимых форматом высоких значений, что приводило, в свою очередь, к менее эффективной работе системы в целом.
Знающие люди уже догадались, что подобная ситуация происходит и с типами decimal/numeric, в которых традиционно хранится что-нибудь важное, например, деньги. В таких случаях, речь уже может пойти о потере реальной прибыли. PostgreSQL избавлен от подобного зоопарка возможностей выстрелить себе в ногу (а то, и в голову) и всегда будет ругаться на выход за пределы заданных «констрейнтов».
INSERT IGNORE + ON DUPLICATE KEY… UPDATE
Одни из самых нежно любимых разработчиками MySQL конструкций не имеют прямых аналогов в PostgreSQL.
Постоянное использование INSERT IGNORE настойчиво намекает о проблемах дизайна схемы вашей БД. Тем не менее, сэмулировать такое поведение можно написанием хранимой процедуры, которая делает INSERT и, в случае возникновения UNIQUE EXCEPTION, перехватывает его и молча «гасит».
ON DUPLICATE KEY… UPDATE, или так называемый UPSERT (UPdate + inSERT), был все-таки признан нужной «фичей» в сообществе PostgreSQL. Его релиз назначен на 9.5 версию СУБД. До тех пор, вы можете сэмулировать это следующей конструкцией:
— обновляем все записи, которые уже есть в таблицеUPDATE existing_table ex
SET field1 = ex.field1 + up.field1, field2 = up.field2, …
FROM updates up
WHERE ex.pkey_field1 = up.pkey_field1 AND ex.pkey_field2 = up.pkey_field2…;
— вставляем несуществующиеINSERT INTO existing_table (columns, …)
SELECT new.field1, new.field2, …
FROM new_data new
WHERE NOT EXISTS (SELECT * FROM existing_table ex WHERE ex.pkey1 = new.pkey1 and ex.pkey2 = new.pkey2 and…;);
Работа с временем в MySQL
Ранее уже упомяналась проблематика округления значений времени в типах данных, имеющихся в MySQL для этих целей. Отдельном рассмотрим разнообразное количество возможных вариантов хранения такой информации. Само по себе это не является категорической проблемой, все неприятности проистекают от незнания разработчиками разницы в типах и, как следствие, фривольное использование всех сразу без какой-то видимой логики. Ситуация, когда для хранения времени используются DATETIME, TIMESTAMP И BIGINT (для значений unix timestamp), довольно регулярна и наблюдается чуть ли не на каждой второй базе.
DATETIME от TIMESTAMP отличается хранением смещенения часового пояса. Исходя из этого, планируйте соответствующие типы данных в PostgreSQL (timestamp with/without time zone) и будьте готовы при импорте на лету осуществлять добавление/вычитание поправки на часовой пояс (если вдруг база данных жестко настроена на конкретную «таймзону», а типы данных при этом используются без разбора).
Не менее важной является проблема хранения unix timestamp в полях типа bigint. MySQL имеет неприятную практику добавлять количество секунд, соответствующих смещению часового пояса, в это значение, которое, идеологически, должно оставаться в UTC. Такое поведение не соответствует стандарту, поэтому, начиная с версии 9.2, в PostgreSQL приходится применять неприятный workaround, чтобы получать значение epoch с поправкой на часовой пояс, в виде SQL-конструкции EXTRACT (EPOCH FROM TZVALUE: TIMESTAMP WITH TIME ZONE), которая извлекает значение эпохи из указанного timestamp, явно конвертируя его в формат с хранением часового пояса.
Работа с часовыми поясами — одна из тех операций, наличие ошибок в которой простительно, поскольку задача действительно является нетривиальной. Объяснение правильных подходов к работе с часовыми поясами в PostgreSQL можно почитать в замечательной статье, расположенной по ссылке www.depesz.com/2014/04/04/how-to-deal-with-timestamps.
Блокировки
Частенько, программисты любят вручную управлять блокировками на базе данных. Явно выставлять shared и exclusive locks или, что еще хуже, менять уровень изоляции транзакции по-умолчанию. Строго говоря, делать это категорически противопоказано. Тем не менее, наблюдались в практике ситуации, когда архитекторы решали реализовывать целостность операций путем «глухой» блокировки обновляемых таблиц. При этом мало кого беспокоило, что на эти же таблицы идет OLTP нагрузка с пользовательских интерфейсов. Очень часто позывы такие бывают от того, что транзакции MySQL не применимы в силу наличия non-transactional движков таблиц или общего безумия в плане настроек, царящих на базе данных. Например, существует долгий запрос, ради которого «таймауты» откручены до предела и нормальные транзакции не применить.
Лучший вариант решения данной проблемы при миграции — попробовать использовать честные транзакции в PostgreSQL, который с большой долей вероятности лучше справится с ситуацией высокого concurrency. Если все-таки блокировки являются неотъемлемой частью логики, изучите механизм advisory locking. Так называемые рекомендательные блокировки являются виртуальными и не приводят к физическому блокированию данных. Их адекватное использование внутри транзакций несет значительно меньшие риски и является более щадящим для ресурсов и производительности СУБД. Но, безусловно, никто не отменяет потребности думать головой и аккуратно отслеживать все «кейсы» применения блокировок в коде приложения.
Заключение
Мы рассмотрели лишь малую часть удивительных вещей, которые могут происходить на сервере СУБД MySQL. Можно очень долго рассуждать от тонкостях работы репликации в MySQL и способах ее воспроизведения уже средствами PG, что, например, будет делать Олег Царев на предстоящем Pgday. В лучшем случае, проблема решается использованием механизма hot-standby. В худшем, придется осваивать нетривиальные в поддержке и эксплуатации инструменты типа Slony!
Я надеюсь, что эти примеры наглядно иллюстрируют, почему задача миграции является процессом сложным и практически нерешаемым с помощью существующих инструментов. Гарантированно работают только лишь вдумчивый, ручной подход и старая военная мудрость «know your data».
