当前位置：首页 > article >正文

PostgreSQL16 的双向逻辑复制

article 2025/3/14 21:55:54

一、配置双向逻辑复制具体步骤

参考:PostgreSQL 16 双向逻辑复制与事务回环控制 - 墨天轮

1. 安装和准备环境

确保在所有参与复制的服务器上都安装了 PostgreSQL 16。
主服务器：192.168.0.100
从服务器：192.168.0.102

2. 配置 PostgreSQL

在每个服务器上执行以下操作：

a. 修改 `postgresql.conf

在文件最后添加以下配置:

wal_level = logical

max_replication_slots = 10

max_wal_senders = 10

max_logical_replication_workers = 4

#shared_preload_libraries = ‘pglogical’

注意:

wal_level：设置 wal_level 为 logical，这是启用逻辑复制的必要条件。
max_replication_slots：设置一个足够大的值，以允许足够的复制槽（replication slots）。
max_wal_senders：设置一个足够大的值，以允许足够的 WAL 发送进程。
max_logical_replication_workers：设置一个足够大的值，以允许足够的逻辑复制工作者。
shared_preload_libraries：添加 pglogical（如果你使用 pglogical 扩展）或其他任何逻辑复制扩展。postgresql10以后自身具有逻辑复制功能，即发布和订阅，但是之前的版本如果想要表级别的同步，需要pglogic插件实现。

b. 修改 `pg_hba.conf

配置适当的认证规则，允许其他服务器连接到当前服务器进行复制。
图中开放所有端口连接

c. 修改配置后需要重启postgresql

如果开始配置的时候就没有启动postgresql,就无需重启,直接启动即可

3. 创建复制用户

在每个服务器上创建一个专门用于复制的用户。
\du 用于列出所有角色以及它们的相关信息

复制

CREATE USER repuser replication LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'repuser';

4. 配置双向复制

在两个服务器上都创建发布和订阅，以便它们可以相互复制。

a. 在服务器 A 上：

创建发布，指向服务器 B。
创建订阅，从服务器 B 接收数据。

b. 在服务器 B 上：

创建发布，指向服务器 A。
创建订阅，从服务器 A 接收数据。

5. 初始化复制

a. 创建发布

一个发布只能存在于一个数据库中，所以创建发布者时，需要先指定数据库。

主服务器复制

CREATE PUBLICATION aspub1 FOR ALL TABLES;
CREATE PUBLICATION nspub1 FOR ALL TABLES;
CREATE PUBLICATION hrpub1 FOR ALL TABLES;

从服务器复制

CREATE PUBLICATION aspub2 FOR ALL TABLES;
CREATE PUBLICATION nspub2 FOR ALL TABLES;
CREATE PUBLICATION hrpub2 FOR ALL TABLES;

b. 创建订阅

主服务器。复制

CREATE SUBSCRIPTION hrsub1 
CONNECTION 'host=192.168.0.102 port=5432 user=repuser password=repuser dbname=postgres' 
PUBLICATION hrpub2 
WITH (origin = none, copy_data = false);

CREATE SUBSCRIPTION assub1 
CONNECTION 'host=192.168.0.102 port=5432 user=repuser password=repuser dbname=loraserver_as' 
PUBLICATION aspub2 
WITH (origin = none, copy_data = false);

CREATE SUBSCRIPTION nssub1 
CONNECTION 'host=192.168.0.102 port=5432 user=repuser password=repuser dbname=loraserver_ns' 
PUBLICATION nspub2 
WITH (origin = none, copy_data = false);

从服务器。复制

CREATE SUBSCRIPTION hrsub2 
CONNECTION 'host=192.168.0.100 port=5432 user=repuser password=repuser dbname=postgres' 
PUBLICATION hrpub1 
WITH (origin = none, copy_data = false);

指定在复制启动后是否应复制正在订阅的发布中的现有数据。默认值是

CREATE SUBSCRIPTION assub2 
CONNECTION 'host=192.168.0.100 port=5432 user=repuser password=repuser dbname=loraserver_as' 
PUBLICATION aspub1 
WITH (origin = none, copy_data = false);

