PG中的逻辑复制（Logical Replication）

由小麦苗 · 发布日期 2021年7月5日 · 已更新 2022年5月11日

0 1586 9

👉 本文共约10801个字，系统预计阅读时间或需41分钟。

逻辑复制简介

从v9.4开始，PostgreSQL正式支持了Logical Decoding，也就是逻辑解码，逻辑解码的实现为最终实现逻辑复制奠定了坚实的基础，终于在v10大版本发布之后，PostgreSQL正式支持了原生的逻辑复制，在此之前可以使用pglogical、BDR等插件实现，pglogical有着更强大的冲突处理能力。BDR特点是支持多主机、DDL复制、全局序列、全局DDL锁，最早开源过一阵子，后面就闭源了。

Postgresql支持“逻辑”和“物理”两种复制方式，逻辑复制是一种基于数据对象的复制标识（通常是主键）复制数据对象及其修改的方法，而后者使用准确的块地址以及逐字节的复制方式（WAL日志）。逻辑复制允许数据复制和安全性上更细粒度的控制（可以对特定表进行复制）。逻辑复制采用发布者->订阅者模型，具体流程结构图如下：

逻辑复制（Logical Replication）是对现有PostgreSQL复制特性的一种扩展。是一种实现表级数据复制的特性。

逻辑复制使用一种发布和订阅模型，其中有一个或者更多订阅者订阅一个发布者节点上的一个或者更多发布。订阅者从它们所订阅的发布拉取数据并且可能后续重新发布这些数据以允许级联复制或者更复杂的配置。

一个表的逻辑复制通常开始于对发布者服务器上的数据取得一个快照并且将快照拷贝给订阅者。一旦这项工作完成，发布者上的更改会被实时发送给订阅者。订阅者以与发布者相同的顺序应用那些数据，这样在一个订阅中能够保证发布的事务一致性。这种数据复制的方法有时候也被称为事务性复制。

逻辑复制是PostgreSQL V10重量级新特性，支持内置的逻辑复制
在10版本之前，虽然没有内置的逻辑复制，也可以通过其它方式实现，触发器、自定义脚本实现表级别同步，另外也可以通过外部工具 Londiste3 实现
从2014年发布的9.4版本开始，PostgreSQL就支持逻辑复制了，只是一直没有将其引入内核
可以针对同一个数据库实例，同时使用逻辑复制和物理复制，因为他们都是基于WAL的

备注：Londiste 是一个较新的基于队列的复制软件，它基于触发器来驱动，它是SkyTools项目集的一部分，该软件可在这个网址找到 http://pgfoundry.org/projects/skytools/

逻辑复制的典型用法是：

在一个数据库或者一个数据库的子集中发生更改时，把增量的改变发送给订阅者。
在更改到达订阅者时引发触发器。
把多个数据库联合到单一数据库中（例如用于分析目的）。
在PostgreSQL的不同主版本之间进行复制。
在不同平台上（例如Linux到Windows）的PostgreSQL实例之间进行复制。
将复制数据的访问给予不同的用户组。
在多个数据库间共享数据库的一个子集。

订阅者数据库的行为与任何其他PostgreSQL实例相同，并且可以被用作其他数据库的发布者，只需要定义它自己的发布。当订阅者被应用当作只读时，单一的订阅中不会有冲突。在另一方面，如果应用或者对相同表集合的订阅者执行了其他的写动作，冲突可能会发生。

角色

Walsender：使用OutputPlugins进行逻辑解码，然后将解码后的数据发送给订阅端
logical replication launcher：类似于autovacuum launcher，守护进程，进行fork logical replication worker，发布端和订阅端都有该进程
logical replication worker：工作进程，接收Walsender发送过来的数据

发布

发布可以被定义在任何物理复制的主服务器上。定义有发布的节点被称为发布者。发布是从一个表或者一组表生成的改变的集合，也可以被描述为更改集合或者复制集合。每个发布都只存在于一个数据库中。

