HGDB快速将所有原生分区转换为普通表

原创 于 2025-11-24 16:16:04 发布 · 209 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #瀚高数据库

文章目录

环境

系统平台：Linux x86-64 Red Hat Enterprise Linux 7
版本：5.6.5

文档用途

某些情况下由于业务预估过多，导致开发把所有表都建立成了分区表，而实际上并不需要分区。本文用于将原生分区表快速转换为普通表。

详细信息

背景

某些情况下由于业务预估过多，导致开发把所有表都建立成了分区表，而实际上并不需要分区。

  1. 在高并发下分区表可能引入一定的优化器消耗

  2. 分区多，会导致会话relcache的内存增加，长连接+高并发+未使用的huge page可能触发OOM

注意：本文不考虑采用serial导致sequence和table挂钩、外键约束，如果分区表包含serial字段，序列属于partition table的情况，删除old table会导致序列被删除，并且新表的默认值也会被清理。

例子

数据准备

create database db1;
\c db1



create table tbl(id int, info text) partition by hash(id);

-- MODULUS子句用于指定除数，REMAINDER子句用于指定哈希值被除后的余数。

-- REMAINDER 子句需要指定一个小于 MODULUS 子句的值。

-- 如果指定的分区数量少于 MODULES 子句的值，将会导致无法插入某些数据，因为没有用于存储这些值的分区。

create table tbl0 partition of tbl for values with (modulus 2, remainder 0);

create table tbl1 partition of tbl for values with (modulus 2, remainder 1);

insert into tbl select generate_series(1, 10000), md5(random()::text);



create table ttbl(id int, info text) partition by hash(id);

create table ttbl0 partition of ttbl for values with (modulus 2, remainder 0);

create table ttbl1 partition of ttbl for values with (modulus 2, remainder 1);

insert into ttbl select generate_series(1, 10000), md5(random()::text);



create table "TTb" (id int, info text) partition by hash (id);

create table "TTb0" partition of "TTb" for values with (modulus 2, remainder 0);

create table "TTb1" partition of "TTb" for values with (modulus 2, remainder 1);

insert into "TTb" select generate_series(1,10000), md5(random()::text);



create schema s1;

set search_path=s1;



create table tbl (id int, info text) partition by hash (id);

create table tbl0 partition of tbl for values with (modulus 2, remainder 0);

create table tbl1 partition of tbl for values with (modulus 2, remainder 1);

insert into tbl select generate_series(1,10000), md5(random()::text);



create table ttbl (id int, info text) partition by hash (id);

create table ttbl0 partition of ttbl for values with (modulus 2, remainder 0);

create table ttbl1 partition of ttbl for values with (modulus 2, remainder 1);

insert into ttbl select generate_series(1,10000), md5(random()::text);



create table "TTb" (id int, info text) partition by hash (id);

create table "TTb0" partition of "TTb" for values with (modulus 2, remainder 0);

create table "TTb1" partition of "TTb" for values with (modulus 2, remainder 1);

insert into "TTb" select generate_series(1,10000), md5(random()::text);



create schema s2;

set search_path=s2;



create table tbl (id int, info text) partition by hash (id);

create table tbl0 partition of tbl for values with (modulus 2, remainder 0);

create table tbl1 partition of tbl for values with (modulus 2, remainder 1);

insert into tbl select generate_series(1,10000), md5(random()::text);



create table ttbl (id int, info text) partition by hash (id);

create table ttbl0 partition of ttbl for values with (modulus 2, remainder 0);

create table ttbl1 partition of ttbl for values with (modulus 2, remainder 1);

insert into ttbl select generate_series(1,10000), md5(random()::text);



create table "TTb" (id int, info text) partition by hash (id);

create table "TTb0" partition of "TTb" for values with (modulus 2, remainder 0);

create table "TTb1" partition of "TTb" for values with (modulus 2, remainder 1);

insert into "TTb" select generate_series(1,10000), md5(random()::text);



db1=# \dPt *.*       List of partitioned tables Schema | Name |  Owner   | Parent name

--------+------+----------+------------- public | TTb  | highgo   | public | tbl  | highgo   | public | ttbl | highgo   | s1     | TTb  | highgo   | s1     | tbl  | highgo   | s1     | ttbl | highgo   | s2     | TTb  | highgo   | s2     | tbl  | highgo   | s2     | ttbl | highgo   | (9 rows)

  使用下面的方法来转换表：

begin;

create table new_tbl (like tbl including all);

insert into new_tbl select * from tbl;

