postgreSQL中的publications简介

在 PostgreSQL（PG）中，Publication（发布）是逻辑复制机制中的一个概念，用于定义哪些表的数据变更（INSERT、UPDATE、DELETE）可以发布到订阅者（Subscribers）。它主要用于 逻辑复制，允许在不同的 PostgreSQL 实例之间同步数据表的变更，特别适合进行数据复制、分发、数据迁移等场景。

逻辑复制的基本概念

逻辑复制是 PostgreSQL 提供的机制之一，用于在主数据库和副本数据库之间复制表数据和 DML 操作。逻辑复制可以对特定的表进行精细化控制，而不必像物理复制那样对整个数据库实例进行完全的复制。

  • Publication（发布）：指发布者数据库中定义的表或表的集合，这些表的数据变化会被传递给订阅者。
  • Subscription（订阅）：订阅者连接到发布者并接收发布的表的数据变更。

Publication 的工作方式

  • 发布者数据库中的 Publication 定义了哪些表的哪些操作（插入、更新、删除）应该被复制。
  • 每个 Subscription 会从一个或多个 Publication 中订阅数据变更。
  • 数据变更通过逻辑流（WAL 日志）发送到订阅者数据库，订阅者会应用这些变更。

创建和管理 Publications

  1. 创建一个发布
    使用 CREATE PUBLICATION 命令可以创建一个发布，并指定要发布哪些表及其操作。

创建一个发布，发布所有表的所有 DML 操作：CREATE PUBLICATION my_publication FOR ALL TABLES;
创建一个发布，发布特定表的 INSERT 和 UPDATE 操作：CREATE PUBLICATION my_publication FOR TABLE my_table WITH (publish = 'insert, update');

  1. 添加或移除表
    可以通过 ALTER PUBLICATION 添加或移除表。

添加表到现有发布：ALTER PUBLICATION my_publication ADD TABLE another_table;
从发布中移除表：ALTER PUBLICATION my_publication DROP TABLE my_table;

  1. 删除发布
    使用 DROP PUBLICATION 命令删除发布：DROP PUBLICATION my_publication;

Publication 选项

CREATE PUBLICATION 命令中可以指定不同的选项控制哪些操作将被发布：

  • insert：发布 INSERT 操作。
  • update：发布 UPDATE 操作。
  • delete：发布 DELETE 操作。
  • truncate：发布 TRUNCATE 操作。
    默认情况下，所有支持的操作都会被发布。如果你只想发布特定操作，可以使用 WITH 选项进行精细控制。

Publication 的应用场景

  • 主从复制：逻辑复制允许将数据从一个主数据库复制到多个从数据库中，每个从数据库可以独立查询和扩展。
  • 数据分发：通过发布和订阅，可以将数据变更实时地从一个数据库分发到其他多个数据库中。
  • 数据迁移：通过逻辑复制机制，可以实现在线数据迁移，避免长时间停机。解决跨大版本、跨操作系统平台进行复制。

示例：逻辑复制的完整流程
假设有一个主数据库和一个从数据库，使用逻辑复制将主数据库的表 my_table 数据变更复制到从数据库中。

  1. 在主数据库上创建 Publication：CREATE PUBLICATION my_publication FOR TABLE my_table;
  2. 在从数据库上创建 Subscription： 从数据库通过订阅发布的表来接收数据变更，使用 CREATE SUBSCRIPTION：CREATE SUBSCRIPTION my_subscription CONNECTION 'host=master_db_host port=5432 dbname=mydb user=myuser password=mypassword' PUBLICATION my_publication;
  3. 数据同步： 在创建订阅时，PostgreSQL 会首先同步现有的数据，然后将增量的 INSERT、UPDATE 和 DELETE 操作应用到从数据库。

Publication 与物理复制的区别

  • 灵活性：逻辑复制通过发布和订阅机制可以选择性地复制特定表的特定操作，而物理复制则只能复制整个数据库实例。
  • 独立性：逻辑复制允许从数据库具有与主数据库不同的结构（如只复制某些表），而物理复制需要从库与主库保持完全一致。
  • 异构复制：逻辑复制支持跨数据库实例的复制，如 PostgreSQL 版本不同的数据库之间的复制，而物理复制需要主库和从库的版本保持一致。

注意事项

  • 主键或唯一索引：为了确保复制过程的顺利进行，复制的表通常需要有主键或唯一约束（复制标识 Replica Identity），否则更新和删除操作可能无法正确应用到订阅者。
  • WAL 日志和网络流量：逻辑复制依赖于 WAL 日志的增量数据，因此需要确保发布者的 WAL 日志保留足够的时间，以便在网络延迟或中断时，订阅者能够继续接收数据。
  • 数据一致性：在某些场景下（例如网络延迟），订阅者的数据可能会比发布者稍微滞后，不过这通常是可接受的。

Publication 是 PostgreSQL 逻辑复制系统中的核心概念，它允许数据库管理员对不同数据库实例之间的数据同步进行细粒度控制。

复制标识（Replica Identity）

默认情况下，主键 （Primary Key）是表的复制标识，非空列上的唯一索引 （UNIQUE NOT NULL）也可以用作复制标识。
如果没有任何复制标识，可以显式将复制标识设置为FULL，也就是把整个行当作复制标识。
使用FULL模式的复制标识效率很低（因为每一行修改都需要在订阅者上执行全表扫描，很容易把订阅者拖垮），所以这种配置只能是保底方案。
使用FULL模式的复制标识还有一个限制，订阅端的表上的复制身份所包含的列，要么与发布者一致，要么比发布者更少。
INSERT操作总是可以无视复制标识直接进行（因为插入一条新记录，在订阅者上并不需要定位任何现有记录；而删除和更新则需要通过复制标识 定位到需要操作的记录）。
如果一个没有 复制标识 的表被加入到带有UPDATE和DELETE的发布中，后续的UPDATE和DELETE会导致发布者上报错。

显式配置复制标识：

alter table test3 REPLICA IDENTITY full;

create unique index idx_test4 on test4(id);
alter table test4 alter column id set not null;
alter table test4 REPLICA IDENTITY using index idx_test4;

select relname,relreplident from pg_class where relname in('test3','test4');
发布端配置

wal_level=logical
max_replication_slots 大于订阅端的数量
max_wal_senders 大于max_replication_slots

订阅端配置

max_logical_replication_workers 逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量。

Tags: PostgreSQL

添加新评论