Как связать mysql и postgresql

Обновлено: 27.03.2024

Рассказываем, как связать Python и реляционные базы данных трёх типов: SQLite, MySQL и PostgreSQL. Зная одну библиотеку для работы с SQL, вы легко разберетесь в остальных.

Все приложения взаимодействуют с данными, чаще всего через систему управления базами данных (СУБД). Одни языки программирования поставляются с модулями для работы с СУБД, другие требуют использования сторонних пакетов. Из этого подробного руководства вы узнаете о различных библиотеках Python для работы с SQL-базами данных. Мы разработаем простое приложение для взаимодействия с БД SQLite, MySQL и PostgreSQL.

Примечание. Если вы не разбираетесь в базах данных, советуем обратить внимание на следующие публикации Библиотеки программиста: 11 типов современных баз данных, SQL за 20 минут, Подборка материалов для изучения баз данных и SQL.

Из этого пособия вы узнаете:

  • как подключиться к различным СУБД с помощью библиотек Python для работы с SQL базами данных;
  • как управлять базами данных SQLite, MySQL и PostgreSQL;
  • как выполнять запросы к базе данных внутри приложения Python;
  • как разрабатывать приложения для разных баз данных.

Чтобы получить максимальную отдачу от этого учебного пособия, необходимо знать основы Python, SQL и работы с СУБД. Вы также должны иметь возможность загружать и импортировать пакеты в Python и знать, как устанавливать и запускать серверы БД локально или удаленно.

Содержание статьи:

  1. Схема базы данных
  2. Подключение к базам данных
  3. Создание таблиц
  4. Вставка записей
  5. Извлечение записей
  6. Обновление содержания
  7. Удаление записей таблицы

В каждом разделе по три подраздела: SQLite, MySQL и PostgreSQL.

В этом уроке мы разработаем очень маленькую базу данных приложения для социальных сетей. База данных будет состоять из четырех таблиц:

Схема базы данных показана на рисунке ниже.

Пользователи ( users ) и публикации ( posts ) будут находиться иметь тип связи один-ко-многим: одному читателю может понравиться несколько постов. Точно так же один и тот же юзер может оставлять много комментариев ( comments ), а один пост может иметь несколько комментариев. Таким образом, и users , и posts по отношению к comments имеют тот же тип связи. А лайки ( likes ) в этом плане идентичны комментариям.

Прежде чем взаимодействовать с любой базой данных через SQL-библиотеку, с ней необходимо связаться. В этом разделе мы рассмотрим, как подключиться из приложения Python к базам данных SQLite , MySQL и PostgreSQL. Рекомендуем сделать собственный .py файл для каждой из трёх баз данных.

Примечание. Для выполнения разделов о MySQL и PostgreSQL необходимо самостоятельно запустить соответствующие серверы. Для быстрого ознакомления с тем, как запустить сервер MySQL, ознакомьтесь с разделом MySQL в публикации Запуск проекта Django (англ.). Чтобы узнать, как создать базу данных в PostgreSQL, перейдите к разделу Setting Up a Database в публикации Предотвращение атак SQL-инъекций с помощью Python (англ.).

SQLite

SQLite, вероятно, является самой простой базой данных, к которой можно подключиться с помощью Python, поскольку для этого не требуется устанавливать какие-либо внешние модули. По умолчанию стандартная библиотека Python уже содержит модуль sqlite3.

Более того, SQLite база данных не требует сервера и самодостаточна, то есть просто читает и записывает данные в файл. Подключимся с помощью sqlite3 к базе данных:

Вот как работает этот код:

sqlite3.connect(path) возвращает объект connection . Этот объект может использоваться для выполнения запросов к базе данных SQLite. Следующий скрипт формирует соединение с базой данных SQLite:

Выполнив вышеуказанный скрипт, вы увидите, как в корневом каталоге диска E появится файл базы данных sm_app.sqlite . Конечно, вы можете изменить местоположение в соответствии с вашими интересами.

MySQL

В отличие от SQLite, в Python по умолчанию нет модуля, который можно использовать для подключения к базе данных MySQL. Для этого вам нужно установить драйвер Python для MySQL. Одним из таких драйверов является mysql-connector-python . Вы можете скачать этот модуль Python SQL с помощью pip:

Обратите внимание, что MySQL – это серверная система управления базами данных. Один сервер MySQL может хранить несколько баз данных. В отличие от SQLite, где соединение равносильно порождению БД, формирование базы данных MySQL состоит из двух этапов:

  1. Установка соединения с сервером MySQL.
  2. Выполнение запроса для создания БД.

Определим функцию, которая будет подключаться к серверу MySQL и возвращать объект подключения:

В приведенном выше коде мы определили новую функцию create_connection() , которая принимает три параметра:

  1. host_name
  2. user_name
  3. user_password

Модуль mysql.connector определяет метод connect() , используемый в седьмой строке для подключения к серверу MySQL. Как только соединение установлено, объект connection возвращается вызывающей функции. В последней строке функция create_connection() вызывается с именем хоста, именем пользователя и паролем.

Пока мы только установили соединение. Самой базы ещё нет. Для этого мы определим другую функцию – create_database() , которая принимает два параметра:

  1. Объект connection ;
  2. query – строковый запрос о создании базу данных.

Вот как выглядит эта функция:

Для выполнения запросов используется объект cursor .

Создадим базу данных sm_app для нашего приложения на сервере MySQL:

Теперь у нас есть база данных на сервере. Однако объект connection , возвращаемый функцией create_connection() подключен к серверу MySQL. А нам необходимо подключиться к базе данных sm_app . Для этого нужно изменить create_connection() следующим образом:

Функция create_connection() теперь принимает дополнительный параметр с именем db_name . Этот параметр указывает имя БД, к которой мы хотим подключиться. Имя теперь можно передать при вызове функции:

Скрипт успешно вызывает create_connection() и подключается к базе данных sm_app .

PostgreSQL

Как и в случае MySQL, для PostgreSQL в стандартной библиотеке Python нет модуля для взаимодействия с базой данных. Но и для этой задачи есть решение – модуль psycopg2 :