发布与模式不同，不会影响表的访问方式。如果需要，每个表都可以被加入到多个发布。当前，发布只能包含表。对象必须被明确地加入到发布，除非发布是用ALL TABLES创建的。

Publication可以选择把它们产生的更改限制为INSERT、UPDATE、DELETE以及TRUNCATE的任意组合，类似于触发器如何被特定事件类型触发的方式。默认情况下，所有操作类型都会被复制。

为了能够复制UPDATE和DELETE操作，被发布的表必须配置有一个“复制标识”，这样在订阅者那一端才能标识对于更新或删除合适的行。默认情况下，复制标识就是主键（如果有主键）。也可以在复制标识上设置另一个唯一索引（有特定的额外要求）。如果表没有合适的键，那么可以设置成复制标识“full”，它表示整个行都成为那个键。不过，这样做效率很低，只有在没有其他方案的情况下才应该使用。如果在发布者端设置了“full”之外的复制标识，在订阅者端也必须设置一个复制标识，它应该由相同的或者少一些的列组成。如何设置复制标识的细节请参考REPLICA IDENTITY。如果在复制UPDATE或DELETE操作的发布中加入了没有复制标识的表，那么订阅者上后续的UPDATE或DELETE操作将导致错误。不管有没有复制标识，INSERT操作都能继续下去。

每一个发布都可以有多个订阅者。

Publication通过使用CREATE PUBLICATION命令创建并且可以在之后使用相应的命令进行修改或者删除。

表可以使用ALTER PUBLICATION动态地增加或者移除。ADD TABLE以及DROP TABLE操作都是事务性的，因此一旦该事务提交，该表将以正确的快照开始或者停止复制。

表的列也通过名称匹配。订阅表中的列顺序不需要与发布表中的顺序一样。列的数据类型也不需要一样，只要可以将数据的文本表示形式转换为目标类型即可。例如，您可以从integer类型的列复制到bigint类型的列。目标表还可以具有发布表中不存在的额外列。额外列都将使用目标表的定义中指定的默认值填充。

复制槽

复制槽提供了一种自动化的方法来确保主控机在所有的后备机收到 WAL 段之前不会移除它们，并且主控机也不会移除可能导致恢复冲突的行，即使后备机断开也是如此。

作为复制槽的替代，也可以使用wal_keep_size阻止移除旧的 WAL 段，或者使用archive_command把段保存在一个归档中。不过，这些方法常常会导致保留的 WAL 段比需要的更多，而复制槽只保留已知所需要的段。另一方面，复制槽可以保留很多的WAL段以至于它们填满了分配给pg_wal的空间； max_slot_wal_keep_size限制复制槽所保留的WAL文件的大小。

类似地，hot_standby_feedback和 vacuum_defer_cleanup_age保护了相关行不被 vacuum 移除，但是前者在后备机断开期间无法提供保护，而后者则需要被设置为一个很高的值已提供足够的保护。复制槽克服了这些缺点。

查询和操纵复制槽

每个复制槽都有一个名字，名字可以包含小写字母、数字和下划线字符。

已有的复制槽和它们的状态可以在 pg_replication_slots 视图中看到。

槽可以通过流复制协议（见第 52.4 节）或者 SQL 函数（见第 9.27.6 节）创建并且移除。

配置实例

你可以这样创建一个复制槽：

postgres=# SELECT * FROM pg_create_physical_replication_slot('node_a_slot');
  slot_name  | lsn
-------------+-----
 node_a_slot |

postgres=# SELECT slot_name, slot_type, active FROM pg_replication_slots;
  slot_name  | slot_type | active 
-------------+-----------+--------
 node_a_slot | physical  | f
(1 row)

postgres=# SELECT * FROM pg_create_physical_replication_slot('node_a_slot');

slot_name | lsn

-------------+-----

node_a_slot |

postgres=# SELECT slot_name, slot_type, active FROM pg_replication_slots;

slot_name | slot_type | active

