postgresql分表及数据迁移_postgresql 如何表中已有数据, 然后创建分区表,这会导致旧数据迁移到新建的表中吗

CREATE OR REPLACE FUNCTION wh_photo_insert_trigger()
RETURNS trigger AS
$BODY$

DECLARE
tablename varchar := ‘’;
sql varchar := ‘’;
BEGIN

if(to_char(NEW.upload_date,'MM') >= '01' and to_char(NEW.upload_date,'MM') <= '06') THEN
tablename := 'wh_photo_' || to_char(NEW.upload_date,'yyyy') || '_first_half_year';
else 	
tablename := 'wh_photo_' || to_char(NEW.upload_date,'yyyy') || '_second_half_year';   
end if;

 sql := format('INSERT INTO %s(
        id,version, address, altitude, file_name, latitude, location_info_id, 
        longitude, mobile, pic_type, remark, snapuser_id, thumbnail_file_name, 
        title_id, tml_group_id, upload_date, uploader_name, flag, photo_num, 
        comment, third_user_id, verify_id, is_replay, replay_date, sms_content, 
        location_time, completed, gener_time, org_id, remark1, remark2)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
tablename,NEW.id,NEW.version, quote_nullable(NEW.address), quote_nullable(NEW.altitude), quote_nullable

(NEW.file_name), quote_nullable(NEW.latitude), quote_nullable(NEW.location_info_id),
quote_nullable(NEW.longitude), quote_literal(NEW.mobile), quote_literal(NEW.pic_type), quote_nullable

(NEW.remark), quote_literal(NEW.snapuser_id), quote_nullable(NEW.thumbnail_file_name),
quote_nullable(NEW.title_id), quote_nullable(NEW.tml_group_id), quote_literal(NEW.upload_date), quote_literal

(NEW.uploader_name), quote_nullable(NEW.flag), quote_nullable(NEW.photo_num),
quote_nullable(NEW.comment), quote_nullable(NEW.third_user_id), quote_nullable(NEW.verify_id), quote_nullable

(NEW.is_replay), quote_nullable(NEW.replay_date), quote_nullable(NEW.sms_content),
quote_nullable(NEW.location_time), quote_nullable(NEW.completed), quote_nullable(NEW.gener_time),

quote_nullable(NEW.org_id), quote_nullable(NEW.remark1), quote_nullable(NEW.remark2));

EXECUTE sql;


RETURN NULL;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION wh_photo_insert_trigger()
OWNER TO elog;

  

**步骤3：本地数据库创建触发函数，在插入主表时执行**

CREATE TRIGGER wh_photo_trigger
BEFORE INSERT
ON wh_photo
FOR EACH ROW
EXECUTE PROCEDURE wh_photo_insert_trigger();

  
 


**步骤4：从生产数据库导出主表数据**

pg_dump -h 生产数据库IP -U 生产数据库账号 -d 生产数据库实例 -t wh_photo -f wh_photo.sql

  

**步骤5：将导出数据导入到本地数据库的子表**

psql -h 本地数据库IP -U 本地数据库账号 -d 本地数据库实例 -f wh_photo.sql

  

**步骤6：在生产数据库创建子表，见步骤一**

  
 


**步骤7：在生产数据库，取消子表的继承关系**

ALTER TABLE wh_photo_2013_first_half_year NO INHERIT wh_photo;
ALTER TABLE wh_photo_2013_second_half_year NO INHERIT wh_photo;

  

**步骤8：导出本地数据库的子表**

pg_dump -h 本地数据库IP -U 本地数据库账号 -d 本地数据库实例 -t wh_photo_* -f wh_photo_all.sql

  

**步骤9：将导出的子表数据导入到生产数据库**

psql -h 生产数据库IP -U 生产数据库账号 -d 生产数据库实例 -f wh_photo_all.sql

  

  

**步骤10：在生产数据库创建子表的索引**

CREATE INDEX “idx_wh_photo_2013_first_half_year_snapuser_id_flag” ON “public”.“wh_photo_2013_first_half_year” USING btree (snapuser_id, flag);

CREATE INDEX “idx_wh_photo_2013_first_half_year_third_user_id” ON “public”.“wh_photo_2013_first_half_year” USING btree (third_user_id);

CREATE INDEX “idx_wh_photo_2013_second_half_year_snapuser_id_flag” ON “public”.“wh_photo_2013_second_half_year” USING btree (snapuser_id, flag);

CREATE INDEX “idx_wh_photo_2013_second_half_year_third_user_id” ON “public”.“wh_photo_2013_second_half_year” USING btree (third_user_id);

  


**步骤11：在生产数据库清空主表数据**

TRUNCATE TABLE wh_photo

  

**步骤12：在生产数据库添加子表的继承关系**

ALTER TABLE wh_photo_2013_first_half_year INHERIT wh_photo;
ALTER TABLE wh_photo_2013_second_half_year INHERIT wh_photo;

  

**备注：后台采用插入子表的sql语句保存数据**

public long getWhPhotoSeq() {

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！

https://bbs.csdn.net/forums/4304bb5a486d4c3ab8389e65ecb71ac0
23)]
[外链图片转存中…(img-dYvo0Glz-1725778062923)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！

https://bbs.csdn.net/forums/4304bb5a486d4c3ab8389e65ecb71ac0