Настройка мастера для потоковой репликации
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Изменяем параметры.

    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> 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'


Запуск первой реплики и проверка
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


postgres2> pg_ctl start -w -l /home/postgres2/logfile 
waiting for server to start..... done
server started

    => create database db13;
    CREATE DATABASE

    => \c db13
    You are now connected to database "db13" as user "postgres".

    => create table replica_test(t text);
    CREATE TABLE

    => create function test() returns integer as $$ select pg_sleep(1); select count(*)::integer from replica_test;$$ language sql;
    CREATE FUNCTION

    |    psql -h localhost -p 5433 -U postgres

    |    => \c db13
    |    You are now connected to database "db13" as user "postgres".

    |    => select * from replica_test;
    |     t 
    |    ---
    |    (0 rows)
    |    

Репликация работает.

Создаем слот репликации (реплика допускает эту операцию):

    |    => select pg_create_physical_replication_slot('slot_a');
    |     pg_create_physical_replication_slot 
    |    -------------------------------------
    |     (slot_a,)
    |    (1 row)
    |    


Создание и настройка второй реплики
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

postgres3> bash -c "rm -rf /usr/local/pgsql3/data/*" 
postgres3> pg_basebackup -U postgres -p 5433 -D /usr/local/pgsql3/data -X stream -R 

Изменение файла postgresql.auto.conf:

postgres3> sed -i "s/port = .*/port = 5434/" /usr/local/pgsql3/data/postgresql.auto.conf 
postgres3> sed -i "s/hot_standby = .*/hot_standby = on/" /usr/local/pgsql3/data/postgresql.auto.conf 
postgres3> sed -i "s/hot_standby_feedback = .*/hot_standby_feedback = on/" /usr/local/pgsql3/data/postgresql.auto.conf 
postgres3> sed -i "s/wal_receiver_status_interval = .*/wal_receiver_status_interval = 1s/" /usr/local/pgsql3/data/postgresql.auto.conf 



postgres3> cat /usr/local/pgsql3/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 = 5434
hot_standby = on
hot_standby_feedback = on
wal_receiver_status_interval = 1s

Добавляем слот репликации и задержку применения в recovery.conf.

postgres3> bash -c "echo primary_slot_name = \\'\'slot_a\\'\' >> /usr/local/pgsql3/data/recovery.conf" 
postgres3> bash -c "echo recovery_min_apply_delay = \\'\'10s\\'\' >> /usr/local/pgsql3/data/recovery.conf" 

Еще один важный шаг: надо установить параметр recovery_target_timeline в значение latest.
Иначе, когда первая реплика будет повышена до мастера и увеличит номер ветви времени,
вторая реплика продолжит чтение данных прежней ветви времени - то есть по сути репликация остановится

postgres3> bash -c "echo recovery_target_timeline = \\'\'latest\\'\' >> /usr/local/pgsql3/data/recovery.conf" 



postgres3> cat /usr/local/pgsql3/data/recovery.conf 
standby_mode = 'on'
primary_conninfo = 'user=postgres port=5433 sslmode=disable sslcompression=1'
primary_slot_name = 'slot_a'
recovery_min_apply_delay = '10s'
recovery_target_timeline = 'latest'


Запуск второй реплики и проверка
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


postgres3> pg_ctl start -w -l /home/postgres3/logfile 
waiting for server to start..... done
server started

    => insert into replica_test values ('Проверка связи');
    INSERT 0 1

    ||        psql -h localhost -p 5434 -U postgres

    ||        => \c db13
    ||        You are now connected to database "db13" as user "postgres".

    ||        => select 'After '||i||' sec', test() from generate_series(1,15) as gen(i);
    ||           ?column?   | test 
    ||        --------------+------
    ||         After 1 sec  |    0
    ||         After 2 sec  |    0
    ||         After 3 sec  |    0
    ||         After 4 sec  |    0
    ||         After 5 sec  |    0
    ||         After 6 sec  |    0
    ||         After 7 sec  |    0
    ||         After 8 sec  |    0
    ||         After 9 sec  |    0
    ||         After 10 sec |    1
    ||         After 11 sec |    1
    ||         After 12 sec |    1
    ||         After 13 sec |    1
    ||         After 14 sec |    1
    ||         After 15 sec |    1
    ||        (15 rows)
    ||        

Репликация с задержкой работает.

Останов мастера и повышение первой реплики
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


    => \q

postgres> pg_ctl stop -m immediate 
waiting for server to shut down... done
server stopped
postgres2> pg_ctl -w promote 
server promoting


Проверка второй реплики
~~~~~~~~~~~~~~~~~~~~~~~


    |    => insert into replica_test values ('Проверка связи - 2');
    |    INSERT 0 1

    ||        => select 'After '||i||' sec', test() from generate_series(1,15) as gen(i);
    ||           ?column?   | test 
    ||        --------------+------
    ||         After 1 sec  |    1
    ||         After 2 sec  |    1
    ||         After 3 sec  |    1
    ||         After 4 sec  |    1
    ||         After 5 sec  |    1
    ||         After 6 sec  |    1
    ||         After 7 sec  |    1
    ||         After 8 sec  |    1
    ||         After 9 sec  |    1
    ||         After 10 sec |    2
    ||         After 11 sec |    2
    ||         After 12 sec |    2
    ||         After 13 sec |    2
    ||         After 14 sec |    2
    ||         After 15 sec |    2
    ||        (15 rows)
    ||        

Репликация продолжает работать.


Возвращаем все обратно: останавливаем реплики, запускаем мастер.

    |    => \q

postgres2> pg_ctl stop -D /usr/local/pgsql2/data 
waiting for server to shut down.... done
server stopped

    ||        => \q

postgres3> pg_ctl stop -D /usr/local/pgsql3/data 
waiting for server to shut down.... done
server stopped
postgres> pg_ctl -w start -l /home/postgres/logfile 
waiting for server to start..... done
server started
    psql -h localhost -p 5432 -U postgres

    => select pg_drop_replication_slot('slot_a');
     pg_drop_replication_slot 
    --------------------------
     
    (1 row)
    

    => 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