目标:
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();
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())