Определим функцию create_connection() для подключения к базе данных PostgreSQL:

Подключение осуществляется через интерфейс psycopg2.connect() . Далее используем написанную нами функцию:

Теперь внутри дефолтной БД postgres нужно создать базу данных sm_app . Ниже определена соответствующая функция create_database() :

Запустив вышеприведенный скрипт, мы увидим базу данных sm_app на своем сервере PostgreSQL. Подключимся к ней:

Здесь 127.0.0.1 и 5432 это соответственно IP-адресу и порт хоста сервера.

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

Как обсуждалось ранее, нам нужно получить и связать четыре таблицы:

SQLite

Для выполнения запросов в SQLite используется метод cursor.execute() . В этом разделе мы определим функцию execute_query() , которая использует этот метод. Функция будет принимать объект connection и строку запроса. Далее строка запроса будет передаваться методу execute( ) . В этом разделе он будет использоваться для формирования таблиц, а в следующих – мы применим его для выполнения запросов на обновление и удаление.

Примечание. Описываемый далее скрипт – часть того же файла, в котором мы описали соединение с базой данных SQLite.

Итак, начнем с определения функции execute_query() :

Теперь напишем передаваемый запрос ( query ):

В запросе говорится, что нужно создать таблицу users со следующими пятью столбцами:

Наконец, чтобы появилась таблица, вызываем execute_query() . Передаём объект connection , который мы описали в предыдущем разделе, вместе с только что подготовленной строкой запроса create_users_table :

Следующий запрос используется для создания таблицы posts:

Поскольку между users и posts имеет место отношение один-ко-многим, в таблице появляется ключ user_id , который ссылается на столбец id в таблице users . Выполняем следующий скрипт для построения таблицы posts :

Наконец, формируем следующим скриптом таблицы comments и likes :

Вы могли заметить, что создание таблиц в SQLite очень похоже на использование чистого SQL. Все, что вам нужно сделать, это сохранить запрос в строковой переменной и затем передать эту переменную cursor.execute() .

MySQL

Так же, как с SQLite, чтобы создать таблицу в MySQL, нужно передать запрос в cursor.execute() . Создадим новый вариант функции execute_query() :

Описываем таблицу users :

Запрос для реализации отношения внешнего ключа в MySQL немного отличается от SQLite. Более того, MySQL использует ключевое слово AUTO_INCREMENT для указания столбцов, значения которых автоматически увеличиваются при вставке новых записей.

Следующий скрипт составит таблицу posts , содержащую внешний ключ user_id , который ссылается на id столбца таблицы users :

Аналогично для создания таблиц comments и likes , передаём соответствующие CREATE -запросы функции execute_query() .

PostgreSQL

Применение библиотеки psycopg2 в execute_query() также подразумевает работу с cursor :

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

Создадим внутри базы данных sm_app таблицу users :

Запрос на создание таблицы users в PostgreSQL немного отличается от SQLite и MySQL. Здесь для указания столбцов с автоматическим инкрементом используется ключевое слово SERIAL . Кроме того, отличается способ указания ссылок на внешние ключи:

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

SQLite

Чтобы вставить записи в базу данных SQLite, мы можем использовать ту же execute_query() функцию, что и для создания таблиц. Для этого сначала нужно сохранить в виде строки запрос INSERT INTO . Затем нужно передать объект connection и строковый запрос в execute_query() . Вставим для примера пять записей в таблицу users :

Поскольку мы установили автоинкремент для столбца id , нам не нужно указывать его дополнительно. Таблица users будет автоматически заполнена пятью записями со значениями id от 1 до 5.

Вставим в таблицу posts шесть записей:

Следующий скрипт вставляет записи в таблицы comments и likes :

MySQL

Есть два способа вставить записи в базы данных MySQL из приложения Python. Первый подход похож на SQLite. Можно сохранить запрос INSERT INTO в строке, а затем использовать для вставки записей cursor.execute() .

Ранее мы определили функцию-оболочку execute_query() , которую использовали для вставки записей. Мы можем использовать ту же функцию:

Второй подход использует метод cursor.executemany() , который принимает два параметра:

  1. Строка query , содержащая заполнители для вставляемых записей.
  2. Список записей, которые мы хотим вставить.

Посмотрите на следующий пример, который вставляет две записи в таблицу likes :

Какой подход выбрать – зависит от вас. Если вы не очень хорошо знакомы с SQL, проще использовать метод курсора executemany() .

PostgreSQL

В предыдущем подразделе мы познакомились с двумя подходами для вставки записей в таблицы баз данных MySQL. В psycopg2 используется второй подход: мы передаем SQL-запрос с заполнителями и списком записей методу execute() . Каждая запись в списке должна являться кортежем, значения которого соответствуют значениям столбца в таблице БД. Вот как мы можем вставить пользовательские записи в таблицу users :

Список users содержит пять пользовательских записей в виде кортежей. Затем мы создаём строку с пятью элементами-заполнителями ( %s ), соответствующими пяти пользовательским записям. Строка-заполнитель объединяется с запросом, который вставляет записи в таблицу users . Наконец, строка запроса и пользовательские записи передаются в метод execute() .

Следующий скрипт вставляет записи в таблицу posts :

По той же методике можно вставить записи в таблицы comments и likes .

SQLite

Чтобы выбрать записи в SQLite, можно снова использовать cursor.execute() . Однако после этого потребуется вызвать метод курсора fetchall() . Этот метод возвращает список кортежей, где каждый кортеж сопоставлен с соответствующей строкой в ​​извлеченных записях. Чтобы упростить процесс, напишем функцию execute_read_query() :

Эта функция принимает объект connection и SELECT -запрос, а возвращает выбранную запись.

SELECT

Давайте выберем все записи из таблицы users :

В приведенном выше скрипте запрос SELECT забирает всех пользователей из таблицы users . Результат передается в написанную нами функцию execute_read_query() , возвращающую все записи из таблицы users .

