PostgreSQL物理复制的部署,其有如下主要优点

• 物理层面完全一致，是主要的复制方式，其类似于Oracle的DG
• 延迟低，事务执行过程中产生REDO record，实时的在备库apply，事务结束时，备库立马能见到数据
• 物理复制的一致性、可靠性高，不必担心数据逻辑层面不一致

但是其又在实际使用的场景中存在一些无法满足的需求，例如：

• 无法满足指定库或部分表的复制需求
• 将多个数据库实例的数据汇聚到同一个目标库或将一个库的数据分发到多个不同的库
• 不同的版本之间的复制
• 不同库名之间的表同步
  对于以上场景,物理复制时无法满足的，因此逻辑复制应运而生了。
  逻辑复制的复制架构图如下：

逻辑复制是基于逻辑解析，其核心原理是逻辑主库将Publication中表的WAL日志解析成一定格式并发送给逻辑备库，逻辑备库Subscription接收到解析后的WAL日志后进行重做，从而实现表数据同步。

2. 逻辑复制的部署

PS： 以下的逻辑从库可以在新的机器上部署，如在原先的从库上修改，需停止原实例，并将recovery.conf文件删除或重命名（如修改为recovery.conf.done）

2.1 修改主库的postgresql.conf

可以基于原先的物理复制的配置文件进行修改,配置逻辑复制主要需调整如下参数

wal_level = logical
max_wal_senders = 10
max_replication_slots = 8

参数简要说明如下
wal_level：设置成logical才支持逻辑复制
max_wal_senders：由于每个订阅节点和流复制备库在主库上都会占用主库上一个WAL发送进程，因此此参数设置值需大于max_replication_slots参数值加上物理备库数量
max_replication_slots：设置值需大于订阅节点的数量

2.2 修改逻辑从库的postgresql.conf

逻辑从库的postgresql.conf也可以在物理复制的基础上修改，与主库不同的是主要修改如下参数

wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8

参数简要说明
wal_level：设置成logical才支持逻辑复制,逻辑从库可以视情况设置
max_replication_slots：设置数据库复制槽数量，应大于订阅节点的数量
max_logical_replication_workers：设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量，此参数默认值为4

2.3 创建逻辑复制账号

lightdb@postgres=# alter USER test REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'test'; CREATE ROLE

逻辑复制用户需要REPLICATION权限即可，可以不需要SUPERUSER权限，之后需要在发布节点上将需要同步的表赋权给logical_repl用户，使logical_repl账号具有对这些表的读权限。

2.4 在逻辑主库上创建库及表

/** 创建用于逻辑复制的主库 */ lightdb@postgres=# \c test testPassword for user test: 
You are now connected to database "test" as user "test".
test@test=> create schema test;CREATE SCHEMAtest@test=> create table test (id int,name varchar(100)) ;CREATE TABLE

2.5 逻辑从库上创建库及表

/** 在逻辑从库上创建不同的库 */lightdb@lt_test=# \c  test_subscription test_subscriptionPassword for user test_subscription: 
You are now connected to database "test_subscription" as user "test_subscription".
test_subscription@test_subscription=> create table test_subscription (id int,name varchar(100));CREATE TABLE

注：逻辑复制的表结构需要手动在从库创建

2.6 在逻辑主库上创建发布

/** 在发布主库上创建发布pub1,注意实在test库下执行 */test@test=> CREATE PUBLICATION pub1 FOR TABLE test; 
CREATE PUBLICATION

如果需发布多张表 则表名间用逗号（,）分割，如果需发布所有库，则将FOR TABLE 调整为FOR ALL TABLES。
此时可以查看到如下信息

test@test=> SELECT * FROM pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 24523 | pub1    |    24512 | f            | t         | t         | t         | t           | f
(1 row)

字段说明如下：

• pubname： 指发布的名称
• pubowner： 指发布的属主，可以和pg_user视图的usesysid字段关联查询得到属主具体信息
• puballtables：是否发布数据库中的所有表，t表示发布数据库中所有已存在的表和以后新建的表
• pubinsert： t表示仅发布表上的INSERT操作
• pubupdate： t表示仅发布表上的UPDATE操作
• pubdelete： t表示仅发布表上的DELETE操作

2.7 在逻辑从库上创建订阅

lightdb@lt_test=# CREATE SUBSCRIPTION sub1 CONNECTION 'hostaddr=10.20.30.10 port=5432 user=test dbname=test password=test' PUBLICATION pub1;ERROR:  could not connect to the publisher: FATAL:  must be superuser or replication role to start walsender-- 在主库上更改test用户的权限lightdb@postgres=# alter USER test REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'test'; ALTER ROLElightdb@lt_test=# CREATE SUBSCRIPTION sub1 CONNECTION 'hostaddr=10.20.30.10 port=5432 user=test dbname=test password=test' PUBLICATION pub1;ERROR:  schema "test" does not exist
报错 schema test不存在，说明发布端和订阅段用户名和库可以不相同，但是表所在的schema名字必须相同，重建订阅端表
test_subscription@test_subscription=> create table test.test_subscription(id int,name varchar(100));CREATE TABLElightdb@test_subscription=# CREATE SUBSCRIPTION sub1 CONNECTION 'hostaddr=10.20.30.10 port=5432 user=test dbname=test password=test' PUBLICATION pub1;ERROR:  relation "test.test" does not exist
lightdb@test_subscription=# create table test.test(id int,name varchar(100));CREATE TABLElightdb@test_subscription=# CREATE SUBSCRIPTION sub1 CONNECTION 'hostaddr=10.20.30.10 port=5432 user=test dbname=test password=test' PUBLICATION pub1;NOTICE:  created replication slot "sub1" on publisherCREATE SUBSCRIPTION