-------------+-----------+--------

node_a_slot | physical | f

(1 row)

要配置后备机使用这个槽，在后备机中应该配置primary_slot_name。这里是一个简单的例子：

primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
primary_slot_name = 'node_a_slot'

1 2	primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' primary_slot_name = 'node_a_slot'

复制槽管理

如早前所提到的，每一个（活跃的）订阅会从远（发布）端上的一个复制槽接收更改。通常，远程复制槽是在使用CREATE SUBSCRIPTION创建订阅是自动创建的，并且在使用DROP SUBSCRIPTION删除订阅时，复制槽也会自动被删除。不过，在一些情况下，有必要单独操纵订阅以及其底层的复制槽。下面是一些场景：

在创建一个订阅时，复制槽已经存在。在这种情况下，可以使用create_slot = false选项创建订阅并关联到现有的槽。
在创建一个订阅时，远程主机不可达或者处于一种不明状态。在这种情况下，可以使用connect = false选项创建订阅。那么远程主机将根本不会被联系。这是pg_dump所使用的方式。这样，在订阅可以被激活之前，必须手工创建远程复制槽。
在删除一个订阅时，复制槽应该被保留。当订阅者数据库正在被移动到一台不同的主机并且将从那里再被激活时，这种行为很有用。在这种情况下，可以在尝试删除该订阅之前，使用ALTER SUBSCRIPTION将复制槽解除关联。
在删除一个订阅是，远程主机不可达。在这种情况下，可以在尝试删除该订阅之前，使用ALTER SUBSCRIPTION将复制槽解除关联。如果远程数据库实例不再存在，那么不需要进一步的行动。不过，如果远程数据库实例只是不可达，那么复制槽应该被手动删除。否则它将会继续保留WAL并且最终可能会导致磁盘被填满。这种情况应该要仔细地研究。

冲突

逻辑复制的行为类似于正常的DML操作，即便数据在订阅者节点本地被修改，逻辑复制也会根据收到的更改来更新数据。如果流入的数据违背了任何约束，复制将停止。这种情况被称为一个冲突。在复制UPDATE或DELETE操作时，缺失的数据将不会产生冲突并且这类操作将被简单地跳过。

冲突将会产生错误并且停止复制，它必须由用户手工解决。在订阅者的服务器日志中可以找到有关冲突的详细情况。

通过更改订阅者上的数据（这样它就不会与到来的数据发生冲突）或者跳过与已有数据冲突的事务可以解决这种冲突。通过调用pg_replication_origin_advance()函数可以跳过该事务，函数的参数是对应于该订阅名称的node_name以及一个位置。复制源头的当前位置可以在pg_replication_origin_status系统视图中看到。

逻辑复制的限制

和版本有关系，请参考：http://postgres.cn/docs/14/logical-replication-restrictions.html

逻辑复制当前有下列限制或者缺失的功能。这些可能在未来的发行中解决。