Примечание. Не рекомендуется использовать SELECT * для больших таблиц, так как это может привести к большому числу операций ввода-вывода, которые увеличивают сетевой трафик.

Результат вышеприведенного запроса выглядит следующим образом:

Таким же образом вы можете извлечь все записи из таблицы posts :

Вывод выглядит так:

Вывод выглядит так:

Из вывода понятно, что имена столбцов не были возвращены методом fetchall() . Чтобы вернуть имена столбцов, нужно забрать атрибут description объекта cursor . Например, следующий список возвращает все имена столбцов для вышеуказанного запроса:

Вывод выглядит так:

WHERE

Теперь мы выполним SELECT -запрос, который возвращает текст поста и общее количество лайков, им полученных:

То есть используя запрос WHERE , вы можете возвращать более конкретные результаты.

MySQL

Процесс выбора записей в MySQL абсолютно идентичен процессу выбора записей в SQLite:

Теперь выберем все записи из таблицы users :

Вывод будет похож на то, что мы видели с SQLite.

PostgreSQL

Процесс выбора записей из таблицы PostgreSQL с помощью модуля psycopg2 тоже похож на SQLite и MySQL. Снова используем cursor.execute() , затем метод fetchall() для выбора записей из таблицы. Следующий скрипт выбирает все записи из таблицы users :

Опять же, результат будет похож на то, что мы видели раньше.

SQLite

Обновление записей в SQLite выглядит довольно просто. Снова можно применить execute_query() . В качестве примера обновим текст поста с id равным 2. Сначала создадим описание для SELECT :

Увидим следующий вывод:

Следующий скрипт обновит описание:

Теперь, если мы выполним SELECT -запрос еще раз, увидим следующий результат:

То есть запись была обновлена.

MySQL

Процесс обновления записей в MySQL с помощью модуля mysql-connector-python является точной копией модуля sqlite3 :

PostgreSQL

Запрос на обновление PostgreSQL аналогичен SQLite и MySQL.

SQLite

В качестве примера удалим комментарий с id равным 5:

Теперь, если мы извлечем все записи из таблицы comments , то увидим, что пятый комментарий был удален. Процесс удаления в MySQL и PostgreSQL идентичен SQLite:

В этом руководстве мы разобрались, как применять три распространенные библиотеки Python для работы с реляционными базами данных. Научившись работать с одним из модулей sqlite3 , mysql-connector-python и psycopg2 , вы легко сможете перенести свои знания на другие модули и оперировать любой из баз данных SQLite, MySQL и PostgreSQL.

Однако это лишь вершина айсберга! Существуют также библиотеки для работы с SQL и объектно-реляционными отображениями, такие как SQLAlchemy и Django ORM, которые автоматизируют задачи взаимодействия Python с базами данных.

Если вам интересна тематика работы с базами данных с помощью Python, напишите об этом в комментариях – мы подготовим дополнительные материалы.

Больше полезной информации вы найдете на наших телеграм-каналах «Библиотека питониста» и «Библиотека data scientist’а».

Нередко бывает так, что в большом проекте в силу тех или иных причин — зачастую исторических, хотя бывает по-всякому — его части могут использовать различные СУБД для хранения и поиска критически важных данных. В числе прочего, этому разнообразию способствует конкуренция и развитие технологий, но, так или иначе, взаимодействие между СУБД описывает стандарт SQL/MED 2003 (Management of External Data), который вводит определение Foreign Data Wrappers (FDW) и Datalink.

Первая часть стандарта предлагает средства для чтения данных как набора реляционных таблиц под управлением одного или нескольких внешних источников; FDW также может представлять возможность использовать SQL-интерфейс для доступа к не SQL данным, таким, как файлы или, например, список писем в почтовом ящике. Вторая часть, Datalink, позволяет управлять удаленным SQL-сервером.

Эти две части были реализованы еще в PostgreSQL 9.1 и называются FDW и dblink соответственно. FDW в PostgreSQL сделан максимально гибко, что позволяет разрабатывать wrapper'ы для большого количества внешних источников. В настоящее время мне известны такие FDW, как PostgreSQL, Oracle, SQL Server, MySQL, Cassandra, Redis, RethinkDB, Ldap, а также FDW к файлам типа CSV, JSON, XML и т.п.

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

Для начала собираем и устанавливаем mysql_fdw:

Устаналиваем extension на базу, чтобы загрузились необходимые библиотеки:

И mapping текущего пользователя в PostgreSQL в пользователя MySQL:

После этого мы имеем возможность подключить таблицу MySQL в PostgreSQL:

Допустим, мы храним справочник customers в PostgreSQL:

Попробуем выбрать 5 самых активных покупателей в январе 2014 года:

Как мы видим, запрос неэффективный, так как со стороны MySQL было получено содержимой всей таблицы: SELECT id, customer_id, order_date FROM data.orders . Сервер, в силу естественных ограничений драйвера MySQL, не в состоянии трансформировать запрос таким образом, чтобы для получения корректного результата этот запрос было бы возможно выполнить на стороне MySQL, и поэтому сначала получает таблицу целиком, а потом уже осуществляет фильтрацию. Однако при изменении запроса можно добиться того, чтобы фильтрация по дате осуществлялась на стороне MySQL:

Сравнение order_date с ('2014-02-01'::timestamp - '1 sec'::interval) неправильно, так как timestamptz хранится с большей точность, чем секунда, но это значение выбрано не случайно, посмотрите:

Тут нас поджидает проблема, из-за которой стоит использовать mysql_fdw с большой осторожностью:

Как мы видим, between, представляя из себя синтаксический сахар, был развернут в два условия, одно из которых не вычислено на стороне PostgreSQL: ('2014-02-01'::timestamp - '1 sec'::interval) и преобразовано в разницу двух строк (а не даты и интервала):

В итоге запрос возвращает неправильный результат.

Теперь план запроса выглядит так:

Запрос стал быстрее по сравнению с первым, так как с MySQL мы возвращаем значение более точечного запроса:

Операция фильтрации выполняется теперь на стороне MySQL. При определенных условиях будет использоваться индекс по order_date, если он создан.

