首页 > 数据库 >PostgreSQL逻辑复制搭建

PostgreSQL逻辑复制搭建

时间:2024-07-10 21:43:31浏览次数:8  
标签:订阅 WAL PostgreSQL publication 复制 subscription ### 搭建

复制作为一种高可用/数据同步方案,在每一种数据库中都有实现,可以借助复制功能实现数据库的高可用或者数据同步/备份方案。

复制的分类

整体上看,复制可以分为物理复制和逻辑复制,对于物理复制或者逻辑复制,没有所谓的优劣,只有各自的适应场景。
所谓的物理复制,也即复制数据库的redo物理日志,通过redo日志在从节点(或者目标端)回放该日志来实现数据的同步,比如SQLServer的镜像/AG可用性组,postgresql的流复制等都属于物理复制,MySQL没有物理复制
所谓的逻辑复制,通过把主节点上的sql语句传到从节点,同样通过回放来实现数据的一致性。
不同的是,物理复制相比逻辑复制效率较高,但是粒度较粗,往往是库级别(MSSQL)或者是实例级别(postgresql)的,逻辑复制相对来说效率较低,但是粒度可大可小,从库级别到行或者列级别都可以实现。

postgresql逻辑复制的原理

熟悉SQLServer复制的话,应该能快速理解postgresql逻辑复制的原理,甚至SQLServer和postgresql逻辑复制的“术语”都一样,发布(publication)&订阅(subscribtion),主节点上将一个或者多个表绑定到一个发布(publication),目标端通过订阅创建一个订阅subscription,来“对接”发布(publication),相当于源头创建一个出水管(publication),目的地通过一个接水管(subscription)来对接,实现数据的同步。

在熟悉SQLServer的复制(订阅发布)的情况下,再来看postgresql的逻辑复制几乎可以无缝衔接,两者的原理一致,发布端解析redo日志生成sql语句,然后将发布的sql传递到订阅端并执行来实现数据的同步。但是SQLServer中多了一个distrubution库,发布待传递的中间数据通过distrubution中转一次,而postgresql的发布直接由后台进程完成,说实话postgresq的逻辑复制要比SQLServer的复制搭建起来简单多了。

 参考下图分别是SQLServer的发布订阅和postgresql的发布订阅原理图。

postgresql逻辑复制搭建步骤

发布端(主节点)创建发布publication

###主节点###
    
###复制用户
    ###创建复制用户
    --drop user app_replication;
    create user app_replication with password 'A-Strong-Password';
    alter role app_replication with replication;

    ###给复制用户授权
    grant usage on schema public to app_replication;
    --revoke usage on schema public from app_replication;
    ###这一点比较坑爹,grant usage on schema是未来在这个schema建表,有usage的权限,而对于已存在的表,仍没有权限,需要通过grant单独授权
    ###更坑爹的是,如果没有单独grant授权,届时复制搭建起来之后是从节点没有权限的错误,而不是主节点报错
    grant select on t1 to app_replication;
    grant select on t2 to app_replication;
    --revoke select on t1  from app_replication;
    --revoke select on t2  from app_replication;

###wal日志级别设置

    ###确认wal_level为logical级别
    select * from pg_settings where name = 'wal_level';

    
###复制槽
    ###创建逻辑复制槽,逻辑复制槽的作用就是记录标记当前发布与订阅日志发送的位置信息
    select * from pg_create_logical_replication_slot('db01_logic_replication_slot01','pgoutput');
    
    ###查看复制槽信息,active: t正在使用,f未使用
    SELECT * FROM pg_replication_slots;

    ###删除逻辑复制槽
    select pg_drop_replication_slot('db01_logic_replication_slot01');
    

