首页 > 数据库 >postgresql从库搭建--物理复制

postgresql从库搭建--物理复制

时间:2022-11-02 16:37:28浏览次数:43  
标签:主库 wal 备库 -- 复制 pg postgresql 从库 select

图如下:

 

 

Postgresql早在9.0版本开始支持物理复制,也称为流复制,通过从实例级复制出一个与主库一模一样的备库。流复制同步方式有同步,异步两种,如果主节点和备节点不是很忙,通常异步模式下备库和主库的延迟时间能够控制在毫秒级。物理复制只能复制整个实例。

逻辑复制也成为选择性复制,可以做到基于表级别的复制,选择需要逻辑复制的表,而不是复制实例上的所有数据库的表,10版本不支持内置的逻辑复制,通常使用第三方逻辑复制。

WAL日志记录数据库变化,格式为二级制格式,尽管流复制都是基于WAL,但是两者本质不同,流复制是基于WAL物理复制,逻辑复制是基于WAL逻辑解析,将WAL解析成一种清晰,易于理解的格式。

 流复制和逻辑复制主要有以下差异:

  • 流复制是物理复制

核心原理是主库将预写入日志WAL日志发给备库,备库接收到WAL日志后进行重做。

逻辑复制核心是基于WAL,逻辑复制会根据预先设置好的规则解析WAL日志,将二进制文件解析成一定格式的逻辑变化信息(有点像oracle的物理备库和逻辑备库)。

 

  • 物理复制只能对实例级别,逻辑复制能够对表级别进行复制
  • 物理复制能够对DDL进行操作,逻辑复制DDL主库不能复制到备库
  • 物理复制必须大版本一致,逻辑复制支持跨大版本。

 

 

1.    物理复制

1.     异步流复制

环境情况:

主机

主机名

IP

操作系统

Postgresql版本

master

10pg1

192.168.10.41

Centos6.9

PostgreSQL 10.8

slave

10pg2

192.168.10.51

Centos6.9

PostgreSQL 10.8

 

 

这种环境的部署包括两种方式:

① 数据文件拷贝的方式

② pg_basebackup方式部署

本次将介绍pg_basebackup方式部署。

1. 两台都要安装postgresql

2. 主库创建创建Replication用户(以下都是主库操作)

CREATE ROLE rep login replication password 'rep';

 

修改master的pg_hba.conf文件:

 

 

修改Master库数据库配置(postgresql.conf)

要使用流复制,一定要把wal_level = hot_standby设置成hot_standby,其中要开启归档模式

 

 

wal_level = hot_standby   # 这个是设置主为wal的主机

max_wal_senders = 5       # 这个设置了可以最多有几个流复制连接

wal_keep_segments = 128   # 设置流复制保留的最多的xlog数目

wal_sender_timeout = 60s  # 设置流复制主机发送数据的超时时间

max_connections = 100     # 这个设置要注意下,从库的max_connections必须要大于主库的

配置完后重启主库。

 

3. 以下都是备库操作

修改master的pg_hba.conf文件:

host   all             all             192.168.10.0/24            md5

host    replication     rep             192.168.10.41/24        md5

host    replication     rep             192.168.10.51/24        md5

 

 

使用pg_basebackup建备库

pg_basebackup -h 192.168.10.41 -Urep -Ft -Pv -Xf -z -Z5 -p 5432 -D /backup/20190629/

 

 

停止备库进行恢复操作:

 

cd /pgsql/

mv pg_data/ pg_databak

 

 

mkdir -p /pgsql/pg_data

cd /pgsql/

chmod 700 pg_data

chown -R postgres:postgres /pgsql/

tar -zxvf /backup/20190629/base.tar.gz  -C /pgsql/pg_data/

 

 拷贝主库的recovery.conf文件到备库(主库执行)

scp /usr/local/pgsql/share/recovery.conf.sample 192.168.10.51:/pgsql/pg_data/recovery.conf

 

 

备库修改recovery.conf

 

standby_mode=on

primary_conninfo = 'user=rep password=rep host=192.168.10.41 port=5432'

recovery_target_timeline = 'latest'

 

 

启动备库后,会报错,接下来修改postgresql.conf

 

vi postgresql.conf

 

 

max_connections = 200              # 一般查多于写的应用从库的最大连接数要比较大

hot_standby = on                   # 说明这台机器不仅仅是用于数据归档,也用于数据查询

max_standby_streaming_delay = 30s  # 数据流备份的最大延迟时间

wal_receiver_status_interval = 10s #  多久向主报告一次从的状态,当然从每次数据复制都会向主报告状态,这里只是设置最长的间隔时间

hot_standby_feedback = on          # 如果有错误的数据复制,是否向主进行反馈

 

测试:

主库操作:

 

 

 

备库:

 

 

同步正常。

 

备库只能执行查询,与Oracle dg类似,且slave停掉后,主库能够正常的运行,wal日志不能传向远端。

 

启动后,把主库的归档日志传向备库,备库继续应用日志(不像ORACLE需要手动应用日志)。

备库停库后主库delete操作:

 

 

备库启动后,主库传完归档日志操作:

 

 

