postgresql分表及数据迁移

目标：

1、主表wh_photo，根据时间（upload_date）进行分表

2、异步完成生产服务器的主表分表数据迁移

步骤1：本地数据库创建子表

CREATE TABLE "public"."wh_photo_2013_first_half_year" (
CONSTRAINT "wh_photo_2013_first_half_year_upload_date_check" CHECK (('2013-01-01 00:00:00'::timestamp without time zone <= upload_date) AND (upload_date < '2013-07-01 00:00:00'::timestamp without time zone))
)
INHERITS ("public"."wh_photo") 
WITH (OIDS=FALSE)
;

ALTER TABLE "public"."wh_photo_2013_first_half_year" OWNER TO "elog";

CREATE TABLE "public"."wh_photo_2013_second_half_year" (
CONSTRAINT "wh_photo_2013_second_half_year_upload_date_check" CHECK (('2013-07-01 00:00:00'::timestamp without time zone <= upload_date) AND (upload_date < '2014-01-01 00:00:00'::timestamp without time zone))
)
INHERITS ("public"."wh_photo") 
WITH (OIDS=FALSE)
;

ALTER TABLE "public"."wh_photo_2013_second_half_year" OWNER TO "elog";

步骤2：本地数据库创建触发函数，在插入主表之前将数据插入字表

-- Function: wh_photo_insert_trigger()

-- DROP FUNCTION wh_photo_insert_trigger();

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() {
        Sql sql = new Sql(dataSource)
        List list = sql.rows("select nextval('whphoto')")
        return list.get(0).nextval
    }

    public String whPhotoTableName(Date date) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(date)
        int year = cal.get(Calendar.YEAR);
        int month = cal.get(Calendar.MONTH) + 1;
        if (month >= 1 && month <= 6) {
            return "wh_photo_" + year + "_first_half_year"
        } else if (month >= 7 && month <= 12) {
            return "wh_photo_" + year + "_second_half_year"
        } else {
            return "wh_photo_null_date"
        }
    }

        Sql sql = new Sql(dataSource)
        String query = "INSERT INTO ${subTableService.whPhotoTableName(upload_date)}(\n" +
                "            id,version, address, altitude, file_name, latitude, location_info_id, \n" +
                "            longitude, mobile, pic_type, remark, snapuser_id, thumbnail_file_name, \n" +
                "            title_id, tml_group_id, upload_date, uploader_name, flag, photo_num, \n" +
                "            third_user_id,location_time)\n" +
                "    VALUES (${subTableService.getWhPhotoSeq()},0, '${address}', '${altitude}', '${file_name}', '${latitude}', null, \n" +
                "            '${longitude}', '${mobile}', '${pic_type}', '${remark}', ${snapuser_id}, '${thumbnail_file_name}', \n" +
                "            ${title_id}, ${tml_group_id}, '${upload_date}', '${uploader_name}', '${flag}', ${photo_num}, \n" +
                "             ${third_user_id}, ${location_time == null ? null : "'" + location_time + "'"});"

        def sqlResult = sql.executeInsert(query)
        long photo_id = Long.valueOf(sqlResult.get(0)[0].toString())