PostgreSQL能否在同一服务器上创建Publication与Subscription？

可以在同一PostgreSQL数据库内配置发布（Publisher）和订阅（Subscriber）来实现schema的只读副本，以下是无需额外服务器的最佳实现步骤：

1. 准备源与目标Schema #

首先创建用于存放只读副本的目标schema，并复制源schema的结构（表、约束等）：

-- 创建目标只读schema
CREATE SCHEMA readonly_schema;

通过pg_dump导出源schema的结构脚本，替换schema名称后导入到目标schema：

pg_dump -U 你的用户名 -d 目标数据库 -n source_schema -s | sed 's/source_schema/readonly_schema/g' | psql -U 你的用户名 -d 目标数据库

2. 创建发布（Publisher） #

针对源schema创建发布对象，指定需要同步的表：

-- 发布source_schema下的所有表
CREATE PUBLICATION source_pub FOR ALL TABLES IN SCHEMA source_schema;

如果只需同步特定表，可替换为：

CREATE PUBLICATION source_pub FOR TABLE source_schema.table1, source_schema.table2;

3. 创建本地订阅（Subscriber） #

在同一数据库内创建订阅，连接本地数据库并关联之前的发布，同时设置schema映射（需PostgreSQL 15+）：

-- 创建订阅，初始同步已有数据
CREATE SUBSCRIPTION source_sub
CONNECTION 'dbname=目标数据库'
PUBLICATION source_pub
WITH (copy_data = true, create_slot = true);

-- 配置schema映射，将源schema的表同步到目标schema
ALTER SUBSCRIPTION source_sub
ADD SCHEMA MAPPING (source_schema => readonly_schema);

如果你的PostgreSQL版本低于15，需手动指定表映射：

ALTER SUBSCRIPTION source_sub
ADD TABLE MAPPING (source_schema.table1 => readonly_schema.table1);

4. 配置只读权限 #

给目标role赋予目标schema的只读访问权限：

-- 授予schema使用权限
GRANT USAGE ON SCHEMA readonly_schema TO readonly_role;
-- 授予现有表的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA readonly_schema TO readonly_role;
-- 确保未来新增表自动继承只读权限
ALTER DEFAULT PRIVILEGES IN SCHEMA readonly_schema GRANT SELECT ON TABLES TO readonly_role;

注意事项 #

• 版本限制：Schema级映射仅支持PostgreSQL 15及以上版本，低版本需手动配置表级映射。
• 避免循环同步：确保发布仅包含源schema，订阅仅写入目标schema，防止产生循环复制。
• 对象支持：逻辑复制仅同步表数据，视图、函数、序列等对象需手动同步或通过定时任务刷新。
• 性能监控：同一库内的逻辑复制会占用数据库资源，高负载场景需关注CPU、内存及磁盘IO情况。

内容的提问来源于stack exchange，提问作者Jim_CJ