PostgreSQLDDL复制

一、概要

PostgreSQL逻辑复制技术是PostgreSQL高可用架构、数据迁移、数据汇集、升级等场景应用得到实现的基础，意义重大。

由于PostgreSQL内置逻辑复制不支持包括表、表结构、视图、索引、触发器等DDL变更的复制。我们不得不寻找增强方案，通过对pglogical&pgl_ddl_deploy两扩展能力判断，它们可做为DML复制与DDL复制的优选组件，实现PostgreSQL在双机或多机环境下的DML复制、DDL复制。为验证其能力及边界设计本测试方案。

二、环境准备

1、前置条件

CentOS7.9 server或虚拟机，已编译安装PostgreSQL17（V9.4以上，也可尝试）

2、时钟同步

服务器时钟同步（主备库都需操作）

echo "*/20 * * * * usr/sbin/ntpdate -u time.pool.aliyun.com >/dev/null" >> var/spool/cron/root

三、pglogical的部署与验证

1、安装部署

1.编译安装

在两台服务器上，都要执行以下操作

wget https://github.com/2ndQuadrant/pglogical/archive/refs/heads/REL2_x_STABLE.zip

如无法下载：找到：

https://github.com/2ndQuadrant/pglogical

下载：REL2_x_STABLE.zip到本地，再通过Shell工具上传到服务器，如不能上传，可在服务器上安装lrzsz

yum install lrzsz
unzip pglogical-REL2_x_STABLE.zip
cd pglogical-REL2_x_STABLE

找到 pg_config

which pg_config

如果找不到，请指向bin所在目录，如：

export PATH=$PATH:/usr/local/pgsql/bin

编译安装

USE_PGXS=1 make clean
USE_PGXS=1 make
USE_PGXS=1 make install

查看安装情况

select * from pg_available_extensions where name='pglogical';
   name    | default_version | installed_version |            comment             
-----------+-----------------+-------------------+--------------------------------
 pglogical | 2.4.5           |                   | PostgreSQL Logical Replication
(1 row)

2.修改配置

修改两节点的postgresql.conf（/var/postgresql/data）

wal_level = logical
max_worker_processes = 10  
max_replication_slots = 10  
max_wal_senders = 10 
shared_preload_libraries = 'pglogical'

如果你想处理解决PostgreS中可用的冲突，可添加以下内容

track_commit_timestamp = on

编辑节点库的pg_hba.conf， 配置成允许从本地主机复制，用户拥有有复制/连接权限；

# 允许从节点全权
host    all             postgres        192.168.1.10/32         trust

从节点 pg_hba.conf

# 允许从节点访问
host    all             postgres        192.168.1.10/32         trust

允许远程连接

sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" postgresql.conf

重启服务

su - postgres
pg_ctl restart
或
pg_ctl restart -D /var/postgresql/data
./psql

在两台服务器上分别安装pglogical拓展

CREATE EXTENSION pglogical;

查询在用扩展

SELECT extname, extversion, extnamespace::regnamespace AS schema_name FROM pg_extension ORDER BY extname;

2、逻辑复制配置

1、发布端配置

1、创建节点

在一个数据库里创建发布端节点

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

# 如果不需要了，使用以下语句删除，注意节点名称要准确
SELECT pglogical.drop_node(node_name := 'provider1');

2、创建复制集

将public schema中的所有表添加到default复制集中

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

⚠️ **注意：**复制集default的表都需要有主键

2、订阅端配置

1、创建节点

在另一个数据库（或称从库）创建订阅端

# 创建订阅端
SELECT pglogical.create_node(
node_name := 'subscriber1',
dsn := 'host=192.168.1.10 port=5432 dbname=postgres'
);

# 如果不需要了，使用以下语句删除，注意节点名称要准确
SELECT pglogical.drop_node(node_name := 'subscriber1');

2、创建订阅

订阅端节点，该订阅将在后台启动同步和复制过程

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=192.168.1.9 port=5432 dbname=postgres user=postgres',
    replication_sets := ARRAY['default', 'ddl_sql'],
    synchronize_structure := true,
    synchronize_data := true  
);

# 如果不用，删除语句如下：
SELECT pglogical.drop_subscription(
    subscription_name := 'subscription1'
);

⚠️ 注意：这里的复制槽是自动创建，不用自建

至此，完成安装/配置工作，如需要对已建工作有所了解，可参考以下查询操作

3、数据复制验证

1、创建测试表（发布端与订阅端都要建）

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

由于需要验证insert/update/delete/truncate操作是否同步；所以发布端创建的表要有主键约束。

2、发布端节点，将表添加对应的复制集

对新建的表；并没有为其分配对应的复制集；需要手动将表添加到对应的复制集中

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

3、查看复制集

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