数据库模式和DDL命令不会被复制。初始模式可以手工使用pg_dump --schema-only进行拷贝。后续的模式改变需要手工保持同步（不过值得注意的是，模式其实不需要在两端保持绝对相同）。当一个活跃的数据库中模式定义改变时，逻辑复制是鲁棒的：当模式在发布者上发生改变并且被复制的数据开始到达订阅者但却不适合表模式时，复制将报错，直至模式被更新。在很多情况下，可以通过先对订阅者应用额外的模式更改来避免间歇性的错误。
序列数据不被复制。后台由序列支撑的serial或者标识列中的数据当然将被作为表的一部分复制，但是序列本身在订阅者上仍将显示开始值。如果订阅者被用作一个只读数据库，那么这通常不会是什么问题。不过，如果订阅者数据库预期有某种转换或者容错，那么序列需要被更新到最后的值，要么通过从发布者拷贝当前数据的防范（也许使用pg_dump），要么从表本身决定一个足够高的值。
支持TRUNCATE命令的复制(从pg11开始，truncate语句可以被pg逻辑复制捕获)，但是在截断由外键连接在一起的表群体时必须要小心。在复制截断动作时，订阅者将截断与发布者上被截断的相同的表群体，这些表或者被明确指定或者通过CASCADE隐含地收集而来，然后还要减去不属于该订阅的表。如果所有受影响的表都属于同一个订阅，这会正确地工作。但是如果订阅者上要被截断的某些表有外键链接到不属于同一订阅的表，那么在订阅者上该截断动作的应用将会失败。
大对象（见第 34 章）不会被复制。没有办法可以解决这个问题，除非把数据存储在普通表中。
只有表支持复制，包括分区表。试图复制其他类型的关系，例如视图、物化视图或外部表，将会导致错误。
在分区表之间进行复制时，实际的复制来源，缺省情况下，源自发布者上的叶子分区，因此发布者上的分区也必须作为有效的目标表存在于订阅者上。 (它们可以是叶分区本身，也可以是进一步子分区段，甚至可以是独立的表。) 发布还可以指定使用已分区根表的标识和模式来复制更改，而不是使用实际产生更改的各个叶分区的标识和模式(参见CREATE PUBLICATION)。

架构

逻辑复制从拷贝发布者数据库上的数据库快照开始。拷贝一旦完成，发布者上的更改会在它们发生时实时传送给订阅者。订阅者按照数据在发布者上被提交的顺序应用数据，这样任意单一订阅中的发布的事务一致性才能得到保证。

逻辑复制被构建在一种类似于物理流复制（见第 26.2.5 节）的架构上。它由“walsender”和“apply”进程实现。walsender进程开始对WAL的逻辑解码（在第 48 章中描述）并且载入标准逻辑解码插件（pgoutput）。该插件把从WAL中读取的更改转换成逻辑复制协议（见第 52.5 节）并且根据发布说明过滤数据。然后数据会被连续地使用流复制协议传输到应用工作者，应用工作者会把数据映射到本地表并且以正确的事务顺序应用它们接收到的更改。

订阅者数据库上的应用进程总是将session_replication_role设置为replica运行，这会产生触发器和约束上通常的效果。

逻辑复制应用进程当前仅会引发行触发器，而不会引发语句触发器。不过，初始的表同步是以类似一个COPY命令的方式实现的，因此会引发INSERT的行触发器和语句触发器。

初始快照

已有的被订阅表中的初始数据会被快照并且以一种特殊类型的应用进程的并行实例进行拷贝。这种进程将创建自己的临时复制槽并且拷贝现有的数据。一旦现有的数据被拷贝完，工作者会进入到同步模式，主应用进程会流式传递在使用标准逻辑复制拷贝初始数据期间发生的任意改变，这会确保表被带到一种已同步的状态。一旦同步完成，该表的复制的控制权会被交回给主应用进程，其中复制会照常继续。

监控

因为逻辑复制是基于与物理流复制相似的架构的，一个发布节点上的监控也类似于对物理复制主节点（见第 26.2.5.2 节）的监控。

有关订阅的监控信息在pg_stat_subscription中可以看到。每一个订阅工作者在这个视图都有一行。一个订阅能有零个或者多个活跃订阅工作者取决于它的状态。

通常，对于一个已启用的订阅会有单一的应用进程运行。一个被禁用的订阅或者崩溃的订阅在这个视图中不会有行存在。如果有任何表的数据同步正在进行，对正在被同步的表会有额外的工作者。

安全性

能够修改订阅者侧模式的用户可以作为超级用户执行任意代码。将这些表上的所有权和TRIGGER特权限制为超级用户信任的角色。此外，如果不受信任的用户可以创建表，则只使用显式列出表的发布。也就是说，只有当超级用户信任允许在发布者或订阅者上创建非临时表的所有用户时，才创建FOR ALL TABLES的订阅。

