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 行）
