postgresql11 主从流复制集群

192.168.10.251 M
192.168.20.252 S
#两台服务器都提前安装好数据库

#主节点
#创建归档路径
mkdir /data/pgsql/pg_archive
#添加两列连接配置
echo "host     all             all          192.168.10.251/32          trust   >> /data/pgsql/data/pg_hba.conf
echo "host   replication      replica       192.168.10.251/32          md5  >>/data/pgsql/data/pg_hba.conf

#测试环境删除了其他配置文件
cat  >>  /data/pgsql/data/postgresql.conf  << EOF
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
listen_addresses = '*'                  # what IP address(es) to listen on;
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
shared_buffers = 128MB                  # min 128kB
dynamic_shared_memory_type = posix      # the default is the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'                     # locale for system error message
                                        # strings
lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'                      # locale for number formatting
lc_time = 'en_US.UTF-8'                         # locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.english'
archive_mode = on  # 允许归档
archive_command = 'cp %p /data/pgsql/pg_archive/%f'  # 用该命令来归档logfile segment
wal_level = hot_standby
max_wal_senders = 32 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个wal_keep_segments = 256 ＃ 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s  #设置流复制主机发送数据的超时时间
EOF

#启动
cd /u02/app/pgsql/bin/
./pg_ctl stop -D /data/pgsql/data
./pg_ctl -D /data/pgsql/data -l /data/pgsql/log/pgsql.log start

#创建主从复制账户
/u02/app/pgsql/bin/psql -U postgres -d postgres -h 127.0.0.1 -W
CREATE ROLE replica login replication encrypted password 'replica';

#从节点
su - postgres
rm -rf /data/pgsql/data/*   #先将data目录下的数据都清空
/u02/app/pgsql/bin/pg_basebackup -h 192.168.10.251 -U replica -D /data/pgsql/data -X stream -P  # 从主拷贝数据到从节点

mkdir /data/pgsql/pg_archive
cp /data/pgsql/recovery.conf.sample /data/pgsql/data/recovery.conf
#添加同步配置
echo "standby_mode = on    # 说明该节点是从服务器" >>/data/pgsql/data/recovery.conf
echo "primary_conninfo = 'host=192.168.10.251 port=5432 user=replica password=replica'  #主服务器的信息以及连接的用户" >>/data/pgsql/data/recovery.conf
echo "recovery_target_timeline = 'latest'" >>/data/pgsql/data/recovery.conf

#从数据库配置
cat >> /data/pgsql/data/postgresql.conf <<EOF
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
listen_addresses = '*'            # what IP address(es) to listen on;
#port = 5432                # (change requires restart)
max_connections = 100            # (change requires restart)
shared_buffers = 128MB            # min 128kB
dynamic_shared_memory_type = posix    # the default is the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'            # locale for system error message
                    # strings
lc_monetary = 'en_US.UTF-8'            # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'            # locale for number formatting
lc_time = 'en_US.UTF-8'                # locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.english'
archive_mode = on  # 允许归档
archive_command = 'cp %p /data/pgsql/pg_archive/%f'  # 用该命令来归档logfile segment
wal_level = hot_standby 
max_wal_senders = 32 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个wal_keep_segments = 256 ＃ 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s  #设置流复制主机发送数据的超时时间
hot_standby = on #说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 10s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈
EOF
#启动从节点
/u02/app/pgsql/bin/pg_ctl -D /data/pgsql/data -l /data/pgsql/log/pgsql.log start

#主节点
#查看wal sender 进程
ps aux | grep postgres 
#登陆查看数据集群
postgres=# select client_addr,sync_state from pg_stat_replication;
  client_addr   | sync_state 
----------------+------------
 192.168.10.254 | async
(1 row)
#查全部看复制状态
postgres=#select * from pg_stat_replication;

#从节点
#查看wal sender 进程
ps aux | grep postgres