用于复制连接的角色必须具有REPLICATION属性(或者是超级用户)。如果角色缺少SUPERUSER 和 BYPASSRLS，发布者的行安全策略可以执行。如果角色不信任全部表所有者，则在连接字符串中包含options=-crow_security=off;如果表所有者随后添加行安全策略，则该设置将导致复制中断，而不是执行该策略。角色的访问权限必须在pg_hba.conf中配置，并且必须具有LOGIN属性。

为了能够拷贝初始表数据，用于复制连接的角色必须在被发布的表上具有SELECT特权（或者是一个超级用户）。

要创建发布，用户必须在数据库中有CREATE特权。

要把表加入到一个发布，用户必须在该表上有拥有权。要创建一个自动发布所有表的发布，用户必须是一个超级用户。

要创建订阅，用户必须是一个超级用户。

订阅的应用过程将在本地数据库上以超级用户的特权运行。

特权检查仅在复制连接开始时被执行一次。在从发布者读到每一个更改记录时不会重新检查特权，在每一个更改被应用时也不会重新检查特权。

配置设置

逻辑复制要求设置一些配置选项。

在发布者端，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)。注意，一些扩展和并行查询也会从max_worker_processes中取得工作者槽。

快速设置

首先在postgresql.conf中设置配置选项：

wal_level = logical

1	wal_level = logical

对于一个基础设置来说，其他所需的设置使用默认值就足够了。

需要调整pg_hba.conf以允许复制（这里的值取决于实际的网络配置以及用于连接的用户）：

host     all     repuser     0.0.0.0/0     md5

1	host all repuser 0.0.0.0/0 md5

然后在发布者数据库上：

CREATE PUBLICATION mypub FOR TABLE users, departments;

1	CREATE PUBLICATION mypub FOR TABLE users, departments;

并且在订阅者数据库上：

CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION mypub;

1	CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION mypub;

上面的语句将开始复制过程，它会同步表users以及departments的初始表内容，然后开始复制对那些表的增量更改。

示例

环境规划

规划发布、订阅节点（可以是一台主机的两个实例，或者不同主机的两个实例）

-- 拉取镜像
docker pull postgres:13

-- 创建专用网络
docker network create --subnet=172.72.6.0/24 pg-network

-- 创建宿主机相关映射路径
mkdir -p /docker_data/pg/lhrpg64310/data
mkdir -p /docker_data/pg/lhrpg64311/data

-- PUBLICATION(发布) 
docker rm -f lhrpg64310
rm -rf /docker_data/pg/lhrpg64310/data
docker run -d --name lhrpg64310 -h lhrpg64310 \
   -p 64310:5432 --net=pg-network --ip 172.72.6.10 \
   -v /docker_data/pg/lhrpg64310/data:/var/lib/postgresql/data \
   -v /docker_data/pg/lhrpg64310/bk:/bk \
   -e POSTGRES_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   postgres:13

-- SUBSCRIPTION(订阅) 
docker rm -f lhrpg64311
rm -rf /docker_data/pg/lhrpg64311/data
rm -rf /docker_data/pg/lhrpg64311/bk
docker run -d --name lhrpg64311 -h lhrpg64311 \
   -p 64311:5432 --net=pg-network --ip 172.72.6.11 \
   -v /docker_data/pg/lhrpg64311/data:/var/lib/postgresql/data \
   -v /docker_data/pg/lhrpg64311/bk:/bk \
   -e POSTGRES_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   postgres:13

-- 远程登录
psql -U postgres -h 192.168.66.35 -p 64310
psql -U postgres -h 192.168.66.35 -p 64311

-- 拉取镜像

docker pull postgres:13

-- 创建专用网络

docker network create --subnet=172.72.6.0/24 pg-network

-- 创建宿主机相关映射路径

mkdir -p /docker_data/pg/lhrpg64310/data

mkdir -p /docker_data/pg/lhrpg64311/data

-- PUBLICATION(发布)

docker rm -f lhrpg64310

rm -rf /docker_data/pg/lhrpg64310/data

