PostgreSQL. AlmaLinux 8. PostgreSQL 13. Потоковая репликация MASTER-SLAVE
Описание
Потоковая репликация в PostgreSQL 13 MASTER-SLAVE AlmaLinux 8.
Когда вы изменяете данные в базе, все изменения пишутся во Write-Ahead Log, или WAL. После записи в WAL СУБД делает системный вызов fsync, благодаря чему данные попадают сразу на диск, а не висят в где-то в кэше файловой системы. Таким образом, если взять и обесточить сервер, при следующей загрузке СУБД прочитает последние записи из WAL и применит к базе данных соответствующие изменения.
Потоковая репликация (streaming replication) в сущности является передачей записей из WAL от мастера к репликам. Писать при этом можно только в мастер, но читать можно как с мастера, так и с реплик. Если с реплики разрешено читать, она называется hot standby, иначе — warm standby. Поскольку во многих приложениях 90% запросов являются запросами на чтение, репликация позволяет масштабировать базу данных горизонтально. Потоковая репликация бывает двух видов — синхронная и асинхронная.
Асинхронная репликация используется в PostgreSQL по умолчанию. При этом методе запрос тут же выполняется на мастере, а соответствующие данные из WAL доезжают до реплик отдельно, в фоне. Недостаток асинхронной репликации заключается в том, что при внезапном падении мастера (например, из-за сгоревшего диска) часть данных будет потеряна, так как они не успели доехать до реплик.
При использовании синхронной репликации данные сначала записываются в WAL как минимум одной реплики, после чего транзакция выполняется уже на мастере. Запросы на запись выполняются медленнее в результате возникающих сетевых задержек (которые, однако, внутри одного ДЦ обычно меньше типичного времени планирования запроса). Кроме того, чтобы запросы на запись не встали колом в результате падения одной из реплик, при использовании синхронной репликации рекомендуется использовать по крайней мере две реплики. Зато потерять данные становится намного сложнее.
Заметьте, что синхронная репликация не предотвращает возможности считать с реплики старые данные, так как потоковая репликация — она только про передачу WAL, а не то, что видно в базе с точки зрения пользователя. По крайней мере, так синхронная репликация работает конкретно в PostgreSQL.
В контексте репликации нельзя также не отметить еще один интересный термин. Если одна из реплик в свою очередь является мастером для другой реплики, такую конфигурацию называют каскадной репликацией.
Помимо потоковой репликации в последнее время выделяют еще и так называемую логическую репликацию (logical replication). Реализаций логической репликации в PostgreSQL существует несколько, например, slony и pglogical. Пожалуй, наиболее существенное отличие логической репликации от потоковой заключается в возможности реплицировать часть баз данных и таблиц на одни реплики, а часть — на другие. Платить за это приходится скоростью. И хотя pglogical в плане скорости выглядит многообещающе, на момент написания этих строк это очень молодое, сырое решение. В рамках этой заметки логическая репликация не рассматривается.
Потоковая репликация в PostgreSQL не работает между разными версиями PostgreSQL, а также если на серверах используется разная архитектура CPU, например, x86 и x64. В частности, это означает, что обновить PostgreSQL до следующей версии при использовании потоковой репликации без даунтайма нельзя.
Входные данные
192.168.199.178 - MASTER
192.168.199.179 - SLAVE-1
192.168.199.183 - SLAVE-2 (Мастером для него будет SLAVE-1, после перевода SLAVE-1 в статус Мастера)
Настройка MASTER
Идём на http://yum.postgresql.org/repopackages.php, ищем самую последнюю версию.
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -qy module disable postgresql
dnf -y update
sudo dnf -y install postgresql13 postgresql13-server
Далее мы должны инициализировать кластер БД.
Кластер БД - это набор баз данных, который управляется одним экземпляром запущенного сервера БД. После инициализации, кластер БД будет содержать БД с именем postgres, которая является БД по умолчанию, которую используют утилиты, пользователи и сторонние приложения. Сам сервер БД не нуждается в этой БД, но многие внешние приложения подразумевают, что она существует. Другая БД, создаваемая на этапе инициализации, называется template1. Как и следует из её имени, она будет использоваться как шаблон для создаваемых БД; она не должна использоваться для работы.
Столкнулся с тем, что СУБД ставится с кодировкой Encoding="SQL_ASCII", Collate="C", Ctype="С".
Чтобы установить и инициализировать БД изначально
Установите пакет
yum install postgresql13-server
Далее нужно зайти под пользователя postgres
su - postgres
И выполнить инициализацию указав соответствующие кодировки
/usr/pgsql-13/bin/initdb --encoding UTF-8 --lc-collate='ru_RU.UTF-8' --lc-ctype='ru_RU.UTF-8' -D /var/lib/pgsql/13/data/
Если пакет postgresql13-server уже был установлен
Убейте СУБД (не забыв про бекап БД, если они уже созданы и нужны) и поставьте пакет заново:
yum remove postgresql13-server
rm -rf /var/lib/pgsql/
yum install postgresql13-server
Далее нужно зайти под пользователя postgres
su - postgres
И выполнить инициализацию указав соответствующие кодировки:
/usr/pgsql-13/bin/initdb --encoding UTF-8 --lc-collate='ru_RU.UTF-8' --lc-ctype='ru_RU.UTF-8' -D /var/lib/pgsql/13/data/
Включим его в автозагрузку и запустим службу
systemctl enable postgresql-13
systemctl start postgresql-13
Теперь нам необходимо включить авторизацию и создать пользователей, так как по умолчанию подключение локально разрешено для любого авторизированного в системе пользователя. Для этого заходим в систему под пользователем postgres и запускаем консоль управления СУБД:
psql -U postgres
Пользователь "postgres" является root'ом в СУБД. Поэтому начнём с того, что создадим для него пароль
ALTER ROLE postgres WITH PASSWORD 'your_super_password';
Теперь возвращаемся в консоль и обратно в root'а, дважды нажав сочетание клавиш "Ctrl+D".
Пора внести изменения в схему авторизации СУБД. Для этого нам потребуется отредактировать файл, расположенный по адресу /var/lib/pgsql/13/data/pg_hba.conf. Как обычно любым текстовым редактором:
nano /var/lib/pgsql/13/data/pg_hba.conf
... local all all md5 host all postgres 192.168.111.0/24 md5 host replication postgres 192.168.111.0/24 md5 ...
nano /var/lib/pgsql/13/data/postgresql.conf
... # какие адреса слушать, замените на IP этого MASTER сервера listen_addresses = '192.168.199.178' wal_level = hot_standby # опционально: не дожидаемся fsync на реплике при синхронной репликации # synchronous_commit = remote_write # это нужно, чтобы работал pg_rewind wal_log_hints = on max_wal_senders = 8 # если хотим синхронную репликацию на одну любую реплику # synchronous_standby_names = '*' hot_standby = on # log name pattern log_filename = 'postgresql_%Y-%m-%d.log' ...
Возвращаемся под пользователя ROOT (Ctrl+D)
Перезапускаем службу
systemctl restart postgresql-13
Пробуем подключиться от имени пользователя postgres:
psql -U postgres
Если успешно - выходим из пользователя
exit
Для удобства, чтобы не вводить пароль, но и не светить его в консоли, можно в домашней директории создать файл (он будет с права на чтение только для Вас), в котором записать пароль к СУБД
nano /root/.pgpass
localhost:*:*:postgres:your_super_password
chmod 600 /root/.pgpass
systemctl restart postgresql-13
Настройка SLAVE
Идём на http://yum.postgresql.org/repopackages.php, ищем самую последнюю версию.
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf -y update
sudo dnf -y install postgresql13 postgresql13-server
Становимся пользователем postgres
su postgres
Под этим пользователем переливаем данные с мастера
cd /var/lib/pgsql/13/
Следующая команда спросит пароль пользователя postgres, который мы меняли при настройке мастера.
Используйте -c fast, чтобы синкнуться как можно быстрее, или -c spread, чтобы минимизировать нагрузку.
Еще есть флаг -r, позволяющий ограничить скорость передачи данных (см man).
В этой команде есть важный параметр -R. Он означает, что PostgreSQL-сервер также создаст пустой файл standby.signal. Несмотря на то, что файл пустой, само наличие этого файла означает, что этот сервер — реплика.
Файл standby.signal появился только в PostgreSQL версии 12. Раньше вместо него создавался файл recovery.conf, в котором хранились настройки для подключения к Master-серверу.
pg_basebackup -P -R -X stream -c fast -h 192.168.199.178 -U postgres -D ./data
Следующие файлы правим аналогично мастеру
В postgresql.conf нужно указать IP нашего SLAVE сервера
nano /var/lib/pgsql/13/data/pg_hba.conf
... # Аналогичные настройки как и на мастере ...
nano /var/lib/pgsql/13/data/postgresql.conf
... # какие адреса слушать, замените на IP этого SLAVE сервера listen_addresses = '192.168.199.179' ...
Запускаем PostgreSQL
systemctl restart postgresql-13
Готово. Репликация настроена.
Проверка репликации
На мастере
psql -U postgres
SELECT * FROM pg_stat_replication;
Должны увидеть, что реплика действительно забирает WAL
-[ RECORD 1 ]----+------------------------------ pid | 5544 usesysid | 10 usename | postgres application_name | walreceiver client_addr | 10.0.3.223 client_hostname | client_port | 45095 backend_start | 2016-01-01 16:42:30.350283+03 backend_xmin | state | streaming sent_location | 0/3000220 write_location | 0/3000220 flush_location | 0/3000220 replay_location | 0/30001E8 sync_priority | 0 sync_state | async
На реплике
less /var/lib/pgsql/13/data/log/postgresql*.log
Должны увидеть что-то на тему "read only connections"
LOG: entering standby mode LOG: redo starts at 0/2000028 LOG: consistent recovery state reached at 0/20000F8 LOG: database system is ready to accept read only connections LOG: started streaming WAL from primary at 0/3000000 on timeline 1
Еще один способ проверить, что репликация работает - сказать на реплике
ps wuax | grep receiver
На мастере
Также можно создать базу данных и пару таблиц на МАСТЕРЕ, записать туда какие-то данные
psql -U postgres
СОЗДАТЬ ТАБЛИЦУ:
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- минимальная температура дня
temp_hi int, -- максимальная температура дня
prcp real, -- уровень осадков
date date
);
ВСТАВИТЬ СТРОКУ В ТАБЛИЦУ:
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('Тамбов', '12', '25', '4', '2019-09-09 09:00');
Проверить что данные внесены
SELECT * FROM weather;
На реплике
Убедиться, что на РЕПЛИКЕ появляется все то же самое
SELECT * FROM weather;
На реплике
При попытке писать в реплику должны увидеть
ERROR: cannot execute INSERT in a read-only transaction
На реплике
Также на слейве можно смотреть, как давно было последнее обновление данных с мастера
sudo -u postgres psql -c \
"select now()-pg_last_xact_replay_timestamp();"
Пример вывода
?column? ----------------- 00:00:03.639424 (1 row)
Если запись происходит постоянно (возможно, искусственно, специальным скриптом), можно с большой точностью считать это значение лагом репликации.
Дополнительно (ПРОВЕРИТЬ!)
Если вы настроили синхронную репликацию, можете попробовать остановить реплику, попытаться выполнить INSERT на мастере, и убедиться, что он повисает. Еще при использовании синхронной репликации можно контролировать степень «синхронности» на стороне клиента.
Не дожидаемся fsync на реплике:
SET synchronous_commit = 'remote_write';
Пишем только локально, не ждем подтверждения от реплики:
SET synchronous_commit = 'local';
Возвращаем обычное поведение:
SET synchronous_commit = 'on';
Проверка репликации при отсутствии сети между MASTER или SLAVE
Упал SLAVE, произошла запись данных в MASTER, SLAVE поднялся
- Упал SLAVE
- Записываем в MASTER:
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('Рязань', '12', '25', '4', '2019-09-09 09:00'); - Поднялся SLAVE
- Проверяем на SLAVE (подтянулась новая строка "Рязань"):
SELECT * FROM weather;
... city | temp_lo | temp_hi | prcp | date --------+---------+---------+------+------------ Тамбов | 12 | 25 | 4 | 2019-09-09 Рязань | 12 | 25 | 4 | 2019-09-09 (2 rows) ...
- После поднятия SLAVE данные с MASTER дописались в SLAVE
Упал SLAVE, произошла запись данных в MASTER, упал MASTER, SLAVE поднялся
- Упал SLAVE
- Записываем в MASTER:
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('Рязань', '12', '25', '4', '2019-09-09 09:00');
- Упал MASTER
- Поднялся SLAVE
- Проверяем на SLAVE (естественно новой строки с "Рязань" тут нет, так как во время того как поднялся SLAVE, MASTER лежал):
SELECT * FROM weather;
... city | temp_lo | temp_hi | prcp | date --------+---------+---------+------+------------ Тамбов | 12 | 25 | 4 | 2019-09-09 (2 rows) ...
- Поднялся MASTER
- Проверяем на SLAVE (подтянулась новая строка "Рязань"):
SELECT * FROM weather;
... city | temp_lo | temp_hi | prcp | date --------+---------+---------+------+------------ Тамбов | 12 | 25 | 4 | 2019-09-09 Рязань | 12 | 25 | 4 | 2019-09-09 (2 rows) ...
- После поднятия MASTER данные с MASTER дописались в SLAVE
Сделаем из Slave-1-сервера Master-сервер при падении основного Master, подключим к Slave-1-серверу (новый Мастер) новый Slave-2-сервер
Настройка SLAVE-1 (новый Мастер)
Сэмулируем выход из строя MASTER сервера - погасим его
Делаем из Slave-сервера Master-сервер
su postgres
bash-4.4$ /usr/pgsql-13/bin/pg_ctl promote -D /var/lib/pgsql/13/data
... waiting for server to promote.... done server promoted ...
Ctrl+D
Проверка
Вставим запись в БД Slave-сервера
psql -U postgres
postgres=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('Тамбов', '12', '25', '4', '2019-09-09 09:00');
... INSERT 0 1 ...
Проверим что запись создалась
postgres=# SELECT * FROM weather;
... city | temp_lo | temp_hi | prcp | date --------+---------+---------+------+------------ Тамбов | 12 | 25 | 4 | 2019-09-09 Тамбов | 12 | 25 | 4 | 2019-09-09 (2 rows) ...
Готово. Теперь SLAVE-1 стал Мастером.
Настройка SLAVE-2
Теперь поднимаем SLAVE-2, Мастером для которого будет бывший SLAVE-1
Идём на http://yum.postgresql.org/repopackages.php, ищем самую последнюю версию.
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf -y update
sudo dnf -y install postgresql13 postgresql13-server
Становимся пользователем postgres:
su postgres
Под этим пользователем переливаем данные с мастера:
cd /var/lib/pgsql/13/
Следующая команда спросит пароль пользователя postgres, который мы меняли при настройке мастера.
Используйте -c fast, чтобы синкнуться как можно быстрее, или -c spread, чтобы минимизировать нагрузку.
Еще есть флаг -r, позволяющий ограничить скорость передачи данных (см man).
В этой команде есть важный параметр -R. Он означает, что PostgreSQL-сервер также создаст пустой файл standby.signal. Несмотря на то, что файл пустой, само наличие этого файла означает, что этот сервер — реплика.
Файл standby.signal появился только в PostgreSQL версии 12. Раньше вместо него создавался файл recovery.conf, в котором хранились настройки для подключения к Master-серверу.
pg_basebackup -P -R -X stream -c fast -h 192.168.199.178 -U postgres -D ./data
Следующие файлы правим аналогично мастеру
В postgresql.conf нужно указать IP нашего SLAVE-2 сервера
nano /var/lib/pgsql/13/data/pg_hba.conf
... # Аналогичные настройки как и на мастере ...
nano /var/lib/pgsql/13/data/postgresql.conf
... # какие адреса слушать, замените на IP этого SLAVE сервера listen_addresses = '192.168.199.183' ...
Запускаем PostgreSQL
systemctl restart postgresql-13
Проверка
На SLAVE-1 (теперь Мастер)
psql -U postgres
postgres=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('Тула', '12', '25', '4', '2019-09-09 09:00');
... INSERT 0 1 ...
postgres=# SELECT * FROM weather;
... city | temp_lo | temp_hi | prcp | date --------+---------+---------+------+------------ Тула | 12 | 25 | 4 | 2019-09-09 (2 rows) ...
На SLAVE-2 (теперь SLAVE-2 для него - Мастер)
psql -U postgres
postgres=# SELECT * FROM weather;
... city | temp_lo | temp_hi | prcp | date --------+---------+---------+------+------------ Тула | 12 | 25 | 4 | 2019-09-09 (2 rows) ...
Видим что данные, записанные на SLAVE-1 (новый Мастер) появились на SLAVE-2.
Новая репликация работает.