900字范文 > 【参赛作品68】PostgrSQL和OpenGauss/MogDB分区表对比（二）

【参赛作品68】PostgrSQL和OpenGauss/MogDB分区表对比（二）

时间：2022-09-10 12:44:32

作者：lmj

往期参考：PostgrSQL和OpenGauss/MogDB分区表对比（一）

对比

1. 分区表基本操作对比

1.1 创建对比所需的分区表（范围分区）

PostgreSQL

-- 创建分区表create table part_test(id int,name varchar)partition by range(id);create table p1 PARTITION OF part_test for values FROM (0) TO (10);create table p2 PARTITION OF part_test for values FROM (10) TO (20);create table p3 PARTITION OF part_test for values FROM (20) TO (30);create table p4 PARTITION OF part_test for values FROM (30) TO (40);create table p5 PARTITION OF part_test DEFAULT;-- 插入测试数据insert into part_test values (2,'aa'),(4,'bb'),(17,'cc'),(31,'dd'),(35,'ee'),(37,'gg');-- 查看表数据pg13=> select * from part_test;id | name----+------2 | aa4 | bb17 | cc31 | dd35 | ee37 | gg(6 rows)pg13=> select * from p4;id | name----+------31 | dd35 | ee37 | gg(3 rows)

OpenGauss/MogDB

-- 创建分区表create table part_test(id int,name varchar)partition by range(id)(PARTITION P1 VALUES LESS THAN(10),PARTITION P2 VALUES LESS THAN(20),PARTITION P3 VALUES LESS THAN(30),PARTITION P4 VALUES LESS THAN(40),PARTITION P5 VALUES LESS THAN(maxvalue) );-- 插入测试数据insert into part_test values (2,'aa'),(4,'bb'),(17,'cc'),(31,'dd'),(35,'ee'),(37,'gg');-- 查看表数据mogdb=> select * from part_test;id | name----+------2 | aa4 | bb17 | cc31 | dd35 | ee37 | gg(6 rows)mogdb=> select * from part_test partition(P4);id | name----+------31 | dd35 | ee37 | gg(3 rows)mogdb=> select * from part_test partition for (10);id | name----+------17 | cc(1 row)

1.2 删除分区 p5

PostgreSQL – 使用 detach 移除分区 p5 后，此时 p5 变成一个单独的表，彻底删除，需要使用 drop table

alter table part_test detach partition p5;drop table p5 ;pg13=> \d+ part_testPartitioned table "public.part_test"Column | Type | Collation | Nullable | Default | Storage | Stats target | Description--------+-------------------+-----------+----------+---------+----------+--------------+-------------id| integer | || | plain | |name | character varying | || | extended | |Partition key: RANGE (id)Partitions: p1 FOR VALUES FROM (0) TO (10),p2 FOR VALUES FROM (10) TO (20),p3 FOR VALUES FROM (20) TO (30),p4 FOR VALUES FROM (30) TO (40)

OpenGauss/MogDB

alter table part_test drop partition p5;

结论：OpenGauss/MogDB 的删除分区操作是把分区和表中的数据一起删除，而PostgreSQL 的 detach 删除分区只是把分区从分区表中移走变成一个单独的表，分区中的数据只是会随着分区一起移走而不会删除，保存在表中，后续还可以使用 ATTACH 将这个表挂载到分区表上。

1.3 增加分区 p5 ：40~50

PostgreSQL