###创建发布(publication)

    ###创建发布master_db01_pulication并添加表
    create publication master_db01_pulication for table t1 with (publish = 'insert,update');

    ###添加表到发布对象master_db01_pulication
    alter publication master_db01_pulication add table t2;
    
    ###删除发布
    drop publication master_db01_pulication;
    
    ###查看发布
    select * from pg_catalog.pg_publication;

    ###查看发布包含的对象
    select current_database(),pgtab.tablename,pgpub.pubname,pgpub.puballtables,pgpub.pubinsert,pgpub.pubupdate,pgpub.pubdelete,pgpub.pubtruncate
    from pg_publication as pgpub
    inner join pg_publication_tables as pgtab on pgpub.pubname = pgtab.pubname;


###发布对象的测试数据
    insert into t1(c2,c3,c4,c5,c6) values ('aa','aa','aa','aa',now());
    insert into t1(c2,c3,c4,c5,c6) values ('bb','bb','bb','bb',now());
    insert into t1(c2,c3,c4,c5,c6) values ('cc','cc','cc','cc',now());
    
    select * from t1 limit 100;
    select * from t2 limit 100;

 

订阅端(从节点)创建订阅subscription 

### 订阅端创建表
    postgresql的逻辑复制(发布订阅)不会传递DDL,所以表结构需要再订阅端创建好
    CREATE TABLE public.t1 (
        c1 serial4 NOT NULL,
        c2 varchar(100) NULL,
        c3 varchar(100) NULL,
        c4 varchar(100) NULL,
        c5 varchar(100) NULL,
        c6 timestamp(3) NULL,
        CONSTRAINT t1_pkey PRIMARY KEY (c1)
    );
    
    CREATE TABLE public.t2 (
        c1 serial4 NOT NULL,
        c2 varchar(100) NULL,
        c3 varchar(100) NULL,
        c4 varchar(100) NULL,
        c5 varchar(100) NULL,
        c6 timestamp(3) NULL,
        CONSTRAINT t2_pkey PRIMARY KEY (c1)
    );

###订阅
    ###创建订阅
    create subscription slave_db01_subscription 
    connection 'host=192.168.90.230 port=10000 dbname=db01 user=app_replication  password=A-Strong-Password' 
    publication master_db01_pulication with (create_slot = false,slot_name = db01_logic_replication_slot01,copy_data = true);

    
    ###查看订阅
    select * from pg_subscription; -- 通过 pg_subscription 来监控当前数据库中创建的 subscription


    ###启动订阅
    alter subscription slave_db01_subscription enable;
    
    
    ###删除订阅的步骤
    --停止订阅
    alter subscription slave_db01_subscription disable;
    
    alter subscription slave_db01_subscription SET (slot_name =NONE);
    
    drop subscription slave_db01_subscription;

  ###查看订阅数据同步
    select  * from t2 limit 10;

 

复制状态监控

发布端状态监控

