Как мигрировать MySQL в PostgreSQL 2 способами?

Системы управления реляционными базами данных (RDBMS) широко используются во многих отраслях, от электронной коммерции и веб-сайтов до финансовых услуг и географического дизайна. MySQL vs PostgreSQL - два популярных из них, открытые для свободного использования, и они занимают места в топ-5 баз данных, привлекающих мировое внимание.

На основании различных сценариев использования, бизнес, которому требуются сложные долгосрочные запросы, продвинутые запросы, такие как соединения, аналитика, окно и т. д., или требования к данным с соблюдением ACID, может обнаружить, что PostgreSQL - предпочтительный вариант по сравнению с MySQL.

Обычно MySQL часто используется как небольшая веб-база данных для организаций, которые тщательно настроили, просмотрели и относительно простые запросы, генерируемые приложением (особенно те, которые могут использовать PRIMARY KEYs). MySQL в таких случаях работает быстрее.

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

Если вы занимаетесь миграцией MySQL в PostgreSQL (см. преобразование PostgreSQL в MySQL), этот блог представит два метода преобразования.

Миграция MySQL на PostgreSQL с помощью Pgloader

Pgloader - это бесплатный инструмент для миграции баз данных, который позволяет пользователям осуществлять непрерывную миграцию из MySQL, SQLite и MS SQL Server в PostgreSQL.

Предпосылки:

• Доступ к серверам, которые настраивают брандмауэр, и пользователю, не являющемуся root, с правами sudo.

• Пользователь Root MySQL аутентифицируется с помощью пароля и принимает зашифрованные соединения.

• Используйте сервер PostgreSQL в качестве клиентской машины MySQL.

1.     Установите Pgloader.

Вы можете установить Pgloader из репозиториев Ubuntu APT с использованием команды apt, но для использования опции useSSL в v3.5.1 и более поздних версиях, вам придется установить из репозитория GitHub с использованием исходного кода.

a.  Обновите индекс пакета сервера Postgres.

          postgres-server$ 

sudo apt update

b.     Введите следующие команды и нажмите Enter, чтобы установить зависимости Pgloader.

postgres-server$ sudo apt install sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev

c.     Перейдите на страницу релизов проекта на GitHUb, найдите последнюю версию> Assets > скопируйте ссылку на исходный код (tar.gz), вставьте в команду curl и замените URL https.

postgres-server$ curl -fsSLO https://github.com/dimitri/pgloader/releases/download/v3.6.9/pgloader-bundle-3.6.9.tgz

d.     Извлеките tarball.

postgres-server$ tar xvf v3.6.9.tar.gz

e.     Перейдите в новый родительский каталог Pgloader.

postgres-server$ cd pgloader-3.6.9/

f.      Скомпилируйте бинарный файл Pgloader.

postgres-server$ сделать pgloader

g.     Переместите двоичный файл в каталог /usr/local/bin.

postgres-server$ sudo mv ./build/bin/pgloader /usr/local/bin/

h.     Проверьте версию Pgloader, чтобы подтвердить его установку.

postgres-server$ pgloader –version

2.     Создайте роль и базу данных PostgreSQL.

Пользователи базы данных PostgreSQL могут быть аутентифицированы через протокол идентификации/ident (по умолчанию) и пароль, в то время как вариант по умолчанию обеспечивает большую безопасность, он создает проблемы для внешнего подключения.

Когда роль аутентифицируется с использованием метода ident и имеет то же имя, что и профиль пользователя Linux, выпускающий команду pgloader, Pgloader может загружать данные в базу данных Postgres с использованием этой роли. В этом руководстве вы узнаете, как настроить роль PostgreSQL с аутентификацией по паролю.

a.     Создайте новую роль. -P флаг запрашивает пароль для новой роли.

postgres-server$ sudo -u postgres createuser --interactive -P

b.     Затем скрипт запросит имя новой роли.

Enter name of role to add:

c.     Введите и подтвердите пароль для роли.

Enter password for new role:

Enter it again:

d.     Классифицируйте новую роль как суперпользователя для расширенных привилегий. Введите y и нажмите ENTER.

Output

. . .

Shall the new role be a superuser? (y/n) y

