Системы управления реляционными базами данных (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.
Предпосылки:
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 для упрощения резервного копирования базы данных и управления несколькими базами данных через централизованную консоль.
