Потоковая репликация (streaming replication) является передачей записей из WAL (Write-Ahead Log) от мастера к репликам. Писать при этом можно только в мастер, но читать можно как с мастера, так и с реплик. В итоге мы получаем не просто горизонтальное масштабирование, а ещё и отказоустойчивую архитектуру (failover).
Приступим к настройке реплики.
Начальные условия
Возьмём 2 сервера (в моём случаи — виртуальные машины) с чистым Debian 8.6 (jessie) amd64.
- master: 192.168.0.100
- slave: 192.168.0.200
Устанавливаем PostgreSQL 9.6 в Debian 8 (jessie) на оба сервера
echo "deb https://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo apt-key add -
sudo apt update
sudo apt install postgresql-9.6
Настраиваем мастер (master)
Правим /etc/postgresql/9.6/main/postgresql.conf
# Задаёт адреса TCP/IP, по которым сервер будет принимать подключения клиентских
# приложений. Это значение принимает форму списка, разделённого запятыми, из имён
# и/или числовых IP-адресов компьютеров. Особый элемент, *, обозначает все
# имеющиеся IP-интерфейсы.
listen_addresses = '*'
# Параметр wal_level определяет, как много информации записывается в WAL. Со
# значением minimal (по умолчанию) в журнал записывается только информация,
# необходимая для восстановления после сбоя или аварийного отключения. Вариант
# replica добавляет в него то, что необходимо для архивирования WAL, а также
# информацию, необходимую для выполнения запросов на резервном сервере в режиме
# «только чтение». Наконец, logical добавляет информацию, требуемую для поддержки
# логического декодирования.
# wal_level = hot_standby # для PostgreSQL < 9.6
wal_level = replica
# Задаёт максимально допустимое число одновременных подключений резервных серверов
# или клиентов потокового копирования (т. е. максимальное количество одновременно
# работающих процессов передачи WAL). По умолчанию это значение равно нулю, то
# есть репликация отключается. Передающие WAL процессы учитываются в общем числе
# соединений, так что этот параметр не может превышать max_connections.
max_wal_senders = 3
# Задаёт минимальное число файлов прошлых сегментов журнала, которые будут сохраняться
# в каталоге pg_xlog, чтобы резервный сервер мог выбрать их при потоковой репликации.
# Обычно сегмент имеет размер 16 мегабайт.
wal_keep_segments = 128
Когда дочитаете донизу, поймёте что эти же настройки /etc/postgresql/9.6/main/postgresql.conf лучше сразу скопировать на слейв, не дожидаясь часа «Ж» 
Теперь правим /etc/postgresql/9.6/main/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host replication replication 192.168.0.0/24 md5
Обратите внимание что в качестве БД указано «replication». Это не имя БД, это системное значение.
Создаем пользователя «replication» c паролем «Hello!» (придумайте что-то посложнее):
# su postgres
$ psql
postgres=# CREATE ROLE replication WITH REPLICATION PASSWORD 'Hello!' LOGIN;
Перезапускаем наш мастер
root@master:/# systemctl restart postgresql && systemctl status postgresql
Настраиваем слейв (slave)
Останавливаем наш слейв
root@slave:/# systemctl stop postgresql
Правим /etc/postgresql/9.6/main/postgresql.conf
# Определяет, можно ли будет подключаться к серверу и выполнять запросы в процессе
# восстановления. Значение по умолчанию — off. Задать этот параметр можно только при
# запуске сервера. Данный параметр играет роль только в режиме резервного сервера или
# при восстановлении архива.
hot_standby = on
Предположим что у вас уже рабочая БД на мастере что только наполняется и увеличивается.
Теперь нам нужно скопировать как начальное состояние мастера на слейв. С версии 9.1 это можно сделать благодаря удобной команде pg_basebackup, ранее это делали с помощью rsync, но не без трудностей.
Очищаем всё, что внутри data_directory, в нашем случаи — папка /var/lib/postgresql/9.6/main на слейве (!!!)
root@slave:/# rm -Rf /var/lib/postgresql/9.6/main/*
Копируем текущее состояние с мастера на слейв, выполняя команду (на слейве) и вводим пароль пользователя replication:
root@slave:/# su postgres -c "pg_basebackup -h 192.168.0.100 -D /var/lib/postgresql/9.6/main -R -P -U replication --xlog-method=stream"
Скрипт автоматически создаст файл recovery.conf приблизительно такого содержания:
standby_mode = 'on'
primary_conninfo = 'user=replication password=Hello! host=192.168.0.100 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'
В который мы допишем ещё параметр trigger_file как путь к файлу, существование которого будет говорить PostgreSQL что он стал новым мастером, при случаи когда произойдёт неизбежное.
И итоге получим следующее содержание файла /var/lib/postgresql/9.6/main/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=replication password=Hello! host=192.168.0.100 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'
trigger_file = '/var/lib/postgresql/9.6/main/trigger_file'
Ещё раз акцентирую внимание на том, что триггер-файл при нормальных условиях создавать не нужно.
В Сети довольно много примеров где копируют с помощью rsync, например так:
root@slave:/# rsync --delete -avc [email protected]:/var/lib/postgresql/9.6/main/ /var/lib/postgresql/9.6/main/ --exclude postmaster.pid
От себя могу добавить что это не рекомендуемый способ, поскольку при нагруженной и/или довольно большой базе на мастере вполне вероятна ситуация что данные быстрее изменяются быстрее, чем будут скопированы. И в итоге вы так и не запустите свой слейв. Конечно эту процедуру можно сделать на выключенном мастере, но мы этот вариант не рассматриваем.
Запускаем наш слейв
root@slave:/# systemctl start postgresql && systemctl status postgresql
Всё работает? Всё работает!
Как узнать что все завелось и работает? Попробуем просто посмотреть в список процессов.
(на мастере):
root@master:/# ps wax|grep sender
Что выведет:
2741 ? Ss 0:00 postgres: 9.6/main: wal sender process replication
192.168.0.200(50696) streaming 0/14002500
(на слейве):
root@slave:/# ps wax|grep receiver
Что выведет:
1178 ? Ss 0:00 postgres: 9.6/main: wal receiver process streaming 0/14002538
Это значит что репликация как-то работает. Более точно помогут следующие команды:
(на мастере):
root@master:/# sudo -u postgres psql -c 'SELECT *,pg_xlog_location_diff(s.sent_location,s.replay_location) byte_lag FROM pg_stat_replication s;'
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state | byte_lag
------+----------+-------------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------+----------
2741 | 16406 | replication | walreceiver | 192.168.0.200 | | 50696 | 2016-11-27 20:17:59.464068+02 | | streaming | 0/1400A9B8 | 0/1400A9B8 | 0/1400A9B8 | 0/14007C38 | 0 | async | 11648
Что покажет не только список клиентов-слейвов, подключенных к мастеру, а и последняя колонка byte_lag покажет отставание слейва от мастера в байтах (11648 байт в примере выше).
UPDATE:
Для PostgreSQL 10+ команда и её вывод будет немного другой
root@master:/# sudo -u postgres psql -c 'SELECT *,pg_wal_lsn_diff(s.sent_lsn,s.replay_lsn) AS byte_lag FROM pg_stat_replication s;'
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | byte_lag
-------+----------+-------------+------------------+--------------+-----------------+-------------+------------------------------+--------------+-----------+-------------+-------------+-------------+-------------+-----------+-----------+------------+---------------+------------+----------
15405 | 16387 | replication | walreceiver | 192.168.0.200| | 47052 | 2017-12-06 10:16:51.94418+02 | | streaming | 46/941D79C0 | 46/941D79C0 | 46/941D79C0 | 46/941D79C0 | | | | 0 | async | 11648
Также на слейве можно смотреть, как давно было последнее обновление данных с мастера:
root@slave:/# sudo -u postgres psql -c "SELECT now()-pg_last_xact_replay_timestamp();"
Что выведет:
?column?
-----------------
00:00:12.778029
Всё работает. Можете добавлять реплик столько, сколько захотите. Не забудьте только увеличить значение max_wal_senders в конфиге мастера и перезапустить его.
При довольно нагруженной работе с БД может быть такой случай, что слейв будет быстро отставать от мастера. В таком случаи увеличьте значение wal_keep_segments до нужного значения. Не забывайте только что каждый сегмент занимает по 16 МБ.
А давайте что-то поломаем?!
Кладём мастер
А теперь сымитируем ситуацию когда «падает» мастер. Заходим на мастер и пишем например:
root@master:/# systemctl stop postgresql
Видим что наш слейв начинает отставать от мастера:
root@slave:/# sudo -u postgres psql -c "SELECT now()-pg_last_xact_replay_timestamp();"
Что выведет:
?column?
-----------------
00:01:02.580578
Делаем из слейва мастера
Создаём триггер-файл на слейве (имя и расположение триггер-файла мы указывали в /var/lib/postgresql/9.6/main/recovery.conf)
root@slave:/# touch /var/lib/postgresql/9.6/main/trigger_file
Слейв автоматически станет мастером, без перезапуска PostgreSQL и спокойно даст в себя влить пачку данных, чего бы не позволил в «обычной жизни»:
root@slave:/# su postgres -c "psql -d test_database -c 'insert into public.data(data_int, data_text) select (random()*1000*gs)::integer, md5((random()*1000*gs)::integer::text) from generate_series(1,1000) gs;'"
INSERT 0 1000
Внезапно возвращаем к жизни прежний мастер
А что будет если вдруг мы поднимем прежний мастер?
root@master:/# systemctl start postgresql && systemctl status postgresql
Скажу сразу — он поднимется без проблем и продолжит давать вливать в себя данные на запись, поскольку он не контролирует слейв и у нас в итоге сейчас получится жёсткая рассинхронизация данных.
Что теперь делать? Как вернуть снова начальную схему работы?
Догоняем прежний мастер до актуального состояния и возвращаем ему прежний статус
Останавливаем это безобразие на прежнем мастере:
root@master:/# systemctl stop postgresql
И теперь нам нужно повторить все действия, что раньше касались слейва в конфигурировании выше.
А именно:
- Правим
/etc/postgresql/9.6/main/postgresql.confна слейве - Правим
/etc/postgresql/9.6/main/pg_hba.confна слейве и перезапускаем слейв - Создаем пользователя «replication» на слейве
- Удаляем все изменения на прежнем мастере в папке
data_directory:root@master:/# rm -Rf /var/lib/postgresql/9.6/main/*
- Копируем актуальное состояние на прежний мастер со слейва:
root@master:/# su postgres -c "pg_basebackup -h 192.168.0.200 -D /var/lib/postgresql/9.6/main -P -U replication --xlog-method=stream"
- Останавливаем слейв:
root@slave:/# systemctl stop postgresql
- Переименовываем recovery.done на recovery.conf:
root@slave:/# mv /var/lib/postgresql/9.6/main/recovery.done /var/lib/postgresql/9.6/main/recovery.conf
Файл переименовался «сам», когда слейв почувствовал себя мастером, созданием триггер-файла. Сам триггер-файл при этом удаляется.
- Запускаем мастер:
root@master:/# systemctl start postgresql && systemctl status postgresql
- Запускаем слейв:
root@slave:/# systemctl start postgresql && systemctl status postgresql
Слейв в себя больше не должен дать вливать данные, выдавая ошибку
ERROR: cannot execute INSERT in a read-only transaction
Теоретически, все вышеописанные действия по восстановлению мастера можно завернуть в какой-то скрипт. Но выполнять его лучше только в ручном режиме, чтобы не столкнуться с тем что из-за незапланированных технических работ в датацентре вы получите два рассинхронизорованных мастера.