一.异步流复制
1.创建用于流复制的角色(在主备节点)
# 创建用于流复制的角色
[root@cdhslave01 pgdata]# su - postgres
[postgres@cdhslave01 ~]$ psql
postgres=#create role replica login replication encrypted password 'replica';在主库
2.修改pg_hba.conf
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 password
#host all postgres 192.168.108.0/22 trust
#host all postgres 192.168.110.0/22 trust
host all replica 192.168.110.0/22 trust
host all replica 192.168.108.0/22 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
#host replication postgres 192.168.108.0/22 md5
#host replication postgres 192.168.110.0/22 md5
host replication replica 192.168.110.0/22 md5
host replication replica 192.168.108.0/22 md5
3.修改postgresql.conf
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/pg_archive/%f'
wal_log_hints = on
full_page_writes = on
hot_standby = on
# 用于同步复制流
#synchronous_standby_names = 'standby01,standby02'
4.创建pg_archive目录
mkdir -p /var/lib/pgsql/pg_archive
chown -R postgres:postgres /var/lib/pgsql/pg_archive5.启动
[root@cdhmaster01 pgdata]# su - postgres
[postgres@cdhmaster01 ~]$ pg_ctl start -D $PGDATA
[postgres@cdhmaster01 pgdata]$ pg_controldata $PGDATA
pg_control version number: 1002
Catalog version number: 201707211
Database system identifier: 6719647834413202808
Database cluster state: in production
从库
[postgres@cdhmaster01 ~]$ cd /home
# 全量备份
[root@cdhmaster01 home]# pg_basebackup -h 192.168.108.183 -U replica -F p -Xs -P -R -D ./backup -l test20190802
Password:
58354/58354 kB (100%), 1/1 tablespace
[postgres@cdhmaster01 home]$ ls
backup data postgres
[root@cdhmaster01 home]# chown -R postgres:postgres ./backup
[root@cdhmaster01 home]# cd backup/
[root@cdhmaster01 backup]# vi recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=replica password=replica host=192.168.108.183 port=5432 sslmode=disable sslcompression=1 target_session_attrs=any'
trigger_file = '/home/trigger'
[root@cdhmaster01 home]# su - postgres
[postgres@cdhmaster01 ~]$ pg_ctl start -D /home/backup/
[postgres@cdhmaster01 ~]$ pg_controldata /home/backup/
pg_control version number: 1002
Catalog version number: 201707211
Database system identifier: 6719647834413202808
Database cluster state: in archive recovery
从库提升为主库
1.主库宕机
[postgres@cdhmaster01 data]$ pg_ctl stop -D $PGDATA
2. 从库
# 提升为主库
[root@cdhmaster01 home]# su - postgres
[postgres@cdhmaster01 ~]$ pg_ctl promote -D /home/backup/
[postgres@cdhmaster01 ~]$ pg_controldata /home/backup/
pg_control version number: 1002
Catalog version number: 201707211
Database system identifier: 6719647834413202808
Database cluster state: in production
3. 主库变为从库
[postgres@cdhmaster01 ~]$ cd /home
# 全量备份
[root@cdhmaster01 home]# pg_basebackup -h 192.168.110.31 -U replica -F p -Xs -P -R -D ./backup -l test20190802
Password:
58354/58354 kB (100%), 1/1 tablespace
[postgres@cdhmaster01 home]$ ls
backup data postgres
[root@cdhmaster01 home]# chown -R postgres:postgres ./backup
[root@cdhmaster01 home]# cd backup/
[root@cdhmaster01 backup]# vi recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=replica password=replica host=192.168.110.31 port=5432 sslmode=disable sslcompression=1 target_session_attrs=any'
trigger_file = '/home/trigger'
[root@cdhmaster01 home]# su - postgres
[postgres@cdhmaster01 ~]$ pg_ctl start -D /home/backup/
[postgres@cdhmaster01 ~]$ pg_controldata /home/backup/
pg_control version number: 1002
Catalog version number: 201707211
Database system identifier: 6719647834413202808
Database cluster state: in archive recovery
查看主从情况
select * from pg_stat_replication
select pg_is_in_recovery();----------------------------------------------------------------------------------
一键配置脚本
下面是异步流复制脚本一键配置：
1. 一主一从
主:192.168.108.183
从:192.168.110.31
异步流复制之主库一键配置脚本
[root@cdhmaster01 pgslave]# cat master_config.sh
# 异步流复制主库一键配置脚本
# pg数据库目录
pgdata=/home/data/pgdata
# pg配置文件
pg_hba_file=pg_hba.conf
pg_postgresqlconf_file=postgresql.conf
# 归档目录
pg_archive_dir=/var/lib/pgsql/pg_archive
# 主从主机IP
master_ipo=192.168.108.0
slave_ipo=192.168.110.0
# 创建专用于复制的角色
su - postgres <<EOF
psql;
create role replica login replication encrypted password 'replica';
EOF
# 修改pg_hba.conf
echo "host replication replica ${master_ipo}/22 md5">>${pgdata}/${pg_hba_file}
echo "host replication replica ${slave_ipo}/22 md5">>${pgdata}/${pg_hba_file}
# 修改postgresql.conf
# csvlog配置，用于记录运行日志 这里主要用来监控主库
echo "log_destination = 'csvlog'">>${pgdata}/${pg_postgresqlconf_file}
echo "logging_collector = on">>${pgdata}/${pg_postgresqlconf_file}
echo "log_directory = 'pg_log'">>${pgdata}/${pg_postgresqlconf_file}
echo "log_statement ='mod'">>${pgdata}/${pg_postgresqlconf_file}
echo "log_filename = '%Y%m%d_postgresql_log'">>${pgdata}/${pg_postgresqlconf_file}
echo "log_rotation_age = 1d">>${pgdata}/${pg_postgresqlconf_file}
echo "log_truncate_on_rotation = off">>${pgdata}/${pg_postgresqlconf_file}
echo "log_rotation_size = 2047MB">>${pgdata}/${pg_postgresqlconf_file}
# wal日志配置,用于流复制
echo "wal_level = replica">>${pgdata}/${pg_postgresqlconf_file}
echo "max_wal_senders = 10">>${pgdata}/${pg_postgresqlconf_file}
echo "wal_keep_segments = 64">>${pgdata}/${pg_postgresqlconf_file}
echo "archive_mode = on">>${pgdata}/${pg_postgresqlconf_file}
echo "archive_command = 'cp %p /var/lib/pgsql/pg_archive/%f'">>${pgdata}/${pg_postgresqlconf_file}
echo "wal_log_hints = on">>${pgdata}/${pg_postgresqlconf_file}
echo "full_page_writes = on">>${pgdata}/${pg_postgresqlconf_file}
echo "hot_standby = on">>${pgdata}/${pg_postgresqlconf_file}
# 创建pg归档目录
mkdir -p /var/lib/pgsql/pg_archive
chown -R postgres:postgres /var/lib/pgsql/pg_archive
# 重载当前数据库(尽量不重启pg_ctl restart -D $pgdata)
su - postgres <<EOF
pg_ctl reload -D $PGDATA
EOF
异步流复制之从库一键配置脚本（兼容主库手动切换为备库脚本）
[root@cdhmaster01 pgslave]# cat pgslave_config.sh
# 当前正常运行的主库的IP
host=192.168.110.31
# 备份别名
bak_label=test20190802
# 用于复制流的pg用户
replica_role=replica
# 从库的PG数据库目录
pgdata=/home/backup
# trigger文件目录
trigger_dir=/home/trigger
# 提示输入密码的时候输入:replica
pg_basebackup -h ${host} -U ${replica_role} -F p -Xs -P -R -D ${pgdata} -l ${bak_label}
# 为目录授权
chown -R postgres:postgres ${pgdata}
# 配置recovery.conf
if [ -f ${pgdata}/recovery.done ];then
mv -f ${pgdata}/recovery.done ${pgdata}/recovery.conf
sed -i "/^primary_conninfo/d" ${pgdata}/recovery.conf
echo "primary_conninfo='user=replica password=replica host=${host} port=5432 sslmode=disable sslcompression=1 target_session_attrs=any'">>${trigger_dir}/recovery.conf
fi
# 增加触发器配置
echo "trigger_file = '${trigger_dir}'">>${pgdata}/recovery.conf
# 启动从数据库
su - postgres <<EOF
pg_ctl start -D ${pgdata}
EOF
异步流复制之从库自动切换为主库 脚本
[root@cdhmaster01 pgslave]# vi checkMasterRun.sh
#!/bin/bash
# 检测主库是否正常运行，如果宕机了，则立即启用当前备库。
# 指定crontab 定时检测即可
time_today=`date -d "today" +"%Y%m%d"`
current_time=`date -d "today" +"%Y-%m-%d %H:%M:%S"`
# pg数据库日志
pg_logdir=/home/backup/pg_log
pg_logname=${time_today}_postgresql_log.csv
# pg数据目录
pgdata=/home/backup
# 本脚本执行日志
log_dir=/home/pgslave/checklog
log_file_name=${time_today}.log
# 创建日志目录
if [ ! -d ${log_dir} ]; then
mkdir -p ${log_dir}
fi
echo "">> ${log_dir}/${log_file_name}
echo "---------------------start--------------------------">> ${log_dir}/${log_file_name}
echo "本次执行开始时间:${current_time}" >> ${log_dir}/${log_file_name}
# 获取本机IP
local_ip=`hostname -i|awk '{print $1}'`
echo "本机IP:${local_ip}">> ${log_dir}/${log_file_name}
# 获取备库日志中主库挂掉的日志信息
errmsg=`cat ${pg_logdir}/${pg_logname}|grep 'could not connect to the primary server'`
trigger_file=/home/trigger
if [ "$errmsg" != "" ];then
echo "PG主库挂了,自动开始切换到备库,请等待...！">> ${log_dir}/${log_file_name}
touch $trigger_file
sleep 10
recovery_comlete=`cat ${pg_logdir}/${pg_logname}|grep 'archive recovery complete'`
if [ "${recovery_comlete}" != "" ];then
echo "备库自动升级为主库成功! 请切换数据库连接到备库,备库IP为:${local_ip} 备库PG数据目录为:${pgdata} ">> ${log_dir}/${log_file_name}
# 删除日志中包括could not connect to the primary server的行
sed -i '/could not connect to the primary server/d' ${pg_logdir}/${pg_logname}
else
echo "备库升级为主库失败,请前往备库所在节点切换到postgres用户 执行命令:pg_ctl promote -D ${pgdata} 手动切换备库为主库！">> ${log_dir}/${log_file_name}
fi
rm -rf ${trigger_file}
else
echo "主库运行正常.">> ${log_dir}/${log_file_name}
fi
current_time=`date -d "today" +"%Y-%m-%d %H:%M:%S"`
echo "本次执行结束时间:${current_time}">>${log_dir}/${log_file_name}
echo "---------------------end----------------------------" >> ${log_dir}/${log_file_name}
echo "">> ${log_dir}/${log_file_name}
二. 同步流复制
1. 一主双从
主:192.168.108.183
从:192.168.110.31/32
主库一键配置脚本
mkdir -p /home/pgslave
[root@cdhmaster01 pgslave]# cat master_config.sh
# 异步流复制主库一键配置脚本
# pg数据库目录
pgdata=/home/data/pgdata
# pg配置文件
pg_hba_file=pg_hba.conf
pg_postgresqlconf_file=postgresql.conf
# 主从主机IP网段
master_ipo=192.168.108.0
slave_ipo=192.168.110.0
# 创建专用于复制的角色
su - postgres <<EOF
psql;
create role replica login replication encrypted password 'replica';
EOF
# 修改pg_hba.conf
echo "host replication replica ${master_ipo}/22 md5">>${pgdata}/${pg_hba_file}
echo "host replication replica ${slave_ipo}/22 md5">>${pgdata}/${pg_hba_file}
# 修改postgresql.conf
# wal日志配置,用于流复制
echo "synchronous_standby_names = 'standby01,standby02'">>${pgdata}/${pg_postgresqlconf_file}
echo "wal_level = replica">>${pgdata}/${pg_postgresqlconf_file}
echo "max_wal_senders = 10">>${pgdata}/${pg_postgresqlconf_file}
# 重载当前数据库(尽量不重启pg_ctl restart -D $pgdata)
su - postgres <<EOF
pg_ctl reload -D $PGDATA
EOF
从库一键配置
mkdir -p /home/pgslave
[root@cdhmaster01 pgslave]# cat asynpgslave_config.sh
# 当前正常运行的主库的IP
host=192.168.108.183
# 备份别名
bak_label=test20190805
# 用于复制流的pg用户
replica_role=replica
# 从库的PG数据库目录
pgdata=/home/backup
# application_name
application_name=standby01
# trigger文件目录
trigger_dir=/home/trigger
# 提示输入密码的时候输入:replica
pg_basebackup -h ${host} -U ${replica_role} -F p -Xs -P -R -D ${pgdata} -l ${bak_label}
# 为目录授权
chown -R postgres:postgres ${pgdata}
# 配置recovery.conf
if [ -f ${pgdata}/recovery.done ];then
mv -f ${pgdata}/recovery.done ${pgdata}/recovery.conf
sed -i "/^primary_conninfo/d" ${pgdata}/recovery.conf
echo "primary_conninfo='application_name=${application_name} user=replica password=replica host=${host} port=5432 sslmode=disable sslcompression=1 target_session_attrs=any'">>${pgdata}/recovery.conf
else
sed -i "/^primary_conninfo/d" ${pgdata}/recovery.conf
echo "primary_conninfo='application_name=${application_name} user=replica password=replica host=${host} port=5432 sslmode=disable sslcompression=1 target_session_attrs=any'">>${pgdata}/recovery.conf
fi
# 增加触发器配置
echo "trigger_file = '${trigger_dir}'">>${pgdata}/recovery.conf
# 启动从数据库
su - postgres <<EOF
pg_ctl start -D ${pgdata}
EOF
查看是否配置成功
总结：
1.异步的优点：当异步复制slave宕机，master读写不受影响，恢复后可即时同步到最新数据；
同步复制的话，如果是执行隐式事务(增删改sql)，用于同步的所有slave宕机，事务挂起 master 受影响，slave恢复后master写事务继续执行，但是只要有一个slave没宕机，master就能正常运行；
如果是显式事务(带begin end的存储过程等)，在slave宕机的情况下，提交的事务会(在master)本地提交，slave恢复后会将master上本地提交的事务同步到slave，rollback的事务在slave宕机时不受影响。
2. 异步的缺点是:实时性没有同步高，在同步的时候有可能还没同步完master挂了，会有丢失数据的风险，不能保证和主库完全一致。
同步流复制 可以重启的方案
1.主库配置
[root@cdhmaster01 pgslave]# cat master_config.sh
# 异步流复制主库一键配置脚本
# pg数据库目录
pgdata=/home/data/pgdata
# pg配置文件
pg_hba_file=pg_hba.conf
pg_postgresqlconf_file=postgresql.conf
# 归档目录
pg_archive_dir=/var/lib/pgsql/pg_archive
# 主从主机IP
master_ipo=192.168.108.0
slave_ipo=192.168.110.0
# 创建专用于复制的角色
su - postgres <<EOF
psql;
create role replica login replication encrypted password 'replica';
EOF
# 修改pg_hba.conf
echo "host replication replica ${master_ipo}/22 md5">>${pgdata}/${pg_hba_file}
echo "host replication replica ${slave_ipo}/22 md5">>${pgdata}/${pg_hba_file}
# 修改postgresql.conf
# csvlog配置，用于记录运行日志 这里主要用来监控主库
echo "log_destination = 'csvlog'">>${pgdata}/${pg_postgresqlconf_file}
echo "logging_collector = on">>${pgdata}/${pg_postgresqlconf_file}
echo "log_directory = 'pg_log'">>${pgdata}/${pg_postgresqlconf_file}
echo "log_statement ='mod'">>${pgdata}/${pg_postgresqlconf_file}
echo "log_filename = '%Y%m%d_postgresql_log'">>${pgdata}/${pg_postgresqlconf_file}
echo "log_rotation_age = 1d">>${pgdata}/${pg_postgresqlconf_file}
echo "log_truncate_on_rotation = off">>${pgdata}/${pg_postgresqlconf_file}
echo "log_rotation_size = 2047MB">>${pgdata}/${pg_postgresqlconf_file}
# wal日志配置,用于流复制
echo "wal_level = replica">>${pgdata}/${pg_postgresqlconf_file}
echo "max_wal_senders = 10">>${pgdata}/${pg_postgresqlconf_file}
echo "wal_keep_segments = 64">>${pgdata}/${pg_postgresqlconf_file}
echo "archive_mode = on">>${pgdata}/${pg_postgresqlconf_file}
echo "archive_command = 'cp %p /var/lib/pgsql/pg_archive/%f'">>${pgdata}/${pg_postgresqlconf_file}
echo "wal_log_hints = on">>${pgdata}/${pg_postgresqlconf_file}
echo "full_page_writes = on">>${pgdata}/${pg_postgresqlconf_file}
echo "hot_standby = on">>${pgdata}/${pg_postgresqlconf_file}
# 创建pg归档目录
mkdir -p /var/lib/pgsql/pg_archive
chown -R postgres:postgres /var/lib/pgsql/pg_archive
# 重载当前数据库(尽量不重启pg_ctl restart -D $pgdata)
su - postgres <<EOF
pg_ctl reload -D $PGDATA
EOF
PG 归档
1. 主备库postgres用户免密登录
su - postgres
[postgres@cdhmaster01 ~]$ ssh-keygen -t rsa
[postgres@cdhmaster01 ~]$ ssh-copy-id postgres@192.168.108.74
[postgres@cdhmaster01 ~]$ ssh-copy-id postgres@192.168.1110.42
[postgres@cdhmaster01 ~]$ ssh postgres@192.168.108.74
Last login: Tue Aug 6 10:52:41 2019 from 192.168.110.42
[postgres@cdhmaster01 ~]$ ssh postgres@192.168.110.42
Last login: Tue Aug 6 10:53:44 2019 from 192.168.108.74
2. 主备库创建归档目录
# 归档目录
pg_archive=/var/lib/pgsql/pg_archive
# 创建pg归档目录
mkdir -p ${pg_archive}
chown -R postgres:postgres ${pg_archive}
3. 主库创建replica角色
su - postgres <<EOF
psql;
create role replica login replication encrypted password 'replica';
EOF
4.主库修改pg_hba.conf以使得replica角色可以在备库远程进行基础备份
host replication replica 192.168.108.0/22 md5
host replication replica 192.168.110.0/22 md5
5. 主库修改postgresql.conf以使得可以进行归档
wal_level = replica
archive_mode = on
archive_command ='cp %p /var/lib/pgsql/pg_archive/%f;scp %p postgres@192.168.108.74:/var/lib/pgsql/pg_archive/%f;ssh postgres@192.168.108.74 chown -R postgres:postgres /var/lib/pgsql/pg_archive;'
6. 在备库执行基础备份
pg_basebackup -h 192.168.110.42 -U replica -F p -Xs -P -R -D /home/backup -l test20190806
7.进入/home/backup 修改recovery.conf
#standby_mode = 'on'
#primary_conninfo = 'user=replica password=replica host=192.168.110.42 port=5432 sslmode=disable sslcompression=1 target_session_attrs=any'
restore_command ='cp /var/lib/pgsql/pg_archive/%f %p'
recovery_target_time = '2019-08-06 11:35:07'
修改备库postgresql.conf
屏蔽以下两行
#archive_mode = on
#archive_command ='cp %p /var/lib/pgsql/pg_archive/%f;scp %p postgres@192.168.108.74:/var/lib/pgsql/pg_archive/%f;ssh postgres@192.168.108.74 chown -R postgres:postgres /var/lib/pgsql/pg_archive;'
7. 修改备库PGDATA(/home/backup)所属用户组及用户
chown -R postgres:postgres /home/backup
8. 主库手动checkpoint 几次
先插数据再执行select pg_switch_wal()
9. 查看备库/var/lib/pgsql/pg_archive是否有新文件
10.编辑备库recovery.conf，指定recovery_target_time为某个还原时间点
recovery_target_time = '2019-08-05 20:50:15'
11.启动备库
su - postgres
pg_ctl restart -D /home/backup/
pg_ctl stop -D /home/backup/
12.查看是否还原成功
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