alter table tbl rename to old_tbl;

alter table new_tbl rename to tbl;  -- 如果有依赖，这个表有被依赖的对象时，会导致drop失败

end;

  一次性转换所有分区表的操作如下：

-- 防止锁等待，等待直接回滚

-- 建议断开业务后连接操作

set lock_timeout='5s';



do language plpgsql $$

declare

   v_nsp name;    v_tbl name;    v_exchange1 name := 'new'||md5(random()::text);    v_exchange2 name := 'old'||md5(random()::text);begin    for v_nsp, v_tbl in        SELECT n.nspname as "Schema",            c.relname as "Name"
       FROM pg_catalog.pg_class c

           LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

           LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid

       WHERE c.relkind IN ('p','')

           AND n.nspname !~ '^pg_toast'

       ORDER BY "Schema", "Name"    LOOP        execute format('create table %I.%I (like %I.%I including all)', v_nsp, v_exchange1, v_nsp, v_tbl);        execute format('insert into %I.%I select * from %I.%I', v_nsp, v_exchange1, v_nsp, v_tbl);        execute format('alter table %I.%I rename to %I', v_nsp, v_tbl, v_exchange2);        execute format('alter table %I.%I rename to %I', v_nsp, v_exchange1, v_tbl);        -- 如果有依赖，以下语句将报错，导致整个事务回滚。        execute format('drop table %I.%I', v_nsp, v_exchange2);    END loop;  end;  
$$;



-- 再查询分区表

db1=# \dPt *.*     List of partitioned tables Schema | Name | Owner | Parent name

--------+------+-------+-------------

(0 rows)

如果分区表内的数据很多，这个可能会执行很久。

如果有依赖问题怎么办

先解决依赖，再执行如上

例如解决sequence的依赖问题，对每个分区表相关的sequence执行去除owner，改成新表：

alter sequecve 序列 owned by none;
以上do脚本改成如下：

do language plpgsql $$

declare

 v_tbl_oid oid;  v_nsp name;  v_tbl name;  v_exchange1 name := 'new'||md5(random()::text);  v_exchange2 name := 'old'||md5(random()::text);  v_seq_nsp name;
 v_seq_name name;

begin

   for v_tbl_oid,v_nsp,v_tbl in    SELECT c.oid, n.nspname as "Schema",        c.relname as "Name"    FROM pg_catalog.pg_class c        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace        LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid    WHERE c.relkind IN ('p','')        AND n.nspname !~ '^pg_toast'    ORDER BY "Schema",  "Name"    LOOP        execute format('create table %I.%I (like %I.%I including all)', v_nsp, v_exchange1, v_nsp, v_tbl);        execute format('insert into %I.%I select * from %I.%I', v_nsp, v_exchange1, v_nsp, v_tbl);        execute format('alter table %I.%I rename to %I', v_nsp, v_tbl, v_exchange2);        execute format('alter table %I.%I rename to %I', v_nsp, v_exchange1, v_tbl);        -- 如果有依赖，以下将报错，导致整个事务回滚.        -- 处理serial类型依赖, 设置owned by为none
       for v_seq_nsp, v_seq_name in

           select n.nspname as "Schema", c.relname as "Name" from pg_class c

           LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace            

           where c.oid in
           (select objid from pg_depend where refobjid=v_tbl_oid)

           and relkind='S'

           AND n.nspname !~ '^pg_toast'        loop            execute format('alter sequence %I.%I owned by none', v_seq_nsp, v_seq_name);
       end loop;

       execute format('drop table %I.%I', v_nsp, v_exchange2);    

   END loop;

