Вопросы Теги

Мигрируйте от MySQL до PostgreSQL на Linux (Kubuntu)

Давным-давно в системе далеко, далеко...

Попытка переместить базу данных от MySQL до PostgreSQL. Вся документация я считал покрытия, в мельчайших подробностях, как переместить структуру. Я нашел очень мало документации относительно миграции данных. Схема имеет 13 таблиц (которые были перемещены успешно), и 9 ГБ данных.

Версия MySQL: 5.1.x
Версия PostgreSQL: 8.4.x

Я хочу использовать язык программирования R для анализа данных с помощью операторов выбора SQL; PostgreSQL имеет PL/R, но MySQL ничего не имеет (насколько я могу сказать).

Новая надежда

Создайте местоположение базы данных (/var имеет недостаточное пространство; также не любите иметь номер версии PostgreSQL везде - обновление повредило бы сценарии!):

  1. sudo mkdir -p /home/postgres/main
  2. sudo cp -Rp /var/lib/postgresql/8.4/main /home/postgres
  3. sudo chown -R postgres.postgres /home/postgres
  4. sudo chmod -R 700 /home/postgres
  5. sudo usermod -d /home/postgres/ postgres

Вся польза к здесь. Затем, перезапустите сервер и настройте базу данных с помощью этих инструкций по установке:

  1. sudo apt-get install postgresql pgadmin3
  2. sudo /etc/init.d/postgresql-8.4 stop
  3. sudo vi /etc/postgresql/8.4/main/postgresql.conf
  4. Изменение data_directory кому: /home/postgres/main
  5. sudo /etc/init.d/postgresql-8.4 start
  6. sudo -u postgres psql postgres
  7. \password postgres
  8. sudo -u postgres createdb climate
  9. pgadmin3

Использовать pgadmin3 настроить базу данных и создать схему.

Эпизод продолжается в удаленной оболочке, известной как bash, с обоими выполнениями баз данных и установкой ряда инструментов с довольно необычным логотипом: Фея SQL.

  1. perl Makefile.PL
  2. sudo make install
  3. sudo apt-get install perl-doc (странно, это не называют perldoc)
  4. perldoc SQL::Translator::Manual

Извлеките PostgreSQL-дружественный DDL и весь MySQL данные:

  1. sqlt -f DBI --dsn dbi:mysql:climate --db-user user --db-password password -t PostgreSQL > climate-pg-ddl.sql
  2. Править climate-pg-ddl.sql и преобразуйте идентификаторы в нижний регистр и вставьте ссылку схемы (использование VIM):
    • :%s/"\([A-Z_]*\)"/\L\1/g
    • :%s/ TABLE / TABLE climate./g
    • :%s/ on / on climate./g
  3. mysqldump --skip-add-locks --complete-insert --no-create-db --no-create-info --quick --result-file="climate-my.sql" --databases climate --skip-comments -u root -p

Могло бы стоить просто переименовать таблицы и столбцы в MySQL к нижнему регистру:

  1. select concat( 'RENAME TABLE climate.', TABLE_NAME, ' to climate.', lower(TABLE_NAME), ';' ) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='climate';
  2. Выполните команды от предыдущего шага.
  3. Существует, вероятно, способ сделать то же для столбцов; я изменил их вручную, потому что это было быстрее, чем выяснение, как записать запрос.

База данных наносит ответный удар

Воссоздайте структуру в PostgreSQL следующим образом:

  1. pgadmin3 (переключитесь на него),
  2. Нажмите Execute произвольный значок SQL-запросов
  3. Открытый climate-pg-ddl.sql
  4. Поиск TABLE " замена TABLE climate." (вставьте имя схемы climate)
  5. Поиск on " замена on climate." (вставьте имя схемы climate)
  6. Пресса F5 выполниться

Это приводит к:

Query returned successfully with no result in 122 ms.

Ответы джедая

В этой точке я озадачен.

  • Где делают я иду отсюда (что является шагами) преобразовать climate-my.sql кому: climate-pg.sql так, чтобы они могли быть выполнены против PostgreSQL?
  • Как я удостоверяюсь, что индексы копируются правильно (для поддержания ссылочной целостности; у меня нет ограничений в данный момент для упрощения перехода)?
  • Как я удостоверяюсь, что добавление новых строк в PostgreSQL будет начинать перечислять от индекса последней вставленной строки (и не конфликтовать с существующим первичным ключом от последовательности)?
  • Как Вы удостоверяетесь, что название схемы проникает, когда преобразование данных от MySQL до PostgreSQL вставляет?

Ресурсы

Маленькая информация была необходима для получения настолько далеко:

