Postgresql普通表转换分区表(上亿条数据)

Postgresql普通表转换为分区表

前言：这里处理的是postgresql数据库的上亿条数据，方法有很多，这里是用的是先创建分区表在导入数据，最后更改数据库表名字达成目的。

查看表普通表

select * from table_原表;

---

创建分区表 t_range,这里根据时间table_time分区

CREATE TABLE t_range (
  "table_id" varchar(36) NOT NULL,
  "table_time" timestamp(0) NOT NULL,
  "table_bool" char(1)  NOT NULL,
  "table_number" numeric(5,2)
)
partition by range(calc_time);

---

创建主键-这里创建的是联合主键

ALTER TABLE t_range
ADD [CONSTRAINT t_range_pkey]   --主键名
PRIMARY KEY (table_id,table_time)

---

创建索引

CREATE INDEX index_table4_id ON t_range (table_id);
CREATE INDEX index_table4_time ON t_range (table_time);

---

创建分区

CREATE TABLE d_table4_p202010 PARTITION OF t_range FOR VALUES FROM ('2020-10-01') TO ('2020-11-01');  
CREATE TABLE d_table4_p202011 PARTITION OF t_range FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
CREATE TABLE d_table4_p202012 PARTITION OF t_range FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');
CREATE TABLE d_table4_p202101 PARTITION OF t_range FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE d_table4_p202102 PARTITION OF t_range FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');

---

注意：

这里需要创建一个默认分区来存放所创建的分区时间以外的数据，否则插入数据时会报以下或其他错误

Error: No partition for row relationship "T_RANGE" was found
> 错误:  没有为行找到关系"t_range"的分区

---

创建默认分区

CREATE TABLE d_table4_default PARTITION OF t_range DEFAULT;

---

插入数据

数据量小的话可以直接用 insert into 插入或者导表导入，但我这里处理的是数据库的大量数据所以要考虑插入性能问题，这里选择的是创建一个存储过程来导入数据

创建过程 sp_into_table4_pt()

CREATE OR REPLACE PROCEDURE sp_into_table4_pt()
 AS $BODY$BEGIN

declare target_text text;
    sqltext text;
    sqltext1 text;
    sqltext2 text;
    rd record; 
    nloop INT;
    isexist text;
    datestr text;
    begindate text;
    n INT;
BEGIN

sqltext := 'select to_char(min(table_time),''yyyy-mm-dd'') datestr from table_原表';

EXECUTE sqltext into begindate;

n := date_part('day',now() - begindate::date);

--enddate:=(to_char(now(),'yyyy-mm-dd'))::text;

FOR nloop in 0..n LOOP

 datestr := (begindate::date + nloop)::TEXT;

 sqltext2:=' 
 insert into hrmw.t_range (
    select 
    table_time,
    table_bool,
    table_number
    from hrmw.table_原表
  where table_time >= ''' ||  datestr || ' 00:00:00'' 
  and table_time <= ''' ||  datestr || ' 23:59:59'')
  ';
   
   EXECUTE sqltext2;
   
 commit;
 
end loop;

RETURN;

END;

END$BODY$
  LANGUAGE plpgsql

---

注意：由于数据量较大，尽量避免在任务高峰期时执行，以免影响数据库其他的定时任务的正常执行。

调用存储过程，插入数据

call sp_into_table4_pt();

---

• 插入数据要等待很长时间，插完之后需要查看一下数据库连接状态，看看有无其他相关联的数据，如果有要关掉，以免导致数据库异常。

我这里使用的数据库由于每过几分钟就会往数据库里插入数据所以要查看状态，并关掉相关联的任务。

查看链接状态

SELECT 
    C.relname 对象名称,
    l.locktype 可锁对象的类型,
    l.pid 进程id,
    psa.datname 数据库名称,
    psa.xact_start 事务开始时间,
    psa.query_start 事务执行此语句时间,
    psa.state_change 事务状态改变时间,
    psa.wait_event_type 等待事件类型,
    psa.wait_event 等待事件,
    psa.STATE 查询状态,
    backend_xid 事务是否有写入操作,
    backend_xmin 是否执事务快照,
    psa.query 执行语句,
    now( ) - query_start 持续时间
FROM
pg_locks l
INNER JOIN pg_stat_activity psa ON ( psa.pid = l.pid )
LEFT OUTER JOIN pg_class C ON ( l.relation = C.oid ) 
-- where l.relation = 'tb_base_apparatus'::regclass
where relkind ='r'
ORDER BY query_start asc

---

记下相关联的进程id

清除相关连接数

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid='00000'  --进程id

---

改数据库名

–杀完进程尽量快的更改名字

alter table table_原表 rename table_原表_old;
alter table t_range rename to table_原表;

---

改完名字后就可以正常接收实时数据了! 不过要注意的是这种方法会缺少一部分数据，解决方法就是过几天把这一天的数据删除然后在用备份还原一下就可以了。

---

这里还有个隐藏问题，就是分区不会自动增加，需要手动添加分区，如果嫌麻烦可以创建一个存储过程，然后用触发器实现自动增长分区，具体可以试试。

这里主要分析一下如果插入的时间在创建的分区以外，则这条数据会自动保存到默认分区（d_table4_default）中，如果在创建改时间了分区，则该条数据不会再新创建的分区中，还会保留在原来的默认分区中。以下是解决办法。

1.解绑分区

–这时表名已经改完

ALTER TABLE table_原表 DETACH PARTITION d_table4_default;

---

2.插入相应数据到新建的分区

INSERT INTO d_table4_p202107 SELECT * FROM d_table4_default where table_time>='2021-07-01' and table_time<'2021-08-01'; --这里用2021-07举例

---

3.删除原表相关数据

DELETE FROM d_table4_default where table_time>='2021-07-01' and table_time<'2021-08-01';

---

4.重新定义默认分区

ALTER TABLE table_原表 ATTACH PARTITION d_table4_default DEFAULT

---

总结：还有其他方法的小伙伴欢迎来讨论！！！