###发布状态监控

    ###查看发布包含的对象
    select current_database(),pgtab.tablename,pgpub.pubname,pgpub.puballtables,pgpub.pubinsert,pgpub.pubupdate,pgpub.pubdelete,pgpub.pubtruncate
    from pg_publication as pgpub
    inner join pg_publication_tables as pgtab on pgpub.pubname = pgtab.pubname;


    ###发布槽信息
    select * from pg_replication_slots;
    
    slot_name :           一个唯一的、集簇范围内的复制槽标识符
    plugin :              包含这个逻辑槽正在使用的输出插件的共享对象基础名称,对于物理槽为空值。
    slot_type:            槽类型 - physical或者logical
    datoid:               与这个槽相关的数据库的OID,或者为空值。只有逻辑槽具有相关的数据库。
    database:             与这个槽相关的数据库的名称,或者为空值。只有逻辑槽具有相关的数据库。
    temporary:            如果这是一个临时复制槽则为真。临时槽不会被保存在磁盘上并且会在出错或会话结束时自动被删除掉。
    active                 如果这个槽当前正在被使用则为真
    active_pid             如果槽当前正在被使用,则记录使用这个槽的会话的进程 ID。如果不活动则为NULL。
    xmin                   这个槽要需要数据库保留的最旧事务。VACUUM不能移除被其后续事务删除的元组。
    catalog_xmin          这个槽要需要数据库保留的影响系统目录的最旧事务。VACUUM不能移除被其后续事务删除的目录元组。
    restart_lsn           可能仍被这个槽的消费者要求的最旧WAL地址(LSN),并且因此不会在检查点期间自动被移除。 如果这个槽的LSN从未被保留过,则为NULL。
    confirmed_flush_lsn   代表逻辑槽的消费者已经确认接收数据到什么位置的地址(LSN)。 比这个地址更旧的数据已经不再可用。对于物理槽这里是NULL。
    wal_status            此插槽定义WAL文件的可用性。
                          可能的值为:
                            reserved        意味着声称的文件包含max_wal_size。
                            extended        意味着max_wal_size已超出,但文件仍保留,通过复制插槽或wal_keep_size。
                            unreserved      意味着该插槽不再保留所需的 WAL 文件,并且将在下一个检查点删除其中一些文件。 此状态可以返回到reserved或extended。
                            lost            意味着某些需要的 WAL 文件已被删除,并且此插槽不再可用。
                            最后两种状态仅在max_slot_wal_keep_size为非负值时才看到。 如果restart_lsn为 NULL,则此字段为空。
    safe_wal_size   可写入 WAL 的字节数,以便此插槽不会处于"丢失"状态的危险中。 对丢失插槽它是NULL,以及如果max_slot_wal_keep_size是-1。
    
    
    ###发布状态信息
    select * from pg_stat_replication ;
    
    id 一个 WAL                  发送进程的进程 ID
    usesysid                    登录到这个 WAL 发送进程的用户的 OID
    usename                     登录到这个 WAL 发送进程的用户的名称
    application_name            连接到这个 WAL 发送进程的应用的名称
    client_addr                 连接到这个 WAL 发送进程的客户端的 IP 地址。 如果这个域为空,它表示该客户端通过服务器机器上的一个Unix 套接字连接。
    client_hostname             连接上的客户端的主机名,由一次对client_addr的逆向 DNS 查找报告。 这个域将只对 IP 连接非空,并且只有在 log_hostname被启用时非空。
    client_port                 客户端用来与这个 WAL 发送进程通讯的 TCP 端口号,如果使用 Unix 套接字则为-1
    backend_start               这个进程开始的时间,即客户端是何时连接到这个WAL 发送进程的。
    backend_xmin                由hot_standby_feedback报告的这个后备机的xmin水平线。
    state                       当前的 WAL 发送进程状态。 可能的值是:
                                  startup: 这个WAL发送器正在启动。
                                  catchup: 这个WAL发送者连接的备用服务器正在赶上主服务器。
                                  streaming: 在其连接的备用服务器赶上主服务器之后,这个WAL发送方正在流化变化。
                                  backup: 这个WAL发送器正在发送一个备份。
                                  stopping: 这个WAL发送器正在停止。
                                
    sent_lsn                    在这个连接上发送的最后一个预写式日志的位置
    write_lsn                   被这个后备服务器写入到磁盘的最后一个预写式日志的位置
    flush_lsn                   被这个后备服务器刷入到磁盘的最后一个预写式日志的位置
    replay_lsn                  被重放到这个后备服务器上的数据库中的最后一个预写式日志的位置
    write_lag                   从本地刷新近期的WAL与接收到此备用服务器已写入WAL的通知(但尚未刷新或应用它)之间的时间经过。 如果将此服务器配置为同步备用服务器,则可以使用此参数来衡量在提交时synchronous_commit级别remote_write所导致的延迟。
    flush_lag                   在本地刷写近期的WAL与接收到后备服务器已经写入并且刷写它(但还没有应用)的通知之间流逝的时间。 如果这台服务器被配置为一个同步后备,这可以用来计量在提交时synchronous_commit的级别on所导致的延迟。
    replay_lag                  在本地刷写近期的WAL与接收到后备服务器已经写入它、刷写它并且应用它的通知之间流逝的时间。 如果这台服务器被配置为一个同步后备,这可以用来计量在提交时synchronous_commit的级别remote_apply所导致的延迟。
    sync_priority               在基于优先的同步复制中,这台后备服务器被选为同步后备的优先级。在基于规定数量的同步复制中,这个值没有效果。
    sync_state                  这一台后备服务器的同步状态。 可能的值是:
                                  async:        这台后备服务器是异步的。
                                  potential:    这台后备服务器现在是异步的,但可能在当前的同步后备失效时变成同步的。
                                  sync:         这台后备服务器是同步的。
                                  quorum:      这台后备服务器被当做规定数量后备服务器的候选。
                                
    reply_time                     带时区的时间戳从备用服务器收到的最后一条回复信息的发送时间

