首页 > 其他分享 >【openGauss】openGauss分区表通过交换分区来实现分区数据迁移至历史表（附：常见错误解答FAQ（Frequently Asked Questions）

【openGauss】openGauss分区表通过交换分区来实现分区数据迁移至历史表（附：常见错误解答FAQ（Frequently Asked Questions）

时间：2025-01-21 13:02:26浏览次数：3  
标签：tzq 00 01 log 分区 分区表 TABLE openGauss

【openGauss】openGauss分区表通过交换分区来实现分区数据迁移至历史表（附：常见错误解答FAQ（Frequently Asked Questions）

本文演练前提在schema为tzq的环境下执行下列SQL，实际情况请根据自身schema进行修改。

一、对一级分区表交换分区

使用ALTER TABLE EXCHANGE PARTITION可以对一级分区表交换分区。
例如，通过指定分区名将范围分区表range_log_t的分区date_202001和普通表exchange_log_t进行交换，不进行分区键校验，并更新Global索引。

ALTER TABLE range_log_t EXCHANGE PARTITION (date_202001) WITH TABLE exchange_log_t WITHOUT VALIDATION UPDATE GLOBAL INDEX;

或者，通过指定分区值将范围分区表range_log_t中’2020-01-08’所对应的分区和普通表exchange_log_t进行交换，进行分区校验并将不满足目标分区约束的数据插入到分区表的其他分区中。由于不带UPDATE GLOBAL INDEX子句，执行该命令后Global索引会失效。

ALTER TABLE range_log_t EXCHANGE PARTITION FOR ('2020-01-08') WITH TABLE exchange_log_t WITH VALIDATION VERBOSE;

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

■普通表和分区的列数目相同，对应列的信息严格一致，包括：列名、列的数据类型、列约束、列的Collation信息、列的存储参数、列的压缩信息等。
■普通表和分区的表压缩信息严格一致。
■普通表索引和分区Local索引的个数相同，且对应索引的信息严格一致。
■普通表和分区的表约束个数相同，且对应表约束的信息严格一致。
■普通表不可以是临时表，分区表只能是范围分区表，列表分区表，哈希分区表或间隔分区表。
■普通表和分区表上不可以有动态数据兑敏，行访问控制约束。
■列表分区表，哈希分区表不能是列存储。

三、实操演练

3.1、创建测试分区表