e.     Создайте целевую базу данных для загрузки исходных данных.

postgres-server$ sudo -u postgres createdb xxx

3.     Создайте специального пользователя MySQL и управляйте сертификатами.

a.     Откройте приглашение MySQL.

mysql -u root -p

b.     Создайте нового пользователя MySQL. Замените your_postgres_server_ip на общедоступный IP-адрес PostgreSQL и password на безопасный пароль или парольную фразу.

mysql> CREATE USER 'new_mysql_name'@'your_postgres_server_ip' IDENTIFIED BY 'password' REQUIRE SSL;

с.     Предоставьте новосозданному пользователю MySQL доступ к цели.

GRANT ALL ON source_db.* TO ' new_mysql_name'@'your_postgresql_server_ip';

d.     Перезагрузите таблицы разрешений, чтобы включить изменения привилегий.

mysql> FLUSH PRIVILEGES;

e.     Закройте приглашение.

mysql> exit

f.      Добавьте файлы ca.pem и client-cert.pem в доверенное хранилище сертификатов Ubuntu, чтобы включить SSL для Pgloader. Не забудьте изменить имена этих файлов, чтобы включить расширение файла .crt.

postgres-server$ sudo cp ~/client-ssl/ca.pem /usr/local/share/ca-certificates/ca.pem.crt

postgres-server$ sudo cp ~/client-ssl/client-cert.pem /usr/local/share/ca-certificates/client-cert.pem.crt

h.     Ищите сертификаты.

postgres-server$ sudo update-ca-certificates

4.     Перенесите данные из MySQL в PostgreSQL.

$ pgloader mysql://mysql_username:password@mysql_server_ip_/source_database_name? useSSL=true postgresql:// postgresql://postgresql_role_name:password@postgresql_server_ip/target_database_name?option_1=value&option_n=value

5.     Проверьте миграцию.

a.     Запустите запрос PostgreSQL.

postgres-server$ sudo -i -u postgres psql

b.     Подключитесь к целевой базе данных, которая загружает данные.

postgres-server$ c xxx

c.     Проверьте, содержит ли ваша база данных PostgreSQL перемещенные данные:

xxx=# SELECT * FROM source_db.sample_table;

6.     Перенесите MySQL на PostgreSQL на той же машине. Запустите команду от пользователя Linux, который может получить доступ к root MySQL пользователя.

$ pgloader mysql://root@localhost/source_dbpgsql://sammy:postgresql_password@localhost/target_db

7.     Выполните миграцию из CSV-файла.

$ pgloader load.csv pgsql://sammy:password@localhost/target_db

Переход с MySQL на PostgreSQL с использованием Py-mysql2pgsql

Py-mysql2pgsql - это инструмент для конвертации/миграции/экспорта данных из MySQL (только проверено на 5.x), записи совместимого с PostgreSQL (8.2 или выше) файла дампа или его передачи в ваш работающий сервер PostgreSQL (8.2 или выше). Сейчас он не может импортировать пространственные данные из MySQL.

Предпосылки:

• Python 2.7

• MySQL-python

• psycopg2

• PyYAML

• termcolor (за исключением установки на Windows)

• pytz

1.     Установите py-mysql2pgsql.

Windows: установите зависимости драйвера, затем инструмент.

psycopg2 для Windows: pip install psycopg2

MySQL-python для Windows

PyPI: автоматическая установка всех зависимостей.

> pip install py-mysql2pgsql

Источник:

> git clone git://github.com/philipsoutham/py-mysql2pgsql.git

> cd py-mysql2pgsql

> python setup.py install

2.  Проверьте правильность установки.

[root@xxx /data/aken/tools/py-mysql2pgsql-0.1.5]# py-mysql2pgsql -h

usage: py-mysql2pgsql [-h] [-v] [-f FILE] [-V]

     Инструмент для миграции/конвертации данных из mysql в postgresql.

     Необязательные аргументы:

-h, --help     Показать это сообщение помощи и выйти

-v, --verbose    Показать прогресс миграции данных.

-f FILE, --file FILE   Расположение файла конфигурации (по умолчанию: mysql2pgsql.yml). Если его там еще нет, он будет создан для вас.

-V, --version    Добавить версию и выйти.