--法一：类似于刚开始创建分区时的操作create table p5 PARTITION OF part_test for values FROM (40) TO (50);--法二：先使用 like 创建一个和分区表结构相同的表，根据要挂载的分区 constraint 来先进行限制，在使用 attach 挂载到分区表上。CREATE TABLE p5(LIKE part_test INCLUDING DEFAULTS INCLUDING CONSTRAINTS);ALTER TABLE p5 ADD CONSTRAINT p5_idCHECK ( id >= 40 AND id < 50 );--- 此处插入数据是为了测试挂载分区时，表中的已有数据是不是会随着挂载而添加到分区表中insert into p5 values (43,'lmj');ALTER TABLE part_test ATTACH PARTITION p5FOR VALUES FROM (40) TO (50);pg13=> \d+ part_testPartitioned table "public.part_test"Column | Type | Collation | Nullable | Default | Storage | Stats target | Description--------+-------------------+-----------+----------+---------+----------+--------------+-------------id| integer | || | plain | |name | character varying | || | extended | |Partition key: RANGE (id)Partitions: p1 FOR VALUES FROM (0) TO (10),p2 FOR VALUES FROM (10) TO (20),p3 FOR VALUES FROM (20) TO (30),p4 FOR VALUES FROM (30) TO (40),p5 FOR VALUES FROM (40) TO (50)pg13=> select * from part_test ;id | name----+------2 | aa4 | bb17 | cc31 | dd35 | ee37 | gg43 | lmj(7 rows)-- 加入默认表分区create table p6 PARTITION OF part_test DEFAULT;pg13=> \d+ part_testPartitioned table "public.part_test"Column | Type | Collation | Nullable | Default | Storage | Stats target | Description--------+-------------------+-----------+----------+---------+----------+--------------+-------------id| integer | || | plain | |name | character varying | || | extended | |Partition key: RANGE (id)Partitions: p1 FOR VALUES FROM (0) TO (10),p2 FOR VALUES FROM (10) TO (20),p3 FOR VALUES FROM (20) TO (30),p4 FOR VALUES FROM (30) TO (40),p5 FOR VALUES FROM (40) TO (50),p6 DEFAULT

OpenGauss/MogDB

alter table part_test add partition p5 VALUES LESS THAN(50);alter table part_test add partition default_P VALUES LESS THAN(maxvalue);-- 以下情况需先删除 maxvalue 的分区，再执行添加新的分区的操作mogdb=> select relname,relname,parentid::regclass,boundaries from pg_partition where parentid = 'part_test' ::regclass ;relname | relname | parentid | boundaries-----------+-----------+-----------+------------part_test | part_test | part_test |p1 | p1 | part_test | {10}p5 | p5 | part_test | {50}p6 | p6 | part_test | {NULL}p4 | p4 | part_test | {40}(5 rows)mogdb=> alter table part_test add partition p2 VALUES LESS THAN(20);ERROR: upper boundary of adding partition MUST overtop last existing partition

结论：PostgreSQL 使用 attach 来挂载分区，并且表中已有的数据也会添加到分区表中；OpenGauss/MogDB 添加分区使用add partition命令。

1.4 重命名分区

PostgreSQL

alter table p6 rename to p6_default;pg13=> \d+ part_testPartitioned table "public.part_test"Column | Type | Collation | Nullable | Default | Storage | Stats target | Description--------+-------------------+-----------+----------+---------+----------+--------------+-------------id| integer | || | plain | |name | character varying | || | extended | |Partition key: RANGE (id)Partitions: p1 FOR VALUES FROM (0) TO (10),p2 FOR VALUES FROM (10) TO (20),p3 FOR VALUES FROM (20) TO (30),p4 FOR VALUES FROM (30) TO (40),p5 FOR VALUES FROM (40) TO (50),p6_default DEFAULT

OpenGauss/MogDB

alter table part_test rename partition default_P to p6;

1.5 OpenGauss/MogDB 置换分区

EXCHANGE PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } WITH TABLE {[ ONLY ] ordinary_table_name | ordinary_table_name * | ONLY ( ordinary_table_name )} [ { WITH | WITHOUT } VALIDATION ] [ VERBOSE ]

进行交换的普通表和分区必须满足如下条件：

普通表和分区的列数目相同，对应列的信息严格一致，包括：列名、列的数据类型、列约束、列的Collation信息、列的存储参数、列的压缩信息等。

普通表和分区的表压缩信息严格一致。

普通表和分区的索引个数相同，且对应索引的信息严格一致。

普通表和分区的表约束个数相同，且对应表约束的信息严格一致。

普通表不可以是临时表。

完成交换后，普通表和分区的数据被置换，同时普通表和分区的表空间信息被置换。此时，普通表和分区的统计信息变得不可靠，需要对普通表和分区重新执行analyze。

-- 新建普通表create table change_test (id int,name varchar);insert into change_test values (20,'test1'),(25,'test2');mogdb=> select * from change_test ;id | name----+-------20 | test125 | test2(2 rows)-- 置换alter table part_test EXCHANGE PARTITION for(20) with table change_test;mogdb=> select * from change_test ;id | name----+------(0 rows)mogdb=> select * from part_test partition (P3);id | name----+-------20 | test125 | test2(2 rows)

1.6 OpenGauss/MogDB 合并分区

MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name [ TABLESPACE tablespacename ]

