PostgreSQL 13: CREATE SUBSCRIPTION新增publish_via_partition_root选项支持异构分区表间的数据逻辑复制

发表于 | 分类于 | | 阅读次数:

PostgreSQL 13 的逻辑复制新增了对分区表的支持，使得分区表也能够进行逻辑复制。

实现方式: PostgreSQL 13 版本CREATE SUBSCRIPTION命令新增 publish_via_partition_root 选项支持异构分区表的数据同步，具体为:

  • 分区表数据逻辑复制到普通表。
  • 分区表数据逻辑复制到异构分区表。

第2点所说的异构分区表是指目标库和源库同一张分区表的分区策略可以不一样，比如源库分区表的分区策略是按月分区，目标库分区表的分区策略可以是按年分区。

这一功能对于分区表具有重要意义，当需要从多个源库汇总数据到同一个目标库的分区表时，目标库的分区策略可以设置成和源库不一致，便于数据汇总统计。

publish_via_partition_root (boolean)

手册中关于 publish_via_partition_root 选项的说明，如下:

This parameter determines whether changes in a partitioned table (or on its partitions) contained in the publication will be published using the identity and schema of the partitioned table rather than that of the individual partitions that are actually changed; the latter is the default. Enabling this allows the changes to be replicated into a non-partitioned table or a partitioned table consisting of a different set of partitions.

If this is enabled, TRUNCATE operations performed directly on partitions are not replicated.

关于 publish_via_partition_root选项，如下:

  • 该选项设置发布中包含的分区表中的更改(或分区上的更改)是否使用分区表父表的标识和模式发布，而不是使用各个分区的标识和模式发布。
  • 默认使用分区进行标识和模式发布。
  • 设置为true，可以将分区表的数据逻辑复制到普通表和异构分区表。
  • 如果设置为true，分区上的 TRUNCATE 操作不会进行逻辑复制。

本文对分区表在上述两种场景下的逻辑复制进行验证，如下:

  • 场景一: 分区表数据逻辑复制到普通表。
  • 场景二: 分区表数据逻辑复制到异构分区表。

环境规划

环境规划，如下:

节点 数据库版本 IP 端口
源库 PostgreSQL 13beta1 192.168.2.11 1922
目标库 PostgreSQL 13beta1 192.168.2.13 1924

环境准备

源库和目标库安装 PostgreSQL 13beta1软件，本文略。

创建分区表

在源库创建分区表，如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--创建父表
CREATE TABLE tbl_log (
  id serial,
  user_id int4,
  create_time timestamp(0) without time zone
) PARTITION BY RANGE(create_time);

--创建子表
CREATE TABLE tbl_log_his PARTITION OF tbl_log FOR VALUES FROM (minvalue) TO ('2020-01-01');
CREATE TABLE tbl_log_202001 PARTITION OF tbl_log FOR VALUES FROM ('2020-01-01') TO ('2020-02-01');
CREATE TABLE tbl_log_202002 PARTITION OF tbl_log FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');
CREATE TABLE tbl_log_202003 PARTITION OF tbl_log FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');
CREATE TABLE tbl_log_202004 PARTITION OF tbl_log FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');
CREATE TABLE tbl_log_202005 PARTITION OF tbl_log FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE TABLE tbl_log_202006 PARTITION OF tbl_log FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');
CREATE TABLE tbl_log_202007 PARTITION OF tbl_log FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');

--创建索引
CREATE INDEX idx_tbl_log_ctime ON tbl_log USING BTREE (create_time);

创建发布

源库上创建发布，如下:

1
2
mydb=> CREATE PUBLICATION pub1 FOR TABLE tbl_log WITH (publish_via_partition_root=true);
CREATE PUBLICATION

创建发布时使用了 publish_via_partition_root选项。

给repuser用户赋权，如下:

1
2
3
4
5
6
mydb=> GRANT CONNECT ON DATABASE mydb TO repuser;
GRANT
mydb=> GRANT USAGE ON SCHEMA pguser TO repuser;
GRANT
mydb=> GRANT SELECT ON ALL TABLES IN SCHEMA pguser TO repuser;
GRANT

给源库上的repuser用户赋相关权限，如果不给repuser用户赋权，创建订阅后目标库无法初始化同步源库数据。

插入数据

源库批量插入数据，如下:

1
2
3
mydb=> INSERT INTO tbl_log(user_id,create_time)
SELECT round(100000000*random()),generate_series('2019-10-01'::date, '2020-06-20'::date, '1 day');
INSERT 0 264

场景一: 分区表逻辑复制到普通表

源库上的tbl_log是分区表，计划在源库上创建一张非分区表tbl_log并配置逻辑复制，验证数据是否能正常同步。

目标库上创建普通表，如下:

1
2
3
4
5
CREATE TABLE tbl_log (
  id serial,
  user_id int4,
  create_time timestamp(0) without time zone
);

创建订阅，如下:

1
2
mydb=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.2.11 port=1922 dbname=mydb user=repuser' PUBLICATION pub1;
NOTICE:  created replication slot "sub1" on publisher