CREATE SUBSCRIPTION nssub2 
CONNECTION 'host=192.168.0.100 port=5432 user=repuser password=repuser dbname=loraserver_ns' 
PUBLICATION nspub1 
WITH (origin = none, copy_data = false);

c. 查看创建

-- 列出所有发布
SELECT * FROM pg_publication;
-- 列出所有订阅
SELECT * FROM pg_subscription;

d. 删除创建

-- 断开订阅（如果它正在运行）
ALTER SUBSCRIPTION your_subscription_name DISABLE;

-- 删除发布
DROP PUBLICATION your_publication_name;

-- 删除订阅
DROP SUBSCRIPTION your_subscription_name;

e. 具体截图

主机截图

从机截图

6. 验证复制

a.使用navicat连接两个数据库

b.在主机192.168.1.181中插入数据,看从机192.168.1.182是否发生变化

主机新建user表,在从机中出现user表

c.在从机192.168.1.182中插入数据,看主机192.168.1.181是否发生变化

从机插入数据

主机更新数据

注意事项

确保使用 WITH (ORIGIN = NONE) 参数来避免复制循环。
考虑网络延迟和带宽对复制性能的影响。
确保数据一致性和完整性。

二、梳理原理图

一个发布只能存在于一个数据库中，一个订阅者只能连接到一个数据库。

参考：https://blog.csdn.net/xuejianxinokok/article/details/135099053PostgreSQL 16 的新功能：双向逻辑复制_postgresql16中的新增功能:双向逻辑复制-CSDN博客

说明：Postgres 16 添加了一个 WITH 语句的新参数，用于过滤掉某些节点的复制。双向逻辑复制使用此参数WITH(ORIGIN = NONE)，这会过滤掉来自非NONE 源的连接的所有复制。本质上，这只允许复制新添加的数据，您可能会明白为什么会出现这种情况。如果一个数据库插入新数据并将其复制到第二个数据库，则第二个数据库将复制该数据并插入它，从而触发对原始数据库的另一次复制。我们很快就会得到无限的复制循环，这就是为什么这个选项对于保持一切有限是必要的。

三、新增一张表步骤

主从pgsql上个创建tb_test表：

create table tb_test(id int primary key ,name varchar(100));

主pgsql上，授权复制用户对新表的SELECT权限：

GRANT SELECT ON tb_test TO repuser;

注意：如果主从pgsql都给复制用户默认授予了SELECT权限，本步骤可以省略：

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO repuser;

主从pgsql上刷新各自的订阅者，启动同步（如果表里没有数据，copy_data可设为false，就不会去复制初始数据。如果有数据，那一定要设为true，或者不设默认为true，否则会报错。报错时只要把数据都清掉就可以）：

ALTER SUBSCRIPTION hrsub1 REFRESH PUBLICATION WITH (copy_data = false);

ALTER SUBSCRIPTION hrsub2 REFRESH PUBLICATION WITH (copy_data = false);

==================================================

四、碰到的问题

日志中文乱码

参考这边文章，修改postgresql.conf文件的下列配置，改成英文：PostgreSQL-解决连接时遇到的乱码问题_postgresql utf8-CSDN博客

lc_messages = ‘en_US.UTF-8’ # locale for system error message
lc_monetary = ‘en_US.UTF-8’ # locale for monetary formatting
lc_numeric = ‘en_US.UTF-8’ # locale for number formatting
lc_time = ‘en_US.UTF-8’ # locale for time formatting

permission denied for table user

从机（订阅者）的pgsql日志上报错：

参考：https://www.51cto.com/article/749596.html

**原因：**没有给复制用户授予对user表的select权限。

**解决方法：**在主机（发布者）上，执行授权sql语句：GRANT SELECT ON public.user TO repuser;

延伸知识：

创建复制用户

选择数据库，创建复制用户：repuser

CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'repuser';

limit 8 ???

授予对公共模式的使用权限

在主机上操作：