Таким образом мы ускорили выполнение запроса. На простом примере мы почуствовали силу Open Source и мощь PostgreSQL в расширяемости.


Я в общих чертах расскажу о перекрестной репликации между PostgreSQL и MySQL, а еще о методах настройки перекрестной репликации между этими двумя серверами базы данных. Обычно базы данных в перекрестной репликации называются однородными, и это удобный метод перехода с одного сервера реляционной СУБД на другой.

Базы данных PostgreSQL и MySQL принято считать реляционными, но с дополнительными расширениями они предлагают возможности NoSQL. Здесь мы обсудим репликацию между PostgreSQL и MySQL, с точки зрения реляционных СУБД.

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

Обычно репликация между двумя идентичными серверами баз данных выполняется либо в двоичном режиме, либо с помощью запросов между ведущим узлом (он же издатель, главный или активный) и ведомым (подписчик, ожидающий или пассивный). Цель репликации — предоставить в реальном времени копию главной базы данных на стороне ведомого. При этом данные передаются от ведущего к ведомому, то есть от активного к пассивному, потому что репликация выполняется только в одну сторону. Но можно настроить репликацию между двумя базами данных в обе стороны, чтобы данные передавались от ведомого к ведущему в конфигурации «активный-активный». Все это, в том числе каскадная репликация, возможно между двумя и более идентичными серверами баз данных, Конфигурация «активный-активный» или «активный-пассивный» зависит от потребности, доступности таких возможностей в исходной конфигурации или использования внешних решений для настройки и существующих компромиссов.

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

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

Что такое pg_chameleon

pg_chameleon — это система репликации из MySQL в PostgreSQL на Python 3. В ней используется опенсорс-библиотека mysql-replication, тоже на Python. Образы строк извлекаются из таблиц MySQL и сохраняются как объекты JSONB в базе данных PostgreSQL, а потом расшифровываются функцией pl/pgsql и воспроизводятся в базе данных PostgreSQL.

Возможности pg_chameleon

Несколько схем MySQL из одного кластера можно реплицировать в одну целевую базу данных PostgreSQL с конфигурацией «один ко многим»
Имена исходной и целевой схем не могут совпадать.
Данные репликации можно извлечь из каскадной реплики MySQL.
Таблицы, которые не могут реплицироваться или создают ошибки, исключаются.
Каждой функцией репликации управляют демоны.
Контроль с помощью параметров и файлов конфигурации на базе YAML.

Пример

Хост vm1 vm2
Версия ОС CentOS Linux 7.6 x86_64 CentOS Linux 7.5 x86_64
Версия сервера БД MySQL 5.7.26 PostgreSQL 10.5
Порт БД 3306 5433
IP-адрес 192.168.56.102 192.168.56.106

Для начала подготовьте все необходимые компоненты для установки pg_chameleon. В этом примере установлен Python 3.6.8, который создает виртуальную среду и активирует ее.

После успешной установки Python3.6 нужно выполнить остальные требования, например создать и активировать виртуальную среду. Кроме того, pip-модуль обновляется до последней версии и используется для установки pg_chameleon. В командах ниже намеренно устанавливается pg_chameleon 2.0.9, хотя последняя версия — 2.0.10. Это нужно, чтобы избежать новых багов в обновленной версии.

Затем мы вызываем pg_chameleon (chameleon — это команда) с аргументом set_configuration_files, чтобы включить pg_chameleon и создать каталоги и файлы конфигурации по умолчанию.

Теперь мы создаем копию config-example.yml как default.yml, чтобы он стал файлом конфигурации по умолчанию. Образец файла конфигурации для этого примера приводится ниже.

Файл конфигурации в этом примере — это образец файла с pg_chameleon с незначительными изменениями в соответствии с исходной и целевой средами, и ниже приводится обзор различных разделов файла конфигурации.

В файле конфигурации default.yml есть раздел глобальных параметров (global settings), где можно управлять такими настройками, как расположение файла блокировки, расположение логов, период хранения логов и т. д. Дальше идет раздел переопределения типов (type override), где указан набор правил для переопределения типов во время репликации. В примере по умолчанию используется правило переопределения типа, которое преобразует tinyint(1) в логическое значение. В следующем разделе указываем детали подключения к целевой базе данных. В нашем случае это база данных PostgreSQL, обозначенная как pg_conn. В последнем разделе указываем данные источника, то есть параметры подключения исходной базы данных, схему сопоставления исходной и целевой баз данных, таблицы, которые нужно пропустить, время ожидания, память, размер пакета. Заметьте, что «sources» указано во множественном числе, то есть мы можем добавить несколько исходных баз данных для одной целевой, чтобы настроить конфигурацию «многие к одному».

База данных world_x в примере содержит 4 таблицы со строками, которые сообщество MySQL предлагает для примера. Ее можно загрузить здесь. Пример базы данных поставляется в виде tar и сжатого архива с инструкциями по созданию и импорту строк.

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

На стороне PostgreSQL создается база данных db_replica, которая будет принимать изменения из базы данных MySQL. Пользователь usr_replica в PostgreSQL автоматически настраивается как владелец двух схем pgworld_x и sch_chameleon, которые содержат фактические реплицированные таблицы и таблицы с каталогами репликации соответственно. За автоматическую конфигурацию отвечает аргумент create_replica_schema, как вы увидите ниже.

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

Сейчас важно проверить подключение к обоим серверам баз данных, чтобы при выполнении команд pg_chameleon не возникло проблем.

На узле PostgreSQL:

Следующие три команды pg_chameleon (chameleon) подготавливают среду, добавляют источник и инициализируют реплику. Аргумент create_replica_schema в pg_chameleon создает схему по умолчанию (sch_chameleon) и схему репликации (pgworld_x) в базе данных PostgreSQL, как мы уже говорили. Аргумент add_source добавляет исходную базу данных в конфигурацию, считывая файл конфигурации (default.yml), и в нашем случае это mysql, а init_replica иницализирует конфигурацию на основе параметров в файле конфигурации.

