Переезд с PostgreSQL на MySQL

    Не секрет, что Drupal 6.x не очень хорошо дружит с PostgreSQL. Верней ядро дружит, а вот отдельные contrib модули далеко не всегда. И вот надоело мне наблюдать периодически вываливающиеся warning'и и накладывать патчи — решил переехать на MySQL.

    Google на всяческие запросы о такого рода переезде упорно выдавал миграцию в обратную сторону — MySQL to PostgreSQL, понятно почему, но не радует. Если выкинуть Drupal из контекста, то начинают находиться платные продукты и всяческие не идеальные скриптики. В итоге решил сделать все самостоятельно ручками.

    Подопытные: drupal-6.8, mysql-5.0.70-r1, postgresql-8.0.15.

    Последовательность:
    1. Не знаю почему, но как то так повелось, что эксперименты ставлю не на тестовых инсталляциях, а сразу на продакшн. Поэтому первым делом отключаем доступ к сайту. Я это сделал на frontend-сервере (nginx): своему айпишнику разрешил, а всем остальным запретил. И не потому, что не доверяю стандартному Drupal'овскому maintenance режиму, а потому что возможно понадобилось бы пару-другую раз прогнать процесс установки с нуля.
    2. Заходим в админку, отключаем и чистим всяческие кэши, индексы и логи, которые хранятся в базе — гемору от них при переносе будет больше чем пользы после.
    3. Сохраняем список включенных модулей. Я воспользовался плагином для Firefox — ScrapBook.
    4. Бэкапим данные текущей базы, причем без схемы:
      pg_dump -U postgres -D -d drupal > old_data.sql
      Вырезаем из этого дампа всё, кроме INSERT'ов.
    5. Создаем для Drupal'а базу и пользователя в MySQL.
    6. Переносим куда-нибудь текущий settings.php и запускаем процесс установки Drupal'а с нуля, указав созданных ранее пользователя и базу.
    7. После установки вносим необходимые правки в settings.php и включаем нужные модули, сверяясь с ранее сохраненным списком. Эта операция создаст в MySQL таблицы необходимые модулям.
    8. Просим phpmyadmin сделать дамп структуры таблиц MySQL с их принудительным DROP'ом — new_schema.sql.
    9. Объединяем дампы:
      cat new_schema.sql old_data.sql > install.sql
    10. В полученном файле search-replace'ом аккуратно заменяем названия столбцов вида «type» на type (с кавычками на без кавычек).
    11. Проверяем:
      mysql -u drupal -p drupal < install.sql
      В моем случае MySQL ругался исключительно на названия столбцов. Повторяем предыдущий и этот шаги до тех пор пока MySQL целиком не скушает install.sql.
    12. Мне дополнительных действий не потребовалось — сайт вернулся к работе, но уже на MySQL.
    13. И не забываем включить обратно кэши и доступ к сайту извне.

    На всё-про-всё понадобилось порядка получаса — гуглил дольше.

    P.S. Не судите строго — это мой первый пост на habr. До этого был «чукча не писатель — чукча читатель».
    Поделиться публикацией
    Похожие публикации
    Реклама помогает поддерживать и развивать наши сервисы

    Подробнее
    Реклама
    Комментарии 51
    • 0
      Вам повезло что в вашей базе ругань была только на «type», когда я проект переносил ругань была буквально на все. Я не понял вы переносили только структуру?, я переносил еще и данные, в результате сбились поля где хранилось время+дата, хотя эти поля были для нас маловажны, поэтому нас это удовлетворило.(кстати у меня был дамп Постгрес базы, а не сервер с самой БД, т.е. я еще себе предварительно устанавливал сам постгрес и пытался туда сунуть этот дамп, тоже кстати без ручной правки не обошлось).

      Для конвертации использовал MSQL2PSQLS131(честно скачаный с просторов русского интернета:)), эта прога мне перелила нужные таблицы из постгрес в мускул.

      Наверно все это можно было проделать проще и быстрее, но я не знал как.
      • +1
        Переносил данные, а схемы таблиц Drupal создал сам. Время+дата не сбились — проверял.
        И да — MySQL пришлось поставить и пытаться туда сунуть дамп :)
        Надо будет взглянуть на MSQL2PSQLS131 — ссылку не подскажите?
        • 0
          И да — ругань была не только на «type», а также на всякие «что-угодно». Прикол именно в кавычках на запросе INSERT INTO name («column») VALUES (...);
        • 0
          > MySQL to PostgreSQL, понятно почему
          С PgSQL оно производительнее?
          • 0
            Утверждается, что да, но лично у меня до сегодняшнего дня не было желания проверять на практике. А сейчас так или иначе придется оценить :)
            • +1
              Их сложно сравнивать. На простых запросах MySQL быстрее, на сложных — PostgreSQL. Но главное: PostgreSQL элементарно больше умеет, посему переехать с него на MySQL практически нереально, а в обратную сторону — достаточно несложно.

              P.S. Речь идёт, понятно, о продуктах более-менее заточенных на определённую СУБД. Если вы используете универсальное решение типа Drupal, то вы теряете на универсальности столько, что говорить о преимуществе MySQL или PostgreSQL смысла нет.
              • НЛО прилетело и опубликовало эту надпись здесь
                • +1
                  Прямо таки все? и даже если есть индекс по user-rating, то использовать его не будет?
                  • НЛО прилетело и опубликовало эту надпись здесь
                    • 0
                      А у вас ведь в табличке articles хранятся тексты? вы используете тип TEXT или BLOB?
                      Есть такая проблема и она связана с тем что mysql не может определить сколько памяти ему понадобится для хранения результата, поэтому создает файл. Все здравомыслящие люди размещают временный каталог mysql в памяти. Особенно кстати оказывается tmpfs в линуксе.

                      Там чуть ниже товарищи недоумевают, я взял их схему бд с Blob и покажу обходной путь, однако запрос будет выглядеть сложнее:
                      explain select * from articles
                      inner join (select article_id FROM articles WHERE user_rating > 5 ORDER BY published_at DESC LIMIT 100 )
                      as lim using (article_id);
                      *************************** 1. row *********
                                 id: 1
                        select_type: PRIMARY
                              table: <derived2>
                               type: ALL
                      possible_keys: NULL
                                key: NULL
                            key_len: NULL
                                ref: NULL
                               rows: 3
                              Extra:
                      *************************** 2. row *********
                                 id: 1
                        select_type: PRIMARY
                              table: articles
                               type: eq_ref
                      possible_keys: PRIMARY
                                key: PRIMARY
                            key_len: 4
                                ref: lim.article_id
                               rows: 1
                              Extra:
                      *************************** 3. row *********
                                 id: 2
                        select_type: DERIVED
                              table: articles
                               type: index
                      possible_keys: NULL
                                key: published_at
                            key_len: 14
                                ref: NULL
                               rows: 5
                              Extra: Using where; Using index
                      

                      Как видите filesort исчез и появился covering index. Как чудесно читать документацию, правда?

                      Другое дело что mysql действительно мог бы быть поумнее. Эта оптимизация выглядит очевидной для человека.
                      • НЛО прилетело и опубликовало эту надпись здесь
                        • НЛО прилетело и опубликовало эту надпись здесь
                          • 0
                            уу… если не идти против ОРМ, то тут сразу надо просто покупать новые сервера… :-) и это слабо зависит от СУБД
                    • 0
                      Простейший запрос вида SELECT * FROM articles WHERE user_rating > 5 ORDER BY published_at DESC LIMIT 100; мусклем выполняется из рук вон плохо: он будет сортировать на жестком диске _все_ статьи.
                      Действительно подобный запрос, который сложнее, чем 99% (а то и 99.9%) запросов, выполняемых web-приложениями в таком виде будет выполняться медленно — особенно если не будет соотвествующих индексов.

                      • НЛО прилетело и опубликовало эту надпись здесь
                        • –1
                          Я не буду даже пытаться этого сделать! Вместо этого я введу ещё одну таблицу где соберу только статьи пользователей с рейтингом больше 5 и отсортирую их по published_at.

                          Что-что? Я потеряю в гибкости? Ну да, разумеется. Но тут уж выбирайте — вам шашечки или ехать.

                          P.S. Вообще для большинства Web-приложений (не требующих обработки 10'000 запросов в секунду) MySQL — не лучший выбор именно потому что сложно заранее предусмотреть все запросы подобного вида и предусмотреть для них отдельные таблицы.
                          • НЛО прилетело и опубликовало эту надпись здесь
                            • 0
                              Я отлично понимаю ограниченность этого решения, но вот насчёт чудовищности — это хороший вопрос. Как я уже говорил выше: вы сильно переоцениваете уровень гибкости, который нужен web-проектам. Но если вас не интересуют экстремальные нагрузки и есть возможность потратить некоторое количество ресурсов сервера впустую (зато получить многократный выигрыш на времени работы программиста), то MySQL — не лучший выбор.

                              Популярность MySQL там, где его козыри не могут себя проявить, а недостатки очевидны — загадка и для меня самого.
                              • НЛО прилетело и опубликовало эту надпись здесь
                                • –1
                                  так в чем его козыри то?
                                  Максимальная скорость на простых запросах. Нужно ли вам это или нет — решать вам. И хотите ли вы перепроектировать систему чтобы получить адекватный результат — тоже.

                                  MySQL позволяет сделать гораздо более эффективную систему, чем PostgreSQL, однако требует для этого гораздо больше усилий.

                                  Какого фига MySQL используют web-студии для простых и слабо загруженных сайтов (когда возможный выигрыш от MySQL не сможет дополнительных денег, вбуханных в разработку и за 1000 лет) — мне неведомо. В большинстве случаев в результате система работает хуже и медленнее, чем могла бы на основе PostgreSQL…

                                  P.S. Есть у PostgreSQL одна вещь из-за которых я не рекомендую использовать его для больших проектов: невозможность постепенного изменения формата базы (нужен цикл dump/restore при смене версий). Но, опять-таки, это проблема для проектов где данных сотни гигабайт, а их не так много…
                                  • НЛО прилетело и опубликовало эту надпись здесь
                                    • 0
                                      ммм, зачем dump/restore? В 8.2+ уже есть достаточно неплохой автовакуум, но как говорят, все равно нужно вакуумизировать руками.
                                      Какая разница? Все эти способы всё равно требуют останова системы на изрядный промежуток времени и не позволяют делать эффективный откат. Как происходит upgrade у нормальной базы данных? В четыре этапа:
                                      1. Устанавливается новая версия бинарников (данные остаются в старом формате).
                                      2. Данные конвертируются в новый формат «на лету» (измененённые данные заносятся в специальные временные таблицы — понятно что для этого нужна поддержка в приложении).
                                      3. Приложение переписывается с использованием новых фич — разумеется они работают только с переконвертированными таблицами.
                                      4. Постепенно все данные переводятся в новый формат и мы готовы к следующему циклу.
                                      Каждый из этих этапов занимает несколько недель (либо сам процесс столько занимает, либо столько времени требуется на то, чтобы убедиться в том, что всё работает) и каждый можно откатить за секунды. Но для того, чтобы это дело работало нужно чтобы версия базы данных X поддерживала два формата: X и X-1.

                                      Насчет более эффективной системы — вы хотите сказать, что мускль быстрее постгреса вытащит данные из базы, если у нас одно поле выборки и по нему есть индекс. Так?
                                      Да. Меньше накладные расходы. Насколько я знаю — это до сих пор правда, хотя последний раз бенчмарки я видел год или полтора назад. Просто разработчики PostgreSQL сконцентрированы на другом.
                                      • НЛО прилетело и опубликовало эту надпись здесь
                                        • 0
                                          Насчет апдейта понял. Разве тут у мускля есть серьезные преимущества перед постгресом?
                                          Да. При upgrade вам необходимо обязательно сконвертировать в новый формат одну базу: mysql. В которой хранятся системные настройки. Они практически никогда не бывает больше пары мег (и даже это — редкость). Всё остальное можно переводить постепенно — буквально по одной таблице.

                                          На 8.3 я посмотрю, но, опять-таки, всё это для небольших проектов. PostgreSQL при всех своих наворотах никак не хочет отказаться от своего «исследовательско-университетского прошлого», что грустно. Может быть поэтому его «серъёзные люди» и не используют?
                                          • НЛО прилетело и опубликовало эту надпись здесь
                      • 0
                        Почему же все тогда MySQL пользуют?
                        • НЛО прилетело и опубликовало эту надпись здесь
                          • +1
                            Привычка. Большинство сложных современных систем выросли из простых вещей типа форума. Там MySQL действительно был идеален: простые запросы, почти нет запросов на запись, только на чтение. Но добавление в них всевозможных примочек (разные виды рейтингов, всевозможные иерархические структуры и т.д. и т.п.) постепенно вывели их в структуру куда грамотно MySQL применить непросто — и никто, в общем, и не пытается. Но перевести работающую и отлаженную систему на PostgreSQL — это же весьма непростое дело.

                            Та же ситуация что и «Windows vs Linux» или «Windows vs MacOS»: почти всем очевидно что Windows — самый худший вариант из трёх, но ведь к нему уже все привыкли и для него написаны миллионы программ! Как же их вот прямо так взять и бросить?

                            P.S. Надо кстати сказать что во многих случаях переход на PostgreSQL всё равно не даст вам того, что можно получить от перепроектирования системы. Ну какой PostgreSQL может спасти «ультрагибкую CMS», которая чтобы породить одну страничку делает пару сотен запросов к базе данных?
                            • 0
                              Ну так и MySQL же тоже растёт, к 6 версии новый Storage Engine обещали, или вы считаете, что её уже ничего не спасёт?

                              Все нынешние вордпрессы, джумлы, рельсы и прочие джанги поддерживают как MySQL, так и PgSQL — аргумент «но ведь к нему уже все привыкли и для него написаны миллионы програм» немного не в тему.
                              • +1
                                Falcon — это жутко крутая вещь. Будет. Когда-нибудь. Когда — одному богу ведомо. Там масса проблем и не так шоб уж сильно много рабочих рук.

                                Что касается «прочие джанги которые поддерживают как MySQL, так и PgSQL» — то это вообще не в кассу. Они поддерживают и MySQL и PostgreSQL одинаково отвратительно. Если вы хотите говорить о производительности — о переносимости сразу забудьте, а если производительность неважна (а это, как ни удивительно, не такой и редкий случай), то не всё ли равно какую СУБД использовать.
                            • 0
                              почему же все пользуют php при наличии python/ruby?
                            • 0
                              Т.е. если есть хоть какая-то загрузка, лично я попросту ещё не нашел смысла в мускле и был бы рад узнать, чем же он крут.
                              MySQL отлично держит нагрузку на огромном количестве простых запросов. Судя по вашему примеру у вас несколько смещено понятие «простого» запроса. Возможно на вас плохо повлияло обучение релицонным базам данных, возможно вы просто как-то по другому мыслите, но простой запрос — это простой запрос: выборка по одному индексу, сравнение на =, <, >. Вы не поверите, но для огромного числа задач ничего больше и не нужно. Если правильно базу организовать.
                              • НЛО прилетело и опубликовало эту надпись здесь
                                • 0
                                  Выше я привёл один вариант. Возможны и другие. Вы вообще не на том уровне задачу решаете. Если вы хотите нормальной скорости от MySQL, то вы должны плясать не от запросов и даже не от структуры данных, а от интерфейса пользователя. Тогда можно понять какие вещи вам нужно выбирать быстро, а какие — не очень. И соответственно спроектировать базу. При необходимости можно и GUI соответствующим образом переделать, чтобы запросы было проще обрабатывать.

                                  Хотите посмотреть на пример грамотно спроектированной системы на основе MySQL с исходниками — посмотрите на mediawiki: Wikipedia выдерживает хорошо за 10'000 qps.

                                  Но если экстремальные нагрузки вас не интересуют и вас интересует гибкость, то MySQL, несомненно, плохой выбор.
                              • 0
                                mysql> create table articles (article_id int auto_increment primary key, user_rating int, published_at datetime, key(published_at, user_rating)) engine=innodb;
                                Query OK, 0 rows affected (0.06 sec)
                                
                                mysql> insert into articles values(1,1,'2008-01-01'),(2,4,'2008-04-04'),(3,10,'2007-12-12'),(4,20,'2008-11-11'),(5,10,'2008-11-11');
                                Query OK, 5 rows affected (0.00 sec)
                                Records: 5  Duplicates: 0  Warnings: 0
                                
                                mysql> explain select * from articles where user_rating > 5 order by published_at desc;
                                +----+-------------+----------+-------+---------------+--------------+---------+------+------+--------------------------+
                                | id | select_type | table    | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
                                +----+-------------+----------+-------+---------------+--------------+---------+------+------+--------------------------+
                                |  1 | SIMPLE      | articles | index | NULL          | published_at | 14      | NULL |    5 | Using where; Using index |
                                +----+-------------+----------+-------+---------------+--------------+---------+------+------+--------------------------+
                                1 row in set (0.02 sec)
                                

                                что я делаю не так?
                                • НЛО прилетело и опубликовало эту надпись здесь
                                  • 0
                                    Вы не используете поля типа TEXT или BLOB А автор текстов и постов просто не достаточно глубоко изучил Mysql.
                                    • 0
                                      проиллюстрирую:

                                      CREATE TABLE `articles` (
                                      `article_id` int(11) NOT NULL AUTO_INCREMENT,
                                      `pagetext` text,
                                      `user_rating` int(11) DEFAULT NULL,
                                      `published_at` datetime DEFAULT NULL,
                                      PRIMARY KEY (`article_id`),
                                      KEY `published_at` (`published_at`,`user_rating`)
                                      ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=cp1251

                                      mysql> explain select * from articles where user_rating > 5 order by published_at desc \G;
                                      *************************** 1. row ***************************
                                      id: 1
                                      select_type: SIMPLE
                                      table: articles
                                      type: ALL
                                      possible_keys: NULL
                                      key: NULL
                                      key_len: NULL
                                      ref: NULL
                                      rows: 5
                                      Extra: Using where; Using filesort
                                      • 0
                                        Прекрасно решается вынесением текстов в отдельную таблицу.
                                        • 0
                                          И в каких известных веб-проектах так сделано? Кто вообще об этом беспокоится?
                                          Я знаю только личные сообщения в vbulletin, но там совершенно по иной причине разделены характеристики сообщения и текст в разных табличках. Причем посты на форуме у них все равно в одной таблице.
                                  • 0
                                    PostgreSQL до 8.3 тоже так делал при отсутствии индекса
                              • +2
                                разумные ходы!!! ;) жизнь наверное до этого уже покидала при переносе с одной бд на другую
                                • 0
                                  Люблю людей которые ставят минус просто так, без разумного обоснования причины
                                  • +3
                                    Люблю людей который так говорят :)
                                    • 0
                                      люблю людей, которые как и я это любят подчеркнуть :)
                                      • 0
                                        А я вообще людей люблю, особенно женского пола.
                                • НЛО прилетело и опубликовало эту надпись здесь
                                  • 0
                                    Заранее спасибо.
                                    Кстати, думаю что в случае с для Drupal'ом предложенный способ должен сработать и в обратную сторону ;)
                                    • НЛО прилетело и опубликовало эту надпись здесь
                                  • 0
                                    Кроме того в пункте 7 наряду с тем, что мы «включаем нужные модули» следует упомянуть, что нужно создать необходимые типы материалов с их дополнительными cck полями, иначе не будут созданы таблицы content_type_NODETYPE.
                                    • 0
                                      Спасибо за статью. Перенес друпал с одного хостинга на другой по пути поменяв базу с PostgreSQL на MySQL.
                                      Было два типа ошибок при переносе:
                                      1) Названия полей в кавычках (всего в двух таблицах было). Быстро решилось заменой в редакторе.
                                      2) Значения полей типа BLOB. Из Постгреса они пришли в виде '\x123abc', а для mysql надо сделать 0x123abc (без кавычек и с нулем). Это самое геморное, но в итоге решил автозаменой регулярным выражением.

                                      Как только эти проблемы исправил — сайт сразу заработал. Плюс надо не забыть создать все таблице кэша, а данные самого кэша не импортировать.

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