/* 创建测试分区表 */
drop   table    if exists tzq.tzq_log_t cascade;
drop   sequence if exists tzq.tzq_log_s;
create sequence           tzq.tzq_log_s;
CREATE TABLE              tzq.tzq_log_t (
  log_id INT8 NOT NULL DEFAULT nextval('tzq.tzq_log_s'),
  log_title NVARCHAR2(100) not null,
  log_type NVARCHAR2(100),
  log_code NVARCHAR2(100),
  enable_flag NVARCHAR2(1) not null DEFAULT 'Y',
  delete_flag NVARCHAR2(1) not null DEFAULT 'N',
  created_by INT8 NOT NULL DEFAULT -1,
  creation_date timestamp without time zone NOT NULL DEFAULT STATEMENT_TIMESTAMP(),
  last_updated_by INT8 NOT NULL DEFAULT -1,
  last_update_date timestamp without time zone NOT NULL DEFAULT STATEMENT_TIMESTAMP(),
  sys_description NVARCHAR2(500),
  system_id int8 not null default 18812345678,
  CONSTRAINT pk_tzq_log_t PRIMARY KEY (log_id)
)partition by RANGE (creation_date) INTERVAL ('3 month')
(
  partition tzq_log_t_p20231 values less than (TO_DATE('2023-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20232 values less than (TO_DATE('2023-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20233 values less than (TO_DATE('2023-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20234 values less than (TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20241 values less than (TO_DATE('2024-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20242 values less than (TO_DATE('2024-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20243 values less than (TO_DATE('2024-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20244 values less than (TO_DATE('2025-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20251 values less than (TO_DATE('2025-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20252 values less than (TO_DATE('2025-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20253 values less than (TO_DATE('2025-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20254 values less than (TO_DATE('2026-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20261 values less than (TO_DATE('2026-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20262 values less than (TO_DATE('2026-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20263 values less than (TO_DATE('2026-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20264 values less than (TO_DATE('2027-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20271 values less than (TO_DATE('2027-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20272 values less than (TO_DATE('2027-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20273 values less than (TO_DATE('2027-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20274 values less than (TO_DATE('2028-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20281 values less than (TO_DATE('2028-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20282 values less than (TO_DATE('2028-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20283 values less than (TO_DATE('2028-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20284 values less than (TO_DATE('2029-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20291 values less than (TO_DATE('2029-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20292 values less than (TO_DATE('2029-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20293 values less than (TO_DATE('2029-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition tzq_log_t_p20294 values less than (TO_DATE('2030-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
);
create index idx_log_title on tzq_log_t (log_title);
create index idx_log_code on tzq_log_t (log_code);
/* 开启行级安全 */
ALTER TABLE tzq.tzq_log_t ENABLE ROW LEVEL SECURITY;
/* 强制开启行级安全 */
alter table tzq.tzq_log_t force row level security;
/* 创建行级策略 */
drop policy if exists system_isolation_policy on tzq.tzq_log_t;
create policy system_isolation_policy on tzq.tzq_log_t to tzq USING (system_id = current_setting('tzq.current_system'));

/* 插入测试数据 */
insert into tzq_log_t (log_title) select 'tzq_log_' || lpad(generate_series(1,1000000),7,'0');
/* 查询数据量 */
set tzq.current_system = 18812345678;
set search_path = tzq;
select count(*) from tzq_log_t;  -- 1000000
select count(*) from tzq_log_t partition (tzq_log_t_p20251);  -- 1000000

3.2、创建测试普通表

/* 创建普通表 */
drop   table    if exists tzq.tzq_log_t_p20251_th;
CREATE TABLE              tzq.tzq_log_t_p20251_th (
  log_id INT8 NOT NULL DEFAULT nextval('tzq.tzq_log_s'),
  log_title NVARCHAR2(100) not null,
  log_type NVARCHAR2(100),
  log_code NVARCHAR2(100),
  enable_flag NVARCHAR2(1) not null DEFAULT 'Y',
  delete_flag NVARCHAR2(1) not null DEFAULT 'N',
  created_by INT8 NOT NULL DEFAULT -1,
  creation_date timestamp without time zone NOT NULL DEFAULT STATEMENT_TIMESTAMP(),
  last_updated_by INT8 NOT NULL DEFAULT -1,
  last_update_date timestamp without time zone NOT NULL DEFAULT STATEMENT_TIMESTAMP(),
  sys_description NVARCHAR2(500),
  system_id int8 not null default 18812345678
);

注意：普通表的结构要跟分区表完全一致，包括默认值都要一致。

3.3、交换分区，把分区的表的分区数据置换到普通表

3.3.1、交换分区前操作

-- 提示要关闭行级策略
/* 关闭行级安全 */
ALTER TABLE tzq.tzq_log_t DISABLE ROW LEVEL SECURITY;
/* 把 force_rowsecurity 属性关闭 */
ALTER TABLE tzq.tzq_log_t RESET (force_rowsecurity);
/* 删除行级策略 */
drop policy if exists system_isolation_policy on tzq.tzq_log_t;

/* 删除分区的表的主键约束 */
alter table tzq.tzq_log_t DROP CONSTRAINT pk_tzq_log_t;

3.3.2、交换分区

-- 执行分区置换
ALTER TABLE tzq.tzq_log_t EXCHANGE PARTITION (tzq_log_t_p20251)
WITH TABLE tzq.tzq_log_t_p20251_th
WITHOUT VALIDATION
UPDATE GLOBAL INDEX;

3.3.3、统计数据量

/* 统计数据量 */
select count(*) from tzq.tzq_log_t_p20251_th;  -- 1000000
select count(*) from tzq.tzq_log_t partition (tzq_log_t_p20251);  -- 0

可以发现，分区表的数据已经置换到普通表了。

3.4、交换分区，把普通表数据置换到分区的表的分区

3.4.1、方式一

/* 普通表数据置换到分区表 */
-- VERBOSE 会把不在该分区的数据自动插到相应的分区
ALTER TABLE tzq.tzq_log_t
EXCHANGE PARTITION (tzq_log_t_p20251) with table tzq.tzq_log_t_p20251_th VERBOSE;

/* 统计数据量 */
select count(*) from tzq.tzq_log_t_p20251_th;  -- 0
select count(*) from tzq.tzq_log_t partition (tzq_log_t_p20251);  -- 1000000

可以发现，置换成功。

3.4.2、方式二

ALTER TABLE tzq.tzq_log_t EXCHANGE PARTITION (tzq_log_t_p20251)
WITH TABLE tzq.tzq_log_t_p20251_th
WITHOUT VALIDATION
UPDATE GLOBAL INDEX;

这种方式跟“3.3.2、交换分区”一样。可以置换来，置换去。

3.5、后续操作

/* 后续操作 */
/* 重建主键，如果数据量大，可以开并行创建主键 */
ALTER TABLE tzq.tzq_log_t set (parallel_workers=16);
ALTER TABLE tzq.tzq_log_t ADD CONSTRAINT pk_tzq_log_t PRIMARY KEY (log_id);
ALTER TABLE tzq.tzq_log_t RESET (parallel_workers);

/* 开启行级安全 */
ALTER TABLE tzq.tzq_log_t ENABLE ROW LEVEL SECURITY;
/* 强制开启行级安全 */
alter table tzq.tzq_log_t force row level security;
/* 创建行级策略 */
drop policy if exists system_isolation_policy on tzq.tzq_log_t;
create policy system_isolation_policy on tzq.tzq_log_t to tzq USING (system_id = current_setting('tzq.current_system'));

四、常见错误解答FAQ（Frequently Asked Questions）

4.1、ERROR: ALTER TABLE EXCHANGE not support with row level security policy table

4.1.1、报错信息

ALTER TABLE tzq.tzq_log_t EXCHANGE PARTITION (tzq_log_t_p20251)
WITH TABLE tzq.tzq_log_t_p20251_th
WITHOUT VALIDATION
UPDATE GLOBAL INDEX
> ERROR:  ALTER TABLE EXCHANGE not support with row level security policy table

> 查询时间: 0.027s

报错提示：要关闭行级策略

4.1.2、原因分析

这个是由于“分区的表”开启了行级策略，无法进行分区置换。

4.1.3、处理方案

删除行级策略，如下SQL：

-- 提示要关闭行级策略
/* 关闭行级安全 */
ALTER TABLE tzq.tzq_log_t DISABLE ROW LEVEL SECURITY;
/* 把 force_rowsecurity 属性关闭 */
ALTER TABLE tzq.tzq_log_t RESET (force_rowsecurity);
/* 删除行级策略 */
drop policy if exists system_isolation_policy on tzq.tzq_log_t;

4.2、ERROR: column default constraint mismatch in ALTER TABLE EXCHANGE PARTITION

4.2.1、报错信息

ALTER TABLE tzq.tzq_log_t EXCHANGE PARTITION (tzq_log_t_p20251)
WITH TABLE tzq.tzq_log_t_p20251_th
WITHOUT VALIDATION
UPDATE GLOBAL INDEX
> ERROR:  column default constraint mismatch in ALTER TABLE EXCHANGE PARTITION

> 查询时间: 0.025s

报错提示：“分区的表”中的列默认约束不匹配

4.2.2、原因分析

这个是由于“普通表”的表结构、默认值没有跟“分区的表”完全一致，无法进行分区置换。

4.2.3、处理方案

重建“普通表”，把表结构、默认值跟“分区的表”完全一致，且不要创建主键。

4.3、ERROR: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of indices

4.3.1、报错信息

ALTER TABLE tzq.tzq_log_t EXCHANGE PARTITION (tzq_log_t_p20251)
WITH TABLE tzq.tzq_log_t_p20251_th
WITHOUT VALIDATION
UPDATE GLOBAL INDEX
> ERROR:  tables in ALTER TABLE EXCHANGE PARTITION must have the same number of indices

> 查询时间: 0.031s

报错提示：ALTER TABLE EXCHANGE PARTITION中的表必须具有相同数量的索引。

即“分区的表”中需要跟普通表中local索引数量一致。

4.3.2、原因分析

这个是由于“分区的表”的表含有主键，无法进行分区置换。

4.3.3、处理方案

删除“分区的表”的主键。

/* 删除主表的主键约束 */
alter table tzq.tzq_log_t DROP CONSTRAINT pk_tzq_log_t;

4.4、ERROR: constraint mismatch in ALTER TABLE EXCHANGE PARTITION

4.4.1、报错信息

ALTER TABLE tzq.tzq_log_t EXCHANGE PARTITION (tzq_log_t_p20251)
WITH TABLE tzq.tzq_log_t_p20251_th
WITHOUT VALIDATION
UPDATE GLOBAL INDEX
> ERROR:  constraint mismatch in ALTER TABLE EXCHANGE PARTITION

> 查询时间: 0.027s

报错提示：ALTER TABLE EXCHANGE PARTITION中的约束不匹配

4.4.2、原因分析

这个是由于“普通表”的表含有主键，无法进行分区置换。

4.4.3、处理方案

删除“普通”的主键。

-- 普通表也不能有主键约束
alter table tzq.tzq_log_t_p20251_th DROP CONSTRAINT pk_tzq_log_t_p20251;

五、总结

1、进行分区置换“分区的表”，不能开启行级策略、主键约束要下掉。
2、“普通表”的结构要跟“分区的表”完全一致，包括默认值都要一致。
3、只能对一级“分区的表”进行交换分区。多级“分区的表”不支持交换分区。

标签：tzq,00,01,log,分区,分区表,TABLE,openGauss
From： https://blog.csdn.net/tttzzzqqq2018/article/details/145258606

相关文章

  • GaussDB云原生数据库SQL引擎继承原来openGauss的词法解析，语法解析，查询重写，查询优化和
    云原生数据库SQL引擎继承原来openGauss的词法解析，语法解析，查询重写，查询优化和执行引擎的能力。由于云原生数据库是shareddisk架构，一个事务在一个节点上执行，所以不需要原来分布式根据分布式key进行数据分布，分布式执行和分布式2PC提交的能力。为了支持数据库粒度的异地多活，云原生......
  • 【虚拟机硬盘的添加及分区挂载】
    提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档虚拟机硬盘的添加及分区挂载一、添加类型为SCSI的硬盘二、添加类型为STAT的硬盘三、添加类型为NVME的硬盘一、添加类型为SCSI的硬盘第一块硬盘，虚拟磁盘类型选择SCSI。大小选择5G。按mbr格式分区。分......
  • GaussDB云原生数据库SQL引擎继承原来openGauss的词法解析，语法解析，查询重写，查询优化和
    云原生数据库SQL引擎继承原来openGauss的词法解析，语法解析，查询重写，查询优化和执行引擎的能力。由于云原生数据库是shareddisk架构，一个事务在一个节点上执行，所以不需要原来分布式根据分布式key进行数据分布，分布式执行和分布式2PC提交的能力。为了支持数据库粒度的异地多活，云原生......
  • CentOS扩容boot分区并升级内核
    本文作者CVE-柠檬i：https://www.cnblogs.com/CVE-Lemon前言由于安装k8s需要升级内核，但我自己的的boot分区只有200M大小，无法安装新内核，所以干脆把swap分区分给boot了。在此期间关于grub的操作踩了好多坑，所以特此记录一下正确操作。使用rpm安装新内核，下载链接：https://mirrors.core......
  • 开发环境的openGauss搭建
    一、搭建准备1、准备用户创建个人账号。useradd-d/home/xxx-mxxx设置密码。passwdxxx切换到个人账号su-xxx2、下载源码mkdirxxxcdxxxgitclonehttps://gitee.com/opengauss/openGauss-server.gitgitclonehttps://gitee.com/opengauss/open......
  • ESXi给虚拟机分区扩容
    ESXI虚拟机磁盘原有是40G现扩容到240G。df-Th这是参数连着写。相当于df-T-h-T：代表type类型，可以查看到磁盘的类型。-h：代表human人类，就是以人们熟悉的单位来表示磁盘大小，如K、M、G。如果不加这个参数，默认以KB字节单位显示，可读性差。查询结果含义：size代表磁盘总大小，used代表......
  • 嵌入式杂谈（问题解决一：使用HAL库时keil中代码的分区）
     如图，代码分区代码区域作用Privateincludes引入所需头文件，提供函数声明、类型定义和宏等Privatetypedef创建自定义数据类型，增强代码可读性与维护性Privatedefine定义常量和宏，方便代码修改与简化Privatemacro实现简单代码替换，简化代码逻辑Privatevariables声明和初始化......
  • Linux 硬盘扩容 分区 & 挂载
    Linux硬盘扩容分区&挂载1.添加分区1.1.查看新添加的硬盘fdisk-l假设当前未挂载的盘符是/dev/sdb，后文中所有操作都按挂载/dev/sdb操作1.2.分区管理小硬盘fdisk/dev/sdb大硬盘（2TB以上）gdisk/dev/sdb1.3.编辑分区⚠️下方注释一行一行看，不要跳过#执行......
  • Q:群晖磁盘断电导致，无法访问系统分区
     1、群晖磁盘断电导致，无法访问系统分区 2、点击存储空间管理员-总览-点击修复 3、存储空间-文件系统检查-重启 4、重启后正常 ......
  • 什么是system分区？root之后如何修改？
    1.什么是System分区？System分区是Android操作系统中用于存放系统文件的一个专用分区，它包含了Android系统的核心组件、系统应用程序和库文件。这些文件是操作系统正常运行所必需的。特点：只读状态：在未Root的设备中，System分区通常是只读的，防止用户误操作导致系统崩溃......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99