Настройка Streaming replication в Postgresql

1. Настроить ключи ssh для пользователя репликации

2. Настроить pg_hba.conf на master и slave

host  replication  all  192.168.0.1/32  trust

host  replication  all  192.168.0.2/32  trust

3. На master:

vi postgresql.conf

wal_level = hot_standby

max_wal_senders = 5

wal_keep_segments = 32

archive_mode    = on

   archive_command = 'cp %p /path_to/archive/%f'

4. Клонировать master на slave

psql -c "SELECT pg_start_backup('label', true)"

rsync -C -a --delete -e ssh --exclude postgresql.conf --exclude postmaster.pid \

--exclude postmaster.opts --exclude pg_log --exclude pg_xlog \

--exclude recovery.conf master_db_datadir/ slavedb_host:slave_db_datadir/

psql -c "SELECT pg_stop_backup()"

или

pg_basebackup -R -D /srv/pgsql/standby -h 192.168.0.1 -p 5432

5. На slave:

vi postgresql.conf

hot_standby = on

vi datadir/recovery.conf

standby_mode          = 'on'

primary_conninfo      = 'host=192.168.0.1 port=5432 user=postgres'

trigger_file = '/path_to/trigger' # не обязательно

restore_command = 'scp masterdb_host:/path_to/archive/%f "%p"'

6. Запустить slave

7. Мониторинг

SELECT pg_current_xlog_location()  #master

select pg_last_xlog_receive_location() #slave

select pg_last_xlog_replay_location() #slave

SELECT * from pg_stat_replication;

на slave:

SELECT * from pg_stat_database_conflicts;

[master] $ ps -ef | grep sender

[slave] $ ps -ef | grep receiver

8. Failover

on slave:

touch /path_to/trigger

        or

pg_ctl promote

9. Воссоздать Primary