查看发布包含的对象以及属性

逻辑复制对应的复制槽信息

复制正常的话,state字段是streaming

 

订阅端复制状态监控

###订阅基础信息查看
SELECT * FROM pg_subscription;

    oid                         行标识符
    subdbid                     订阅所在的数据库的OID
    subname                     订阅的名称
    subowner                    订阅的拥有者
    subenabled                  如果为真,订阅被启用并且应该被复制。
    subsynccommit               包含订阅工作者的synchronous_commit设置的值。
    subconninfo                 到上游数据库的连接字符串
    subslotname                 上游数据库中的复制槽的名称。也被用于本地复制源名称。
    subpublications             被订阅的publication名称的数组。这些引用的是发布者服务器上的publication。
 
###订阅以及对应的表
select s.*,c.relname 
from pg_subscription_rel s 
inner join pg_class c on s.srrelid = c.oid ;
    
    ###字段说明:
    srsubid         (references pg_subscription.oid) 对订阅的引用
    srrelid         (references pg_class.oid)对关系的引用
    srsubstate       状态代码: i = 初始化, d = 数据正在被拷贝, s = 已同步, r = 准备好(普通复制)
    srsublsn        在s或r状态中,用于同步协调的状态更改的远程 LSN,否则为空

###订阅日志同步状态
    select * from pg_stat_subscription;
    ###字段说明:
    subid                       订阅的OID
    subname                     订阅的名称
    pid                         订阅工作者进程的进程ID
    relid                       工作器正在同步的关系的OID;Null用于主应用工作器
    received_lsn                接收到的最后一个预写式日志位置,该字段的初始值为0
    last_msg_send_time          从WAL发送器收到的最后一条信息的发送时间
    last_msg_receipt_time       从WAL发送器收到的最后一条信息的接收时间
    latest_end_lsn              向WAL发送器报告的最后预写式日志位置
    latest_end_time             向WAL发送器报告的最后一次预写式日志位置的时间

 查看订阅状态信息

 查看订阅的表以及复制状态信息

查看复制状态

postgresql逻辑复制要吐槽的

逻辑复制本身的粒度很细,已经到表级别了,这一点是所有逻辑复制的共同点,包SQLServer和MySQL,但是postgresql逻辑复制中已有一个要吐槽的,就是一个“发布”publication的数据传递属性是不能基于表的。
怎么理解呢?包含了多张表的时候create publication master_db01_pulication for table t1,t2 with (publish = 'insert,update');其发布类型只能是一样的,比如master_db01_pulication 包含了2张表,如果想让t1表的增删改(insert/update/delete)都发布,t2表只发布增改(insert/update),postgresql的逻辑发布做不到,但是在其他数据库是可以的。

曲线救国的办法就是做多个发布,将不同类型的日志传递动作的表,放在不同的发布中。缺点就是搞得主节点发布端的publication太多了。

 

标签:订阅,WAL,PostgreSQL,publication,复制,subscription,###,搭建
From: https://www.cnblogs.com/wy123/p/18294960