end; $$;
博客
瀚高用户反馈调研
04-22 1398
瀚高用户调研
博客
【只为求才,Want AD】
12-10 2156
【求才】有数据库DBA经验，或开发经验均可谈。中国大陆地区。感谢关注和推荐。company: www.highgo.comSend Resumes To Mail: lisong@highgo.com
博客
基于PostgreSQL进行Java应用开发
11-04 4384
该手册由PG实验室的成员结合多年对postgresql数据库的使用及Java应用开发经验，总结了一套实战入门手册。帮助PostgreSQL软件爱好者及初学者快速学习postgresql数据库的相关知识。针对应用如何适配postgresql数据库，提供了丰富的技巧案例。开发人员在不了解postgresql的情况下，通过该手册的学习，能够自主的实现基于postgresql的应用开发工作。以上是适配手册涵盖的章节内容。本手册一共6个章节：第1章PostgreSQL介绍 1.1.PostgreSQL起源
博客
Tomcat下使用JNDI方式连接瀚高数据库实践
11-26 192
1）将瀚高JDBC驱动包放到/tomcat/lib目录下，如果在项目中已经引用了瀚高JDBC驱动包，则不需要此操作。3）在/tomcat/conf/web.xml文件中对照加入如下的配置引用JNDI数据源。系统平台：Microsoft Windows (64-bit) 10。本文主要介绍在tomcat下，如何使用JNDI方式连接瀚高数据库。在tomcat下，如何使用JNDI方式连接瀚高数据库；4）在项目中引用JNDI数据源。5）启动项目测试即可。
博客
HGDB兼容Oracle隐式转换text-text
11-26 393
提示: No operator matches the given name and argument type(s). You might need to add explicit type casts.系统平台：Microsoft Windows (64-bit) 10。第1行select ‘10’::text - ‘2’::text;Oracle的两个文本相减，会自动转换为数值进行相减操作。1、创建text到numeric的隐式转换。2、创建text-text的操作符。
博客
HGDB-with查询
11-25 258
这个例子就是首先定义了regional_sales 和 top_regions 两个辅助语句，regional_sale算出每个区域的总销售量，top_regions算出销售量中占总销售量10%的所有地区，主查询语句通过辅助语句与orders表关联，算出了顶级区域每件商品的销售量和销售额。WITH查询这一特性通常被称为CTE(Common Table Expressions)，WITH查询在复杂查询中定义一个辅助语句（临时表），通常这一特性用于复杂查询或递归查询。介绍如何使用WITH查询。
博客
libpq-C库 Windows下C语言链接瀚高数据库
11-25 407
D:\Dev-Cpp\编程文件>gcc -o main.exe main.o -L /D:/highgo/database/5.6.4/lib -lpq。D:\Dev-Cpp\编程文件>gcc -c -I /D:/highgo/database/5.6.4/include main.c。3、使用gcc –o 生成.exe可执行文件，并且使用-L指定lib目录，注意使用参数-lpq。1、下载安装，打开dev++，文件-新建-项目，选择控制台应用程序，语言选C语言。2、使用gcc –c –I编译.c文件。
博客
psql无法正常连接数据库
11-24 1041
修改LD_LIBRARY_PATH为：export LD_LIBRARY_PATH=/opt/HighGoDB-4.3.4.5/lib:/usr/lib64:$LD_LIBRARY_PATH。如上图所示：将原生postgresql的相关lib包卸载后，重新执行psql -U sysdba -d highgo 可正常连接。psql连接数据库报错：没有找到/var/run/postgresql/.s.PGSQL.5866锁文件。系统平台：中标麒麟（龙芯）7。可正常连接，问题解决。版本：4.3.4.5。
博客
pg_archivecleanup的简单使用及注意事项
11-21 290
该工具可以删除指定目录下某归档文件之前的所有归档。1、使用时尽量保存至少一天的归档，保证清理后可以从备份文件中恢复数据库。介绍pg_archivecleanup工具的使用及注意事项。2、如果在集群或者流复制环境下，清理前先检查流复制状态。
博客
hgdb中的时区与时间戳
11-21 464
下面示例演示了客户端时区变化时，带有时区的时间跟随时区发生变化，但不带时区的时间并未跟随时区改变而改变。timestampz将存储为UTC形式，hgdb会将timestampz转换为格林威治时间1970年01月01日00时00分00秒起至现在的总秒数（UTC)，查询时，根据每个会话设定的时区进行转换。to_timestamp将符合条件的其他类型（字符串、数值等）转换为带时区的时间戳，通过下面示例可以看到转换后的数据是带时区的。本文介绍了在HGDB中，带时区和不带时区的时间戳类型如何使用及选择。
博客
手动切换REPMGR节点的步骤和注意事项
11-20 429
2.在切换时 repmgrd 不应该在 repmgr.conf中启用 failover=automatic，否则 repmgrd守护进程可能尝试并自行提升备用数据库。repmgr集群主备切换使用的是repmgr standby switchover工具，它的作用是将备库提升为主库，所以该命令只能在备库上执行。4.建议在切换之前加上–dry-run，可以测试是否切换成功而不进行切换，测试成功后再进行正式切换。介绍repmgr手动主备切换的方法，以及使用过程中的注意事项。首先检查一下repmgr集群状态。
博客
修改表字段长度时更新视图字段长度
11-20 1184
2.导出涉及view,暂不考虑function 有使用到view情况,暂不考虑交叉调用情况。3.现通过函数将涉及的视图保存到表中，根据排序的规则删除视图，修改表字段，重建视图。1.修改表字段长度时，会提示有视图正在使用不允许修改，此时需要删除对应的视图。系统平台：Microsoft Windows (64-bit) 10。–根据表名，或者view 查出对应view名，view 建立语句。–测试，不可重复执行，会删除掉已保存view信息。–测试，删除关联view。–测试，创建关联view。
博客
HGDB开关归档、修改归档路径的方法
11-19 1290
archive_command参数的默认值是个空字符串，它的值可以是一条shell命令或者一个复杂的shell脚本，%p表示将要归档的WAL文件的包含完整路径信息的文件名，%f代表不包含路径信息的WAL文件的文件名。修改参数archive_directory的值为‘/new_archive’修改参数archive_command中路径为新的归档路径。新建新的归档存放位置/new_archive。新建新的归档存放位置/new_archive。HGDB开关归档、修改归档路径的方法。
博客
类型转换处理思路
11-19 1007
问题描述：比如，insert boolean类型的值到numeric类型列中，需要对值进行处理，由于insert时在HighGoDB中默认true值为t，false的值为f，而numeric类型无法直接接收t/f，第1行insert into t_varchar_to_numeric(code) values(‘123’::varchar…下面就来介绍一下错误场景及解决方案。对于不同类问题有不同的解决思路，当然，也存在同一思路就行解决两类相关问题，这个需要大家自己仔细研究一下，就可以摸索出其中的规律。
博客
HGDB修改端口号指导手册
11-18 444
对于Windows操作系统来说，以瀚高数据库企业版V5.6.5来举例，该文件是指D:\highgo\database\5.6.5\bin\runpsql.bat。对于Linux操作系统来说，以瀚高数据库安全版V4.3.4.8来举例，该文件是指/opt/HighGo4.3.4.8-see/bin/runpsql.sh。在其它脚本，例如runipsql.sh，配置文件，例如hg_repmgr.conf等地方也有可能包含实际端口号。请修改端口时，考虑到修改需使用的脚本和配置文件的端口。此文档会不定期更新。
博客
瀚高数据库自定义操作符‘&‘
11-17 263
SQL 错误 [42883]: ERROR: 42883: operator does not exist: integer & text。系统平台：Microsoft Windows (64-bit) 10。3、解决方式：自定义操作符。
博客
MySQL与瀚高数据库的范围分区的语法及实例
11-17 976
对于原生RANGE分区，分区字段必须是整型或者转换为整型，如果分区字段是日期类型的字段，那么就必须将日期类型的字段转换成整型类型。对于日期类型的转换，优化器只支持year()，to_days，to_seconds，unix_timestamp()函数的转换，其他的并不支持，也就是说，在按日期字段分区的时候，如果不是使用上述几个函数转换的，查询优化器将无法对相关查询进行优化。当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。分区字段的值不能为NULL。
博客
安全版普通用户获取系统对象的访问权限
11-14 509
oracle_catalog模式下user_table_column视图，是记录表字段信息的系统视图，三权分立和强制访问控制开启下,sysdba之外的所有用户无权访问该模式下的对象并且无法进行授权操作，对于普通用户来说，此时的权限就是自己所拥有的对象的访问和控制权限。三权分立开关、强制访问控制开关、行安全开关不能单独开启或者关闭，由Secure level统一管理，三个可选的参数，其中table代表行安全开关关闭，其余两个开启，row代表三个都开启，off代表三个都关闭。版本：4.3.4.7。
博客
HGDW报错：WAL丢失的处理办法
11-14 346
系统平台：Linux x86-64 Red Hat Enterprise Linux 7。standby节点startup进程异常，该进程一直请求某一WAL,导致无法正常运行。gpinitstandby -s redhat164 （重新生成新的standby）3. 验证 重做standby，查看集群日志，未发现之前报错。gpinitstandby -r （删除原有standby）
博客
Oracle大对象往HGDB迁移详解
11-13 727
Oracle的long raw主要用于不同的系统之间转移数据的时候所用，是Oracle不推荐使用的数据类型。Oracle的Clob类型，主要存储基于数据库字符集的单字节或多字节文本信息，最大长度是(4G-1)*database block size。Oracle的NClob类型，主要存储固定长度的UNICODE字符串，最大长度是(4G-1)*database block size。text的最大长度是1G，比Oracle的小。但是，实际应用中，1G已经足够。text的最大长度是1G，比Oracle的小。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值