Задача — конвертировать базы MySQL в PostgreSQL.
PostgreSQL будет в AWS RDS, пока используем локальный сервер.
Установка PostgreSQL — тут>>>, Pentaho — тут>>>.
Содержание
MySQL
Добавляем пользователя с доступом из нашей сети, сread only правами ко всем базам:
[simterm]
MariaDB [(none)]> create user 'userkiev'@'194.***.***.45' identified by 'password'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant select on *.* to 'userkiev'@'194.***.***.45'; Query OK, 0 rows affected (0.00 sec)
[/simterm]
Проверяем:
[simterm]
MariaDB [(none)]> select host, user, password from mysql.user where user like '%kiev'; +----------------+----------+-------------------------------------------+ | host | user | password | +----------------+----------+-------------------------------------------+ | 194.***.***.45 | userkiev | *37FFCD0F3335D8ACB394E3AD769189D597D8786D | +----------------+----------+-------------------------------------------+ 1 row in set (0.00 sec)
[/simterm]
Подключаемся с локальной машины — проверяем удалённый доступ:
[simterm]
16:04:07 [setevoy@setevoy-arch-work ~] $ mysql -h 52.***.***.154 -u userkiev -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9536 Server version: 10.1.17-MariaDB-1~xenial mariadb.org binary distribution Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +----------------------------+ | Database | +----------------------------+ | backup_logs | | frank_test | | helpspot_db | | information_schema | | marin_pb_v2_autodesk | | marin_pb_v2_autodesk_31309 | | marin_pb_v2_autodesk_33244 | | marin_pb_v2_autodesk_33245 | ...
[/simterm]
Проверяем размер базы данных, для теста возьмём marin_pb_v2_yahoo_49987:
[simterm]
MariaDB [(none)]> select sum(round(((data_length + index_length) / 1024 /1024 ), 2)) as "Size in MB" from information_schema.tables where table_schema = "marin_pb_v2_yahoo_49987"; +------------+ | Size in MB | +------------+ | 11.35 | +------------+ 1 row in set (0.20 sec)
[/simterm]
ОК — база маленькая, можно использовать.
PostgreSQL
Переключаемся под пользователя postgres:
[simterm]
$ su -l postgres Password:
[/simterm]
Запускаем сервер:
[simterm]
[postgres@setevoy-arch-work ~]$ pg_ctl start -D /var/lib/postgres/data/ server starting
[/simterm]
Создаём аналогичную базу в PostgresSQL:
[simterm]
[postgres@setevoy-arch-work ~]$ createdb marin_pb_v2_yahoo_49987
[/simterm]
Проверяем:
[simterm]
[postgres@setevoy-arch-work ~]$ psql -c '\l marin*'
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------------------+----------+----------+-------------+-------------+-------------------
marin_pb_v2_yahoo_49987 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)
[/simterm]
Pentaho
Загружаем MariaDB JDBC драйвер:
[simterm]
$ sudo wget https://downloads.mariadb.com/Connectors/java/connector-java-2.1.2/mariadb-java-client-2.1.2.jar -O /opt/pdi/lib/mariadb-java-client-2.1.2.jar
[/simterm]
Запускаем Spoon:
[simterm]
16:21:13 [setevoy@setevoy-arch-work ~] $ spoon &
[/simterm]
Добавляем два соединения к базам данных — удалённая MySQL:
Жмём Test, если всё ОК — сохраняем.
Аналогично — добавляем базу для локального Postgres, в которую будем копировать данные:
Т.к. подключение локальное — то пароль не обязателен:
[simterm]
[postgres@setevoy-arch-work ~]$ cat /var/lib/postgres/data/pg_hba.conf | grep trust # METHOD can be "trust", "reject", "md5", "password", "gss", "sspi", # CAUTION: Configuring the system for local "trust" authentication # the database superuser. If you do not trust all your local users, local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust #local replication postgres trust #host replication postgres 127.0.0.1/32 trust #host replication postgres ::1/128 trust
[/simterm]
Детали тут>>>.
Сохраняем вторую базу.
Копирование данных
Из Tools > Wizard выбираем Copy Tables:
Выбираем Source (слева) и Target (справа):
Выбираем таблицы, которые надо скопировать:
Указываем имя задачи и путь к krt-файлам (krt — Kettle Transformation) и имя новой задачи:
Жмём Finish — проверяем файлы задачи:
[simterm]
16:49:32 [setevoy@setevoy-arch-work ~] $ ls -l Work/PDI\ Jobs/ total 720 -rw-r--r-- 1 setevoy setevoy 17800 Oct 25 16:49 copy_oskar_marin_pb_v2_yahoo_49987account_history_to_postgre_local_test_marin_pb_v2_yahoo_49987.ktr -rw-r--r-- 1 setevoy setevoy 17712 Oct 25 16:49 copy_oskar_marin_pb_v2_yahoo_49987account_to_postgre_local_test_marin_pb_v2_yahoo_49987.ktr -rw-r--r-- 1 setevoy setevoy 17767 Oct 25 16:49 copy_oskar_marin_pb_v2_yahoo_49987calendar_day_to_postgre_local_test_marin_pb_v2_yahoo_49987.ktr -rw-r--r-- 1 setevoy setevoy 17789 Oct 25 16:49 copy_oskar_marin_pb_v2_yahoo_49987calendar_month_to_postgre_local_test_marin_pb_v2_yahoo_49987.ktr -rw-r--r-- 1 setevoy setevoy 17811 Oct 25 16:49 copy_oskar_marin_pb_v2_yahoo_49987calendar_quarter_to_postgre_local_test_marin_pb_v2_yahoo_49987.ktr ...
[/simterm]
Теперь в Spoon появилось представление того, что будет выполнено:
Можно кликнуть правой кнопкой по любому элементу задачи — Edit, или просто дабл-клик и посмотреть получившийся запрос, который будет выполнен:
Готово — жмём Run, запускаем задачу:
Проверяем:
[simterm]
[postgres@setevoy-arch-work ~]$ psql
psql (9.6.5)
Type "help" for help.
postgres=# \c marin_pb_v2_yahoo_49987;
You are now connected to database "marin_pb_v2_yahoo_49987" as user "postgres".
marin_pb_v2_yahoo_49987=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------------------+-------+----------
public | account | table | postgres
public | account_history | table | postgres
public | calendar_day | table | postgres
...
public | publisher_history | table | postgres
(30 rows)
[/simterm]
Ссылки по теме
Converting from other Databases to PostgreSQL
http://sqlfiddle.com — «валидатор» SQL запросов