注意配置好源库的pg_hba.conf和.pgpass文件，否则创建订阅会报相关的连接不上错误。

验证目标库数据，如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[pg13@ydtf03 ~]$ psql mydb pguser -p 1924
psql (13beta1)
Type "help" for help.

mydb=> SELECT COUNT(*) FROM tbl_log;
 count
-------
   264
(1 row)

mydb=> \dt+ tbl_log*
                           List of relations
 Schema |  Name   | Type  | Owner  | Persistence | Size  | Description
--------+---------+-------+--------+-------------+-------+-------------
 pguser | tbl_log | table | pguser | permanent   | 40 kB |
(1 row)

数据已从源库同步。

文档中提到对分区上的TRUNCATE操作不会进行逻辑复制，验证下:

源库执行如下操作:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[pg13@ydtf01 ~]$ psql mydb pguser -p 1922
psql (13beta1)
Type "help" for help.

mydb=> select count(*) from tbl_log;
 count
-------
   264
(1 row)

mydb=> select count(*) from tbl_log_his;
 count
-------
    92
(1 row)

mydb=> TRUNCATE TABLE tbl_log_his;
TRUNCATE TABLE

目标库上验证数据是否已删除，如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[pg13@ydtf03 ~]$ psql mydb pguser -p 1924
psql (13beta1)
Type "help" for help.

mydb=> SELECT COUNT(*) FROM tbl_log;
 count
-------
   264
(1 row)

mydb=> SELECT COUNT(*) FROM tbl_log_his;
 count
-------
    92
(1 row)

发现目标库上数据没有变化。

场景二: 分区表逻辑复制到异构分区表

源库的tbl_log是按月分区表，计划在目标库上创建一张按年分区表tbl_log并配置逻辑复制，验证数据是否能正常同步。

目标库上删除表 tbl_log ，如下:

1
2
mydb=> DROP TABLE tbl_log_tmp ;
DROP TABLE

目标库上创建按年分区表，如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--创建父表
CREATE TABLE tbl_log (
  id serial,
  user_id int4,
  create_time timestamp(0) without time zone
) PARTITION BY RANGE(create_time);

--创建子表
CREATE TABLE tbl_log_his PARTITION OF tbl_log FOR VALUES FROM (minvalue) TO ('2020-01-01');
CREATE TABLE tbl_log_2020 PARTITION OF tbl_log FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
CREATE TABLE tbl_log_2021 PARTITION OF tbl_log FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

--创建索引
CREATE INDEX idx_tbl_log_ctime ON tbl_log USING BTREE (create_time);

源库数据，如下:

1
2
3
4
5
6
7
8
9
[pg13@ydtf01 ~]$ psql mydb pguser -p 1922
psql (13beta1)
Type "help" for help.

mydb=> select count(*) from tbl_log;
 count
-------
   172
(1 row)

观察目标库数据，如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[pg13@ydtf03 ~]$ psql mydb pguser -p 1924
psql (13beta1)
Type "help" for help.

mydb=> \dt+ tbl_log*
                                    List of relations
 Schema |     Name     |       Type        | Owner  | Persistence |  Size   | Description
--------+--------------+-------------------+--------+-------------+---------+-------------
 pguser | tbl_log      | partitioned table | pguser | permanent   | 0 bytes |
 pguser | tbl_log_2020 | table             | pguser | permanent   | 0 bytes |
 pguser | tbl_log_2021 | table             | pguser | permanent   | 0 bytes |
 pguser | tbl_log_his  | table             | pguser | permanent   | 0 bytes |
(4 rows)

mydb=> SELECT COUNT(*) FROM tbl_log;
 count
-------
     0
(1 row)

发现目标库数据还没有同步过来。

目标库刷新订阅，如下:

1
2
mydb=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
ALTER SUBSCRIPTION

再次在目标库上验证数据，发现数据已同步，如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mydb=> \dt+ tbl_log*
                                      List of relations
 Schema |     Name     |       Type        | Owner  | Persistence |    Size    | Description
--------+--------------+-------------------+--------+-------------+------------+-------------
 pguser | tbl_log      | partitioned table | pguser | permanent   | 0 bytes    |
 pguser | tbl_log_2020 | table             | pguser | permanent   | 8192 bytes |
 pguser | tbl_log_2021 | table             | pguser | permanent   | 0 bytes    |
 pguser | tbl_log_his  | table             | pguser | permanent   | 0 bytes    |
(4 rows)

mydb=> SELECT COUNT(*) FROM tbl_log;
 count
-------
   172
(1 row)

mydb=> SELECT COUNT(*) FROM tbl_log_2020;
 count
-------
   172
(1 row)

总结

本文演示了分区表数据逻辑复制到普通表和异构分区表的场景，实际上普通表也可以逻辑复制到分区表，有兴趣的同学可查阅 PostgreSQL 13: 普通表数据逻辑复制到分区表。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》，本书基于PostgreSQL 10 编写，共18章，重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等，涵盖大量实战用例！

购买链接：https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持！
0%