同时，数据也同步到 subscriber 节点。因为在第二种方法有 同步 的操作。

4、在订阅端同步表的操作（可选，可不理会）

#重新同步一个表
pglogical.alter_subscription_resynchronize_table(subscription_name name, relation regclass) 

#将所有的表都同步
pglogical.alter_subscription_synchronize(subscription_name name, truncate bool)
select pglogical.alter_subscription_synchronize(subscription1 name, truncate bool)

5、查看订阅端

查看表 tbl_test01 订阅信息

postgres=# select * from pglogical.show_subscription_table('subscription1','tbl_test01');
 nspname |   relname   |    status    
---------+-------------+--------------
 public  | tbl_test01 | synchronized
(1 row)

6、验证：insert 同步

订阅端同步到发布端的数据－insert
#在发布端
insert into tbl_test01 select generate_series(1,10000),'steven',now();

#在订阅端
postgres=# select count(1) from tbl_test01;

#有10000条，为正确

7、验证：truncate 同步

订阅端同步到发布端的数据－truncate
#在发布端
postgres=# TRUNCATE table  tbl_test01;
#在订阅端
postgres=# select count(1) from tbl_test01;

#有0条，为正确

在复制集default中：update/delete/truncate 操作也是同步复制

SELECT * FROM pglogical.replication_set;

4、验证DDL_SQL

ddl_sql允许将一系列 DDL 操作从主节点同步到订阅端节点。支持包括创建、修改和删除表、索引、视图、序列等数据库对象的操作。

⚠️ **注意：**本方案验证结果，可以在发布端同步DDL到订阅端，但需要手动干预执行上述命令。

四、pgl_ddl_deploy的部署与验证

1.发布端与订阅端都要安装

git clone https://github.com/darold/pgl_ddl_deploy.git 
#如下载困难，可下载pgl_ddl_deploy.zip到本机，再rz上传到服务器上。
unzip　pgl_ddl_deploy.zip
cd pgl_ddl_deploy

export PATH=$PATH:/usr/local/pgsql/bin
USE_PGXS=1 make clean
USE_PGXS=1 make
USE_PGXS=1 make install

2.发布与订阅端都要建扩展

进入psql

CREATE EXTENSION pgl_ddl_deploy;

3.插入配置信息表

INSERT INTO pgl_ddl_deploy.set_configs (set_name, include_schema_regex, driver) VALUES ('default', '.*', 'pglogical'::pgl_ddl_deploy.driver);

4.检查权限

SELECT pgl_ddl_deploy.add_role(oid) FROM pg_roles WHERE rolname IN ('postgres', 'replication');

5.验证：DDL修改表同步

ALTER TABLE tbl_test01 ADD COLUMN f INT;

#发布端与订阅端顺次查询
select * from tbl_test01 ;
 id | name | reg_time | f 
----+------+----------+---
(0 rows)

说明：两端都有新增列f，证明ALTER TABLE同步有效

6.验证：DDL建表同步

create table tbl_test02 (id int primary key, name text, job text, reg_time timestamp );
CREATE TABLE

#发布端与订阅端顺次查询
select * from tbl_test02;
 id | name | job | reg_time 
----+------+-----+----------
(0 rows)

说明：两端都有新建表tbl_test02，证明create table同步有效。

7.扩展说明

据pgl_ddl_deploy说明文档，它可实现以下DDL复制操作。未做进一步验证。

command_tag
-----------------
 ALTER FUNCTION
 ALTER SEQUENCE
 ALTER TABLE
 ALTER TYPE
 ALTER VIEW
 CREATE FUNCTION
 CREATE SCHEMA
 CREATE SEQUENCE
 CREATE TABLE
 CREATE TABLE AS
 CREATE TYPE
 CREATE VIEW
 DROP FUNCTION
 DROP SCHEMA
 DROP SEQUENCE
 DROP TABLE
 DROP TYPE
 DROP VIEW
 SELECT INTO

8.限制和约束

• 不支持同时修改复制表和非复制表的单个SQL语句，例如跨schema的DROP TABLE或ALTER TABLE操作，尤其是当复制表和非复制表之间存在外键关系时。
• 不支持CREATE TABLE AS和SELECT INTO命令；
• 当客户端将多个SQL语句作为单个字符串发送到PostgreSQL时，可能会导致问题。例如禁止混合DDL和DML语句。
• 对于本机逻辑复制，只有包含复制插入的发布才能支持DDL复制。

五、总结

本次测试，证实pglogical逻辑复制与pgl_ddl_deployDDL复制在双机下正确部署与工作，证实了pglogical扩展支持包括insert、delete、update、truncate在内的各类数据操作，证实了ddl_sql尚需要手动干预的情况；证实了pgl_ddl_deploy扩展支持create　table、alert table 等自动化操作，基本达到了预期目标。