PG-流复制
1. 流复制介绍
在9.0版本开始支持流复制(物理复制)。通过流复制技术,从实例级复制出一个和主库一样的从库。
2. 异步复制模式配置
2.1 软件部署和数据库初始化
2.2 参数配置(复制参数保持一致)
1)postgresql.conf文件
grep -Ev '^[[:space:]]|^#|^$' ${PGDATA}/postgresql.conf # 备份配置文件 cp ${PGDATA}/postgresql.conf{,_$(date +%Y%m%d)} # 添加复制参数配置 cat >> ${PGDATA}/postgresql.conf <<EOF wal_level = replica archive_mode = on archive_command = '/usr/bin/lz4 -q -z %p /ups/data/pgdata/12/archive_wals/%f.lz4' recovery_target_timeline='latest' max_wal_senders = 10 wal_keep_segments = 64 hot_standby = on hot_standby_feedback = on full_page_writes = on wal_log_hits = on EOF # 检查确认配置 grep -Ev '^[[:space:]]|^#|^$' ${PGDATA}/postgresql.conf
2) pg_hba.conf文件(客户端认证连接)
cp ${PGDATA}/pg_hba.conf ${PGDATA}/pg_hba.conf_$(date +%Y%m%d) cat >> ${PGDATA}/pg_hba.conf <<EOF # 同步账号 host replication sync 192.168.10.181/24 md5 host replication sync 192.168.10.182/24 md5 EOF
2.3 创建复制账号
CREATE USER sync REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD 'sync12345'; GRANT EXECUTE ON FUNCTION pg_read_binary_file(text) TO sync;
3.创建备库
1)拷贝文件方式
-- pg_start_backup() 函数在主库上发起一个在线备份,命令执行成功后,将数据文件拷贝到备节点 SELECT pg_start_backup('francs_bkl'); --拷贝数据文件 cd /ups/data/pgdata/11/ tar czvf pgdata.tgz pg_* --exclude=pg_root/pg_wal scp pgdata.tgz postgres@192.168.10.182:/ups/data/pgdata/11 -- 备库解压 tar -xf pgdata.tgz mkdir -p /ups/data/pgdata/11/pg_root/pg_wal/archive_status chown -R postgres:postgres /ups/data/pgdata/11/pg_root/pg_wal chmod -R 700 /ups/data/pgdata/11/pg_root/pg_wal -- 文件拷贝到备节点后,在主库上执行以下命令 SELECT pg_stop_backup();
2)pg_basebackup 方式部署流复制
pg_basebackup -D /ups/data/pgdata/11/pg_root -Fp -Xs -v -P -h 192.168.10.181 -p 1921 -U sync
3.2 备库参数配置
1)recovery.conf文件配置
# 备库配置( 注意:12版本已经 将recovery.conf 配置文件中的参数合并到 postgresql.conf) # 备份 cp $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf grep -Ev '^[[:space:]]|^#|^$' ${PGDATA}/recovery.conf # 添加参数 cat >> ${PGDATA}/recovery.conf <<EOF recovery_target_timeline = 'latest' standby_mode = on primary_conninfo = 'host=192.168.10.181 port=1921 user=sync application_name=s1' trigger_file = '/ups/data/pgdata/11/pg_root/.postgresql.trigger.1921' EOF
2)账号密码文件
touch ~/.pgpass chmod 0600 ~/.pgpass cat >> ~/.pgpass <<EOF 192.168.10.181:1921:replication:sync:sync12345 192.168.10.182:1921:replication:sync:sync12345 EOF
4. 启动备库
systemctl start postgresql-11.service systemctl status postgresql-11.service
5. 检查状态
# 主库检查复制状态: SELECT usename , application_name , client_addr, sync_state FROM pg_stat_replication ; # 结果是f则为主库,t为备库 select pg_is_in_recovery();
6. 问题
6.1 同步主备库一致时间点(故障切换时运用)
pg_rewind --target-pgdata=/ups/data/pgdata/11/pg_root --source-server='host=192.168.10.181 port=1921 user=sync dbname=postgres password=sync12345'