Спасибо!

18
задан Dave Jarvis 25 February 2011 в 02:23
поделиться

3 ответа

Обычно для таких миграций я делаю двоякое:

  • Извлекаю полное определение базы данных из MySQL и адаптирую его к синтаксису PostgreSQL.
  • Просмотрите определение базы данных и преобразуйте его, чтобы воспользоваться функциями PostgreSQL, которых нет в MySQL.

Затем выполните преобразование и напишите программу на любом удобном для вас языке, которая выполняет следующие действия:

  • Считывает данные из базы данных MySQL.
  • Выполняет все необходимые преобразования данных, которые должны храниться в базе данных PostgreSQL.
  • Сохраняет преобразованные данные в базе данных PostgreSQL.

Измените дизайн таблиц для PostgreSQL, чтобы воспользоваться его возможностями.

Если вы просто делаете что-то вроде использования сценария sed для преобразования дампа SQL из одного формата в другой, все, что вы делаете, - это помещаете базу данных MySQL на сервер PostgreSQL. Вы можете это сделать, и от этого все равно будет некоторая польза, но если вы собираетесь мигрировать, выполняйте миграцию полностью.

Это потребует немного больше времени на предварительную подготовку, но я еще не сталкивался с ситуацией, когда это того не стоит.

4
ответ дан 30 November 2019 в 09:36
поделиться

Конвертируйте файл mysqldump в формат, совместимый с PostgreSQL

Конвертируйте данные следующим образом (не используйте mysql2pgsql.perl ) :

  1. Избегайте кавычек.

    sed "s / \\\ '/ \' \ '/ g" clim-my.sql | sed "s / \\\ r / \ r / g" | sed "s / \\\ n / \ n / g"> escaped-my.sql

  2. Замените USE "климат"; путем поиска и прокомментируйте комментарии:

    sed " s / USE \ "климат \"; / SET search_path TO климат; / g "escaped-my.sql | sed "s / ^ \ / \ * / - /"> clim-pg.sql

  3. Подключиться к базе данных.

    sudo su - postgres
    psql климат

  4. Установите кодировку (mysqldump игнорирует свой параметр кодировки), а затем выполните сценарий.

    \ encoding iso-8859-1
    \ i clim-pg.sql

Эта последовательность шагов, вероятно, не будет работать для сложных баз данных со многими смешанными типами. Однако он работает для целых s, varchar s и float s.

Индексы, первичные ключи и последовательности

Поскольку mysqldump включал первичные ключи при генерации операторов INSERT , они будут превосходить автоматическую последовательность таблицы. Последовательности для всех таблиц при проверке оставались равными 1.

Установить последовательность после импорта

Использование команды ALTER SEQUENCE установит для них любое необходимое значение.

Префикс схемы

Нет необходимости в префиксе таблиц с именем схемы. Используйте: 

SET search_path TO climate;
2
ответ дан 30 November 2019 в 09:36
поделиться

Если вы конвертировали схему, то перенос данных будет простой частью:

  • дамп схемы из PostgreSQL (вы сказали, что конвертировали схему в Postgres, поэтому мы пока сделаем дамп, так как мы будем удалять и воссоздавать целевую базу данных, чтобы очистить ее):

    pg_dump dbname > /tmp/dbname-schema.sql

  • разбиваем схему на 2 части - /tmp/dbname-schema-1.sql, содержащий операторы создания таблиц, /tmp/dbname-schema-2.sql - остальное. PostgreSQL требует импорта данных до импорта внешних ключей, триггеров и т.д., но после импорта определений таблиц.

  • воссоздать базу данных только с 1 частью схемы:

    drop database dbname
создать базу данных dbname
\i /tmp/dbname-schema-1.sql
-- теперь у нас есть таблицы без данных, триггеров, внешних ключей и т.д.

  • import data:

    (
 echo 'start transaction';
 mysqldump --skip-quote-names dbname | grep ^INSERT;
 echo 'commit'
) | psql dbname
-- теперь у нас есть таблицы с данными, но без триггеров, внешних ключей и т.д.

    Опция --skip-quote-names добавлена в MySQL 5.1.3, поэтому если у вас более старая версия, то временно установите новый mysql в /tmp/mysql (configure --prefix=/tmp/mysql && make install должны сделать это) и используйте /tmp/mysql/bin/mysqldump.

  • импортируйте остальную часть схемы:

    psql dbname
начать транзакцию
\i /tmp/dbname-schema-2.sql
commit
-- мы закончили
2
ответ дан 30 November 2019 в 09:36
поделиться
Другие вопросы по тегам:

Похожие вопросы: