Потоковая репликация в PostgreSQL

Потоковая репликация (streaming replication) является передачей записей из WAL (Write-Ahead Log) от мастера к репликам. Писать при этом можно только в мастер, но читать можно как с мастера, так и с реплик.

В данной статье рассмотрим настройку репликации postgreSQL сервера в CentOS 7. Подразумевается что сам PostgreSQL у вас уже установлен и настроен. Как установить и настроить postgresql подробно рассмотрено в этой статье.

Настройка мастера (master)

Допустим основной сервер у нас имеет ip 10.10.10.1, а резервный 10.10.10.2

Правим на мастере /var/lib/pgsql/data/postgresql.conf.

Параметр wal_level определяет, как много информации записывается в WAL. Со значением replica (по умолчанию) в журнал записываются данные, необходимые для поддержки архивирования WAL и репликации, включая запросы только на чтение на ведомом сервере. Вариант minimal оставляет только информацию, необходимую для восстановления после сбоя или аварийного отключения. Наконец, logical добавляет информацию, требующуюся для поддержки логического декодирования. Каждый последующий уровень включает информацию, записываемую на всех уровнях ниже. Задать этот параметр можно только при запуске сервера.

для PostgreSQL < 9.6

wal_level = hot_standby

для PostgreSQL > 9.6

wal_level = replica

Задаёт максимально допустимое число одновременных подключений резервных серверов или клиентов потокового копирования (т. е. максимальное количество одновременно работающих процессов передачи WAL). По умолчанию это значение равно нулю, то есть репликация отключается. Передающие WAL процессы учитываются в общем числе соединений, так что этот параметр не может превышать max_connections. В случае неожиданного отключения клиента потоковой передачи слот подключения может оставаться занятым до достижения тайм-аута, так что этот параметр должен быть немного больше максимального ожидаемого числа клиентов, чтобы отключившиеся клиенты могли переподключиться немедленно. Задать этот параметр можно только при запуске сервера. Чтобы к данному серверу могли подключаться резервные, уровень wal_level должен быть archive или выше.

max_wal_senders = 3

Задаёт минимальное число файлов прошлых сегментов журнала, которые будут сохраняться в каталоге pg_xlog, чтобы ведомый сервер мог выбрать их при потоковой репликации. Обычно сегмент имеет размер 16 мегабайт. Если ведомый сервер, подключённый к передающему, отстаёт больше чем на wal_keep_segments сегментов, передающий удаляет сегменты WAL, всё ещё необходимые ведомому, и в этом случае соединение репликации прерывается. В результате этого затем также будут прерваны зависимые соединения. (Однако ведомый сервер сможет восстановиться, выбрав этот сегмент из архива, если осуществляется архивация WAL.)

wal_keep_segments = 128

Теперь правим /etc/postgresql/9.6/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replication     10.10.10.0/24         md5

Создаем пользователя «replication» c паролем «passw0rd»:

sudo -u postgres psql
CREATE ROLE replication WITH REPLICATION PASSWORD 'passw0rd' LOGIN;

Рестартуем postgresql

systemctl restart postgresql

Настраиваем слейв (slave)

Останавливаем наш слейв

systemctl stop postgresql

Очищаем всё, что внутри data_directory, в нашем случаи — папка /var/lib/pgsql/data/

rm -Rf /var/lib/pgsql/data/*

Копируем текущее состояние postgresql с мастера.

su postgres -c "pg_basebackup -h 10.10.10.1 -D /var/lib/pgsql/data -P -U replication --xlog-method=stream"

Правим /var/lib/pgsql/data/postgresql.conf

hot_standby = on

Создаем файл /var/lib/pgsql/data/recovery.conf следующего содержания:

standby_mode = 'on'
primary_conninfo = 'user=replication password=passw0rd host=10.10.10.1 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'
trigger_file = '/var/lib/pgsql/data/trigger_file'

Меняем владельца на файл

chown postgres:postgres recovery.conf

Запускаем slave

systemctl start postgresql

На этом настройка репликации завершена.

Проверка работы репликации

Для проверки запустите на мастере следующую команду

sudo -u postgres psql -c 'SELECT *,pg_xlog_location_diff(s.sent_location,s.replay_location) byte_lag FROM pg_stat_replication s;'

Вывод должен быть примерно такой

Вывод показывает список клиентов-слейвов, подключенных к мастеру, а и последняя колонка byte_lag покажет отставание слейва от мастера в байтах.

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

 sudo -u postgres psql -c "SELECT now()-pg_last_xact_replay_timestamp();"

Тестирование

Создаем на мастере новую таблицу

sudo -u postgres psql
CREATE TABLE replicatest (test varchar(100));
INSERT INTO replicatest VALUES ('test string1');
INSERT INTO replicatest VALUES ('test string2');
INSERT INTO replicatest VALUES ('test string3');

Переходим на слейв и смотрим синхронизацию данных

sudo -u postgres psql
select * from replicatest;

Если видим такой вывод

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

postgres=# INSERT INTO replicatest VALUES ('test string4');
ОШИБКА: в транзакции в режиме "только чтение" нельзя выполнить INSERT

Останавливаем мастер

systemctl stop postgresql

Создаем на slave файл trigger_file, который мы указали в /var/lib/pgsql/data/recovery.conf

touch /var/lib/pgsql/datat/trigger_file

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

sudo -u postgres psql
INSERT INTO replicatest VALUES ('test string4');

При восстановлении мастера нужно будет проделать все выше проделанные операции уже на новом slave(бывшем master).