postgresql流式复制(Streaming Replica

1、创建流复制用户

psql -U postgres

//CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 
CREATE USER <user_name> REPLICATION LOGIN ENCRYPTED PASSWORD ’<password>’;

2、配置PostgreSQL(postgresql.conf)相关属性

# 相关值 replica|minimal|logical
wal_level = replica
# 主备机不同步时，re_wind恢复结点
wal_log_hints = on
# 设置备机最大并发连接数.
max_wal_senders = 3

wal_keep_segments = 8
#备份角色，主服务器自动忽略该属性
hot_standby = on

3、在pg_hba.conf文件中添加复制(replication)节点服务，允许服务器之间进行数据复制:

//host replication replicator 192.168.3.5/32 md5
# TYPE    DATABASE    USER    ADDRESS    METHOD
host    replication    replicator    IPaddress(CIDR)    md5

重启postgresql服务让配置生效。
4、配置备节点

//pg_basebackup -D C:/pgsql/data  -h 192.168.3.4 -X stream -c fast -U replicator -W
pg_basebackup -D <data_directory>  -h <master_host> -X stream -c fast -U replicator -W

chmod 700 -R /postgresql/data

5、启动备服务节点服务

pg_ctl start -D <data_directory>

6、配置windows启停脚本"pg_ctl.bat":

@echo off
set/p option=请输入你的选择(1：开启数据库;2：关闭数据库):
if "%option%"=="1" "C:/pgsql/bin/pg_ctl" -D "C:/pgsql/data" -l C:/pgsql/logfile start
if "%option%"=="2" "C:/pgsql/bin/pg_ctl" -D "C:/pgsql/data" -l C:/pgsql/logfile stop

7、备机在PGDATA中创建recovery.conf，并加入以下内容：

standby_mode          = 'on'
primary_conninfo      = 'host=192.168.3.4 port=5432 user=replicator password=<password>'