pglogical支持DDL搭建

发布日期: 2026-03-11

文章字数: 1.6k

阅读时长: 5 分钟

阅读次数:

难度中等

编译数据库和pglogical插件

git clone https://git.postgresql.org/git/postgresql.git
cd postgresql
git checkout REL_15_STABLE # 以pg15为例
./configure --prefix=`pwd`/debug
make world -j16
make install-world

git clone https://github.com/2ndQuadrant/pglogical.git
cd pglogical
export PG_CONFIG=/path/pg_config
make
make install

配置启动

配置数据库1

使用如下命令初始化数据库：

./initdb -D data -A trust

初始化data后，修改postgres.conf，添加如下内容：

wal_level = logical

max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10

shared_preload_libraries = 'pglogical'

启动数据库并配置插件：

./pg_ctl -D data start -l logfile

./psql -d postgres
psql (15.12)
Type "help" for help.

postgres=# create extension pglogical;
CREATE EXTENSION

配置数据库2

在同一台机器上再初始化一个数据库，

./initdb -D data -A trust

初始化data后，修改postgres.conf，添加如下内容：(修改一下数据库运行的端口)

wal_level = logical

max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10

shared_preload_libraries = 'pglogical'
port=5433

./pg_ctl -D data1 start -l logfile1

./psql -d postgres -p 5433
psql (15.12)
Type "help" for help.

postgres=# create extension pglogical;
CREATE EXTENSION

配置pub

连接数据库，注册pub。

# 创建节点
SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=127.0.0.1 port=5432 dbname=postgres'
);

./psql -d postgres
psql (15.12)
Type "help" for help.