GRANT USAGE ON SCHEMA public TO repuser;

能重复执行吗？

授予对表的读权限

在主机上操作：为了确保逻辑复制能够正常进行，还需要授予用户repuser对特定表user的读权限。

GRANT SELECT ON public.user TO repuser;

设置默认权限

为了简化未来的权限管理，可以设置默认权限，使得新创建的表自动授予该用户读权限。

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO repuser;

添加表

在逻辑主库和逻辑从库手动添加表后，需要在主机把表添加到发布列表中。

ALTER PUBLICATION hrpub1 ADD TABLE logical_tb2;

在主库查看发布列表中的表明。

SELECT * FROM pg_publication_tables;

刷新订阅

使用REFRESH PUBLICATION命令来刷新订阅的发布信息，并确保所有表都被正确复制，避免潜在的数据丢失。

ALTER SUBSCRIPTION hrsub1 REFRESH PUBLICATION;

duplicate key value violates unique constraint “t2_pkey”

把冲突的那行数据删除。

如果有自增主键冲突，参考：postgresql增序列报错问题ERROR: duplicate key value violates unique constraint “sys_department_pkey”_dumplicate key values-CSDN博客

copy_data尽量用false

在创建订阅者和刷新订阅者时，如果把copy_data = true，它会先复制主机（发布者）的初始数据，如果从机（订阅者）的数据库里有数据时，会造成冲突。

只有在从机（订阅者）的数据库里没有数据，需要复制初始数据时，才需要设为true。

创建订阅者

CREATE SUBSCRIPTION nssub2

CONNECTION ‘host=192.168.0.100 port=5432 user=repuser password=repuser dbname=loraserver_ns’

PUBLICATION nspub1

WITH (origin = none, copy_data = false);

刷新订阅者

ALTER SUBSCRIPTION nssub2 REFRESH PUBLICATION with (copy_data = false);

【碰到的问题】

问题描述：

创建订阅者时，如果没有带上WITH (origin = none, copy_data = false)，默认copy_data = true。会生成很多复制槽，应该是同步数据时需要。

导致复制槽数量超上限（默认10个）。后续再创建其它订阅者时，因为创建不了复制槽而失败。

解决办法：

首先，删除订阅者，它会同步删除对应的复制槽。然后加上WITH (origin = none, copy_data = false)重新创建订阅者。

删除订阅者

在订阅者数据库侧，先禁用再删除：

ALTER SUBSCRIPTION assub2 DISABLE;

DROP SUBSCRIPTION assub2;

在发布者数据库侧，删除复制槽：

SELECT pg_drop_replication_slot(‘assub2’);

创建发布者

需要选择数据库，否则就会创建再默认的progres数据库下。

查询发布者相关信息时，也需要选择数据库，否则只查到progres数据库下的。

select * from pg_publication;

SELECT * FROM pg_publication_tables;

用到的sql记录

select * from pg_publication;

select * from pg_subscription;

select * from pg_user;

select * from pg_replication_slots;

SELECT * FROM pg_publication_tables;

SELECT * FROM pg_stat_replication;

SELECT * FROM pg_stat_subscription;

GRANT SELECT ON api_key, application, code_migration, device, device_keys, device_multicast_group, device_profile, gateway,

gateway_ping, gateway_ping_rx, gateway_profile, integration, multicast_group, network_server, organization,

organization_user, schema_migrations, service_profile, public.user TO repuser;

GRANT USAGE ON SCHEMA public TO repuser;

create table logical_tb2(id int primary key ,addr varchar(100));

GRANT SELECT ON logical_tb2 TO repuser;

ALTER SUBSCRIPTION nssub1 REFRESH PUBLICATION with (copy_data = false);

SELECT pg_get_serial_sequence(‘t2’, ‘aa’);

create table tb_test(id int primary key ,name varchar(100));

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO repuser;

SELECT * FROM pg_stat_subscription;

select * from pg_stat_replication;

select * from pg_stat_subscription;

SELECT COUNT(*) FROM pg_stat_activity;

