Migrate partitions

Usage notes

• To migrate level-1 partitions, the version of your Alibaba Cloud PolarDB for Xscale (PolarDB-X) instance must be 5.4.14-16539836 or later.

• To migrate level-2 partitions, the version of your PolarDB-X instance must be 5.4.17-16952556 or later.

Terms

• Table group: a collection of logical tables or global index tables that share identical partition key columns.

• Global index: an indexing technique for partitioned tables. You can create global indexes on partitioned tables by using non-partition keys. Global indexes can provide unique constraints.

• Partition migration: an operation performed on a partition. You can migrate partitions of a table from one data node (DN) to another.

Syntax

ALTER { TABLE tbl_name | TABLEGROUP tg_name | TABLEGROUP BY TABLE tbl_name } 
		move_partition_specs_definition
 | move_subpartition_specs_definition

 
move_partition_specs_definition:
MOVE PARTITIONS part_name[,...,part_name] to dn_id

move_subpartition_specs_definition:
MOVE SUBPARTITIONS subpartition_name[,...,subpartition_name] to dn_id

Scenario 1: Migrate level-1 partitions that does not contain level-2 partitions

The following code block shows the definition of the tb1 table:

create table tb1(a int) partition by key(a) partitions 3;

By default, the names of the three level-1 partitions are p1, p2, and p3.

You can execute the following SQL statement to migrate the p1 and p3 level-1 partitions to the data node DN2 (where DN2 is the ID of the data node):

alter table tb1 move partitions p1,p3 to 'DN2';

Migrate partitions in a table group

In this example, the tb1 table shares the same definition as the tb2 table and they belong to the same table group, mytg1. The table group name must be unique. In the following examples, the mytg1 table group is used.

create tablegroup mytg1;
create table tb1(a int) partition by key(a) partitions 3 tablegroup=mytg1;
 
create table tb2(a int) partition by key(a) partitions 3 tablegroup=mytg1;
 

By default, the names of the three level-1 partitions are p1, p2, and p3.

You can execute the following statement to migrate the p1 and p3 level-1 partitions of the mytg1 table group to the specified data node DN2 (where DN2 is the ID of the data node):

alter tablegroup mytg1 move partitions p1,p3 to 'DN2';

Scenario 2: Migrate level-1 partitions that contain level-2 partitions

When you migrate level-1 partitions that contain level-2 partitions, all level-2 partitions under the level-1 partitions will be migrated to the specified data node.

Migrate partitions of a table

The following code block shows the definition of the t1 table:

create table t1 (
a bigint unsigned not null,
b bigint unsigned not null,
c datetime NOT NULL,
d varchar(16) NOT NULL,
e varchar(16) NOT NULL
)
partition by key (a,b) partitions 4
subpartition by range columns (c,d)
(
partition p1
(
subpartition p1sp1 values less than ( '2020-01-01', 'abc' ),
subpartition p1sp2 values less than ( maxvalue, maxvalue )
),
partition p2
(
subpartition p2sp1 values less than ( '2020-01-01', 'abc' ),
subpartition p2sp2 values less than ( '2021-01-01', 'abc' ),
subpartition p2sp3 values less than ( '2022-01-01', 'abc' ),
subpartition p2sp4 values less than ( maxvalue, maxvalue )
),
partition p3
(
subpartition p3sp1 values less than ( '2020-01-01', 'abc' ),
subpartition p3sp2 values less than ( maxvalue, maxvalue )
),
partition p4
(
subpartition p4sp1 values less than ( '2020-01-01', 'abc' ),
subpartition p4sp2 values less than ( maxvalue, maxvalue )
)
);

You can execute the following statement to migrate the p1 and p3 level-1 partitions of the mytg1 table group to the specified data node DN2 (where DN2 is the ID of the data node):

alter table t1 move partitions p1,p3 to 'DN2';

After you execute the preceding SQL statement, all level-2 partitions under the p1 and p3 level-1 partitions are migrated to the data node DN2. The following SQL statement can achieve the same result as the preceding SQL statement:

alter table t1 move subpartitions p1sp1,p1sp2,p3sp1,p3sp2 to 'DN2';

Migrate partitions in a table group

The operations are similar to those for migrating table-level partitions. You need to only replace alter table #tb with alter tablegroup #tgname or alter tablegroup by #tb.

Scenario 3: Migrate templated level-2 partitions

You can migrate level-2 partitions that share the same template-based definition or specific level-2 partitions. When you migrate templated level-2 partitions, the corresponding templated level-2 partitions under all level-1 partitions are migrated.

Migrate partitions of a table

The following code block shows the definition of the tb1 table:

create table tb1 (
a bigint unsigned not null,
b bigint unsigned not null,
c bigint NOT NULL,
d varchar(16) NOT NULL,
e varchar(16) NOT NULL
)
partition by key (a,b) partitions 4
subpartition by range (c) (
 subpartition sp1 values less than ( 1000 ),
 subpartition sp2 values less than ( 2000 ),
 subpartition sp3 values less than ( maxvalue )
);

By default, the names of the four level-1 partitions are p1, p2, p3, and p4. The tb1 table contains the following 12 level-2 partitions:

p1sp1, p2sp1, p3sp1, p4sp1

p1sp2, p2sp2, p3sp2, p4sp2

p1sp3, p2sp3, p3sp3, p4sp3

You can migrate all level-2 partitions that share the same template-based definition. For example, you can execute the following SQL statement to migrate all level-2 partitions that share the template-based definition of sp1 to the data node DN-1:

alter table tb1 move subpartitions sp1 to 'DN-1'

After you execute the preceding SQL statement, all level-2 partitions that share the template-based definition of sp1 are migrated to the data node DN-1. The following SQL statement can achieve the same result as the preceding SQL statement:

alter table tb1 move subpartitions p1sp1, p2sp1, p3sp1, p4sp1 to 'DN-1'

You can also execute the following SQL statement to migrate specific level-2 partitions, such as the p1sp1 and p2sp1 partitions, to the data node DN-1:

alter table tb1 move subpartitions p1sp1, p2sp1 to 'DN-1'

Migrate partitions in a table group

The operations are similar to those for migrating table-level partitions. You need to only replace alter table #tb with alter tablegroup #tgname or alter tablegroup by #tb.

Scenario 4: Migrate non-templated level-2 partitions

When you migrate non-templated level-2 partitions, you must individually migrate the desired non-templated level-2 partitions. For more information about the statements that you can execute to migrate partitions, see Scenario 3: Migrate templated level-2 partitions.