创建成功后，可以在逻辑主库上查询到如下信息：

test@test=> 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      | ltoutput | logical   | test     | t      | 0/23E902D0
(1 row)

在逻辑从库上可以查询到如下信息：

lightdb@test_subscription# SELECT * FROM pg_subscription;
  oid  | subdbid | subname | subowner | subenabled |                            subconninfo                             | subslotname | subsynccommit | subpu
blications 
-------+---------+---------+----------+------------+--------------------------------------------------------------------+-------------+---------------+-----------------
 24283 |   24225 | sub1    |       10 | t          | hostaddr=10.20.30.10 port=5432 user=test dbname=test password=test | sub1        | off           | {pub1
}
(1 row)

2.8 给复制账号授权

如果不是同用户的，需要将表给到publication用户查询权限
因为步骤2.3中只创建了复制账号并未对需要复制的表进行授权，因此，此时逻辑从库的日志中有如下错误
2023-02-14 15:57:21.847 CST [27443] ERROR: could not start initial contents copy for table “test.test”: ERROR: permission denied for table test
2023-02-14 15:57:21.848 CST [24722] LOG: background worker “logical replication worker” (PID 27443) exited with exit code 1
因此 ，现在需要对复制用户授权。

sourcedb=# GRANT USAGE ON SCHEMA public TO test;GRANT sourcedb=# GRANT SELECT ON logical_tb1 TO test; GRANT

授权后,显示正常
2023-02-14 16:00:25.959 CST [28204] LOG: logical replication table synchronization worker for subscription “sub1”, table “test” has started
2023-02-14 16:00:25.967 CST [28204] LOG: logical replication table synchronization worker for subscription “sub1”, table “test” has finished

2.9 测试数据同步

在逻辑主库插入数据

/** 在主库插入数据 */ alter table test add  primary key (id);ALTER TABLEtest@test=> insert into test values (1,'aaa');INSERT 0 1

在逻辑从库查看结果

/** 查看数据是否同步完成 */ lightdb@test_subscription=# select * from test.test;
 id | name 
----+------
  1 | aaa
(1 row)

由此可见数据已同步完成。

2.10 添加复制所需的表

在逻辑主库和逻辑从库均添加一张新表，并添加到发布列表中

/** 主库上创建表结构 */ sourcedb=# create table logical_tb2(id int primary key ,addr varchar(100)); CREATE TABLE sourcedb=# /** 从库上创建表结构 */desdb=# create table logical_tb2(id int primary key ,addr varchar(100)); CREATE TABLE /** 在主库上给逻辑复制账号授权 */ sourcedb=# GRANT SELECT ON logical_tb2 TO logical_repl; GRANT/** 添加新表至发布列表 */ sourcedb=# ALTER PUBLICATION pub1 ADD TABLE logical_tb2; ALTER PUBLICATION 
/** 在主库查看发布列表中的表名 */sourcedb=# SELECT * FROM pg_publication_tables;
 pubname | schemaname |  tablename  
---------+------------+-------------
 pub1    | public     | logical_tb1
 pub1    | public     | logical_tb2
(2 rows)

此时已加入一张表进入发布列表中。
此时在主库写入数据，查看从库情况如下：

/** 主库插入一条记录 */ sourcedb=# insert into logical_tb2(id,addr) values(1,'beijing'); INSERT 0 1 /** 此时在逻辑从库查看，结果却没有数据 */desdb=# select  * from logical_tb2;
 id | addr 
----+------(0 rows)

因为还需要在从库刷新一下订阅

/** 此时在从库刷新订阅 */ test@test=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;/** 刷新完成后再查数据已经有数据了 */ ALTER SUBSCRIPTION
desdb=# select  * from logical_tb2;
 id |  addr   
----+---------
  1 | beijing
(1 row)

至此，PostgreSQL的逻辑复制也部署完毕。

3. 物理复制与逻辑复制特点和应用场景

PostgreSQL的逻辑复制与物理复制的差异比较突出，在使用中可以根据其特点选择使用哪种复制方式。

• 逻辑订阅，适合于发布端与订阅端都有读写的情况。
• 逻辑订阅，更适合于小事务，或者低密度写（轻度写）的同步。如果有大事务、高密度写，逻辑订阅的延迟相比物理复制更高。
• 逻辑订阅，适合于双向，多向同步；但是 schema名和表名订阅端必须和发布端相同
• 物理复制，适合于单向同步。
• 物理复制，适合于任意事务，任意密度写（重度写）的同步。
• 物理复制，适合于HA、容灾、读写分离。
• 物理复制，适合于备库没有写，只有读的场景。