主备库一致性查询操作:

 

 

主备一致,且主库执行同步查询:

select pid,state, client_addr,sync_priority,sync_state from pg_stat_replication;

 

select * from pg_stat_replication ;

 

检查数据库主从复制进度:

查看流复制的信息可以使用主库上的视图

select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

 

查看备库落后主库多少字节

select pg_xlog_location_diff(pg_current_xlog_location(),replay_location)/1024/1024 as MB from pg_stat_replication;

select pg_xlog_location_diff(pg_current_xlog_location(),replay_location)/1024/1024/1024 as GB from pg_stat_replication;

 

级联复制

select pg_xlog_location_diff(pg_last_xlog_replay_location(),replay_location)/1024/1024/1024 as GB from pg_stat_replication;

 

 

查看备库因为冲突而被取消的SQL:

select * from pg_stat_database_conflicts;

 

显示备库详细信息:pg_controldata

备库wal 日志清理:

由于我的备库WAL日志存在/pgsql/pg_data/pg_wal目录。

vi /pgsql/pg_data/recovery.conf

archive_cleanup_command = 'pg_archivecleanup /pgsql/pg_data/pg_wal %r'

 

2.     同步流复制

异步流复制可以转换成同步流复制。

主库配置postgresql.conf:

synchronous_commit = on

synchronous_standby_names = 'standby1'  --备库设置节点别名

 

备库配置recovery.conf

primary_conninfo = 'application_name=standby1 user=rep password=rep host=192.168.10.41 port=5432 sslmode=disable sslcompression=1'

recovery_target_timeline = 'latest'

配置完后重启主备库。

 

查看同步方式:

select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

 

同步复制环境陷阱:

同步复制环境中,由于主库提交事务至少需要一个备库接收WAL,并返回确认信息后主库才向客户端返回成功,一方面保证了数据的完整性,另一方面对于一主一备的同步环境变现的陷阱是,如果备库宕机,主库上的写操作即处于等待状态(这点跟ORACLE不一样,Oracle 有gap,主库会向备库传送归档),读操作不影响,需要手动的把归档同步到备库,所以生产上,建议使用异步方式(一主一从架构)。

 

3.同步查看

查看延迟(wal延迟时间衡量):

select * from pg_stat_replication ;

 

通过WAL日志应用延迟量衡量:

select

pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) as sent_delay,  

pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) as write_delay,  

pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) as flush_delay,  

pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_delay

from pg_stat_replication;

 

 

select * from pg_stat_wal_receiver;

 

 

查看恢复进程是否处于恢复模式:

SELECT PG_IS_IN_RECOVERY();

 

 

显示备库最近接收的WAL日志位置:

 select pg_last_wal_receive_lsn();

 

 

显示备库最近应用的WAL日志位置:

select pg_last_wal_replay_lsn();

 

 

显示备库最近事务的应用时间:

select pg_last_xact_replay_timestamp();

 

 

显示主库WAL当前写入位置:

select pg_current_wal_lsn();

 

 

2.    流复制备升主库

建议主备库事先做个快照

 

首先判断主备库

ps -ef | grep "wal"

可以查看有

 

备:

 

 

或者查看以下SQL 有内容的为主库,没有内容的为从库。

select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

或者查看

select pg_is_in_recovery();  -- t为备 f为主库

 

pg_controldata  备库Database cluster state参数 为 in archive recovery模式;主库为in production 模式。

 

 

 

 

9.0之前切换需要文件出发方式,9.1开始,支持pg_ctlpromote出发方式,相比文件出发方式更方便。

 

 

Promote命令发出后,运行中的备库将停止恢复模式,并切换成读写模式的主库。步骤如下:

1.关闭主库,建议使用-m fast模式关闭。

pg_ctl stop -D /pgsql/pg_data/ -m fast

 

 

2.备库执行命令激活备库

pg_ctl promote -D /pgsql/pg_data

 

 

查看备库原备库recovery.conf 变成recovery.done,表示切换完成(测试已切换完成)

 

 

查看新主库:

 

 

测试新主库可以进行读写操作,切换成功。

 

由于考录到主库宕机之后不可用,并没有做主备互相切换,只做备库升为主库操作。

 

3.    流复制主备互换角色

Pg_rewind 是pgsql一个非常好的数据同步工具,如果主备互相切换的时候忘记关闭主库,除了重新搭建备库外,就会用到提供的pg_rewind工具。

pg_rewind:

主备库设置参数 wal_log_hints = on ,如果数据库初始化的时候是 --data-checksums选项可以不用设置此参数,由于--data-checksums会在数据块上进行检测,发现I/O错误,开启后后性能损失。

设置号wal_log_hints = on 后,进行重启生效。

 

① 激活备库

参数设置好后,备库提升为主库

pg_ctl promote -D /pgsql/pg_data

 

 

 

提升成功。

② 主库转换为备库

关闭原来的主库。

pg_ctl stop -D /pgsql/pg_data/ -m fast

 

 

使用pg_rewind 工具增量同步10pg2到10pg1的数据。

 

pg_rewind --target-pgdata=/pgsql/pg_data/ --source-server='host=192.168.10.51 port=5432 user=postgres password=postgres dbname=postgres' -P

 

 

