PostgreSQL-逻辑复制（Logic）部署方案

运维小路

发布于 2026-01-26 12:33:14

830

文章被收录于专栏：运维小路运维小路

作者介绍：简历上没有一个精通的运维工程师，下面的思维导图也是预计更新的内容和当前进度(不定时更新)。

数据库是一个系统（应用）最重要的资产之一，所以我们的数据库将从以下几个数据库来进行介绍。

MySQL

PostgreSQL（本章节）

MongoDB

Redis

Etcd

上个小节我们完成PGSQL基于WAL的流复制的主从集群搭建，这个虽然底层的复制逻辑不一样，但是他和MySQL主从一样都可以作为集群的高可用来使用的（因为他是完整的备份）。但是我们今天讲的逻辑复制，考虑的就只是复制自己想要的数据，其他不相关的数据都是不复制，这个小节就来介绍如何配置逻辑复制。

1.主节点环境配置

按照下面的配置修改，并重启数据库，这里同步表是在postgres库里面，如果实际环境则替换成实际数据库。

#修改postgresql.conf
wal_level = logical          # 开启逻辑复制模式
max_replication_slots = 5    # 预留足够的复制槽
max_wal_senders = 5          # 允许足够的发送进程

#修改pg_hba.conf
# 允许访问 postgres 数据库（初始验证）
host  postgres     repl_user  192.168.31.0/24  md5
# 允许复制协议连接（数据同步）
host  replication  repl_user  192.168.31.0/24  md5

2.主节点数据库配置

基于上面的pg_hba.conf配置创建对应的角色。

CREATE ROLE repl_user 
  WITH 
    REPLICATION  -- 授予复制权限（逻辑复制必需）
    LOGIN        -- 允许该角色登录数据库
    PASSWORD 'your_password';  -- 设置登录密码

3.创建测试表

这里为了测试方面直接在默认postgres库里面操作，只要前后保持一致均可。

-- 创建测试表
CREATE TABLE simple_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

4.创建发布（核心关键）

-- 创建发布，没指定在是public模式 
CREATE PUBLICATION simple_pub FOR TABLE simple_table;

-- 创建发布，默认复制INSERT/UPDATE/DELETE/TRUNCATE操作
-- 配置多表 
CREATE PUBLICATION pub_test FOR TABLE public.user_info, public.order_info;
-- 若需指定操作类型，例如仅复制INSERT和UPDATE
CREATE PUBLICATION pub_test FOR TABLE public.user_info WITH (publish = 'insert, update');

5.从节点配置创建一样的表

这里的库表实际都可以不相同，甚至表结构也可以略有不一样（必须要约束必须保持一致），我这里为了演示方便，所以配置成一样的。

-- 创建相同结构的表
CREATE TABLE simple_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

6.连接订阅

postgres=# CREATE SUBSCRIPTION simple_sub
  CONNECTION 'host=192.168.31.188 
              port=5432 
              dbname=postgres 
              user=repl_user 
              password=your_password'
  PUBLICATION simple_pub;
NOTICE:  created replication slot "simple_sub" on publisher
CREATE SUBSCRIPTION
postgres=#

7.检查订阅

postgres=# -- 查看订阅基本信息（是否存在、连接参数等）
postgres=# SELECT * FROM pg_subscription WHERE subname = 'simple_sub';
  oid  | subdbid |  subname   | subowner | subenabled | subbinary | substream |             subconninfo              | subslotname | subsynccommit | subpublications 
-------+---------+------------+----------+------------+-----------+-----------+--------------------------------------+-------------+---------------+-----------------
 16394 |   14486 | simple_sub |       10 | t          | f         | f         | host=192.168.31.188                 +| simple_sub  | off           | {simple_pub}
       |         |            |          |            |           |           |               port=5432             +|             |               | 
       |         |            |          |            |           |           |               dbname=postgres       +|             |               | 
       |         |            |          |            |           |           |               user=repl_user        +|             |               | 
       |         |            |          |            |           |           |               password=your_password |             |               | 
(1 row)

postgres=# SELECT * FROM pg_stat_subscription WHERE subname = 'simple_sub';
 subid |  subname   | pid  | relid | received_lsn |      last_msg_send_time       |    last_msg_receipt_time     | latest_end_lsn |        latest_end_time        
-------+------------+------+-------+--------------+-------------------------------+------------------------------+----------------+-------------------------------
 16394 | simple_sub | 1392 |       | 0/30290A8    | 2025-11-06 23:28:43.307229+08 | 2025-11-06 23:28:43.30751+08 | 0/30290A8      | 2025-11-06 23:28:43.307229+08
(1 row)

8.插入数据验证

这个时候我们向源库表插入数据，只有配置了订阅的表才会同步过来，因为他的逻辑是表对表进行复制，DDL 语句（如ALTER TABLE修改表结构、CREATE INDEX创建索引等）是不会同步过来的。简单总结就是只同步数据，其他并不会同步。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-11-06，如有侵权请联系 cloudcommunity@tencent.com 删除

配置