SHOW max_connections;

SELECT rolname, rolconnlimit FROM pg_roles WHERE rolname = ‘repuser’;

ALTER ROLE repuser CONNECTION LIMIT 10;

CREATE SUBSCRIPTION assub1

CONNECTION ‘host=192.168.0.102 port=5432 user=repuser password=repuser dbname=loraserver_as’

PUBLICATION aspub2

WITH (origin = none, copy_data = false);

CREATE SUBSCRIPTION nssub1

CONNECTION ‘host=192.168.0.102 port=5432 user=repuser password=repuser dbname=loraserver_ns’

PUBLICATION nspub2

WITH (origin = none, copy_data = false);

CREATE SUBSCRIPTION assub2

CONNECTION ‘host=192.168.0.100 port=5432 user=repuser password=repuser dbname=loraserver_as’

PUBLICATION aspub1

WITH (origin = none, copy_data = false);

CREATE SUBSCRIPTION nssub2

CONNECTION ‘host=192.168.0.100 port=5432 user=repuser password=repuser dbname=loraserver_ns’

PUBLICATION nspub1

WITH (origin = none, copy_data = false);

CREATE PUBLICATION aspub1 FOR ALL TABLES;

CREATE PUBLICATION nspub1 FOR ALL TABLES;

CREATE PUBLICATION aspub2 FOR ALL TABLES;

CREATE PUBLICATION nspub2 FOR ALL TABLES;

ALTER SUBSCRIPTION assub1 SET PUBLICATION aspub2;

ALTER SUBSCRIPTION nssub1 SET PUBLICATION nspub2;

ALTER SUBSCRIPTION assub2 SET PUBLICATION aspub1;

ALTER SUBSCRIPTION nsSub2 SET PUBLICATION nspub1;

SELECT * FROM pg_subscription;

DROP SUBSCRIPTION assub2;

ALTER SUBSCRIPTION assub2 DISABLE;

SELECT pg_drop_replication_slot(‘pg_90159_sync_33441_7399912264007474316’);

DROP PUBLICATION aspub1;

查看全文

http://www.kler.cn/a/584674.html

Netty基础—4.NIO的使用简介一

【贪心算法5】

使用DeepSeek完成一个简单嵌入式开发

如何优化AI模型的Prompt：深度指南

基于jvisualvm的内存监控与远程连接配置指南

K8s 1.27.1 实战系列（十）PV PVC

C# Unity 唐老狮 No.9 模拟面试题

Vue：其他指令

Qt的QMenu 和 QAction的样式设置

golang从入门到做牛马:第二十篇-Go语言接口：行为的“契约”

C#类型转换大总结

4.3 数组和集合的初始及赋值

云原生可观测性：智能运维的数据中枢

DeepSeek-R1深度解读

HarmonyOS开发 - 电商App实例三（网络请求axios）

nginx中proxy_pass和root的区别

STL-List模拟

【QT】:QT图形化界面相关准备工作

【python运行Janus-Pro-1B文生图功能】

开源！速度100Kb/s的有线和无线双模ESP32S3芯片的DAP-Link调试器

一、配置 双向逻辑复制具体步骤

1. 安装和准备环境

2. 配置 PostgreSQL

a. 修改 `postgresql.conf

b. 修改 `pg_hba.conf

c. 修改配置后需要重启postgresql

3. 创建复制用户

4. 配置双向复制

a. 在服务器 A 上：

b. 在服务器 B 上：

5. 初始化复制

a. 创建发布

b. 创建订阅

c. 查看创建

d. 删除创建

e. 具体截图

6. 验证复制

注意事项

二、梳理原理图

三、新增一张表步骤

四、碰到的问题

日志中文乱码

permission denied for table user

创建复制用户

授予对公共模式的使用权限

授予对表的读权限

设置默认权限

添加表

刷新订阅

duplicate key value violates unique constraint “t2_pkey”

copy_data尽量用false

删除订阅者

创建发布者

用到的sql记录

相关文章：

一、配置双向逻辑复制具体步骤