Логическая репликация PostgreSQL 10 в Ubuntu 18.04

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

PostgreSQL – это открытая объектно-реляционная система баз данных. Она легко расширяется и поддерживает совместимость с ACID (Atomicity, Consistency, Isolation, Durability) и стандартом SQL. Версия 10.0 PostgreSQL предлагает поддержку логической репликации в дополнение к физической. В схеме логической репликации операции записи высокого уровня передаются с сервера основной базы данных на один или несколько серверов-реплик. При физической репликации операции записи передаются с master-сервера на реплику, создавая точную копию исходного содержимого до байта. Если вы хотите реплицировать конкретный поднабор данных (например, это могут быть отчеты, патчи или обновления), настройте логическую репликацию – она обеспечит скорость и гибкость процессов.

Данный мануал поможет настроить логическую репликацию в PostgreSQL 10 между двумя серверами Ubuntu 18.04, где один сервер будет мастером, а второй – репликой.

Требования

• Два сервера Ubuntu 18.04, db-master и db-replica, настроенные по этому мануалу.
• Частная сеть на серверах. Частная сеть позволяет поддерживать безопасную связь между серверами.
• СУБД PostgreSQL 10. Установить ее можно с помощью мануала Установка и использование PostgreSQL в Ubuntu 18.04.

1: Настройка PostgreSQL

Для поддержки логической репликации между серверами необходимо будет изменить несколько параметров конфигурации. Во-первых, нужно настроить Postgres для прослушивания частного сетевого интерфейса (а не публичного – иначе данные не будут защищены в сети). Затем нужно внести соответствующие настройки, чтобы включить репликацию на db-replica.

Войдите на сервер db-master и откройте  главный конфигурационный файл /etc/postgresql/10/main/postgresql.conf:

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

Найдите строку:

...
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
...

Раскомментируйте ее, удалив #, и укажите внутренний ip-адрес вашего сервера db-master, чтобы включить поддержку соединений по частной сети.

...
listen_addresses = 'localhost, db_master_private_ip_address'
...

Примечание: Здесь и далее в мануале важно использовать внутренние IP-адреса серверов, а не внешние адреса. Доступ к серверу базы данных в публичной сети Интернет – это серьезный риск для безопасности.

Теперь db-master прослушивает входящие соединения в частной сети и на loopback интерфейсе.

Теперь найдите строку:

...
#wal_level = replica                    # minimal, replica, or logical
...

Раскомментируйте ее и укажите в PostgreSQL Write Ahead Log (WAL) уровень logical. Это увеличивает объем записей в логе, добавляя необходимую информацию для извлечения отличий или изменений в конкретных наборах данных:

...
wal_level = logical
...

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

Сохраните и закройте файл.

Теперь отредактируйте /etc/postgresql/10/main/pg_hba.conf – этот файл управляет поддерживаемыми хостами, аутентификацией и доступом к БД.

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

В конец файла нужно добавить строку, которая поддерживает входящие сетевые соединения от db-replica. Используйте внутренний IP-адрес db-replica и укажите, что сервер должен поддерживать соединения всех пользователей и баз данных:

...
# TYPE      DATABASE        USER            ADDRESS                               METHOD
...
host         all            all             db_replica_private_ip_address/32      md5

Теперь мастер поддерживает сетевые соединения от db-replica по парольному хэшу (md5).

Сохраните и закройте файл.

Затем измените настройки брандмауэра, чтобы пропустить трафик db-replica по порту 5432 на db-master.

sudo ufw allow from db_replica_private_ip_address to any port 5432

Перезапустите сервер PostgreSQL, чтобы изменения вступили в силу:

sudo systemctl restart postgresql

2: Создание базы данных, ролей и таблиц

Чтобы проверить работу репликации, создайте базу данных, таблицу и роль пользователя. Сейчас достаточно создать тестовую базу данных с таблицей example, которую вы сможете использовать для проверки логической репликации между серверами. Также нужно создать отдельного пользователя и присвоить ему права на БД и таблицу.

Примечание: Следующие команды нужно запустить на обоих серверах, db-master и db-replica.

Сначала откройте командную строку psql как пользователь postgres:

sudo -u postgres psql

Создайте БД example:

CREATE DATABASE example;

Примечание: Конечная точка с запятой (;) в этих командах необходима – в интерактивных сеансах без этого символа PostgreSQL не будет выполнять команды SQL. Метакоманды (те, которые начинаются с обратного слэша, такие как \q и \c) напрямую управляют самим клиентом psql и поэтому являются исключением из этого правила. Дополнительные сведения о метакомандах и клиенте psql см. в документации PostgreSQL.

С помощью метакоманды \connect подключитесь к созданным БД:

\c example

Создайте новую таблицу widgets со случайными полями:

CREATE TABLE widgets
(
id SERIAL,
name TEXT,
price DECIMAL,
CONSTRAINT widgets_pkey PRIMARY KEY (id)
);

Таблица на db-replica не обязательно должна быть идентична таблице db-master. Однако она должна содержать такие же столбцы, как в таблице db-master. Дополнительные столбцы не могут поддерживать ограничений типа NOT NULL. Иначе репликация не будет работать.