-- 将分区 p3,p4 合并成分区 p3_4alter table part_test MERGE PARTITIONS P3,P4 INTO PARTITION P3_4;mogdb=> select relname,parttype,boundaries from pg_partition where parentid='part_test'::regclass ;relname | parttype | boundaries-----------+----------+------------part_test | r |p1 | p | {10}p2 | p | {20}p3_4| p | {40}p5 | p | {50}p6 | p | {NULL}(6 rows)

1.7 OpenGauss/MogDB 切割分区

SPLIT PARTITION { partition_name | FOR ( partition_value [, ...] ) } { split_point_clause | no_split_point_clause }

指定切割点split_point_clause的语法为

AT ( partition_value ) INTO ( PARTITION partition_name [ TABLESPACE tablespacename ] , PARTITION partition_name [ TABLESPACE tablespacename ]--◾ 列存分区表不支持切割分区。--◾ 切割点的大小要位于正在被切割的分区的分区键范围内，指定切割点的方式只能把一个分区切割成两个新分区。

不指定切割点no_split_point_clause的语法

INTO { ( partition_less_than_item [, ...] ) | ( partition_start_end_item [, ...] ) }--◾ 不指定切割点的方式，partition_less_than_item指定的第一个新分区的分区键要大于正在被切割的分区的前一个分区（如果存在的话）的分区键，partition_less_than_item指定的最后一个分区的分区键要等于正在被切割的分区的分区键大小。--◾ 不指定切割点的方式，partition_start_end_item指定的第一个新分区的起始点（如果存在的话）必须等于正在被切割的分区的前一个分区（如果存在的话）的分区键，partition_start_end_item指定的最后一个分区的终止点（如果存在的话）必须等于正在被切割的分区的分区键。--◾ partition_less_than_item支持的分区键个数最多为4，而partition_start_end_item仅支持1个分区键，其支持的数据类型参见PARTITION BY RANGE(parti...。--◾ 在同一语句中partition_less_than_item和partition_start_end_item两者不可同时使用；不同split语句之间没有限制。

-- 使用指定切割点，将 p3_4 分区从 25 开始拆分成两个分区：p3 p4。alter table part_test SPLIT PARTITION P3_4 AT (25) INTO (PARTITION P3,PARTITION P4);mogdb=> select relname,parttype,boundaries from pg_partition where parentid='part_test'::regclass ;relname | parttype | boundaries-----------+----------+------------part_test | r |p1 | p | {10}p2 | p | {20}p5 | p | {50}p6 | p | {NULL}p3 | p | {25}p4 | p | {40}(7 rows)mogdb=> select * from part_test partition (p3);id | name----+-------20 | test1(1 row)mogdb=> select * from part_test partition (p4);id | name----+-------25 | test231 | dd35 | ee37 | gg(4 rows)-- 切割分区后的分区表中的数据会重新路由到新的分区

1.8 默认分区

PostgreSQL

-- 创建默认分区语法create table partition_name PARTITION OF partitioned_table_name DEFAULT;-- 经过一系列上面的默认分区为 p6_defaultpg13=> \d+ part_testPartitioned table "public.part_test"Column | Type | Collation | Nullable | Default | Storage | Stats target | Description--------+-------------------+-----------+----------+---------+----------+--------------+-------------id| integer | || | plain | |name | character varying | || | extended | |Partition key: RANGE (id)Partitions: p1 FOR VALUES FROM (0) TO (10),p2 FOR VALUES FROM (10) TO (20),p3 FOR VALUES FROM (20) TO (30),p4 FOR VALUES FROM (30) TO (40),p5 FOR VALUES FROM (40) TO (50),p6_default DEFAULT-- 当插入的 id > 50 或者 id < 0 时，数据会插入到默认分区中pg13=> insert into part_test values (100,'max') ;INSERT 0 1pg13=> select * from p6_default ;id | name-----+------100 | max(1 row)pg13=> insert into part_test values (-2,'min') ;INSERT 0 1pg13=> select * from p6_default ;id | name-----+------100 | max-2 | min(2 rows)

OpenGauss/MogDB

mogdb=> select relname,relname,parentid::regclass,boundaries from pg_partition where parentid = 'part_test' ::regclass ;relname | relname | parentid | boundaries-----------+-----------+-----------+------------part_test | part_test | part_test |p1 | p1 | part_test | {10}p2 | p2 | part_test | {20}p5 | p5 | part_test | {50}p6 | p6 | part_test | {NULL}p3 | p3 | part_test | {25}p4 | p4 | part_test | {40}(7 rows)-- 当插入数据时的 id > 50 时，会插入到 LESS THAN(maxvalue) 即p6分区mogdb=> insert into part_test values (100,'max') ;INSERT 0 1mogdb=> select * from part_test partition(p6) ;id | name-----+------100 | max(1 row)mogdb=> insert into part_test values (-2,'min') ;INSERT 0 1mogdb=> select * from part_test partition(p1);id | name----+------2 | aa4 | bb-2 | min(3 rows)

结论：在范围分区表中，不论是 PostgreSQL 的 default 分区，还是 OpenGauss/MogDB 的 less than(maxvalue)，都是为了防止插入不在分区范围内的数据时的报错。

1.9 清理表分区数据

PostgreSQL

pg13=> select * from part_test ;id | name-----+------2 | aa4 | bb17 | cc31 | dd35 | ee37 | gg100 | max-2 | min(8 rows)pg13=> select * from p2;id | name----+------17 | cc(1 row)-- 清空分区 p2 的数据pg13=> truncate table p2;TRUNCATE TABLEpg13=> select * from p2;id | name----+------(0 rows)pg13=> select * from part_test ;id | name-----+------2 | aa4 | bb31 | dd35 | ee37 | gg100 | max-2 | min(7 rows)

OpenGauss/MogDB

语法：

ALTER TABLE [ IF EXISTS ] { [ ONLY ] table_name | table_name * | ONLY ( table_name ) }TRUNCATE PARTITION { partition_name | FOR ( partition_value [, ...] ) } ;

示例：

mogdb=> select * from part_test;id | name-----+-------2 | aa4 | bb-2 | min25 | test231 | dd35 | ee37 | gg17 | cc100 | max(9 rows)mogdb=> select * from part_test partition (p1);id | name----+------2 | aa4 | bb-2 | min(3 rows)-- 清空分区 p2 数据mogdb=> alter table part_test truncate partition p1;ALTER TABLEmogdb=> select * from part_test partition (p1);id | name----+------(0 rows)mogdb=> select * from part_test;id | name-----+-------25 | test231 | dd35 | ee37 | gg17 | cc100 | max(6 rows)

1.10 备份

PostgreSQL - pg_dump

# 分区表pg=> \d+ part_testPartitioned table "public.part_test"Column | Type | Collation | Nullable | Default | Storage | Stats target | Description--------+-------------------+-----------+----------+---------+----------+--------------+-------------id| integer | || | plain | |name | character varying | || | extended | |Partition key: RANGE (id)Partitions: part_test_1 FOR VALUES FROM (0) TO (10),part_test_2 FOR VALUES FROM (10) TO (20),part_test_3 FOR VALUES FROM (20) TO (30),part_test_4 FOR VALUES FROM (30) TO (40),part_test_5 DEFAULT# 导出自定义的归档模式，之后可以使用 pg_restore 恢复数据库，如果只想导出表结构，可以加 -s 选项pg_dump -d pg -U pg -t part_test -t 'part_test_*' -Fc --load-via-partition-root > db.dump# 导出的文件为 sqlpg_dump -d pg -U pg -t part_test -t 'part_test_*' -Fp --load-via-partition-root > part.sql## 可以查看导出的 sql 文本[postgres@pg13 ~]$ cat part.sql---- PostgreSQL database dump---- Dumped from database version 13.4-- Dumped by pg_dump version 13.4SET statement_timeout = 0;SET lock_timeout = 0;SET idle_in_transaction_session_timeout = 0;SET client_encoding = 'UTF8';SET standard_conforming_strings = on;SELECT pg_catalog.set_config('search_path', '', false);SET check_function_bodies = false;SET xmloption = content;SET client_min_messages = warning;SET row_security = off;SET default_tablespace = '';---- Name: part_test; Type: TABLE; Schema: public; Owner: pg--CREATE TABLE public.part_test (id integer,name character varying)PARTITION BY RANGE (id);ALTER TABLE public.part_test OWNER TO pg;SET default_table_access_method = heap;---- Name: part_test_1; Type: TABLE; Schema: public; Owner: pg--CREATE TABLE public.part_test_1 (id integer,name character varying);ALTER TABLE ONLY public.part_test ATTACH PARTITION public.part_test_1 FOR VALUES FROM (0) TO (10);ALTER TABLE public.part_test_1 OWNER TO pg;---- Name: part_test_2; Type: TABLE; Schema: public; Owner: pg--CREATE TABLE public.part_test_2 (id integer,name character varying);ALTER TABLE ONLY public.part_test ATTACH PARTITION public.part_test_2 FOR VALUES FROM (10) TO (20);ALTER TABLE public.part_test_2 OWNER TO pg;---- Name: part_test_3; Type: TABLE; Schema: public; Owner: pg--CREATE TABLE public.part_test_3 (id integer,name character varying);ALTER TABLE ONLY public.part_test ATTACH PARTITION public.part_test_3 FOR VALUES FROM (20) TO (30);ALTER TABLE public.part_test_3 OWNER TO pg;---- Name: part_test_4; Type: TABLE; Schema: public; Owner: pg--CREATE TABLE public.part_test_4 (id integer,name character varying);ALTER TABLE ONLY public.part_test ATTACH PARTITION public.part_test_4 FOR VALUES FROM (30) TO (40);ALTER TABLE public.part_test_4 OWNER TO pg;---- Name: part_test_5; Type: TABLE; Schema: public; Owner: pg--CREATE TABLE public.part_test_5 (id integer,name character varying);ALTER TABLE ONLY public.part_test ATTACH PARTITION public.part_test_5 DEFAULT;ALTER TABLE public.part_test_5 OWNER TO pg;---- Data for Name: part_test_1; Type: TABLE DATA; Schema: public; Owner: pg--COPY public.part_test (id, name) FROM stdin;2 aa4 bb\.---- Data for Name: part_test_2; Type: TABLE DATA; Schema: public; Owner: pg--COPY public.part_test (id, name) FROM stdin;17cc\.---- Data for Name: part_test_3; Type: TABLE DATA; Schema: public; Owner: pg--COPY public.part_test (id, name) FROM stdin;\.---- Data for Name: part_test_4; Type: TABLE DATA; Schema: public; Owner: pg--COPY public.part_test (id, name) FROM stdin;31dd35ee37gg\.---- Data for Name: part_test_5; Type: TABLE DATA; Schema: public; Owner: pg--COPY public.part_test (id, name) FROM stdin;\.---- PostgreSQL database dump complete--

OpenGauss/MogDB - gs_dump

# 导出自定义的归档模式，之后可以使用 gs_restore 恢复数据库，如果只想导出表结构，可以加 -s 选项gs_dump mogdb -U mogdb -t part_test -p 26000 -Fc -f db.dump# 导出的文件为 sql gs_dump mogdb -U mogdb -t part_test -p 26000 -Fp -f part.sql## 可以查看导出的 sql 文本[omm@mogdb ~]$ cat part.sql---- PostgreSQL database dump--SET statement_timeout = 0;SET xmloption = content;SET client_encoding = 'UTF8';SET standard_conforming_strings = on;SET check_function_bodies = false;SET client_min_messages = warning;SET search_path = public;SET default_tablespace = '';SET default_with_oids = false;---- Name: part_test; Type: TABLE; Schema: public; Owner: mogdb; Tablespace:--CREATE TABLE part_test (id integer,name character varying)WITH (orientation=row, compression=no)PARTITION BY RANGE (id)(PARTITION p1 VALUES LESS THAN (10) TABLESPACE pg_default,PARTITION p4 VALUES LESS THAN (40) TABLESPACE pg_default,PARTITION p5 VALUES LESS THAN (50) TABLESPACE pg_default,PARTITION p6 VALUES LESS THAN (MAXVALUE) TABLESPACE pg_default)ENABLE ROW MOVEMENT;ALTER TABLE public.part_test OWNER TO mogdb;---- Data for Name: part_test; Type: TABLE DATA; Schema: public; Owner: mogdb--COPY part_test (id, name) FROM stdin;2 aa4 bb-2min25test231dd35ee37gg17cc100max\.;---- PostgreSQL database dump complete--

结论：PostgreSQL 导出完整的分区表，需要在执行 pg_dump 时指定 “-t” 加分区表名和各个分区名,同理，如果只指定分区表名，则只会导出分区表，在进行 pg_restore 时，只会显示分区表名，数据也不会导入；而 MogDB 只需指定分区表名即可

注：MogDB 使用 gs_dump 导出指定的数据库，不需要 “-d”，直接指定“dbname”。

1.11 恢复

PostgreSQL - pg_restore

OpenGauss/MogDB - gs_restore

# 导入数据库的gs_restore -d dbtest -U omm -p 26000 -c -e db.dump# 登录数据库查看[omm@mogdb ~]$ gsql -d dbtest -p 26000 -rgsql ((MogDB 2.0.1 build f892ccb7) compiled at -07-09 16:12:59 commit 0 last mr )Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.dbtest=# \dList of relationsSchema | Name | Type | Owner | Storage--------+-----------+-------+-------+----------------------------------public | part_test | table | mogdb | {orientation=row,compression=no}(1 row)dbtest=# select * from part_test ;id | name-----+-------2 | aa4 | bb-2 | min25 | test231 | dd35 | ee37 | gg17 | cc100 | max(9 rows)

注：在执行 restore 数据恢复时，所指定的用户必须对该数据库有创建表和插入数据的权限，否则执行时会报错。

1.12 OpenGauss/MogDB 分区表操作注意

添加分区的表空间不能是PG_GLOBAL。添加分区的名称不能与该分区表已有分区的名称相同。添加分区的分区键值要和分区表的分区键的类型一致，且要大于分区表中最后一个范围分区的上边界。如果目标分区表中已有分区数达到了最大值，则不能继续添加分区（范围分区表的分区数最大值是32767，哈希/列表分区表最大值是64）。当分区表只有一个分区时，不能删除该分区。选择分区使用PARTITION FOR()，括号里指定值个数应该与定义分区时使用的列个数相同，并且一一对应。Value分区表不支持相应的Alter Partition操作。列存分区表不支持切割分区。间隔分区表不支持添加分区。哈希分区表不支持修改表分区操作。列表分区表仅支持添加/删除分区。只有分区表的所有者或者被授予了分区表ALTER权限的用户有权限执行ALTER TABLE PARTITION命令，系统管理员默认拥有此权限。

2. mtk迁移工具：pg–>OpenGauss/MogDB 分区表

2.1 迁移步骤

PostgreSQL 创建分区表

pg 的分区表有两种定义方法：

声明式划分：

继承

create table part_inherit(id int,name varchar);CREATE TABLE p1_inherit (CHECK ( id >= 0 AND id < 10 )) INHERITS (part_inherit);CREATE TABLE p2_inherit (CHECK ( id >= 10 AND id < 20 )) INHERITS (part_inherit);CREATE TABLE p3_inherit (CHECK ( id >= 20 AND id < 30 )) INHERITS (part_inherit);pg=> \dList of relationsSchema |Name| Type | Owner--------+--------------+-------+-------public | p1_inherit | table | pgpublic | p2_inherit | table | pgpublic | p3_inherit | table | pgpublic | part_inherit | table | pg(4 rows)pg=> \d+ p1_inheritTable "public.p1_inherit"Column | Type | Collation | Nullable | Default | Storage | Stats target | Description--------+-------------------+-----------+----------+---------+----------+--------------+-------------id| integer | || | plain | |name | character varying | || | extended | |Check constraints:"p1_inherit_id_check" CHECK (id >= 0 AND id < 10)Inherits: part_inheritAccess method: heappg=> \d+ part_inheritTable "public.part_inherit"Column | Type | Collation | Nullable | Default | Storage | Stats target | Description--------+-------------------+-----------+----------+---------+----------+--------------+-------------id| integer | || | plain | |name | character varying | || | extended | |Child tables: p1_inherit,p2_inherit,p3_inheritAccess method: heap

配置 pg2mogdb.json 文件

查看迁移的表

[postgres@pg13 mtk_v2.1.1-beta-3d766b40_linux_amd64]$ ./mtk show-table -c pg2mogdb.jsonuse config :pg2mogdb.jsonlogfile: ./data/report_postgres_mogdb.logtime="-12-12 17:46:17.623347" level=info msg="Release version: v2.1.1-beta-3d766b40Git Commit hash: 3d766b40Git Tag : v2.1.0Build timestamp: -11-26T15:50:23Z" function=genMtk line=97 file="mtk/cmd/mtk/services/cmd.go"time="-12-12 17:46:17.623442" level=info msg="start init reader " function=initReader line=73 file="mtk/pkg/mtk/reader.go" taskID=1469966830129909760time="-12-12 17:46:17.623466" level=info msg="start init Postgres reader " function=Init line=45 file="mtk/pkg/databases/postgres/reader.go" taskID=1469966830129909760time="-12-12 17:46:17.623493" level=info msg="start init openGauss writer " function=Init line=40 file="mtk/pkg/databases/opengauss/writer.go" taskID=1469966830129909760time="-12-12 17:46:17.62351" level=info msg="start init openGauss " function=Init line=238 file="mtk/pkg/databases/opengauss/gauss.go" taskID=1469966830129909760time="-12-12 17:46:17.623597" level=info msg="the connect database using: application_name=mtk_postgres connect_timeout=30 cp_buffer_size=8388608 dbname=pg host=192.168.183.201 min_read_buffer_size=8388608 password=****** port=6000 sslmode=disable user=pg" function=connectDatabase line=293 file="mtk/pkg/databases/postgres/postgres.go" taskID=1469966830129909760time="-12-12 17:46:17.626827" level=info msg="the server full version : PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 0623 (Red Hat 4.8.5-44), 64-bit" function=GetServerInfo line=332 file="mtk/pkg/databases/postgres/postgres.go" taskID=1469966830129909760time="-12-12 17:46:17.626887" level=info msg="the server version : 13.4" taskID=1469966830129909760 function=GetServerInfo line=335 file="mtk/pkg/databases/postgres/postgres.go"time="-12-12 17:46:17.627934" level=info msg="the database charset UTF8" function=GetCharSet line=510 file="mtk/pkg/databases/postgres/postgres.go" taskID=1469966830129909760time="-12-12 17:46:17.628108" level=info msg="database Timezone: Asia/Shanghai, local timezone: Local" function=GetTimeZone line=362 file="mtk/pkg/databases/postgres/postgres.go" taskID=1469966830129909760TabNamePartitioned TotalSize DataSize LobSize Rows AvgRowsLen ColCount UnCompresSizepublic.part_inherit false 0B 0B 0B0 0B0 0Bpublic.p1_inherit false 0B 0B 0B0 0B0 0Bpublic.p2_inherit false 0B 0B 0B0 0B0 0Bpublic.p3_inherit false 0B 0B 0B0 0B0 0Bpublic.part_test false 0B 0B 0B0 0B0 0B

迁移

./mtk -c pg2mogdb.json --reportFile mtk_report.html --logfile mtk_report.log --debug

在 MogDB 中查看结果

test=> \dList of relationsSchema |Name| Type | Owner | Storage--------+--------------+-------+-------+----------------------------------public | p1_inherit | table | mogdb | {orientation=row,compression=no}public | p2_inherit | table | mogdb | {orientation=row,compression=no}public | p3_inherit | table | mogdb | {orientation=row,compression=no}public | part_inherit | table | mogdb | {orientation=row,compression=no}public | part_test | table | mogdb | {orientation=row,compression=no}(5 rows)test=> \d+ part_testTable "public.part_test"Column | Type | Modifiers | Storage | Stats target | Description--------+-------------------+-----------+----------+--------------+-------------id| integer | | plain | |name | character varying | | extended | |Range partition by(id)Number of partition: 4 (View pg_partition to check each partition range.)Has OIDs: noOptions: orientation=row, compression=no-- 查看声明式分区test=> select relname,parttype,parentid::regclass,boundaries from pg_partition ;relname | parttype | parentid | boundaries-----------+----------+-----------+------------part_test | r | part_test |p1 | p | part_test | {10}p2 | p | part_test | {20}p3 | p | part_test | {30}p4 | p | part_test | {40}(5 rows)test=> select * from part_test ;id | name----+------2 | aa4 | bb17 | cc31 | dd35 | ee37 | gg(6 rows)-- 查看继承test=> \d+ part_inheritTable "public.part_inherit"Column | Type | Modifiers | Storage | Stats target | Description--------+-------------------+-----------+----------+--------------+-------------id| integer | | plain | |name | character varying | | extended | |Has OIDs: noOptions: orientation=row, compression=notest=> \d+ p1_inheritTable "public.p1_inherit"Column | Type | Modifiers | Storage | Stats target | Description--------+-------------------+-----------+----------+--------------+-------------id| integer | | plain | |name | character varying | | extended | |Check constraints:"p1_inherit_id_check" CHECK (id >= 0 AND id < 10)Has OIDs: noOptions: orientation=row, compression=no