Настройка логической репликации PostgreSQL
Обновлено:
Опубликовано:
Используемые термины: PostgreSQL.
Репликация PostgreSQL представляет из себя способ реализации отказоустойчивого кластера. Инструкция написана на примере PostgreSQL 14, но она также будет работать для PostgreSQL более ранних версий (до 10).
Логическая репликация добавлена в PostgreSQL с версии 10. Она позволяет:
- Объединить несколько баз в одну.
- Использовать особые условия для передачи данных подписчику.
- Дать доступ к реплицированным данным другим группам пользователей.
- Разделить данные из нескольких баз между несколькими другими базами данных.
- Передать инкрементальные изменения в одной или нескольких базах в момент редактирования информации.
- Упростить условия для настройки репликации: могут использоваться разные мажорные версии PostgreSQL, а также платформы операционных систем.
Используемые в данном руководстве команды, применимы для операционных систем Linux. Если PostgreSQL работает под Windows, данную инструкцию можно использовать как шпаргалку для настройки конфигурационных файлов СУБД.
Подготовка сервера
Настройка сервера Master
Настройка сервера Slave
Создание публикации и подписки
Выполнение проверки работы репликации
Пример команд
1. Подготовка серверов
Для начала, готовим наши серверы к настройке кластера.
PostgreSQL
На всех серверах должна быть установлена PostgreSQL версии 10 и выше. Пример установки в инструкции PostgreSQL на CentOS.
Брандмауэр
При использовании брандмауэра, необходимо открыть TCP-порт 5432 — он используется сервером postgresql.
а) Если управление выполняется с помощью Firewalld:
firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --reload
б) Если используем Iptables:
iptables -I INPUT -p tcp --dport 5432 -j ACCEPT
Для сохранения правил можно использовать iptables-persistent:
apt install iptables-persistent
netfilter-persistent save
в) Если используем UFW:
ufw allow 5432/tcp
SELinux
Если активирована система безопасности SELinux (по умолчанию в системах Red Hat / CentOS / Fedora), отключаем ее:
setenforce 0
sed -i 's/^SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config
Если необходимо, чтобы SELinux работал, настраиваем его.
2. Настройки на Master
В данной статье мы будем настраивать серверы с IP-адресами 192.168.1.10 (первичный или master) и 192.168.1.11 (вторичный или slave). Реплицировать будет базу с названием db_test.
Переходим на сервер, с которого будем реплицировать данные (мастер) и выполняем следующие действия.
Смотрим расположение конфигурационного файла postgresql.conf командой:
su - postgres -c "psql -c 'SHOW config_file;'"
В моем случае система вернула строку:
/var/lib/pgsql/14/data/postgresql.conf
Открываем конфигурационный файл postgresql.conf.
vi /var/lib/pgsql/14/data/postgresql.conf
* мы открываем файл, который получили sql-командой SHOW config_file;.
Редактируем следующие параметры:
listen_addresses = 'localhost, 192.168.1.10'
...
wal_level = logical
* где
- listen_addresses — IP-адрес сервера, на котором он будем слушать запросы PostgreSQL. Можно перечислить адреса, имя или поставить * для разрешения всех адресов.
- wal_level указывает, сколько информации записывается в WAL (журнал операций, который используется для репликации). Возможны варианты:
- replica — записывает достаточно данных для поддержки архивирования и репликации WAL, включая выполнение запросов только для чтения на резервном сервере.
- minimal — удаляет все журналы, кроме информации, необходимой для восстановления после сбоя или немедленного завершения работы.
- logical — добавляет информацию, необходимую для поддержки логического декодирования.
Смотрим расположение конфигурационного файла pg_hba.conf:
su - postgres -c "psql -c 'SHOW hba_file;'"
В моем случае ответ такой:
/var/lib/pgsql/14/data/pg_hba.conf
Открываем его:
vi /var/lib/pgsql/14/data/pg_hba.conf
Добавляем строки:
host db_test postgres 192.168.1.11/32 trust
host postgres postgres 192.168.1.11/32 trust
* данной настройкой мы разрешаем подключение к базам данных postgres и db_test пользователю postgres с сервера 192.168.1.11.
Перезапускаем службу postgresql:
systemctl restart postgresql
Обратите внимание, что название для сервиса в системах Linux может различаться, например:
systemctl restart postgresql-14
Мастер настроен — переходим к вторичному серверу.
3. Настройки на Slave
Смотрим расположение конфигурационного файла postgresql.conf командой:
su - postgres -c "psql -c 'SHOW config_file;'"
Открываем конфигурационный файл postgresql.conf.
vi /var/lib/pgsql/14/data/postgresql.conf
* мы открываем файл, который получили sql-командой SHOW config_file;.
Редактируем параметр wal_level:
wal_level = logical
Перезапускаем службу postgresql:
systemctl restart postgresql
Обратите внимание, что название для сервиса в системах Linux может различаться, например:
systemctl restart postgresql-14
Входим под пользователем postgres:
su - postgres
Реплицируем на вторичный сервер базы postgres и db_test:
$ pg_dumpall --database=postgres --host=192.168.1.10 --no-password --globals-only --no-privileges | pg_dump --dbname db_test --host=192.168.1.10 --no-password --create --schema-only | psql
Вторичный сервер готов к репликации.
4. Настройка публикации
Теперь настраиваем логику. На стороне мастера мы создаем публикацию, а на стороне слейва — подписку. Рассмотрим пример.
На мастере
Заходим в postgresql и подключаемся к базе db_test:
su - postgres -c "psql db_test"
Создадим публикацию базы:
=# CREATE PUBLICATION db_test_pub FOR ALL TABLES;
* мы создали публикацию с названием db_test_pub для базы db_test (мы к ней подключены) и всех таблиц.
Если нам нужно создать подписку для некоторых таблиц, то вводим:
=# CREATE PUBLICATION db_test_pub FOR TABLE users, objects, projects;
* в данном примере мы сделаем публикацию для базы db_test и таблиц users, objects, projects.
Идем на слейв.
На слейве
Заходим в postgresql:
psql
И подключаемся к базе:
=# \c db_test
Создаем подписку:
=# CREATE SUBSCRIPTION db_test_sub CONNECTION 'host=192.168.1.10 dbname=db_test' PUBLICATION db_test_pub;
* где:
- db_test_sub — произвольное имя подписки.
- 192.168.1.10 — адрес мастера.
- db_test — таблица, которую мы будем реплицировать.
- db_test_pub — имя публикации, которая была создана на мастере.
5. Проверка
Убедимся, что репликация работает. Статус работы репликации можно посмотреть следующими командами.
а) На мастере:
=# SELECT * FROM pg_stat_replication;
А данной командой можно посмотреть настройки публикации:
=# \dRp+
Чтобы увидеть задержки, используем запрос:
=# SELECT slot_name, confirmed_flush_lsn, pg_current_wal_lsn(), (pg_current_wal_lsn() - confirmed_flush_lsn) AS lsn_distance FROM pg_replication_slots;
* значение lsn_distance — показатель задержки. Чем оно меньше, тем лучше.
б) На слейве:
=# SELECT * FROM pg_stat_subscription;
Сведения о подписке смотрим командой:
=# \dRs+
Дополнительно
Рассмотрим некоторые команды, которые могут оказаться полезными.
1. Удалить публикацию.
Для удаления публикации используем команду:
=# DROP PUBLICATION <имя публикации>
Например:
=# DROP PUBLICATION db_test_pub;
2. Обновить публикацию.
Обновить публикацию можно с помощью команды:
=# ALTER PUBLICATION <имя публикации>
Например:
=# ALTER PUBLICATION db_test_pub ADD TABLE builds;
* в данном примере мы добавили к публикации таблицу builds.