docker run -d --name lhrpg64310 -h lhrpg64310 \

-p 64310:5432 --net=pg-network --ip 172.72.6.10 \

-v /docker_data/pg/lhrpg64310/data:/var/lib/postgresql/data \

-v /docker_data/pg/lhrpg64310/bk:/bk \

-e POSTGRES_PASSWORD=lhr \

-e TZ=Asia/Shanghai \

postgres:13

-- SUBSCRIPTION(订阅)

docker rm -f lhrpg64311

rm -rf /docker_data/pg/lhrpg64311/data

rm -rf /docker_data/pg/lhrpg64311/bk

docker run -d --name lhrpg64311 -h lhrpg64311 \

-p 64311:5432 --net=pg-network --ip 172.72.6.11 \

-v /docker_data/pg/lhrpg64311/data:/var/lib/postgresql/data \

-v /docker_data/pg/lhrpg64311/bk:/bk \

-e POSTGRES_PASSWORD=lhr \

-e TZ=Asia/Shanghai \

postgres:13

-- 远程登录

psql -U postgres -h 192.168.66.35 -p 64310

psql -U postgres -h 192.168.66.35 -p 64311

发布节点参数配置

pg_hba.conf


-- 发布端pg_hba.conf:  host   all repuser  订阅端ip/32     trust
cat  << EOF > /docker_data/pg/lhrpg64310/data/pg_hba.conf
# TYPE  DATABASE    USER    ADDRESS       METHOD
local     all       all                    trust
host      all       all   127.0.0.1/32     trust
host      all     repuser   172.72.6.11/32   trust
host      all       all    0.0.0.0/0        md5
host   replication  all    0.0.0.0/0        md5
EOF

-- 发布端pg_hba.conf: host all repuser 订阅端ip/32 trust

cat << EOF > /docker_data/pg/lhrpg64310/data/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD

local all all trust

host all all 127.0.0.1/32 trust

host all repuser 172.72.6.11/32 trust

host all all 0.0.0.0/0 md5

host replication all 0.0.0.0/0 md5

EOF

postgresql.conf


-- 配置归档
docker exec -it lhrpg64310 bash

-- 发布端postgresql.conf配置:wal_level = logical & listen_addresses = '*'
alter system set wal_level='logical';
alter system set listen_addresses='*';
SELECT pg_reload_conf();

-- 重启
docker restart lhrpg64310

psql -U postgres -h 192.168.66.35 -p 64310

select * from pg_settings where name in ('wal_level','archive_mode','archive_command','listen_addresses','max_replication_slots','max_wal_senders');

-- 切换归档
select pg_switch_wal();

-- 查看日志
docker logs -f lhrpg64310
docker logs -f lhrpg64311

-- 配置归档

docker exec -it lhrpg64310 bash

-- 发布端postgresql.conf配置:wal_level = logical & listen_addresses = '*'

alter system set wal_level='logical';

alter system set listen_addresses='*';

SELECT pg_reload_conf();

-- 重启

docker restart lhrpg64310

psql -U postgres -h 192.168.66.35 -p 64310

select * from pg_settings where name in ('wal_level','archive_mode','archive_command','listen_addresses','max_replication_slots','max_wal_senders');

-- 切换归档

select pg_switch_wal();

-- 查看日志

docker logs -f lhrpg64310

docker logs -f lhrpg64311

发布节点创建复制用户和表

-- 用于逻辑复制的用户必须至少是replication 角色
CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'lhr';

grant connect on database postgres to repuser;
grant select on test to repuser;
grant USAGE on SCHEMA public to repuser;

create table test(id int4 primary key ,name text); 
insert into test values (1,'a');
\dp+ test;

-- 用于逻辑复制的用户必须至少是replication 角色

CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'lhr';

grant connect on database postgres to repuser;

grant select on test to repuser;

grant USAGE on SCHEMA public to repuser;

create table test(id int4 primary key ,name text);