3.     Отредактируйте файл конфигурации миграции mysql2pgsql.yml.

# specify socket to choose a socket connection

# localhost is a special MySQL hostname overriding the port option

# choose tcp to c

Mysql:

hostname:

port:

socket:

username:

password:

database:

compress: false

destination:

# output goes to file if file is provided, otherwise postgres

file:

postgres:

hostname:

port:

username:

password:

database:

# Only the tables listed will be transformed. To convert all tables, leave empty.

#only_tables:

#- tablex

#- tablex

# if exclude_tables is given, the tables listed below will not be converted.

#exclude_tables:

#- tablex

#- tablex

#Only the schema definition will be exported or migrated if supress_data is true.

supress_data: false

#Only the schema definition will be exported or migrated if supress_ddl is true.

supress_ddl: false

# if force_truncate is true, a table is forced to be truncated before table loading

force_truncate: false

# if timezone is true, makes the tzinfo mysql data appendable or convertible to UTC

timezone: false

# If index prefix_is specified, indexes with that name prefix will be generated.

index_prefix:

4.     Перенесите данные из MySQL в PostgreSQL.

[root@xxx]# py-mysql2pgsql -v -f mysql2pgsql.yml

>>>>>>>>>> STARTING <<<<<<<<<<

START CREATING TABLES

START - CREATING TABLE tab_xxx

FINISH - CREATING TABLE tab_xxx

DONE CREATING TABLES

START WRITING TABLE DATA

START - WRITING DATA TO tab_xxx

FINISH - WRITING DATA TO tab_xxx

DONE WRITING TABLE DATA

START CREATING INDEXES AND CONSTRAINTS

START - ADDING INDEXES TO tab_xxx

FINISH - ADDING INDEXES TO tab_xxx

START - ADDING CONSTRAINTS ON tab_xxx

FINISH - ADDING CONSTRAINTS ON tab_xxx

DONE CREATING INDEXES AND CONSTRAINTS

>>>>>>>>>> FINISHED <<<<<<<<<<

Резервное копирование MySQL и PostgreSQL до и после миграции базы данных

Это существенно для резервного копирования ваших баз данных перед любой операцией, которая может повлиять на целостность ваших данных. И после миграции базы данных, резервное копирование также необходимо для подготовки к возможным катастрофам, связанным с потерей данных.

На рынке существует много надежных решений для резервного копирования баз данных, и Vinchin Backup & Recovery - одно из них, которое автоматизирует резервное копирование баз данных и обеспечивает простое восстановление данных для нескольких баз данных, включая MySQL, PostgreSQL, Postgres Pro, SQL Server, Oracle DB и MariaDB.

С Vinchin Backup & Recovery вы можете настроить повторное резервное копирование базы данных из полного, дифференциального, инкрементного, архивного лога и резервного копирования транзакционного журнала, варианты которого различаются в зависимости от базы данных и уменьшают данные на 50% после дедупликации и сжатия.

Вы можете шифровать путь для безопасной передачи и автоматически управлять резервными копиями с помощью политики удержания.

Вы также можете выбрать восстановление базы данных к оригиналу или новому.

Хотите попробовать Vinchin Backup & Recovery? Вот 60-дневная полная версия Enterprise-версии, доступная для тестирования.

 Вот обзор процесса резервного копирования MySQL с помощью Vinchin Backup & Recovery:

1.     Выберите источник резервного копирования из списка лицензированных баз данных.

2.     Укажите желаемый узел резервного копирования и хранилище для вашего резервного копирования.

3.     Настройте настраиваемые стратегии резервного копирования.

4.     Просмотрите и нажмите Submit.

Заключение

Некоторые пользователи баз данных MySQL могут считать PostgreSQL более предпочтительным вариантом за его улучшенную производительность при сложных запросах, в этой статье я представляю два способа миграции с MySQL на PostgreSQL с использованием двух инструментов миграции данных с GitHub, Pgloader и Py-mysql2pgsql.

Обратите внимание, что резервное копирование базы данных до и после процесса преобразования необходимо для обеспечения безопасности данных. Используйте Vinchin Backup & Recovery для упрощения резервного копирования базы данных и управления несколькими базами данных через централизованную консоль.