mv recovery.done recovery.conf

vi recovery.conf

把主库信息修改一下

 

 

vi postgresql.conf

修改监听地址。

后启动成功后

pg_ctl start -D /pgsql/pg_data/

 

 

查看日志有报错

 

 

把51的日志cp到41 wal日志目录(由于我主库有新的数据生成)

 

 

新备库立马同步正常。

 

 

延迟设置:

如果备库不需要实时同步,设置此参数:

 

vi recovery.conf

recovery_min_apply_delay = 30s

默认是0 毫秒,支持ms,s,min,h,d(毫秒,秒,分钟,小时,天),注意参数需要重启生效;

如果设置时间过大,需要注意wal目录的空间是否足够大。

 

4. 流复制槽备库高可用

很多时候在主库产生xlog或者wal日志的时候,还没有传到从库就被覆盖了,为了保证xlog/wal日志不被覆盖,postgres 就启用流复制槽,让没有传到从库的xlog保存不被覆盖,新的日志继续产生。

 

配置流复制主备库都需要进行参数设置。

 主库设置以下,并需要重启

     max_replication_slots = 4            # max number of replication slots

     wal_level = hot_standby 

 

  1. 在主库上创建slot

select * from pg_create_physical_replication_slot('pg_5432');

 

 

 

 

查看是主库否创建成功:

select * from pg_replication_slots ;

 

 

 

  1. 备库recovery文件进行设置

 

vi /pgsql/pg_data/recovery.conf

 

 

primary_slot_name = 'pg_5432'

standby_mode = 'on'

recovery_target_timeline = 'latest'

primary_conninfo = 'user=rep password=rep host=192.168.10.41 port=5432

application_name=pg_5432 sslmode=disable sslcompression=1'  

 

 

 

其他的都一样,就是多了一个primary_slot_name 配置,以及primary_conninfo里面需要加上application_name=流复制槽名称

备库进行重启。

 

备库重启后再次查看主库流复制槽信息:

select * from pg_replication_slots ;

 

 

  

标签:主库,wal,备库,--,复制,pg,postgresql,从库,select
From: https://www.cnblogs.com/yaoyangding/p/16851450.html

相关文章

  • AI云边端调度能力EasyCVR视频融合平台分辨率的优化实现
    将AI智能分析网关接入到EasyCVR视频融合云平台,通过对监控场景中的视频图像进行智能识别与分析,可提供人脸、人体、车辆、烟火、物体、行为等识别、抓拍、比对、告警等服务,支......
  • python 中 if语句取反
     001、>>>str1=">aabbcc">>>ifstr1.startswith(">"):...print("yes")...yes>>>ifnotstr1.startswith(">"):##python中if语句取反...prin......
  • MongoDB副本集搭建
    一、安装mongodb服务   1、下载mongodb二进制包,解压,移动到/usr/local/下。tarzxfmongodb-linux-x86_64-rhel70-4.4.17.tgzmvmongodb-linux-x86_64-rhel70-4.4......
  • 使用cpolar发布群晖NAS上的网页(3)
    在前两篇介绍中,我们成功的在局域网内的群晖NAS上,搭建起一个简单的静态网页,虽然这个网页不复杂,但其展示的原理却是通用的。接着我们就可以使用cpolar建立的数据隧道,将这个网......
  • 使用cpolar发布群晖NAS上的网页 中篇(7.X版)
    在上篇文章中,我们在群晖系统中使用webstation创建了一个简单的静态网页,并且在局域网内进行测试,网页也能够正常显示。接下来,我们就可以使用cpolar建立一条数据隧道,将这个网......
  • mysql——partition by
    参考:https://www.cnblogs.com/xiaohuizhenyoucai/p/10636747.htmlhttps://www.jianshu.com/p/785f0edac47f partition by关键字是分析性函数的一部分,它和聚合函数(如......
  • 【视频】CNN(卷积神经网络)模型以及R语言实现回归数据分析|附代码数据
    全文链接:http://tecdat.cn/?p=18149无人驾驶汽车最早可以追溯到1989年。神经网络已经存在很长时间了,那么近年来引发人工智能和深度学习热潮的原因是什么呢?(点击文末“阅读......
  • 使用cpolar发布群晖NAS上的网页(设置固定域名)
    在上篇介绍中,我们使用cpolar的数据隧道功能,成功地将位于内网群晖NAS上的网页发布到了公共互联网,让公共互联网上的用户也能浏览到它。不过,此时的公网链接还是临时性质,是网页......
  • 设计模式十一(空对象模式,策略模式、模板模式)
    在空对象模式(NullObjectPattern)中,一个空对象取代NULL对象实例的检查。Null对象不是检查空值,而是反应一个不做任何动作的关系。这样的Null对象也可以在数据不可用的......
  • 使用cpolar发布群晖NAS上的网页 下篇(7.X版)
    在之前的两篇介绍中,我们使用群晖系统的套件(主要为webstation和php7.4)和开源模板,在本地群晖NAS中建立起一个简单网页,并通过cpolar建立的临时数据隧道,将其发布到公共互联网上......