postgres=# SELECT pglogical.create_node(
postgres(#     node_name := 'provider1',
postgres(#     dsn := 'host=127.0.0.1 port=5432 dbname=postgres'
postgres(# );
 create_node 
-------------
  2976894835
(1 row)

创建复制集

将public架构中的所有表添加到default复制集中。

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

复制集default的表都必需要primary key。

配置subscribe

连接数据库，创建订阅者节点。

SELECT pglogical.create_node(
node_name := 'subscriber1',
dsn := 'host=127.0.0.1 port=5433 dbname=postgres'
);

./psql -d postgres -p 5433
psql (15.12)
Type "help" for help.

postgres=# SELECT pglogical.create_node(
postgres(# node_name := 'subscriber1',
postgres(# dsn := 'host=127.0.0.1 port=5433 dbname=postgres'
postgres(# );
 create_node 
-------------
   330520249
(1 row)

节点创建完成后，创建订阅者。

SELECT pglogical.create_subscription(
subscription_name := 'subscription1',
provider_dsn := 'host=127.0.0.1 port=5432 dbname=postgres'
);

postgres=# SELECT pglogical.create_subscription(
postgres(# subscription_name := 'subscription1',
postgres(# provider_dsn := 'host=127.0.0.1 port=5432 dbname=postgres'
postgres(# );
 create_subscription 
---------------------
          1763399739
(1 row)

此时查看机器上的进程如下：

ps -ef | grep postgres
dys      1201049       1  0 3月08 ?       00:00:00 /work/cwork/postgresql/debug/bin/postgres -D /home/dys/data
dys      1201050 1201049  0 3月08 ?       00:00:00 postgres: checkpointer 
dys      1201051 1201049  0 3月08 ?       00:00:00 postgres: background writer 
dys      1201053 1201049  0 3月08 ?       00:00:00 postgres: walwriter 
dys      1201054 1201049  0 3月08 ?       00:00:00 postgres: autovacuum launcher 
dys      1201055 1201049  0 3月08 ?       00:00:00 postgres: pglogical supervisor 
dys      1201056 1201049  0 3月08 ?       00:00:00 postgres: logical replication launcher 
dys      1202949       1  0 00:08 ?        00:00:00 /work/cwork/postgresql/debug/bin/postgres -D /home/dys/data1
dys      1202950 1202949  0 00:08 ?        00:00:00 postgres: checkpointer 
dys      1202951 1202949  0 00:08 ?        00:00:00 postgres: background writer 
dys      1202953 1202949  0 00:08 ?        00:00:00 postgres: walwriter 
dys      1202954 1202949  0 00:08 ?        00:00:00 postgres: autovacuum launcher 
dys      1202955 1202949  0 00:08 ?        00:00:00 postgres: pglogical supervisor 
dys      1202956 1202949  0 00:08 ?        00:00:00 postgres: logical replication launcher 
dys      1220248 1201049  0 02:25 ?        00:00:00 postgres: pglogical manager 5 
dys      1222134 1202949  0 02:44 ?        00:00:00 postgres: pglogical manager 5 
dys      1222717 1202949  0 02:51 ?        00:00:00 postgres: pglogical apply 5:1763399739 
dys      1222722 1201049  0 02:51 ?        00:00:00 postgres: walsender dys 127.0.0.1(57692) START_REPLICATION
dys      1222772 1218880  0 02:51 pts/1    00:00:00 grep --color=auto postgres

验证逻辑复制

创建表：

create table test_lo(id int primary key, name text, reg_time timestamp);

./psql -d postgres
psql (15.12)
Type "help" for help.

postgres=# create table test_lo(id int primary key, name text, reg_time timestamp);
CREATE TABLE
postgres=# \d
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | test_lo | table | dys
(1 row)

可以看到已经在源库（pub）创建了表，可以看下目的库有没有同步该表。

./psql -d postgres -p 5433
psql (15.12)
Type "help" for help.

postgres=# \d
Did not find any relations.

可以看到目标库并没有自动同步该表。

生成测试数据

在pub端执行：

insert into test_lo select generate_series(1,1000),'postgres',now();

postgres=# insert into test_lo select generate_series(1,1000),'postgres',now();
INSERT 0 1000
postgres=# \d+
                                  List of relations
 Schema |  Name   | Type  | Owner | Persistence | Access method | Size  | Description 
--------+---------+-------+-------+-------------+---------------+-------+-------------
 public | test_lo | table | dys   | permanent   | heap          | 88 kB | 
(1 row)
postgres=# select * from test_lo limit 10;
 id |   name   |          reg_time          
----+----------+----------------------------
  1 | postgres | 2026-03-09 03:00:15.304868
  2 | postgres | 2026-03-09 03:00:15.304868
  3 | postgres | 2026-03-09 03:00:15.304868
  4 | postgres | 2026-03-09 03:00:15.304868
  5 | postgres | 2026-03-09 03:00:15.304868
  6 | postgres | 2026-03-09 03:00:15.304868
  7 | postgres | 2026-03-09 03:00:15.304868
  8 | postgres | 2026-03-09 03:00:15.304868
  9 | postgres | 2026-03-09 03:00:15.304868
 10 | postgres | 2026-03-09 03:00:15.304868
(10 rows)

将新建的表添加到对应的复制集

对新建的表；并没有为其分配对应的复制集；需要手动添加。（也可以使用触发器添加）

postgres=# select * from pglogical.replication_set_table ;
 set_id | set_reloid | set_att_list | set_row_filter 
--------+------------+--------------+----------------
(0 rows)

select pglogical.replication_set_add_table( set_name := 'default', relation := 'test_lo',synchronize_data := true);

postgres=# select * from pglogical.replication_set_table ;
 set_id | set_reloid | set_att_list | set_row_filter 
--------+------------+--------------+----------------
(0 rows)

postgres=# select pglogical.replication_set_add_table( set_name := 'default', relation := 'test_lo',synchronize_data := true);
 replication_set_add_table 
---------------------------
 t
(1 row)

postgres=# select * from pglogical.replication_set_table ;
  set_id   | set_reloid | set_att_list | set_row_filter 
-----------+------------+--------------+----------------
 290045701 | test_lo    |              | 
(1 row)

select * from pglogical.show_subscription_table('subscription1','test_lo');

同步DDL

逻辑复制不会自动同步DDL，需要在创建表时使用pglogical指定的语句才会同步。

在创建表时同步DDL。

SELECT pglogical.replicate_ddl_command(
$$
SET search_path = public;
create table test_lo(id int primary key, name text, reg_time timestamp);
$$
);

./psql -d postgres
psql (15.12)
Type "help" for help.

postgres=# SELECT pglogical.replicate_ddl_command(
postgres(# $$
postgres$# SET search_path = public;
postgres$# create table test_lo(id int primary key, name text, reg_time timestamp);
postgres$# $$
postgres(# );
 replicate_ddl_command 
-----------------------
 t
(1 row)

postgres=# \d
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | test_lo | table | dys
(1 row)

postgres=# \q
[dys@localhost bin]$ ./psql -d postgres -p 5433
psql (15.12)
Type "help" for help.

postgres=# \d
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | test_lo | table | dys
可以看到源库和目标库都有了test_lo表。

重新生成测试数据。

```sql
[dys@localhost bin]$ ./psql -d postgres
psql (15.12)
Type "help" for help.

postgres=# INSERT INTO public.test_lo VALUES (1001,'check',now());
INSERT 0 1
postgres=# \q
[dys@localhost bin]$ ./psql -d postgres -p 5433
psql (15.12)
Type "help" for help.

postgres=# \d+
                                  List of relations
 Schema |  Name   | Type  | Owner | Persistence | Access method | Size  | Description 
--------+---------+-------+-------+-------------+---------------+-------+-------------
 public | test_lo | table | dys   | permanent   | heap          | 16 kB | 
(1 row)

postgres=# SELECT * FROM pglogical.subscription;^C
postgres=# select * from test_lo;
  id  | name  |          reg_time          
------+-------+----------------------------
 1001 | check | 2026-03-09 03:28:47.696612
(1 row)
可以看到新插入的数据已经同步。
# reference

1.https://www.cnblogs.com/lottu/p/10972773.html
2.https://www.modb.pro/db/376539

growdu

https://growdu.cn/2026/03/11/db/logical_decode/pglogical%E6%94%AF%E6%8C%81DDL%E6%90%AD%E5%BB%BA%E6%95%99%E7%A8%8B/