insert into test values (1,'a');

\dp+ test;

发布节点创建发布


create PUBLICATION pub1 FOR TABLE test;       #为表test创建发布
select * from pg_publication;         #查看创建的发布
pubname：指发布的名称
pubowner：指发布的属主，可以和pg_user视图的usesysid字段关联查询得到属主具体信息
puballtables：是否发布数据库中的所有表，t表示发布数据库中所有已存在的表和以后新建的表
pubinsert：t表示仅发布表上的INSERT操作
pubupdate：t表示仅发布表上的UPDATE操作
pubdelete：t表示仅发布表上的DELETE操作

-- 无主键或者唯一性约束时开启补全日志，此时会导致复制效率很低
-- alter table test replica identity full;

create PUBLICATION pub1 FOR TABLE test; #为表test创建发布

select * from pg_publication; #查看创建的发布

pubname：指发布的名称

pubowner：指发布的属主，可以和pg_user视图的usesysid字段关联查询得到属主具体信息

puballtables：是否发布数据库中的所有表，t表示发布数据库中所有已存在的表和以后新建的表

pubinsert：t表示仅发布表上的INSERT操作

pubupdate：t表示仅发布表上的UPDATE操作

pubdelete：t表示仅发布表上的DELETE操作

-- 无主键或者唯一性约束时开启补全日志，此时会导致复制效率很低

-- alter table test replica identity full;

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

如果需发布多张表则表名间用逗号（,）分割，如果需发布所有库，则将FOR TABLE 调整为FOR ALL TABLES。

订阅节点

-- 订阅节点创建接收表及订阅
create table test(id int4 primary key ,name text);
create subscription sub1 connection 'host=172.72.6.10 port=5432 dbname=postgres user=repuser password=lhr' publication pub1;
select * from pg_subscription;

-- 此时在主库可以查询到复制槽
SELECT slot_name,plugin,slot_type,database,active,restart_lsn    FROM pg_replication_slots where slot_name='sub1';
 slot_name |  plugin  | slot_type | database | active | restart_lsn
-----------+----------+-----------+----------+--------+-------------
 sub1      | pgoutput | logical   | postgres | f      | 0/15ED338
(1 row)

-- 订阅节点创建接收表及订阅

create table test(id int4 primary key ,name text);

create subscription sub1 connection 'host=172.72.6.10 port=5432 dbname=postgres user=repuser password=lhr' publication pub1;

select * from pg_subscription;

-- 此时在主库可以查询到复制槽

SELECT slot_name,plugin,slot_type,database,active,restart_lsn FROM pg_replication_slots where slot_name='sub1';

-----------+----------+-----------+----------+--------+-------------

sub1 | pgoutput | logical | postgres | f | 0/15ED338

(1 row)

测试同步


--  发布节点分别向表中插入、删除数据
insert into test values (6,'e');
delete from test where id=1;

-- 订阅节点查看逻辑复制效果
select * from  test ;

-- 发布节点分别向表中插入、删除数据

insert into test values (6,'e');

delete from test where id=1;

-- 订阅节点查看逻辑复制效果

select * from test ;

大表同步

-- 1.  发布节点修改发布，测试插入10万数据的逻辑同步
create table test_big2(id int4 primary key, create_time timestamp without time zone default clock_timestamp(), name character varying(32));
insert into test_big2(id,name) select n,n*random()*10000 from generate_series(1,100000) n ; 
grant select on test_big2 to repuser;
ALTER PUBLICATION pub1 add TABLE test_big2;

-- 2.  订阅节点创建同步表并手动执行刷新命令
create table test_big2(id int4 primary key, create_time timestamp without time zone default clock_timestamp(), name character varying(32));
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION ;

-- 3.   逻辑同步结果
select count(*) from test_big2 ;

-- 1. 发布节点修改发布，测试插入10万数据的逻辑同步

create table test_big2(id int4 primary key, create_time timestamp without time zone default clock_timestamp(), name character varying(32));

