Настройка и виды репликаций Postgresql.

Write-Ahead Log (WAL) Когда данные в базе меняются, они сначала записываются в WAL, после записи в WAL система делает системный вызов fsync и данные записываются на диск, а не висят в кеше. Поэтому, если произойдёт выключение сервера или другой сбой, то при следующем включении СУБД во время старта прочитает данные из WAL и применит изменения к базе данных.

Потоковая репликация (Streaming Replication) Суть в том, что записи из WAL передаются от мастер-сервера(master) репликам(slave). Запись и изменение данных происходит только в master, но с реплик можно читать (hot standby). Если с реплики чтнение запрещено, то она называется warm standby или log shipping. Во многих приложениях очень много запросов на чтение (80% - 90%), поэтому репликация позволяет масштабировать базу данных горизонтально.

При потоковой репликации применение изменений происходит без понимания “смысла изменений”, поэтому важна двоичная совместимость между серверами. (одинаковые платформы и одинаковые мажорные версии postgresql)

Потоковая репликация бывает двух видов:

• синхронная
• асинхронная

При асинхронной репликации запись данных происходить сначала на master, а потом, в фоне, отправляется на slave. Минус в том, что будут потеряны данные, если на master произойдёт сбой, а они ещё не доехали до slave.

При использовании синхронной репликации данные сначала записываются в WAL любой реплики (если их несколько), а после чего транзакция выполняется на master. Из-за такого подхода запросы на запись выполняются медленее из-за сетевых задержек. Рекомендуется использовать более одной реплики при таком подходе. К плюсам можно отнести то, что потерять данные становится сложнее.

Каскадная репликация - это когда у реплики есть свои реплики, с которыми она синхронизирует WAL. master ~> slave ~> slave

Существует логическая репликация, которая доступна с 10й версии postgresql.

При логической репликации на одном сервере создается публикация, другие серверы могут на нее подписаться. У сервера нет выделенной роли: один и тот же сервер может как публиковать изменения, так и подписываться на другие (или даже свои) подписки. Подписчику передается информация об изменениях строк в таблицах в платформонезависимом виде; двоичная совместимость не требуется. Для работы логической репликации в журнале публикующего сервера необходима дополнительная информация (параметр wal_level = logical). Логическая репликация позволяет транслировать не все изменения, а только касающиеся определенных таблиц.

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

Установим postgresql на обе виртуалки (debian 10):

master - 192.168.122.181 slave - 192.168.122.102

Создадиим файл с конфигурацией репозитория:

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Импортируем ключ:

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Обновим пакеты и установим postgresql:

$ sudo apt-get update
$ sudo apt-get -y install postgresql

То же самое нужно повторить для второй виртуальной машины.

Создадим пользователя:

$ sudo -iu postgres
$ createuser --replication -P rep

Настройка master:

Посмотрим, где находятся конфиги postgresq:

$ psql -c 'SHOW config_file;'
/etc/postgresql/13/main/postgresql.conf

Редактируем следующие строки в файле /etc/postgresql/13/main/postgresql.conf:

listen_addresses = 'localhost, 192.168.122.181'
wal_level = replica
max_wal_senders = 2
max_replication_slots = 2
hot_standby = on
hot_standby_feedback = on

• listen_addresses - ip-адреса, на которых сервер будет слушать запросы postgresql
• wal_level - указывает, сколько информации записывается в WAL
• max_wal_senders - количество планируемых слейвов
• max_replication_slots - максимальное число слотов репликации (?)
• hot_standby - значение on указывает, что есть возможность подключаться к postgresql для выполнения запросов в процессе восстановления
• hot_standby_feedback - значение on указывает, что сервер slave будет сообщать мастеру о запросах, которые он выполняет

Аутентификация клиентов управляется конфигурационным файлом /etc/postgresql/13/main/pg_hba.conf. Добавим следующие строки в конец файла, это позвонит подключаться по паролю пользователю rep:

$ cat << EOF | sudo tee --append /etc/postgresql/13/main/pg_hba.conf
host replication rep 127.0.0.1/32 md5
host replication rep 192.168.122.181/32 md5
host replication rep 192.168.122.102/32 md5
EOF

Перезапускаем postresql:

$ sudo systemctl restart postgresql

Настройка slave:

Найдём конфиги:

$ sudo -u postgres psql -c 'SHOW config_file;'
/etc/postgresql/13/main/postgresql.conf

Посмотрим, где лежит база данных:

$ sudo -u postgres psql -c 'SHOW data_directory;'
$ sudo -u postgres psql -c 'SHOW all;'

Остановим сервис postgresql:

$ sudo systemctl stop postgresql

Переместим папку со старой базой в сторону и создадим новую папку под базу с master:

$ sudo -u postgres mv /var/lib/postgresql/13/main /var/lib/postgresql/13/main-old
$ sudo -u postgres mkdir /var/lib/postgresql/13/main
$ sudo -u postgres chmod 0700 /var/lib/postgresql/13/main

$ sudo rm -rf /var/lib/postgresql/13/main/*

Реплицируем данные с master:

$ sudo -u postgres pg_basebackup --host=192.168.122.181 --username=rep --pgdata=/var/lib/postgresql/13/main/ --wal-method=stream --write-recovery-conf

Теперь редактируем конфиг файл /etc/postgresql/13/main/postgresql.conf на slave :

listen_addresses = 'localhost, 192.168.122.102'

Запускаем:

$ sudo systemctl start postgresql

Проверка репликации:

Статус репликации на master:

postgres=# select * from pg_stat_replication;

Статус репликации на slave:

postgres=# select * from pg_stat_wal_receiver;

Если создать базу на master, она появится на slave:

На master:

postgres=# CREATE DATABASE test ENCODING='UTF8';
postgres=# \l

На slave:

postgres=# \l

Всё отлично. При создании базы на master она появляется на slave.
При удалении на master - удаляется со slave.