Postgres 流复制配置

主要 - 现有测试服务器：
=======
主机名：nijam-1
IP 地址：53.99.198.11

次要 - 新测试服务器
=========
主机名：nijam-2
IP 地址：53.99.198.12

 

步骤 1. 停止从属服务器

 /optt/10.5/bin/pg_ctl -D  /DATA stop 

第 2 步，将从属数据目录移动到备份位置：

mv /DATA/* /tmp/backup

步骤 3. 在主 postgresql.conf 和 pg_hba.conf 文件上更改以下参数。

listen_addresses = 'localhost,53.99.198.11'  
wal_level = replica                     # minimal, replica, or logical
archive_mode = on             #启用归档
archive_command =  'cp %p /ARCHIVES/%f'   #归档命令
max_wal_senders = 10      
wal_keep_segments = 25

On pg_hba.conf:

host    replication     postgres        53.99.198.12/24          trust #信任主机
host    replication     postgres        53.99.198.11/24          trust

步骤4.重新启动主服务器：

/optt/10.5/bin/pg_ctl -D  /DATA restart   

第五步、进行一致性备份：

psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace /DATA/* postgres@53.99.198.12:/DATA/ #数据传输至从库
psql -c "select pg_stop_backup();"      

步骤 5. 在从属服务器上更改postgresql.conf 上的以下参数：

listen_addresses = 'localhost,53.99.198.12'  #监听服务器
wal_level = replica                     # minimal, replica, or logical
archive_mode = on 
archive_command = '/bin/cp -av %p /ARCHIVES/%f'
max_wal_senders = 10 
wal_keep_segments = 25
hot_standby = on        #热备

on pg_hba.conf:

host    replication     postgres        53.99.198.12/24          trust
host    replication     postgres        53.99.198.11/24          trust

On recovery.conf :

standby_mode = 'on'      # to enable the standby (read-only) mode.     
primary_conninfo = 'host=53.99.198.11 port=5444 user=postgres'  
                         # to specify a connection info to the master node.
trigger_file = '/tmp/pg_failover_trigger'
                         # to specify a trigger file to recognize a fail over. 指定一个触发器文件来识别故障转移。
restore_command = 'cp /ARCHIVES/%f "%p"'
archive_cleanup_command = '/optt/10.5/bin/pg_archivecleanup /ARCHIVES/%r'  #归档清除

步骤6.重新启动从服务器：

/optt/10.5/bin/pg_ctl -D  /DATA start

如果您遇到任何错误，例如存档丢失或 wal 日志序列丢失...请获取丢失的存档/wall 备份并将其移动到从服务器，否则请对 postgres 主服务器进行全新备份。

以下脚本将帮助您获取存档和 xlog 备份

psql -c "select pg_start_backup('initial_backup');"   #打个开始备份的标签
rsync -cva --inplace --exclude=pg_hba.conf --exclude=postgresql.conf --exclude=recovery.conf /DATA/* postgres@53.99.198.12:/DATA      #同步数据文件到从库
rsync -cva /DATA/xlog/* postgres@53.99.198.12:/DATA/xlog/
rsync -cva /ARCHIVES/* postgres@53.99.198.12:/ARCHIVES
psql -c "select pg_stop_backup();"    结束备份

步骤 7. 监控流复制：

在 MASTER 上：

1. 创建一些虚拟表，检查从属服务器是否正确复制。

postgres=# create table t(id int);
CREATE TABLE

-- 检查主服务器是否处于恢复模式，

 postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

主服务器不会处于恢复模式，只有从服务器才会处于恢复模式。

2.. 使用 pg_stat_replication 视图

postgres=# select client_addr,client_hostname,client_port,state,sent_lsn,write_lsn,flush_lsn,replay_lsn,write_lag,replay_lag,flush_lag,sync_state from pg_stat_replication;
 client_addr | client_hostname | client_port |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | replay_lag | flush_lag | sync_state
-------------+-----------------+-------------+-----------+-----------+-----------+-----------+------------+-----------+------------+-----------+------------
 192.168.2.3 |                 |       60000 | streaming | 0/8017A28 | 0/8017A28 | 0/8017A28 | 0/8017A28  |           |            |           | async
(1 row)

3.使用linux命令检查wal sender进程是否启动：

[postgres@mster ~]$ ps -ef|grep postgres
root      82472  82437  0 08:34 pts/1    00:00:00 su postgres
postgres  82473  82472  0 08:34 pts/1    00:00:00 bash
root      94549  94494  0 20:36 pts/3    00:00:00 su postgres
postgres  94550  94549  0 20:36 pts/3    00:00:00 bash
postgres  94582      1  0 20:36 pts/3    00:00:00 /optt/10.5/bin/postgres -D /DATA/
postgres  94584  94582  0 20:36 ?        00:00:00 postgres: logger process
postgres  94586  94582  0 20:36 ?        00:00:00 postgres: checkpointer process
postgres  94587  94582  0 20:36 ?        00:00:00 postgres: writer process
postgres  94588  94582  0 20:36 ?        00:00:00 postgres: wal writer process
postgres  94589  94582  0 20:36 ?        00:00:00 postgres: autovacuum launcher process
postgres  94591  94582  0 20:36 ?        00:00:00 postgres: stats collector process
postgres  94592  94582  0 20:36 ?        00:00:00 postgres: bgworker: logical replication launcher
postgres  94741  94582  0 20:43 ?        00:00:00 postgres: wal sender process postgres 192.168.2.3(60000) streaming 0/8017B08
postgres  95178  94550  0 21:08 pts/3    00:00:00 ps -ef
postgres  95179  94550  0 21:08 pts/3    00:00:00 grep --color=auto postgres

-- 监控当前的墙壁序列。

$ ps -ef|grep sender
postgres   7585   3383  0 15:59 ?        00:00:00 postgres: wal sender process postgres 192.168.2.2(42586) streaming 0/18017CD8
postgres   7598   6564  0 15:59 pts/2    00:00:00 grep --color=auto sender

在从属服务器上：

1.检查先前创建的表是否被复制

 

postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | qrtransaction | table | postgres
 public | t             | table | postgres
(2 rows)

yes! it is successfully replicated the table.

-- Checking server mode.

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

-- 尝试在从属服务器上加载数据，从属服务器处于只读模式，不允许加载数据。

postgres=# insert into t values(1);
ERROR:  cannot execute INSERT in a read-only transaction

2.使用 pg_stat_replication 视图：

postgres=# select status,receive_start_lsn,received_lsn,last_msg_send_time,latest_end_lsn,latest_end_time,conninfo from pg_stat_wal_receiver ;
  status   | receive_start_lsn | received_lsn |        last_msg_send_time        | latest_end_lsn |         latest_end_time          |                                                           conninfo
-----------+-------------------+--------------+----------------------------------+----------------+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 streaming | 0/6000000         | 0/8017B08    | 2018-05-26 21:26:34.577733+05:30 | 0/8017B08      | 2018-05-26 21:06:32.309548+05:30 | user=postgres password=******** dbname=replication host=192.168.2.2 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any
(1 row)

3. 使用 linux 命令，您可以监视 wal 接收器是否已启动。此 linux 命令将有助于找出 postgres 数据目录以及 postgres 实用程序路径（即 bin 路径）

[postgres@slave data]$ ps -ef|grep postgres
root       3971   3573  0 10:27 pts/0    00:00:00 su postgres
postgres   3972   3971  0 10:27 pts/0    00:00:00 bash
root      45410  45321  0 20:36 pts/1    00:00:00 su postgres
postgres  45411  45410  0 20:36 pts/1    00:00:00 bash
postgres  45606      1  0 20:43 pts/1    00:00:00 /optt/10.5/bin/postgres -D /DATA/
postgres  45607  45606  0 20:43 ?        00:00:00 postgres: logger process
postgres  45608  45606  0 20:43 ?        00:00:00 postgres: startup process   recovering 000000010000000000000008
postgres  45610  45606  0 20:43 ?        00:00:00 postgres: checkpointer process
postgres  45611  45606  0 20:43 ?        00:00:00 postgres: writer process
postgres  45612  45606  0 20:43 ?        00:00:06 postgres: wal receiver process   streaming 0/8017B08
postgres  45613  45606  0 20:43 ?        00:00:00 postgres: stats collector process
postgres  45995  45411  0 21:16 pts/1    00:00:00 ps -ef
postgres  45996  45411  0 21:16 pts/1    00:00:00 grep --color=auto postgres

4.此命令将有助于获取多少个 wal 段 postgres 接收器

[postgres@slave data]$ ps -ef|grep receiver;
postgres  45612  45606  0 20:43 ?        00:00:06 postgres: wal receiver process   streaming 0/8017B08
postgres  46018  45411  0 21:18 pts/1    00:00:00 grep --color=auto receiver

5. 如果从属服务器处于热备用模式，则可以使用以下查询了解在从属服务器上应用事务的延迟时间（以秒为单位）：

postgres = # select now() - pg_last_xact_replay_timestamp() AS replication_delay; 
 replication_delay 
------------------- 
 00：18：35.207663 
（1 行）