作者：lmj
往期参考：PostgrSQL和OpenGauss/MogDB分区表对比（一）
对比
| PostgreSQL | OpenGauss/MogDB | |
|---|---|---|
| 增加分区（挂载分区） | ATTACH | ADD PARTITION |
| 删除分区（卸载分区） | DETACH | drop partition |
| 重命名分区 | RENAME TO | RENAME PARTITION |
| 置换分区 | 不支持 | EXCHANGE PARTITION |
| 合并分区 | 不支持 | MERGE PARTITIONS |
| 切割分区（拆分分区） | 不支持 | SPLIT PARTITION |
| 默认分区 | DEFAULT | MAXVALUE(less than 语法) |
| 清理表分区数据 | TRUNCATE TABLE | TRUNCATE PARTITION |
| 备份 | pg_dump | gs_dump |
| 恢复 | pg_restore | gs_restore |
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 | aa
4 | bb
17 | cc
31 | dd
35 | ee
37 | gg
(6 rows)
pg13=> select * from p4;
id | name
----+------
31 | dd
35 | ee
37 | 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 | aa
4 | bb
17 | cc
31 | dd
35 | ee
37 | gg
(6 rows)
mogdb=> select * from part_test partition(P4);
id | name
----+------
31 | dd
35 | ee
37 | 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_test
Partitioned 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_id
CHECK ( id >= 40 AND id < 50 );
--- 此处插入数据是为了测试挂载分区时，表中的已有数据是不是会随着挂载而添加到分区表中
insert into p5 values (43,'lmj');
ALTER TABLE part_test ATTACH PARTITION p5
FOR VALUES FROM (40) TO (50);
pg13=> \d+ part_test
Partitioned 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 | aa
4 | bb
17 | cc
31 | dd
35 | ee
37 | gg
43 | lmj
(7 rows)
-- 加入默认表分区
create table p6 PARTITION OF part_test DEFAULT;
pg13=> \d+ part_test
Partitioned 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_test
Partitioned 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 | test1
25 | 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 | test1
25 | test2
(2 rows)
1.6 OpenGauss/MogDB 合并分区
MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name
[ TABLESPACE tablespacename ]
-- 将 分区 p3,p4 合并成 分区 p3_4
alter 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 | test2
31 | dd
35 | ee
37 | gg
(4 rows)
-- 切割分区后的分区表中的数据会重新路由到新的分区
1.8 默认分区
PostgreSQL
-- 创建默认分区语法
create table partition_name PARTITION OF partitioned_table_name DEFAULT;
-- 经过一系列上面的默认分区为 p6_default
pg13=> \d+ part_test
Partitioned 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 1
pg13=> select * from p6_default ;
id | name
-----+------
100 | max
(1 row)
pg13=> insert into part_test values (-2,'min') ;
INSERT 0 1
pg13=> 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 1
mogdb=> select * from part_test partition(p6) ;
id | name
-----+------
100 | max
(1 row)
mogdb=> insert into part_test values (-2,'min') ;
INSERT 0 1
mogdb=> select * from part_test partition(p1);
id | name
----+------
2 | aa
4 | bb
-2 | min
(3 rows)
结论：在范围分区表中，不论是 PostgreSQL 的 default 分区，还是 OpenGauss/MogDB 的 less than(maxvalue)，都是为了防止插入不在分区范围内的数据时的报错。
1.9 清理表分区数据
PostgreSQL
pg13=> select * from part_test ;
id | name
-----+------
2 | aa
4 | bb
17 | cc
31 | dd
35 | ee
37 | gg
100 | max
-2 | min
(8 rows)
pg13=> select * from p2;
id | name
----+------
17 | cc
(1 row)
-- 清空分区 p2 的数据
pg13=> truncate table p2;
TRUNCATE TABLE
pg13=> select * from p2;
id | name
----+------
(0 rows)
pg13=> select * from part_test ;
id | name
-----+------
2 | aa
4 | bb
31 | dd
35 | ee
37 | gg
100 | 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 | aa
4 | bb
-2 | min
25 | test2
31 | dd
35 | ee
37 | gg
17 | cc
100 | max
(9 rows)
mogdb=> select * from part_test partition (p1);
id | name
----+------
2 | aa
4 | bb
-2 | min
(3 rows)
-- 清空分区 p2 数据
mogdb=> alter table part_test truncate partition p1;
ALTER TABLE
mogdb=> select * from part_test partition (p1);
id | name
----+------
(0 rows)
mogdb=> select * from part_test;
id | name
-----+-------
25 | test2
31 | dd
35 | ee
37 | gg
17 | cc
100 | max
(6 rows)
1.10 备份
PostgreSQL - pg_dump
# 分区表
pg=> \d+ part_test
Partitioned 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
# 导出的文件为 sql
pg_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.4
SET 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 aa
4 bb
\.
--
-- Data for Name: part_test_2; Type: TABLE DATA; Schema: public; Owner: pg
--
COPY public.part_test (id, name) FROM stdin;
17 cc
\.
--
-- 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;
31 dd
35 ee
37 gg
\.
--
-- 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 aa
4 bb
-2 min
25 test2
31 dd
35 ee
37 gg
17 cc
100 max
\.
;
--
-- PostgreSQL database dump complete
--
结论：PostgreSQL 导出完整的分区表，需要在执行 pg_dump 时指定 “-t” 加分区表名和各个分区名,同理，如果只指定分区表名，则只会导出分区表，在进行 pg_restore 时，只会显示分区表名，数据也不会导入；而 MogDB 只需指定分区表名即可
注：MogDB 使用 gs_dump 导出指定的数据库，不需要 “-d”，直接指定“dbname”。
1.11 恢复
PostgreSQL - pg_restore
pg_restore -d mogdb db.dump
# 查看导入的数据
[postgres@pg13 ~]$ psql -d mogdb
psql (13.4)
Type "help" for help.
mogdb=#
mogdb=#
mogdb=#
mogdb=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+-------------------+-------
public | part_test | partitioned table | pg
public | part_test_1 | table | pg
public | part_test_2 | table | pg
public | part_test_3 | table | pg
public | part_test_4 | table | pg
public | part_test_5 | table | pg
(6 rows)
mogdb=# select * from part_test;
id | name
----+------
2 | aa
4 | bb
17 | cc
31 | dd
35 | ee
37 | gg
(6 rows)
OpenGauss/MogDB - gs_restore
# 导入数据库的
gs_restore -d dbtest -U omm -p 26000 -c -e db.dump
# 登录数据库查看
[omm@mogdb ~]$ gsql -d dbtest -p 26000 -r
gsql ((MogDB 2.0.1 build f892ccb7) compiled at 2021-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=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+-----------+-------+-------+----------------------------------
public | part_test | table | mogdb | {orientation=row,compression=no}
(1 row)
dbtest=# select * from part_test ;
id | name
-----+-------
2 | aa
4 | bb
-2 | min
25 | test2
31 | dd
35 | ee
37 | gg
17 | cc
100 | 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_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);
pg=> \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------------------+-------
public | p1 | table | pg
public | p2 | table | pg
public | p3 | table | pg
public | p4 | table | pg
public | p5 | table | pg
public | part_range | partitioned table | pg
(6 rows)
-- 插入测试数据
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 | aa
4 | bb
17 | cc
31 | dd
35 | ee
37 | gg
(6 rows)
- 继承
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=> \d
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+-------
public | p1_inherit | table | pg
public | p2_inherit | table | pg
public | p3_inherit | table | pg
public | part_inherit | table | pg
(4 rows)
pg=> \d+ p1_inherit
Table "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_inherit
Access method: heap
pg=> \d+ part_inherit
Table "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_inherit
Access method: heap
配置 pg2mogdb.json 文件
查看迁移的表
[postgres@pg13 mtk_v2.1.1-beta-3d766b40_linux_amd64]$ ./mtk show-table -c pg2mogdb.json
use config :pg2mogdb.json
logfile: ./data/report_postgres_mogdb.log
time="2021-12-12 17:46:17.623347" level=info msg="Release version: v2.1.1-beta-3d766b40
Git Commit hash: 3d766b40
Git Tag : v2.1.0
Build timestamp: 2021-11-26T15:50:23Z
" function=genMtk line=97 file="mtk/cmd/mtk/services/cmd.go"
time="2021-12-12 17:46:17.623442" level=info msg="start init reader " function=initReader line=73 file="mtk/pkg/mtk/reader.go" taskID=1469966830129909760
time="2021-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=1469966830129909760
time="2021-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=1469966830129909760
time="2021-12-12 17:46:17.62351" level=info msg="start init openGauss " function=Init line=238 file="mtk/pkg/databases/opengauss/gauss.go" taskID=1469966830129909760
time="2021-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=1469966830129909760
time="2021-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 20150623 (Red Hat 4.8.5-44), 64-bit" function=GetServerInfo line=332 file="mtk/pkg/databases/postgres/postgres.go" taskID=1469966830129909760
time="2021-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="2021-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=1469966830129909760
time="2021-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=1469966830129909760
TabName Partitioned TotalSize DataSize LobSize Rows AvgRowsLen ColCount UnCompresSize
public.part_inherit false 0B 0B 0B 0 0B 0 0B
public.p1_inherit false 0B 0B 0B 0 0B 0 0B
public.p2_inherit false 0B 0B 0B 0 0B 0 0B
public.p3_inherit false 0B 0B 0B 0 0B 0 0B
public.part_test false 0B 0B 0B 0 0B 0 0B
迁移
./mtk -c pg2mogdb.json --reportFile mtk_report.html --logfile mtk_report.log --debug
在 MogDB 中查看结果
test=> \d
List of relations
Schema | 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_test
Table "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: no
Options: 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 | aa
4 | bb
17 | cc
31 | dd
35 | ee
37 | gg
(6 rows)
-- 查看继承
test=> \d+ part_inherit
Table "public.part_inherit"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character varying | | extended | |
Has OIDs: no
Options: orientation=row, compression=no
test=> \d+ p1_inherit
Table "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: no
Options: orientation=row, compression=no
2.2 结论
- 不支持 pg分区表中按声明式划分创建的默认分区的迁移：create table p5 PARTITION OF part_test DEFAULT;
报错提示：
- 除上述不支持的条件外，其他迁移成功。
- mtk不支持表继承关系的迁移，所以根据继承方式在pg中创建的分区表迁移到 OpenGauss/MogDB 中继承关系不存在，成为独立的表。
附：想要使用更高级的功能管理 PostgreSQL 的分区表，可以使用插件：pg_pathman、pg_partman
1081
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