insert into test_big2(id,name) select n,n*random()*10000 from generate_series(1,100000) n ;

grant select on test_big2 to repuser;

ALTER PUBLICATION pub1 add TABLE test_big2;

-- 2. 订阅节点创建同步表并手动执行刷新命令

create table test_big2(id int4 primary key, create_time timestamp without time zone default clock_timestamp(), name character varying(32));

ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION ;

-- 3. 逻辑同步结果

select count(*) from test_big2 ;

10万数据同步大概用了不到30秒，速度很快，在同步过程中看到了 COPY 进程，推测在数据初始化时用的是 copy

运维监控

alter publication pub1 add table test.tab1;#从节点需要刷新才能看到新添加表
alter subscription pub1 refresh publication; #刷新订阅
drop pubilcation pub1; #删除发布
select * from pg_publication;#发布
select * from pg_publication_tables;#发布哪些表
select * from pg_stat_replication;#所有的订阅者
select * from pg_stat_subscription;#复制进度
select * from pg_subscription; #订阅
select *,srrelid::regclass from pg_subscription_rel; #订阅哪些表
drop subscription pub1;#删除订阅

alter publication pub1 add table test.tab1;#从节点需要刷新才能看到新添加表

alter subscription pub1 refresh publication; #刷新订阅

drop pubilcation pub1; #删除发布

select * from pg_publication;#发布

select * from pg_publication_tables;#发布哪些表

select * from pg_stat_replication;#所有的订阅者

select * from pg_stat_subscription;#复制进度

select * from pg_subscription; #订阅

select *,srrelid::regclass from pg_subscription_rel; #订阅哪些表

drop subscription pub1;#删除订阅

总结

publication - 发布者

逻辑复制的前提是将数据库 wal_level 参数设置成 logical；
源库上逻辑复制的用户必须具有 replicatoin 或 superuser 角色；
逻辑复制目前仅支持数据库表逻辑复制，其它对象例如函数、视图不支持；
逻辑复制支持DML(UPDATE、INSERT、DELETE)操作，TRUNCATE从PG11开始被支持（PG 11之前的版本不支持），DDL 操作不支持；
需要发布逻辑复制的表，须配置表的 REPLICA IDENTITY 特性，或者逻辑同步的表两端含有主键约束；
一个数据库中可以有多个publication，通过 pg_publication 查看；
允许一次发布所有表，语法： CREATE PUBLICATION alltables FOR ALL TABLES;

subscription - 订阅者

订阅节点需要指定发布者的连接信息；
一个数据库中可以有多个订阅者；
可以使用enable/disable启用/暂停该订阅；
发布节点和订阅节点表的模式名、表名必须一致，订阅节点允许表有额外字段；
发布节点增加表字段，订阅节点需要执行： ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION

流复制与逻辑复制主要差异：

流复制是物理复制，核心原理是主库将预写日志WAL日志流发送给备库，备库接收到WAL日志流后进行重做
流复制只能对PG实例级进行复制，而逻辑复制能够对数据库表级记性复制
流复制能对DDL操作进行复制，逻辑复制主库上的DDL操作不会复制到备库
流复制主库可读写，但从库只允许查询不允许写入，而逻辑复制的从库可读写
流复制要求PG大版本必须一致，逻辑复制支持跨PG大版本

逻辑复制和流复制各自的应用领域不同：

流复制只能做到整个cluster的复制，逻辑复制则更加灵活，可以做到表级同步，另外cybertc提供了一个patial replication的插件walbouncer，可以对WAL日志进行过滤，允许进行部分数据库的复制，而不是整个实例：https://www.cybertec-postgresql.com/en/products/walbouncer-partial-replication/ ，如下图：
流复制的standby备库是只读的，不能写入，而逻辑复制读写都可以。
逻辑复制支持在不同版本数据库之间进行逻辑复制，所以可以利用来进行跨版本升级。
支持异构架构之前的同步，比如Linux -> Windows