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') THENtablename := '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())