Pentaho DI: миграция базы MySQL в PostgreSQL

By | 10/26/2017
 

Задача – конвертировать базы MySQL в PostgreSQL.

PostgreSQL будет в AWS RDS, пока используем локальный сервер.

Установка PostgreSQLтут>>>, Pentahoтут>>>.

MySQL

Добавляем пользователя с доступом из нашей сети, сread only правами ко всем базам:

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)

Проверяем:

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)

Подключаемся с локальной машины – проверяем удалённый доступ:

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 |
...

Проверяем размер базы данных, для теста возьмём marin_pb_v2_yahoo_49987:

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)

ОК – база маленькая, можно использовать.

PostgreSQL

Переключаемся под пользователя postgres:

su -l postgres
Password:

Запускаем сервер:

[postgres@setevoy-arch-work ~]$ pg_ctl start -D /var/lib/postgres/data/
server starting

Создаём аналогичную базу в PostgresSQL:

[postgres@setevoy-arch-work ~]$ createdb marin_pb_v2_yahoo_49987

Проверяем:

[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)

Pentaho

Загружаем MariaDB JDBC драйвер:

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

Запускаем Spoon:

16:21:13 [setevoy@setevoy-arch-work ~]  $ spoon &

Добавляем два соединения к базам данных – удалённая MySQL:

Жмём Test, если всё ОК – сохраняем.

Аналогично – добавляем базу для локального Postgres, в которую будем копировать данные:

Т.к. подключение локальное – то пароль не обязателен:

[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

Детали тут>>>.

Сохраняем вторую базу.

Копирование данных

Из Tools > Wizard выбираем Copy Tables:

Выбираем Source (слева) и Target (справа):

Выбираем таблицы, которые надо скопировать:

Указываем имя задачи и путь к krt-файлам (krtKettle Transformation) и имя новой задачи:

Жмём Finish – проверяем файлы задачи:

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
...

Теперь в Spoon появилось представление того, что будет выполнено:

Можно кликнуть правой кнопкой по любому элементу задачи – Edit, или просто дабл-клик и посмотреть получившийся запрос, который будет выполнен:

Готово – жмём Run, запускаем задачу:

Проверяем:

[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)

Ссылки по теме

Converting from other Databases to PostgreSQL

http://sqlfiddle.com – “валидатор” SQL запросов