Логическая репликация PostgreSQL: инструкция

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

Всё описанное далее актуально для PostgreSQL версии 10.0 и выше. 

Логическая репликация PostgreSQL — это синхронизация копий без привязки к конкретному представлению данных на диске. Не имеет значения архитектура процессора, платформа или версия СУБД. Синхронизация выполняется на основе идентификатора репликации — обычно это первичный ключ.

В логической репликации используется модель публикации и подписки. 

Процесс репликации

В общих чертах процесс репликации выглядит так:

1. На узле издателя создаётся одна или больше публикаций.
2. Один или больше подписчиков подписываются на одну или больше публикаций.
3. Моментальный снимок базы данных публикации копируется на подписчика. Этот шаг также называется фазой синхронизации таблицы. Чтобы сократить время, затрачиваемое на эту фазу, можно создать несколько обработчиков синхронизации таблиц. Однако может быть только один рабочий процесс синхронизации для каждой таблицы. 
4. После завершения копирования последующие изменения, сделанные на узле-издателе, будут отправлены узлу-подписчику. Изменения будут применяться в порядке фиксации для обеспечения согласованности транзакций. 

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

Логическая репликация на практике

Допустим, вы хотите настроить логическую репликацию на одном хосте. Чтобы добиться желаемого, используйте разные порты. Например, публикация будет на 5432, а подписчик — на 5431.

Настройка логической репликации PostgreSQL начинается с редактирования файла конфигурации. Откройте его:

sudo nano /etc/postgresql/10/main/postgresql.conf

Раскомментируйте параметр wal_level и задайте значение logical. Должно получиться так: wal_level = logical. 

Затем сохраните и закройте файл конфигурации и перезапустите PostgreSQL:

sudo systemctl restart postgresql

Затем на мастере в основной таблице выполните:

pg_dumpall --database=postgres --host=192.168.1.2 --no-password --globals-only --no-privileges | psql

Команда pg_dumpall позволяет выгрузить базы данных в формате скрипта. В параметре --database задаётся имя БД, через подключение к которой выгружаются глобальные объекты и находятся другие базы. По умолчанию используется база postgres. Параметр --globals-only сообщает о том, что нужно выгружать только глобальные объекты, без БД. Подробности смотрите в документации.

На реплике выполните команду:

pg_dump --dbname=db_name --host=192.168.1.2 --no-password --create --schema-only | psql

Теперь нужно создать публикацию на мастере. Но сначала добавьте данные для тестирования. Пусть это будет таблица с двумя столбцами:

CREATE TABLE table1(x int primary key, y int); 

В столбце x будет храниться первичный ключ, в столбце y — числа. Добавьте в таблицу первую строку:

INSERT INTO table1 VALUES(10, 11);  

В таблице одна строка — число 11 с первичным ключом 10. Этого минимального набора данных хватит для того, чтобы проверить синхронизацию.

Создайте публикацию на стороне мастера:

CREATE PUBLICATION my_publication FOR TABLE table1;   

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

dbaas

Публикация с именем my_publication готова. Пришло время создать подписку на порту 5431.

Воссоздайте таблицу на узле подписчика:

CREATE TABLE table1(x int primary key, y int); 

Создайте подписку с именем my_subscription на стороне реплики:

CREATE SUBSCRIPTION my_subscription 
CONNECTION 'host=localhost port=5432 dbname=postgres' PUBLICATION my_publication;

Проверьте, что данные синхронизированы:

SELECT * FROM table1;  

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

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

На практике это значит, что на узле подписчика должна вернуться та строка, которую ранее добавили на узле издателя — число 11 с первичным ключом 10. Чтобы убедиться в том, что копии синхронизируются, добавьте на узле издателя ещё пару строк, а затем отобразите их на подписчике.

Другой пример — у вас несколько серверов и вы хотите настроить репликацию. В таком случае придётся дополнительно настроить разрешения.

• В файле /etc/postgresql/10/main/postgresql.conf главного сервера укажите частный IP-адрес хоста в строке listen_addresses = 'localhost, MASTER_ВНУТРЕННИЙ_IP'. Это нужно для того, чтобы он слушал входящие соединения в частной сети.
• В файле /etc/postgresql/10/main/pg_hba.conf основного сервера разрешите входящие сетевые подключения от реплики. Для этого нужно указать частный IP-адрес реплики. В файле также есть строка If you want to allow non-local connections, you need to add more. После неё добавляется примерно следующее содержание: host replication postgres REPLICA_ВНУТРЕННИЙ_IP/32 md5.

Чтобы разрешить трафик от реплики к главной копии, также измените настройки брандмауэра:

sudo ufw allow from db_replica_private_ip_address to any port 5432

Для применения изменений перезагрузите PostgreSQL.

Исправление проблем

Если кажется, что репликация не работает, проверьте журнал PostgreSQL на реплике на наличие возможных ошибок. Журнал найдите по адресу /var/log/postgresql/postgresql-10-main.log. 

Вот распространённые проблемы:

• Частная сеть не включена на обоих серверах или серверы расположены в разных сетях.
• Сервер не настроен на прослушивание подключений по правильному IP-адресу частной сети.
• Не установлено значение logical.
• Брандмауэр блокирует входящие соединения.
• Имена таблиц или полей не совпадают на издателе и подписчике.
• Таблица не добавлена ​​в публикацию.

После решения проблем репликация должна выполняться автоматически. Если это не помогло, удалите подписку командой DROP SUBSCRIPTION my_subscription; и создайте её заново.

Физическая репликация

PostgreSQL предлагает два типа репликации — физическую и логическую. Логическую мы рассмотрели выше; коротко коснемся второго вида репликации.

Физическая репликация появилась в PostgreSQL 9.0. В ней изменения описываются на уровне файлов. В основе синхронизации лежат точные адреса блоков, а сама репликация выполняется побайтово. Каждое изменение в мастере передается через WAL (журнал с предзаписью) и затем применяется на резервном сервере. Однако у этого механизма есть ряд ограничений:

• Нельзя реплицировать часть базы данных.
• Высокие расходы — нужно передавать все изменения, из-за этого растёт нагрузка на сеть. 
• Нельзя синхронизировать копии между разными платформами, например Windows и Linux. Для физической репликации требуются одинаковые серверы, вплоть до архитектуры процессора. 
• Нельзя синхронизировать копии между разными версиями СУБД.

Выгодные тарифы на облачные базы данных

Заключение

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

Упростить управление базами данных помогают сервисы облачных провайдеров. Например, Timeweb Cloud предлагает облачные базы данных, среди которых доступен и PostgreSQL.