相关文章

  • 【Linux】00.Linux 介绍及其环境搭建
    一、Linux的发展史想要谈Linux的发展史,还是得先从UNIX开始讲起。1.1UNIX的发展历程1968年,一些来自通用电器公司、贝尔实验室和麻省理工学院的研究人员开发了一个名叫Multics的特殊操作系统。Multics在多任务文件管理和用户连接中综合了许多新概念。1969-1970年,AT&T的......
  • 撸包小游戏对接广告联盟APP系统开发源码搭建
    “撸包小游戏广告联盟APP”源码搭建涉及多个关键步骤,以下是一个简化的流程:市场调研与需求分析:对市场进行深入调研,了解目标用户群体和他们的需求。分析竞争对手的小游戏和广告策略,确定自己小游戏的特色和定位。游戏开发:根据市场调研的结果,设计并开发具有吸引力的撸包小......
  • 海外交友一对一社交软件APP开发搭建
    海外交友一对一软件APP的开发源码搭建是一个复杂但有序的过程,以下是基于搜索结果中提供的信息,概括的搭建步骤和注意事项:市场调研和需求分析:深入了解海外交友市场的需求和趋势,包括用户画像、使用习惯、竞争对手分析等。确定APP的核心功能和特色,如一对一视频聊天、语音聊天......
  • 1 搭建编程环境
    对于一个刚从图形化编程进阶来的小白来说,Python是一门不错的语言。它关键字偏少,采用面向对象(OOP)的方式来进行代码的运行。不要担心你学不会,因为Python是一门精简的语言,易上手,我敢说,你只要熟练,就永远忘不掉他了。好了,话不多说,让我们开始Python之旅吧!!!1.1下载Python访问该网站:......
  • IDEA社区版搭建Spring工程(04-加载配置文件及加解密)
    SpringMVC加载配置文件的几种方式通过context:property-placeholde实现加载配置文件在springmvc.xml配置文件里加入context相关引用<?xmlversion="1.0"encoding="UTF-8"?><beansxmlns="http://www.springframework.org/schema/beans"xmlns:conte......
  • Docker-搭建部署Jenkins(保姆篇)
    文章目录Jenkins部署拉取镜像启动容器查看初始密码关闭CSRFJenkins页面使用解决插件下载缓慢访问jenkins页面推荐插件安装创建一个管理员账号实例配置页面展示更多相关内容可查看Jenkins部署拉取镜像如果想拉取对应版本请指明版本号dockerpulljenkins/jenki......
  • Linux捣鼓记录:快速搭建alist+aria2+qbittorrent
    简介:使用docker-compose创建alistaria2qbittorrent服务,前置条件安装docker及docker-compose插件,docker镜像仓库访问不了,建议配置代理用来拉取镜像。一、确认路径,确认UIDGID,确认端口路径alist挂载路径:-/home/dalong/app/alist:/opt/alist/data-/home/dalong:/homearia......
  • IDEA社区版搭建Spring工程(03-Spring MVC搭建)
    新建一个基于Maven的"webapp"模板的基础工程在main文件夹下新建java源码文件夹将自动生成的index.jsp移入webapp的view文件夹下,在java下新建一个controller文件夹添加SpringMVC框架所需的POM配置<properties><project.build.sourceEncoding>UTF-8</pro......
  • 搭建自己的局域网,在自己电脑上搭建DHCP服务器
    文章目录前言一、dhcp是什么?二、软件下载2.开启服务总结前言我用我的笔记本直接用一根网线连接B的电脑,这样可以进行共享吗?答案是否。那怎么才能通过一根网线就实现上述功能呢?答案就是在自己电脑上搭建一个dhcp服务器。搭建dhcp服务器的作用:自己电脑搭建完dhcp服......
  • 138. 随机链表的复制
    138.随机链表的复制递归和哈希表时间&空间复杂度O(n)复杂链表的特点是每个节点除了有一个指向下一个节点的指针外,还有一个随机指针可能指向链表中的任意节点或null。通过递归和哈希表的方式,能够确保每个节点只被复制一次,并且正确地复制了next和random指针。/*//Definitionf......