1.1.1 主节点配置
cd $PGDATA
编辑vi pg_hba.conf文件,添加如下内容:
host replication replica 127.0.0.1/32 md5
host replication replica 0.0.0.0/0 md5
编辑vi postgresql.conf文件,添加如下内容:
wal_level = hot_standby
archive_mode = on
archive_command = '/bin/date'
max_wal_senders = 10
wal_keep_segments = 256
synchronous_standby_names = '*'
hot_standby = on
max_standby_archive_delay = 300s
max_standby_streaming_delay = 300s
wal_receiver_status_interval = 1s
hot_standby_feedback = on
wal_receiver_timeout = 60s
synchronous_commit = off #当从节点宕机时off模式 主节点不受影响,on模式主节点将不能提交事务。
su - pg93 切换用户
psql -h 127.0.0.1 -p 1999 -U postgrespostgres 登录数据库
create role replica login replicationencrypted password 'replica'; 创建用户
配置密码文件
cd /home/pg93
vi .pgpass
主数据库IP:主数据库端口号:replication:replica:replica
chmod 400 .pgpass
配置主从切换文件
cd $PGDATA
cp /opt/pgsql932/share/recovery.conf.samplerecovery.done
vi recovery.conf 添加如下内容
standby_mode = 'on'
recovery_target_timeline = 'latest'
primary_conninfo = 'host=从节点IP port=主节点端口user=replica password=replica'
trigger_file = '/tmp/trigger_file'
1.2 备节点配置
su - pg93
pg_ctl stop -m fast 关闭备数据库
cd /home/pg93
配置密码文件
vi .pgpass
主节点IP:主节点端口:replication:replica:replica
chmod 400 .pgpass
备节点和主节点同步。
su - pg93
cd ~
rm -rf pg_root
pg_basebackup -F p -D $PGDATA -h 主节点IP -p 主节点端口 -U replica
配置备库配置文件
cd $PGDATA
cp /opt/pgsql932/share/recovery.conf.samplerecovery.conf
vi recovery.conf 添加如下内容
standby_mode = 'on'
recovery_target_timeline = 'latest'
primary_conninfo = 'host=主节点IP port=主节点端口user=replicapassword=replica'
trigger_file = '/tmp/trigger_file'
启动备数据库 pg_ctl start
1.2.1 检验双机热备配置情况
su - pg93
psql -h 主数据库IP -p 主数据库端口 -U postgrespostgres
select * from pg_stat_replication; 查询主从节点状态