Статус репликации можно запросить с помощью аргумента show_status, а просмотреть ошибки — с помощью аргумента show_errors.

Как мы уже говорили, каждой функцией репликации управляют демоны. Чтобы просмотреть их, запросим таблицу процессов командой Linux ps, как показано ниже.

Репликация не считается настроенной, пока мы не протестируем ее в реальном времени, как показано ниже. Мы создаем таблицу, вставляем пару записей в базу данных MySQL и вызываем аргумент sync_tables в pg_chameleon, чтобы обновить демоны и реплицировать таблицу с записями в базу данных PostgreSQL.

Чтобы подтвердить результаты теста, запрашиваем таблицу из базы данных PostgreSQL и выводим строки.

Если мы выполняем миграцию, следующие команды pg_chameleon будут ее окончанием. Команды нужно выполнять после того, как мы убедимся, что строки всех целевых таблиц были реплицированы, а результатом будет аккуратно перенесенная база данных PostgreSQL без ссылок на исходную базу данных или схему репликации (sch_chameleon).

По желанию следующими командами можно удалить исходную конфигурацию и схему репликации.

Преимущества pg_chameleon

Недостатки pg_chameleon

Поддерживается только с MySQL 5.5 и выше в качестве источника и PostgreSQL 9.5 и выше в качестве целевой базы данных.
У каждой таблицы должен быть первичный или уникальный ключ, иначе таблицы инициализируются в процессе init_replica, но не реплицируются.
Односторонняя репликация — только из MySQL в PostgreSQL. Поэтому подходит только для схемы «активный-пассивный».
Исходной может быть только база данных MySQL, а поддержка базы данных PostgreSQL как источника только экспериментальная и с ограничениями (узнайте больше здесь)

Итоги по pg_chameleon

Метод репликации в pg_chameleon отлично подходит для миграции базы данных из MySQL в PostgreSQL. Существенный минус в том, что репликация только односторонняя, поэтому специалисты по базам данных вряд ли захотят использовать его для чего-то, кроме миграции. Но проблему односторонней репликации можно решить еще одним опенсорс-инструментом — SymmetricDS.

Подробнее читайте в официальной документации здесь. Справку по командной строке можно найти здесь.

Обзор SymmetricDS

SymmetricDS — это опенсорс-инструмент, который реплицирует любую базу данных в любую другую распространенную базу данных: Oracle, MongoDB, PostgreSQL, MySQL, SQL Server, MariaDB, DB2, Sybase, Greenplum, Informix, H2, Firebird и другие облачные экземпляры БД, например Redshift, и Azure и т. д. Доступные функции: синхронизация баз данных и файлов, репликация нескольких ведущих баз данных, фильтрованная синхронизация, преобразование и другие. Это инструмент на Java, и требуется стандартный выпуск JRE или JDK (версии 8.0 или выше). Здесь можно записывать изменения данных по триггерам в исходной базе данных и направлять их в соответствующую целевую базу данных в виде пакетов.

Возможности SymmetricDS

Инструмент не зависит от платформы, то есть две или несколько разных БД могут обмениваться данными.
Реляционные БД синхронизируются с помощью записи изменения данных, а БД на основе файловых систем используют синхронизацию файлов.
Двусторонняя репликация с использованием методов Push и Pull на основе набора правил.
Передача данных возможна по защищенным сетям и сетям с низкой пропускной способностью.
Автоматическое восстановление при возобновлении работы узлов после сбоя и автоматическое разрешение конфликтов.
Совместимость с облаком и эффективные API расширений.

Пример

SymmetricDS можно настроить в одном из двух вариантов:
Ведущий (родительский) узел, который централизованно координирует репликацию данных между двумя ведомыми (дочерними) узлами, и обмен данными между дочерним узлами осуществляется только через родительский.
Активный узел (узел 1) может обмениваться данными для репликации с другим активным узлом (узел 2) без посредника.

В обоих вариантах обмен данными происходит с помощью Push и Pull. В этом примере мы рассмотрим конфигурацию «активный-активный». Описывать всю архитектуру слишком долго, так что изучите руководство, чтобы узнать больше об устройстве SymmetricDS.

Установить SymmetricDS очень просто: загрузите опенсорс-версию zip-файла отсюда и извлеките ее, куда захотите. В таблице ниже приводятся сведения о месте установки и версии SymmetricDS в этом примере, а также версии баз данных, версии Linux, IP-адреса и порты для обоих узлов.

Хост vm1 vm2
Версия ОС CentOS Linux 7.6 x86_64 CentOS Linux 7.6 x86_64
Версия сервера БД MySQL 5.7.26 PostgreSQL 10.5
Порт БД 3306 5832
IP-адрес 192.168.1.107 192.168.1.112
Версия SymmetricDS SymmetricDS 3.9 SymmetricDS 3.9
Путь установки SymmetricDS /usr/local/symmetric-server-3.9.20 /usr/local/symmetric-server-3.9.20
Имя узла SymmetricDS corp-000 store-001

Здесь мы устанавливаем SymmetricDS в /usr/local/symmetric-server-3.9.20, и тут же будут храниться разные вложенные каталоги и файлы. Нас интересуют вложенные каталоги samples и engines. В каталоге samples хранятся примеры файлов конфигурации со свойствами узла, а также примеры скриптов SQL для быстрого начала демонстрации.

В каталоге samples видим три файла конфигурации со свойствами узла — имя показывает характер узла в определенной схеме.

В SymmetricDS есть все необходимые файлы конфигурации для базовой схемы из 3 узлов (вариант 1), и те же файлы можно использовать для схемы из 2 узлов (вариант 2). Копируем нужный файл конфигурации из каталога samples в engines на хосте vm1. Получается так:

Этот узел в конфигурации SymmetricDS называется corp-000, а подключение к базе данных обрабатывается драйвером mysql jdbc, который использует строку подключения, указанную выше, и учетные данные для входа. Мы подключаемся к базе данных replica_db, а во время создания схемы будут созданы таблицы. sync.url показывает место связи с узлом для синхронизации.

