Основы postgres

Репликация PostgreSQL представляет из себя способ реализации отказоустойчивого кластера. Инструкция написана на примере PostgreSQL 14, но она также будет работать для PostgreSQL более ранних версий (до 10).

Логическая репликация добавлена в PostgreSQL с версии 10. Она позволяет:

• Объединить несколько баз в одну.
• Использовать особые условия для передачи данных подписчику.
• Дать доступ к реплицированным данным другим группам пользователей.
• Разделить данные из нескольких баз между несколькими другими базами данных.
• Передать инкрементальные изменения в одной или нескольких базах в момент редактирования информации.
• Упростить условия для настройки репликации: могут использоваться разные мажорные версии PostgreSQL, а также платформы операционных систем.

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.