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

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

    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 db11;
    CREATE DATABASE

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

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

    |    psql -h localhost -p 5433 -U postgres

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

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

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

Остановка реплики
~~~~~~~~~~~~~~~~~


    |    => \q

postgres2> pg_ctl stop -w 
waiting for server to shut down.... done
server stopped

    => select pg_current_xlog_location() as start_lsn \gset

    => insert into replica_test(t) select repeat('a',1000) from generate_series(1,100000);
    INSERT 0 100000

    => select pg_current_xlog_location() as end_lsn \gset

Вставили в журнал:

    => select pg_size_pretty(:'end_lsn'::pg_lsn - :'start_lsn'::pg_lsn);
     pg_size_pretty 
    ----------------
     101 MB
    (1 row)
    


Повторный запуск реплики
~~~~~~~~~~~~~~~~~~~~~~~~


postgres2> pg_ctl start -l /home/postgres2/logfile 
server starting

Отслеживаем задержку репликации:

    => select pg_size_pretty(pg_current_xlog_location() - replay_location) from pg_stat_replication;
     pg_size_pretty 
    ----------------
    (0 rows)
    

sleep 1 

    => select pg_size_pretty(pg_current_xlog_location() - replay_location) from pg_stat_replication;
     pg_size_pretty 
    ----------------
     24 MB
    (1 row)
    

sleep 1 

    => select pg_size_pretty(pg_current_xlog_location() - replay_location) from pg_stat_replication;
     pg_size_pretty 
    ----------------
     4171 kB
    (1 row)
    

sleep 1 

    => select pg_size_pretty(pg_current_xlog_location() - replay_location) from pg_stat_replication;
     pg_size_pretty 
    ----------------
     0 bytes
    (1 row)
    

Пока реплика стартует и еще не подключилась к мастеру, pg_stat_replication показывает 0 строк.

Восстанавливаем параметры, останавливаем реплику.

postgres2> pg_ctl stop -w 
waiting for server to shut down.... done
server stopped

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

    => alter system reset all;
    ALTER SYSTEM

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

    => \q
    |    => \q