Узел 2 на хосте vm2 настраивается как store-001, а остальное указано в файле node.properties, который приводится ниже. Узел store-001 выполняет базу данных PostgreSQL, а pgdb_replica — это база данных для репликации. registration.url позволяет хосту vm2 связаться с хостом vm1 и получить от него детали конфигурации.

Готовый пример SymmetricDS содержит параметры для настройки двусторонней репликации между двумя серверами базы данных (двумя узлами). Приведенные ниже шаги выполняются на хосте vm1 (corp-000), который создаст пример схемы с 4 таблицами. Затем выполнение create-sym-tables командой symadmin создает таблицы каталогов, где будут храниться правила и направление репликации между узлами. Наконец, в таблицы загружается пример данных.

В примере таблицы item и item_selling_price настроены автоматически для репликации из corp-000 в store-001, а таблицы sale (sale_transaction и sale_return_line_item) автоматически настроены для репликации из store-001 в corp-000. Теперь создаем схему в базе данных PostgreSQL на хосте vm2 (store-001), чтобы подготовить ее к приему данных от corp-000.

Обязательно проверяем, что в базе данных MySQL на vm1 есть примеры таблиц и таблицы каталогов SymmetricDS. Заметьте, что системные таблицы SymmetricDS (с префиксом sym_) сейчас доступны только на узле corp-000, потому что там мы выполнили команду create-sym-tables и будем управлять репликацией. А еще в базе данных на узле store-001 будет всего 4 таблицы примера без данных.

Все. Среда готова для запуска серверных процессов sym на обоих узлах, как показано ниже.

Записи логов отправляются в файл фонового лога (symmetric.log) в папке логов в каталоге, где установлен SymmetricDS, а также в стандартные выходные данные. Сервер sym теперь можно инициировать на узле store-001.

На этом этапе в таблицу item в базе данных MySQL на узле corp-000 (хост: vm1) вставляется новая запись, и можно проверить ее репликацию в базу данных PostgreSQL на узле store-001 (хост: vm2). Мы видим операцию Pull для перемещения данных из corp-000 в store-001.

Чтобы выполнить операцию Push для перемещения данных из store-001 в corp-000, вставляем запись в таблицу sale_transaction и проверяем, что репликация выполнена.

Мы видим успешную настройку двусторонней репликации таблиц примера между базами данных MySQL и PostgreSQL. Чтобы настроить репликацию для новых пользовательских таблиц, выполняем следующие действия. Создаем таблицу t1 для примера и настраиваем правила ее репликации следующим образом. Так мы настраиваем только репликацию из corp-000 в store-001.

Затем конфигурация получает уведомление об изменении схемы, то есть добавлении новой таблицы, с помощью команды symadmin с аргументом sync-triggers, который воссоздает триггеры для сопоставления определений таблиц. Выполняется send-schema для отправки изменений схемы на узел store-001, и репликация таблицы t1 настроена.

Преимущества SymmetricDS

Простая установка и конфигурация, включая готовый набор файлов с параметрами для создания схемы с тремя или двумя узлами.
Кросплатформенность баз данных и независимость от платформы, включая серверы, ноутбуки и мобильные устройства.
Репликация любой базы данных в любую другую базу данных локально, в WAN или в облаке.
Возможность оптимальной работы с парой баз данных или нескольким тысячами для удобной репликации.
Платная версия с графическим интерфейсом и отличной поддержкой.

Недостатки SymmetricDS

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

Итоги по SymmetricDS

Пример создан на основе официального краткого руководства по SymmetricDS. В руководстве пользователя подробно описываются различные понятия, связанные с настройкой репликации с помощью SymmetricDS.



Публикация основана на докладе тренера и инженера технической поддержки Zabbix Александра Петрова-Гаврилова.

Многих волнует вопрос о том как мигрировать с MySQL на PostgreSQL и воспользоваться преимуществами TimescaleDB.

Зачем мигрировать

Первый вопрос, который обычно возникает, — зачем мигрировать с MySQL на PostgreSQL при использовании Zabbix.

Вариантов не так много, особенно если система уже работает и работает неплохо. Возможные причины для миграции:

  • Команда лучше разбирается в PostgeSQL.
  • Желание попробовать TimescaleDB, учитывая обещанное повышение производительности и экономию места.
  • Документация на русском языке
  • Преимущества PostgreSQL Pro.

Необходимость миграции и эффективность использования зависят от опыта пользователя. Кроме того, платформы постоянно развиваются, добавляются новые функции. Предсказать, какая именно платформа будет предпочтительнее, невозможно. На данный момент для среднего пользователя Zabbix разница между платформами незначительна.

  • Производительность PostgreSQL и MySQL практически одинакова для Zabbix DB.
  • Тюнинг необходим в любом случае, например buffer и transaction log.
  • Партицирование для MySQL немного проще, чем для PostgreSQL.
  • Партицирование PostgreSQL поддерживает foreign key, т. е. взаимосвязи между партициями.
  • PostgreSQL работает стабильнее в случае высокого IO.

Подготовка к миграции

Ниже продемонстрирован пример того, как мигрировать базу данных с MySQL на PostgreSQL с использованием:

  • Zabbix 5.0,
  • CentOS 7,
  • MariaDB 5.5.65 (доступна «из коробки» после установки CentOS и часто используется по умолчанию).

Примечание. Представленный метод может сработать с другими операционными системами и базами данных.

Для миграции вам понадобится:

  • PostgreSQL,
  • PGloader,
  • Исходный код Zabbix.

Примечание. PGloader выбран с учетом успешного опыта пользователей и меньшего количества ошибок в сравнение с другими инструментами миграции.

1). Добавить репозиторий и установить PostgreSQL.

Для установки PostgreSQL можно просто скопировать репозиторий с сайта PostgreSQL, инициализировать, добавить в автозапуск и запустить. Возможно, придется поменять конфигурацию в pg_hba.conf, чтобы разрешить нужный тип доступа.

2). Установить pgloader.

3). Создать директорию для работы.

