Postgres逻辑复制构建

IT那活儿发布于2023-01-11 13:19 / 1560人阅读

Postgres逻辑复制构建

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

概述

Postgresql流复制是物理复制技术，跟Oracle ADG一样，要求主备数据库在数据块级完全一样，这样就大大保证了数据同步的低延迟和数据一致性。但是在某些场景下，物理复制技术并不适用，比如基于库级或者表级的复制、多对一汇总的数据仓库、要求备机可写等。

Postgresql逻辑复制技术基于WAL日志解析实现，可实现表级同步，并且可以跨版本同步数据。

逻辑复制相关概念

PG逻辑复制基于发布和订阅模型，一个发布可以有多个订阅者。

发布publication可以创建在任意单机上，也可以在物理复制的主库上，发布端要求数据库参数WAL_LEVEL为logical。一个发布可以被多个数据库订阅。一个发布只能包含一个数据库中的表，但是这些表可以分布在不同的schema下。

订阅处于逻辑复制的下游端，一个订阅只能对应一个发布。订阅端的数据库名可以跟发布端不同，但是订阅发布的表名和schema必须相同。

当订阅创建成功后，就会自动在发布端创建多个逻辑复制槽，其中一个用于后续的增量数据同步，永久保留，另外还会创建一个或者多个的临时复制槽，用于复制表的全量快照数据。是当发布端是物理复制架构时，如果发生主从切换，发布是不会跟随数据库failover的。但是可以通过手工拷贝文件或者利用插件实现。

2.1 逻辑复制的限制

数据库的表结构不会被复制，所以需要在订阅端先创建对应的schema和表
DDL不会不复制，如果要进行DDL变更，需要先在订阅端执行DDL，然后在发布端执行。
Sequence不会不复制，当发布端使用了sequence数据，其写入了表的值会同步到订阅端，但是目标库的sequence并不会发生变化。
不支持大对象。
复制只能是基表到基表，不支持视图、物化视图、外部表等。支持DDL，如果表是分区表，需要基于分区进行复制。

2.2 逻辑复制参数要求

发布端：

wal_level = logical
max_replication_slots ##大于订阅数+物理复制槽数量+少量剩余用作临时快照复制
max_wal_senders #大于max_replication_slots+物理复制的备机数量

订阅端：

max_replication_slots = 订阅数加上保留给表同步的连接数
max_logical_replication_workers =订阅数加上保留给表同步的连接数
max_worker_processes =至少max_logical_replication_workers+1

2.3 逻辑复制构建

需要进行逻辑复制的表，要求最好是有主键或者唯一索引。为了能够复制UPDATE和DELETE操作，被发布的表必须配置有一个“复制标识”，这样在订阅者那一端才能标识对于更新或删除合适的行。默认情况下，复制标识就是主键，如果既没有主键也没有唯一索引，就需要配置复制标识为full

复制标识配置：

Alter table table_name
REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

Default表示使用主键，默认标识，可以不设置；
Using index index_name表示使用唯一索引；
Full 表示表中无主键和唯一索引，full标识影响复制效率；
Noting 标识的表不能进行delete和update复制。

创建发布：

Create publication pub_name for table customize_prod.t_migu_scenelooptone_11 ,
customize_prod.t_migu_scenelooptone_12 ,
customize_prod.t_migu_scenelooptone_13 ;

这样就成功创建了发布。

创建订阅：

CREATE SUBSCRIPTION subscription_name CONNECTION conninfo PUBLICATION publication_name；

当在逻辑复制架构下，需要增加同步的表时，我们可以使用如下命令：

Alter publication pub_name add table table_name;
Alter subscription sub_name refresh publication;

2.4 逻辑复制相关视图和监控

发布端：

pg_publication
pg_publication_tables;

pg_stat_replications
pg_replication_slots

复制延迟：

select application_name,client_addr,COALESCE(trunc(extract(epoch 
FROM (now() - (now()- replay_lag) 
))::numeric),0),trunc(pg_wal_lsn_diff(pg_current_wal_lsn(), 
replay_lsn)/1024) replay_delay from pg_stat_replication;

订阅端：

pg_subscription
pg_stat_subscription

pg_subscription_rel

END

本文作者：刘运彬

本文来源：IT那活儿（上海新炬王翦团队）

GPU云服务器云服务器 Postgres postgres 时区 postgres 使用 postgres 9.5

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129505.html

构建可扩展的PostgreSQL解决方案

摘要：这可以通过负载平衡来实现数据分片当问题不是并发查询的数量，而是数据库的大小和单个查询的速度时，可以实现不同的方法。 showImg(https://segmentfault.com/img/remote/1460000018875091); 来源 | 愿码(ChainDesk.CN)内容编辑愿码Slogan | 连接每个程序员的故事网站 | http://chaindesk.cn...

jonh_felix 2019-07-25 12:38 评论0 收藏0
构建可扩展的PostgreSQL解决方案

摘要：这可以通过负载平衡来实现数据分片当问题不是并发查询的数量，而是数据库的大小和单个查询的速度时，可以实现不同的方法。 showImg(https://segmentfault.com/img/remote/1460000018875091); 来源 | 愿码(ChainDesk.CN)内容编辑愿码Slogan | 连接每个程序员的故事网站 | http://chaindesk.cn...

FrozenMap 2019-06-25 19:12 评论0 收藏0
Dockerfile最佳实践

摘要：比如和指令，镜像中的文件内容被检查并且为每个文件计算校验和。这些文件的最终修改和访问时间将不被考虑到校验和内。在查找缓存期间，校验和将被用于与已存在的镜像校验和进行对比。 Docker 可以从 Dockerfile 中读取指令自动构建镜像，Dockerfile是一个包含构建指定镜像所有命令的文本文件。Docker坚持使用特定的格式并且使用特定的命令。你可以在 Dockerfile参考 ...

张金宝 2019-06-28 18:01 评论0 收藏0
PG的两种集群技术：Pgpool-II与Postgres-XL

摘要：最近研究了的两种集群方案，分别是和，在这里总结一下二者的机制结构优劣测试结果等。其中的前身的，的前身是。为了避免单点故障，可以为所有节点配置对应的节点。测试测试结果测试结果显示，两种集群与单机的性能指标几乎一致，无法分辨高下。最近研究了PG的两种集群方案，分别是Pgpool-II和Postgres-XL，在这里总结一下二者的机制、结构、优劣、测试结果等。 1、 Pgpool-I...

gitmilk 2019-07-25 12:32 评论0 收藏0
postgresql 10主备流复制测试

postgresql 10主备流复制测试 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; ...

IT那活儿 2023-01-11 13:20 评论0 收藏2959