Настройка мастера для репликации и непрерывного архивирования ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Изменяем параметры. psql -h localhost -p 5432 -U postgres => alter system set wal_level = hot_standby; ALTER SYSTEM => alter system set archive_mode = on; ALTER SYSTEM => alter system set archive_command = 'test ! -f /home/postgres/archivedir/%f && cp %p /home/postgres/archivedir/%f && chmod g+r /home/postgres/archivedir/%f'; ALTER SYSTEM => alter system set max_standby_archive_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 archive_timeout = '10s'; ALTER SYSTEM => alter system set port = 5432; ALTER SYSTEM => alter system set hot_standby = off; ALTER SYSTEM База данных и таблица. Функция тоже пригодится - выводит количество строк с задержкой в секунду. => create database db10; CREATE DATABASE => \c db10 You are now connected to database "db10" 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 Очистим каталог /home/postgres/archivedir для сегментов WAL. postgres> rm -rf /home/postgres/archivedir postgres> mkdir /home/postgres/archivedir Файл 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 Создание базовой резервной копии ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ postgres2> bash -c "rm -rf /usr/local/pgsql2/data/*" postgres2> pg_basebackup -U postgres -p 5432 -D /usr/local/pgsql2/data NOTICE: pg_stop_backup complete, all required WAL segments have been archived Настройка реплики ~~~~~~~~~~~~~~~~~ Изменение файла 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> 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 = 'on' archive_command = 'test ! -f /home/postgres/archivedir/%f && cp %p /home/postgres/archivedir/%f && chmod g+r /home/postgres/archivedir/%f' max_standby_archive_delay = '10s' max_replication_slots = '5' max_wal_senders = '5' archive_timeout = '10s' port = 5433 hot_standby = on Файл recovery.conf: postgres2> bash -c "echo standby_mode = on >> /usr/local/pgsql2/data/recovery.conf" postgres2> bash -c "echo restore_command = \\'\'cp /home/postgres/archivedir/%f %p\\'\' >> /usr/local/pgsql2/data/recovery.conf" postgres2> cat /usr/local/pgsql2/data/recovery.conf standby_mode = on restore_command = 'cp /home/postgres/archivedir/%f %p' Запуск реплики ~~~~~~~~~~~~~~ postgres2> pg_ctl start -w -l /home/postgres2/logfile waiting for server to start..... done server started | psql -h localhost -p 5433 -U postgres | => \c db10 | You are now connected to database "db10" as user "postgres". Проверка задержки репликации ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ => \c db10 You are now connected to database "db10" as user "postgres". => insert into replica_test(t) values ('Hi!'); INSERT 0 1 | => 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) | Задержка должна быть примерно равна archive_timeout (10 секунд). Восстанавливаем параметры, останавливаем реплику. => 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 postgres2> pg_ctl stop -w waiting for server to shut down.... done server stopped => \q | => \q