Директория для работы необходима, потому что для проведения миграции нам потребуются файлы создания схемы и скрипт. В эту же директорию скачивается и исходный код Zabbix.

5). После того как версия скачана, нужно, конечно, распаковать архив.

6). Разделить schema.sql на create.sql и alter.sql.

В архиве исходников Zabbix есть sql-скрипты для создания базы данных не только PostgreSQL, но и MySQL. Скрипты находятся в одноименных директориях, в которых нам понадобится только файл schema.sql, чтобы создать схему для данных, все таблицы и т. д. Придется также создать alter.sql, чтобы создать все индексы, все взаимосвязи между таблицами и т. д.

7). Создать пользователя и базу данных в PostgreSQL

Создание пользователя и базы данных PosgreSQL подробно описано в разделе Документация на сайте Zabbix.

Примечание. Не забудьте дать пользователю Zabbix необходимые разрешения.

8). Создать скрипт для миграции в директории, где находятся созданные create.sql и alter.sql

LOAD DATABASE FROM mysql://zabbix:zabbix-password@localhost/zabbix INTO postgresql://zabbix:zabbix-password@localhost/zabbix WITH include no drop, truncate, create no tables, create no indexes, no foreign keys, reset sequences, data only SET maintenance_work_mem TO '1024MB', work_mem to '256MB' ALTER SCHEMA 'zabbix' RENAME TO 'public' BEFORE LOAD EXECUTE create.sql AFTER LOAD EXECUTE alter.sql;

В скрипте для миграции указываются все необходимые данные, например, откуда мигрируются данные, где находится база данных, а также куда выполняется миграция. При использовании pgloader скрипт будет практически выполнять копированиеCOPY) PostgreSQL.

Кроме того, указываются дополнительные параметры. В нашем примере указано, что переноситься будут только данные без создания индексов, ключей или таблиц, какой объем памяти мы можем пожертвовать для миграции (чем больший объем памяти указан, тем быстрее проходит миграция).

Примечание. Для использования pgloader необходимо переименовать схему в public.

Примечание. До фактической загрузки данных необходимо создать schema.sql для Zabbix, все взаимосвязи между таблицами, а также все необходимые файлы alter.sql.

Миграция

1). Остановить Zabbix Server.

systemctl stop zabbix -server

Остановить Zabbix Server необходимо, чтобы в какой-то момент перестали записываться данные в существующую базу данных.

Примечание. Рекомендуется сделать резервную копию базы данных. При этом не обязательно мигрировать саму базу данных. Можно сделать для миграции копию базы или даже экспортировать ее в csv-файл.

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

2). Запустить pgloader.

pgloader запускается с инструкциями, которые указаны в скрипте для миграции.

3). Видеть Warnings — это нормально.


Предупреждения после завершения миграции

После завершения копирования, появится отчет о том, какие данные были перенесены, сколько действий было сделано, сколько времени заняла миграция, а также количество ошибок (если они всё таки произошли).


Отчет о миграции

5). Проверить результаты.

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

Проверка результатов миграции в PostgreSQL

Также необходимо убедиться в том, что данные были перенесены.


Подтверждение переноса данных в PostgreSQL

Завершение

1). Удалить Zabbix сервер для работы с MySQL.

2). Удалить веб интерфейс для работы с MySQL.

3). Установить Zabbix сервер для работы с PostgreSQL.

4). Установить веб интерфейс для работы с PostgreSQL.

5). Отредактировать новый zabbix_server.conf

6). Добавить свой пароль.

7). Удалить предыдущую конфигурацию веб интерфейса (обязательно).

8). Раскомментировать строку и указать свой часовой пояс.

10). Настроить веб-интерфейс для PostgreSQL.



12). Остановить MySQL.

Заключение

Вы можете ознакомиться с подробными инструкциями о том, как обновить Zabbix 4.0 до текущей версии, а также как установить расширение TimescaleDB. Материалы также доступны в разделе Документация на сайте Zabbix.

Notepad++ нам понадобится только для удобного ввода и хранения необходимых данных, а IDE – для понимания работы с SQL через другие языки программирования (в примерах используется Java). Основная часть работы будет выполняться через терминал.

Это не тот язык, который придется осваивать годами. В SQL используются довольно простые читабельные запросы, которые легко выучить и понять. К базам данных можно обращаться как посредством программного кода, так и через терминал.

Вот пример работы с БД в Java:

Запросы, которые «обрамлены» двойными кавычками после .prepareStatement, – это и есть SQL. А вот как аналогичные запросы будут выглядеть в терминале:

Все о языке SQL на примере SQLite, MySQL и PostgreSQL

На первый взгляд, слишком сложно, но сейчас мы разберем все по порядку.

Запросы на SQL – это простая линейная последовательность операторов. В запросах используются:

  • зарезервированные ключевые слова;
  • идентификаторы для столбцов, таблиц, операций и функций;
  • строковые, арифметические и логические выражения для создания условий поиска и вычисления значений ячеек.

Любой оператор начинается с ключевого слова-действия вроде SELECT, CREATE, UPDATE и т. д. В конце обязательно ставится точка с запятой. Оператор может свободно занимать как одну, так и несколько строк. Разделителями логических единиц выступают:

  • 1 или несколько пробелов;
  • 1 или несколько символов новой строки;
  • 1 или несколько символов табуляции.

Комментарии могут помечаться такими способами:

В SQLite нет таких понятий, как пользователь или пароль. База данных представлена в виде файла, и если у вас есть доступ к файлу – есть доступ и к базе. Для создания БД и подключения к ней нужно выполнить следующее:

SQLite

Запомнить абсолютно все команды невозможно, и чтобы просмотреть перечень доступных команд, введите .help.

К MySQL подключиться также несложно, но здесь придется убедиться, что сервер запущен. Для этого перейдите в «Службы» и проверьте состояние:

MySQL

Если сервер запущен, введите mysql -u имя_пользователя -p, где имя_пользователя – это логин, под которым вы зарегистрировались. Пароль можно также написать следом за -p, но после все равно придется вводить его еще раз, а терминал предупредит о том, что пароль нельзя «светить». Зачастую -u и -p – это root и root, но если вы сменили на что-то более сложное, постарайтесь не забыть, так как при работе с MySQL авторизовываться придется часто.

