postgres=# select * from pg_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | syn
c_priority | sync_state
------+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+----
-----------+------------
6229 | 10 | postgres | walreceiver | 192.168.100.24 | | 44710 | 2017-07-03 03:34:59.061489-04 | | streaming | A/B46D9348 | A/B40AD628 | A/B40AD628 | A/B40AD628 |
0 | async
(1 row)

[postgres@test2 ~]$ pg_ctl -D data1/ start
server starting
[postgres@test2 ~]$ LOG: database system was interrupted; last known up at 2017-07-03 05:13:56 EDT
LOG: entering standby mode
LOG: redo starts at A/F90000D0
LOG: consistent recovery state reached at A/FE6B1380
LOG: started streaming WAL from primary at A/FF000000 on timeline 1

[postgres@test2 ~]$ psql
FATAL: the database system is starting up
psql: FATAL: the database system is starting up

Потоковая репликация

Настройка мастера

1. yum install gcc, readline-devel, zlib-devel
2. Отключаем firewall:
1. # service iptables save
2. # service iptables stop
3. # chkconfig iptables off
3. скачиваем, распаковываем https://www.postgresql.org/ftp/source/ (tar xzf postgresql*)
4. из папки распакованного архива $(#) ./configure
5. из папки распакованного архива $(#) make world (-world полный пакет)
6. из папки распакованного архива $(#) make install-world (-world полный пакет)
7. [root@1 ~]# adduser postgres
8. [root@1 ~]# passwd postgres
9. mkdir /usr/local/pgsql/data (директория для инициализации версии postgresql, может быть любая, можно создавать под пользователем postgres)
10. Переменные среды:
1. postgres> bash -c "echo 'export PATH=/usr/local/pgsql/bin:/usr/local/pgsql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin' >> ~/.profile"
2. postgres> bash -c "echo 'export PGDATA=/usr/local/pgsql/data' >> ~/.profile" (путь из пункта mkdir)
3. . /home/postgres/.profile (применение переменных)
11. [postgres@1 ~]$ initdb -k (инициализация бд только на мастере)
12. [postgres@1 ~]$ vi /usr/local/pgsql/data/postgresql.conf
1. listen_addres (*)
2. wal_level = replica
3. max_wal_senders = 3
4. wal_keep_segments = 32
5. hot_standby = on
13. [postgres@1 ~]$ vi /usr/local/pgsql/data/pg_hba.conf
1. # "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all postgres 192.168.100.0/24 trust

# IPv6 local connections:
#host all all ::1/128 trust
# 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
host replication postgres 192.168.100.0/24 trust
14. [postgres@1 ~]$ pg_ctl -D /usr/local/pgsql/data start -w -l /usr/local/pgsql/data/logfile (restart)
1. server starting



Настройка slave (с 1 по 10 пункты полностью совпадают):



1. yum install gcc, readline-devel, zlib-devel
2. Отключаем firewall:
1. # service iptables save
2. # service iptables stop
3. # chkconfig iptables off
3. скачиваем, распаковываем https://www.postgresql.org/ftp/source/ (tar xzf postgresql*)
4. из папки распакованного архива $(#) ./configure
5. из папки распакованного архива $(#) make world (-world полный пакет)
6. из папки распакованного архива $(#) make install-world (-world полный пакет)
7. [root@1 ~]# adduser postgres
8. [root@1 ~]# passwd postgres
9. mkdir /usr/local/pgsql/data (директория для инициализации версии postgresql, может быть любая, можно создавать под пользователем postgres)
10. Переменные среды:
1. postgres> bash -c "echo 'export PATH=/usr/local/pgsql/bin:/usr/local/pgsql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin' >> ~/.profile"
2. postgres> bash -c "echo 'export PGDATA=/usr/local/pgsql/data' >> ~/.profile" (путь из пункта mkdir)
3. . /home/postgres/.profile (применение переменных)
11. [postgres@1 ~]$ pg_basebackup -P -R -X stream -c fast -h 192.168.100.33 -U postgres -D /usr/local/pgsql/data/ (-h мастер)
12. [postgres@1 ~]$ vi /usr/local/pgsql/data/recovery.conf
1. standby_mode = 'on'
primary_conninfo = 'user=postgres host=192.168.100.33 port=5432 sslmode=disable sslcompression=1'
trigger_file = '/usr/local/pgsql/data/failover' #добаляем эту строку
13. [postgres@1 ~]$ pg_ctl -D /usr/local/pgsql/data start -w -l /usr/local/pgsql/data/logfile (restart)
1. server starting

Проверка:

на мастере :

[postgres@1 selinux]$ psql
postgres=# select * from pg_stat_replication ;

pid | usesysid | usename | application_name | client_addr | client_hostnam
e | client_port | backend_start | backend_xmin | state | sen
t_location | write_location | flush_location | replay_location | sync_priority |
sync_state
-------+----------+----------+------------------+---------------+---------------
--+-------------+-------------------------------+--------------+-----------+----
-----------+----------------+----------------+-----------------+---------------+
------------
30897 | 10 | postgres | walreceiver | 192.168.100.7 |
| 45027 | 2017-07-04 07:43:07.205399-04 | | streaming | 0/3
000728 | 0/3000728 | 0/3000728 | 0/3000728 | 0 |
async
(1 row)

на slave:

[postgres@1 selinux]$ psql
postgres=# select pg_is_in_recovery();

pg_is_in_recovery
-------------------
t
(1 row)