Долгое время в системе далеко, далеко...
Попытка переноса базы данных с MySQL на PostgreSQL. Вся документация, которую я прочитал, содержит подробные сведения о том, как перенести структуру. Я очень мало документировал миграцию данных. Схема имеет 13 таблиц (которые были успешно перенесены) и 9 ГБ данных.
Версия MySQL: 5.1.x
Версия PostgreSQL: 8.4.x
Я хочу использовать язык программирования R для анализа данных с помощью операторов выбора SQL; PostgreSQL имеет PL/R, но MySQL не имеет ничего (насколько я могу судить).
Новая надежда
Создайте расположение базы данных (/var
не хватает места, а также не нравится, что версия версии PostgreSQL повсюду - обновление приведет к поломке скриптов!):
-
sudo mkdir -p /home/postgres/main
-
sudo cp -Rp /var/lib/postgresql/8.4/main /home/postgres
-
sudo chown -R postgres.postgres /home/postgres
-
sudo chmod -R 700 /home/postgres
-
sudo usermod -d /home/postgres/ postgres
Все хорошо здесь. Затем перезапустите сервер и настройте базу данных с помощью этих инструкций по установке:
-
sudo apt-get install postgresql pgadmin3
-
sudo /etc/init.d/postgresql-8.4 stop
-
sudo vi /etc/postgresql/8.4/main/postgresql.conf
- Измените
data_directory
на/home/postgres/main
-
sudo /etc/init.d/postgresql-8.4 start
-
sudo -u postgres psql postgres
-
\password postgres
-
sudo -u postgres createdb climate
-
pgadmin3
Используйте pgadmin3
для настройки базы данных и создания схемы.
Эпизод продолжается в удаленной оболочке, известной как bash
, при запуске обеих баз данных и установке набора инструментов с довольно необычным логотипом: SQL Fairy.
-
perl Makefile.PL
-
sudo make install
-
sudo apt-get install perl-doc
(как ни странно, он не называетсяperldoc
) -
perldoc SQL::Translator::Manual
Извлеките DDL, совместимый с PostgreSQL, и все данные MySQL
:
-
sqlt -f DBI --dsn dbi:mysql:climate --db-user user --db-password password -t PostgreSQL > climate-pg-ddl.sql
- Измените
climate-pg-ddl.sql
и преобразуйте идентификаторы в нижний регистр и вставьте ссылку схемы (используя VIM):-
:%s/"\([A-Z_]*\)"/\L\1/g
-
:%s/ TABLE / TABLE climate./g
-
:%s/ on / on climate./g
-
-
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 в нижний регистр:
-
select concat( 'RENAME TABLE climate.', TABLE_NAME, ' to climate.', lower(TABLE_NAME), ';' ) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='climate';
- Выполните команды с предыдущего шага.
- Возможно, есть способ сделать то же самое для столбцов; Я изменил их вручную, потому что это было быстрее, чем выяснить, как написать запрос.
База данных наносит ответный удар
Восстановите структуру в PostgreSQL следующим образом:
-
pgadmin3
(переключитесь на него) - Нажмите Выполнить произвольные запросы SQL
- Открыть
climate-pg-ddl.sql
- Найдите
TABLE "
замените наTABLE climate."
(введите имя схемыclimate
) - Найдите
on "
замените наon climate."
(введите имя схемыclimate
) - Нажмите
F5
для выполнения
Это приводит к:
Query returned successfully with no result in 122 ms.
Ответы джедая
В этот момент я в тупике.
- Где я могу перейти отсюда (каковы шаги) для преобразования
climate-my.sql
вclimate-pg.sql
, чтобы они могли быть выполнены против PostgreSQL? - Как я уверен, что индексы скопированы правильно (для поддержания ссылочной целостности, у меня нет ограничений на данный момент, чтобы облегчить переход)?
- Как я могу гарантировать, что добавление новых строк в PostgreSQL начнет перечисляться из индекса последней вставленной строки (и не конфликтует с существующим первичным ключом из последовательности)?
- Как вы гарантируете, что имя схемы появляется при преобразовании данных из MySQL в PostgreSQL вставки?
Ресурсы
Для достижения этой цели понадобилась справедливая информация:
- https://help.ubuntu.com/community/PostgreSQL
- http://articles.sitepoint.com/article/site-mysql-postgresql-1
- http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL
- http://pgfoundry.org/frs/shownotes.php?release_id=810
- http://sqlfairy.sourceforge.net/
Спасибо!