MySQL

Для вызова списка доступных команд используется help или \h.

Подключаться к Workbench во время работы необязательно, а вот для более удобного визуального представления таблиц можно использовать:

Workbench

В PostgreSQL все аналогично, только вводится psql -U postgres. Также PostgreSQL можно настроить для быстрой авторизации без пароля:

PostgreSQL

Список доступных команд выводится по help. В списке содержатся команды программы и SQL-команды (а в языке SQL их немало).

Так подключение к БД выглядит в Java:

К самым распространенным типам данных относятся: INTEGER (он же INT), BIGINT, FLOAT, DOUBLE, BOOLEAN, VARCHAR (до 255 символов), TEXT, LONGTEXT, DATE, DATETIME, TIME, TIMESTAMP. С ними придется столкнуться при создании и редактировании таблиц, так как у каждого столбца будет свой тип данных.

Запросы в SQL очень удобны: это просто английские слова, которые отображают желаемое действие.

Например, создание базы данных, переход в нее и создание таблицы будет выглядеть следующим образом:

MySQL

В SQLite просмотреть таблицы можно с помощью команды .tables:

SQLite

В MySQL это делается при помощи show tables;:

MySQL

В PostgreSQL – через \d:

PostgreSQL

Не поленитесь воспользоваться сперва Notepad++, так как при возникновении ошибки (банальная опечатка) вы сможете своевременно отредактировать запрос и вставить его правильный вариант в терминал.

Вот наши команды в формате кода:

Так создание таблицы выглядит в Java:

Регистр букв не имеет значения, но если вы работаете с Notepad++ или IDE, для команд лучше использовать прописные буквы: текст будет визуально отделен от остального кода, и вы сможете четко прослеживать начало и конец запросов на языке SQL.

В этом случае используются такие команды:

Если вы запускаете какой-то файл, чтобы не было сбоев, просто напишите проверку на существование таблицы и/или базы данных:

Если вы воспользуетесь командами show databases или show tables, то увидите, что удаление прошло успешно.

Запросы на языке SQL будут одинаковыми для всех СУБД, поэтому рассмотрим заполнение, редактирование и вывод таблицы на примере MySQL.

Чтобы заполнить таблицу значениями, необходимо помнить типы данных в столбцах, и в соответствии с этими типами заполнять. Допустим, у нас есть таблица test с группами данных id INT PRIMARY KEY (первичный ключ) и name VARCHAR (255) NOT NULL (не нулевое значение: обязательно заполняется). Тогда заполняться эти поля должны следующим образом:

В Java добавление в таблицу информации выглядит так:

Если мы установим для id констрейн AUTO_INCREMENT, это поле будет заполняться автоматически, начиная с единицы и далее. В таком случае нам не придется прописывать id: мы просто будем заполнять name.

Для изменения значений используем следующую команду:

Так весь столбец name заполнится значениями New_name. Если нам нужно выборочное изменение, оттолкнемся от соседних столбцов и создадим условие:

В данном случае мы поменяем только второе имя.

По тому же принципу мы можем удалять из таблицы данные, как все, так и 1 строку:

Выводить данные можно все или какие-то конкретные. В приведенном ниже примере мы выполняем следующие действия:

  1. Выводим все данные таблицы.
  2. Выводим только 1 столбец.
  3. Выводим 1 строку.
  4. Выводим все строки, идущие после первой.
  5. Выводим данные по id в возрастающем порядке.
  6. Выводим данные по id в обратном порядке.

Чтобы было более наглядно, увеличьте количество данных в таблице и поиграйте с условиями.

Вывод данных в Java выглядит так:

Мы также можем переименовать поле и сменить его тип:

Или просто сменить тип, оставив прежнее имя:

В языке SQL можно использовать импорт и экспорт (дамп), что значительно упрощает работу. Как это сделать посредством командой строки?

FOREIGN KEY (внешний ключ) необходим для ограничения по ссылкам. Создается прямая связь между значениями двух полей. Поле, которое ссылается на другое, называется внешним ключом, а поле, на которое ссылаются – родительским ключом. Их имена могут быть разными, но тип поля должен соответствовать. Внешний ключ связан с таблицей, на которую он ссылается. Каждое значение внешнего ключа должно ссылаться на одно и то же значение родительского ключа. Если это условие верно, БД пребывает в состоянии ссылочной целостности.

Давайте рассмотрим на примере. Допустим, у нас есть 2 таблицы: регионы и города.

Примечание: для SQLite вместо AUTO_INCREMENT используется AUTOINCREMENT, а в PostgreSQL – SERIAL.

В regions_id хранится идентификатор региона, и мы делаем его внешним ключом на поле id таблицы regions.

Если таблица регионов пуста, при выполнении следующей команды должна возникнуть ошибка:

Однако запрос успешно выполнится. Это связано с тем, что зачастую в разных СУБД используются специальные команды для включения механизма внешних ключей. Как его включить?

В PostgreSQL данный механизм включен по умолчанию.

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

Наличие родительского ключа будет препятствовать удалению. Для этого используется либо первоочередное удаление таблицы в наследнике, либо такой запрос:

На языке SQL можно вывести сразу несколько таблиц. Создадим в базе данных test две таблицы: test1 и test2. Заполним их значениями, при этом id будут одинаковыми и идти по порядку (можно реализовать с помощью констрейна AUTO_INCREMENT). Чтобы вывести обе таблицы рядом, выполним следующую команду:

Все о языке SQL на примере SQLite, MySQL и PostgreSQL

Да, мы затронули лишь базис, но даже с этим базисом вы можете смело вписывать в резюме, что понимаете и умеете работать с БД на языке SQL. Вот только чем более сложные операции необходимо реализовать, тем большим будет различие в реализации для разных СУБД. Так что устраиваясь каким-нибудь Salesforce-разработчиком, просто подтяните знания по каждой из этих систем. Удачи!

Читайте также: