Настройка мастера для потоковой репликации ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Изменяем параметры. psql -h localhost -p 5432 -U postgres => alter system set wal_level = hot_standby; ALTER SYSTEM => alter system set archive_mode = off; ALTER SYSTEM => alter system set max_standby_streaming_delay = '10s'; ALTER SYSTEM => alter system set max_replication_slots = 5; ALTER SYSTEM => alter system set max_wal_senders = 5; ALTER SYSTEM => alter system set port = 5432; ALTER SYSTEM => alter system set hot_standby = off; ALTER SYSTEM => alter system set hot_standby_feedback = off; ALTER SYSTEM => alter system set wal_receiver_status_interval = 0; ALTER SYSTEM Файл pg_hba.conf должен содержать строку для базы данных replication: postgres> sed -i s/#\(\w\+\s\+replication\)/\1/ /usr/local/pgsql/data/pg_hba.conf postgres> tail -n 5 /usr/local/pgsql/data/pg_hba.conf # Allow replication connections from localhost, by a user with the # replication privilege. local replication postgres trust host replication postgres 127.0.0.1/32 trust host replication postgres ::1/128 trust Перезапустим сервер. => \q postgres> pg_ctl restart -w -l /home/postgres/logfile waiting for server to shut down.... done server stopped waiting for server to start.... done server started Слот репликации. psql -h localhost -p 5432 -U postgres => select pg_create_physical_replication_slot('slot_a'); pg_create_physical_replication_slot ------------------------------------- (slot_a,) (1 row) Создание базовой резервной копии ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ postgres2> bash -c "rm -rf /usr/local/pgsql2/data/*" postgres2> pg_basebackup -U postgres -p 5432 -D /usr/local/pgsql2/data -X stream -R Настройка реплики ~~~~~~~~~~~~~~~~~ Изменение файла postgresql.auto.conf: postgres2> sed -i "s/port = .*/port = 5433/" /usr/local/pgsql2/data/postgresql.auto.conf postgres2> sed -i "s/hot_standby = .*/hot_standby = on/" /usr/local/pgsql2/data/postgresql.auto.conf postgres2> sed -i "s/hot_standby_feedback = .*/hot_standby_feedback = on/" /usr/local/pgsql2/data/postgresql.auto.conf postgres2> sed -i "s/wal_receiver_status_interval = .*/wal_receiver_status_interval = 1s/" /usr/local/pgsql2/data/postgresql.auto.conf postgres2> cat /usr/local/pgsql2/data/postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by ALTER SYSTEM command. wal_level = 'hot_standby' archive_mode = 'off' max_standby_streaming_delay = '10s' max_replication_slots = '5' max_wal_senders = '5' port = 5433 hot_standby = on hot_standby_feedback = on wal_receiver_status_interval = 1s Добавляем слот репликации и триггерный файл в recovery.conf. postgres2> bash -c "echo primary_slot_name = \\'\'slot_a\\'\' >> /usr/local/pgsql2/data/recovery.conf" postgres2> bash -c "echo trigger_file = \\'\'promote_me\\'\' >> /usr/local/pgsql2/data/recovery.conf" postgres2> cat /usr/local/pgsql2/data/recovery.conf standby_mode = 'on' primary_conninfo = 'user=postgres port=5432 sslmode=disable sslcompression=1' primary_slot_name = 'slot_a' trigger_file = 'promote_me' Запуск реплики и проверка ~~~~~~~~~~~~~~~~~~~~~~~~~ postgres2> pg_ctl start -w -l /home/postgres2/logfile waiting for server to start..... done server started => create database db12; CREATE DATABASE => \c db12 You are now connected to database "db12" as user "postgres". => create table replica_test(t text); CREATE TABLE | psql -h localhost -p 5433 -U postgres | => \c db12 | You are now connected to database "db12" as user "postgres". | => select * from replica_test; | t | --- | (0 rows) | Репликация работает. Сбой мастера и повышение реплики ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ => \q postgres> pg_ctl stop -m immediate waiting for server to shut down... done server stopped postgres2> touch /usr/local/pgsql2/data/promote_me | => select pg_is_in_recovery(); | pg_is_in_recovery | ------------------- | f | (1 row) | Бывшая реплика стала новым мастером. Слот репликации на новом мастере: | => select pg_create_physical_replication_slot('slot_a'); | pg_create_physical_replication_slot | ------------------------------------- | (slot_a,) | (1 row) | Восстановление мастера из резервной копии ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ postgres> bash -c "rm -rf /usr/local/pgsql/data/*" postgres> pg_basebackup -U postgres -p 5433 -D /usr/local/pgsql/data -X stream -R Изменение файла postgresql.auto.conf: postgres> sed -i "s/port = .*/port = 5432/" /usr/local/pgsql/data/postgresql.auto.conf postgres> sed -i "s/hot_standby = .*/hot_standby = on/" /usr/local/pgsql/data/postgresql.auto.conf postgres> sed -i "s/hot_standby_feedback = .*/hot_standby_feedback = on/" /usr/local/pgsql/data/postgresql.auto.conf postgres> sed -i "s/wal_receiver_status_interval = .*/wal_receiver_status_interval = 1s/" /usr/local/pgsql/data/postgresql.auto.conf postgres> cat /usr/local/pgsql/data/postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by ALTER SYSTEM command. wal_level = 'hot_standby' archive_mode = 'off' max_standby_streaming_delay = '10s' max_replication_slots = '5' max_wal_senders = '5' port = 5432 hot_standby = on hot_standby_feedback = on wal_receiver_status_interval = 1s Добавляем слот репликации в recovery.conf. postgres> bash -c "echo primary_slot_name = \\'\'slot_a\\'\' >> /usr/local/pgsql/data/recovery.conf" postgres> cat /usr/local/pgsql/data/recovery.conf standby_mode = 'on' primary_conninfo = 'user=postgres port=5433 sslmode=disable sslcompression=1' primary_slot_name = 'slot_a' Запуск новой реплики и проверка ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ postgres> pg_ctl start -w -l /home/postgres/logfile waiting for server to start..... done server started Слот репликации инициализировался и используется: | => \x | Expanded display is on. | => select * from pg_replication_slots; | -[ RECORD 1 ]+----------- | slot_name | slot_a | plugin | | slot_type | physical | datoid | | database | | active | t | active_pid | 24001 | xmin | 722023 | catalog_xmin | | restart_lsn | 4/13000000 | Проверим передачу данных. | => insert into replica_test values ('Привет!'); | INSERT 0 1 psql -h localhost -p 5432 -U postgres => \c db12 You are now connected to database "db12" as user "postgres". => select * from replica_test; t --------- Привет! (1 row) Повышаем старый мастер, восстанавливаем параметры, останавливаем старую реплику. postgres2> pg_ctl stop -D /usr/local/pgsql2/data waiting for server to shut down.... done server stopped | => \q postgres> pg_ctl -w promote server promoting => alter system reset all; ALTER SYSTEM => \q postgres> pg_ctl restart -w -l /home/postgres/logfile waiting for server to shut down.... done server stopped waiting for server to start.... done server started