Примечание: Напоминаем, что все вышеперечисленные команды нужно было выполнить на обоих хостах.

На db-master создайте новую роль пользователя с помощью опции REPLICATION и паролем. Атрибуту REPLICATION нужно присвоить любую роль для репликации. Мы назовем нашего пользователя 8host, но вы можете указать свое имя пользователя. Обязательно замените my_password надежным паролем.

CREATE ROLE 8host WITH REPLICATION LOGIN PASSWORD 'my_password';

Этот пароль нужно запомнить или записать, так как он понадобится вам позже, чтобы настроить репликацию на db-replica.

Оставайтесь на db-master. Передайте полные права на БД example новому пользователю:

GRANT ALL PRIVILEGES ON DATABASE example TO 8host;

Теперь передайте ему все права на все таблицы в этой БД:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO 8host;

Схема public – это схема по умолчанию для каждой БД, в которой автоматически помещаются таблицы.

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

3: Создание публикации

Публикации – это механизм, который PostgreSQL использует для создания таблиц для репликации. Сервер базы данных будет отслеживать внутреннее состояние соединения и репликации всех серверов-реплик, связанных с данной публикацией. Создайте на db-master публикацию my_publication, которая будет работать как основная копия данных. Эти данные будут отправлены подписчикам – в данном случае это db-replica.

На сервере db-master создайте публикацию:

CREATE PUBLICATION my_publication;

Добавьте в нее таблицу widgets:

ALTER PUBLICATION my_publication ADD TABLE widgets;

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

4: Создание подписки

Подписка используется PostgreSQL для подключения к существующим публикациям. Публикация может иметь множество подписок на разных серверах-репликах, а серверы-реплики могут также иметь свои собственные публикации с подписчиками. Чтобы получить доступ к данным из таблицы, созданной на db-master, нужно создать подписку на публикацию my_publication.

Перейдите на сервер db-replica и создайте подписку my_subscription. Команда CREATE SUBSCRIPTION задает имя подписки, а параметр CONNECTION определяет строку для доступа к публикующему серверу. Эта строка включает сведения о соединении главного сервера и учетные данные для входа, включая имя пользователя и пароль, которые вы определили ранее, а также имя базы данных example. Здесь тоже нужно использовать внутренний IP-адрес db-master и заменить my_password собственным паролем:

CREATE SUBSCRIPTION my_subscription CONNECTION 'host=db_master_private_ip_address port=5432 password=my_password user=8host dbname=example' PUBLICATION my_publication;

Вы получите такой вывод:

NOTICE:  created replication slot "my_subscription" on publisher
CREATE SUBSCRIPTION

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

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

5: Тестирование репликации и устранение неполадок

Чтобы проверить репликацию между мастером и репликой, добавьте новые данные в таблицу widgets и убедитесь, что данные были скопированы на сервер-реплику.

На сервере db-master добавьте в таблицу widgets такие данные:

INSERT INTO widgets (name, price) VALUES ('Hammer', 4.50), ('Coffee Mug', 6.20), ('Cupholder', 3.80);

На сервере db-replica запустите такую команду, чтобы извлечь все записи этой таблицы:

SELECT * FROM widgets;

Вы должны увидеть:

id |    name    | price
----+------------+------
1   | Hammer     |  4.50
2   | Coffee Mug |  6.20
3   | Cupholder  |  3.80
(3 rows)

Если это так – записи были успешно реплицированы с db-master на db-replica. С этого момента все запросы INSERT, UPDATE и DELETE будут реплицироваться по серверам в одностороннем порядке.

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

Закройте командную строку psql на обоих серверах:

\q

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

Устранение неполадок

Если репликация не работает, для начала следует проверить ошибки в логе PostgreSQL на db-replica:

tail /var/log/postgresql/postgresql-10-main.log

Вот самые распространенные ошибки при настройке репликации:

Частная сеть не включена на одном из серверов, или серверы находятся в разных сетях.

• db-master прослушивает соединения на неправильном внутреннем IP-адресе.
• Write Ahead Log на db-master настроен неправильно (он должен быть logical).
• db-master не поддерживает входящие соединения от внутреннего IP-адреса сервера db-replica.
• Брандмауэр (например, UFW) блокирует входящие соединения PostgreSQL на порт 5432.
• Имена таблиц или поля между db-master и db-replica не согласованы.
• У роли базы данных 8host нет необходимых прав для доступа к базе данных example на db-master.
• У роли базы данных 8host нет опции REPLICATION для db-master.
• У роли базы данных 8host отсутствуют необходимые права для доступа к таблице widgets на db-master.
• Таблица не была добавлена в публикацию на db-master.

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

DROP SUBSCRIPTION my_subscription;

Затем создайте подписку снова.

Заключение

Теперь вы умеете настраивать логическую репликацию PostgreSQL 10  между серверами Ubuntu 18.04.

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

Чтобы узнать больше о логической репликации в PostgreSQL 10, вы можете прочитать главу в официальной документации PostgreSQL, а также получить справку о командах CREATE PUBLICATION и CREATE SUBSCRIPTION.