PostgresSQL 转换表为分区表

---

修改表名称与索引名称
alter table 旧表名 rename to 新表名
alter index if exists 旧索引名 rename to 新索引名;
alter table 旧表名 rename constraint 旧约束名 to 新约束名;

alter table v_xml_cdr rename to v_xml_cdr_old;
alter index if exists v_xml_cdr_idx1 rename to v_xml_cdr_idx1_old;
alter index if exists v_xml_cdr_idx2 rename to v_xml_cdr_idx2_old;
alter table v_xml_cdr_old rename constraint v_xml_cdr_pkey to v_xml_cdr_old_pkey;

修改postgresql.conf配置文件：/var/lib/pgsql/9.4/data/postgresql.conf

max_connections = 2000
constraint_exclusion = partition    # on, off, or partition

创建主表结构, 其中的时间字段名: start_stamp

CREATE TABLE public.v_xml_cdr
(
    xml_cdr_uuid uuid NOT NULL,
    domain_uuid uuid,
    extension_uuid uuid,
    domain_name text COLLATE pg_catalog."default",
    accountcode text COLLATE pg_catalog."default",
    direction text COLLATE pg_catalog."default",
    default_language text COLLATE pg_catalog."default",
    context text COLLATE pg_catalog."default",
    xml text COLLATE pg_catalog."default",
    json jsonb,
    caller_id_name text COLLATE pg_catalog."default",
    caller_id_number text COLLATE pg_catalog."default",
    caller_destination text COLLATE pg_catalog."default",
    source_number text COLLATE pg_catalog."default",
    destination_number text COLLATE pg_catalog."default",
    start_epoch numeric,
    start_stamp timestamp without time zone,
    answer_stamp timestamp without time zone,
    answer_epoch numeric,
    end_epoch numeric,
    end_stamp timestamp(0) without time zone,
    duration numeric,
    mduration numeric,
    billsec numeric,
    billmsec numeric,
    bridge_uuid text COLLATE pg_catalog."default",
    read_codec text COLLATE pg_catalog."default",
    read_rate text COLLATE pg_catalog."default",
    write_codec text COLLATE pg_catalog."default",
    write_rate text COLLATE pg_catalog."default",
    remote_media_ip text COLLATE pg_catalog."default",
    network_addr text COLLATE pg_catalog."default",
    record_path text COLLATE pg_catalog."default",
    record_name text COLLATE pg_catalog."default",
    leg character(1) COLLATE pg_catalog."default",
    pdd_ms numeric,
    rtp_audio_in_mos numeric,
    last_app text COLLATE pg_catalog."default",
    last_arg text COLLATE pg_catalog."default",
    cc_side text COLLATE pg_catalog."default",
    cc_member_uuid uuid,
    cc_queue_joined_epoch numeric,
    cc_queue text COLLATE pg_catalog."default",
    cc_member_session_uuid uuid,
    cc_agent_uuid uuid,
    cc_agent text COLLATE pg_catalog."default",
    cc_agent_type text COLLATE pg_catalog."default",
    cc_agent_bridged text COLLATE pg_catalog."default",
    cc_queue_answered_epoch numeric,
    cc_queue_terminated_epoch numeric,
    cc_queue_canceled_epoch numeric,
    cc_cancel_reason text COLLATE pg_catalog."default",
    cc_cause text COLLATE pg_catalog."default",
    waitsec numeric,
    conference_name text COLLATE pg_catalog."default",
    conference_uuid uuid,
    conference_member_id text COLLATE pg_catalog."default",
    digits_dialed text COLLATE pg_catalog."default",
    pin_number text COLLATE pg_catalog."default",
    hangup_cause text COLLATE pg_catalog."default",
    hangup_cause_q850 numeric,
    sip_hangup_disposition text COLLATE pg_catalog."default",
    CONSTRAINT v_xml_cdr_pkey PRIMARY KEY (xml_cdr_uuid)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.v_xml_cdr
    OWNER to fusionpbx;

为主表创建触发器, 其中,调用了触发器函数 auto_insert_into_tbl_partition('start_stamp')

CREATE TRIGGER insert_v_xml_cdr_trigger
  BEFORE INSERT
  ON v_xml_cdr
  FOR EACH ROW
  EXECUTE PROCEDURE auto_insert_into_v_xml_cdr('start_stamp');

创建可重用的触发器函数: auto_insert_into_tbl_partition( time_column_name )

CREATE OR REPLACE FUNCTION auto_insert_into_v_xml_cdr()
  RETURNS trigger AS
$BODY$
DECLARE
    time_column_name     text ;            -- 父表中用于分区的时间字段的名称[必须首先初始化!!]
    curTblDate         varchar(8);        -- 'YYYYMMDD'字串,用做分区子表的后缀
    isExist         boolean;        -- 分区子表,是否已存在
    startTime         text;
    endTime        text;
    strSQL          text;
    
BEGIN
    -- 调用前,必须首先初始化(时间字段名):time_column_name [直接从调用参数中获取!!]
    time_column_name := TG_ARGV[0];
   
    -- 判断对应分区表 是否已经存在?
    EXECUTE 'SELECT $1.'||time_column_name INTO strSQL USING NEW;
    curTblDate := to_char( strSQL::timestamp , 'YYYYMMDD' );
    select count(*) INTO isExist from pg_class where relname = (TG_RELNAME||'_'||curTblDate);
 
    -- 若不存在, 则插入前需 先创建子分区
    IF ( isExist = false ) THEN  
        -- 创建子分区表
        startTime := to_char( strSQL::timestamp , 'YYYY-MM-DD' );
        -- endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS.MS'); -- 按月分区
        endTime := startTime||' 23:59:59.999';
        strSQL := 'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'||curTblDate||
                  ' ( CHECK('||time_column_name||'>='''|| startTime ||''' AND '
                             ||time_column_name||'< '''|| endTime ||''' )
                          ) INHERITS ('||TG_RELNAME||') ;'  ;  
        EXECUTE strSQL;
 
        -- 创建索引
        strSQL := 'CREATE INDEX '||TG_RELNAME||'_'||curTblDate||'_INDEX1_'||time_column_name||' ON '
                  ||TG_RELNAME||'_'||curTblDate||' ('||time_column_name||');' ;
        EXECUTE strSQL;
        
        strSQL := 'CREATE INDEX '||TG_RELNAME||'_'||curTblDate||'_INDEX2_'||time_column_name||' ON '
                  ||TG_RELNAME||'_'||curTblDate||' (caller_id_number COLLATE pg_catalog."default");' ;
        EXECUTE strSQL;
        
        strSQL := 'CREATE INDEX '||TG_RELNAME||'_'||curTblDate||'_INDEX3_'||time_column_name||' ON '
                  ||TG_RELNAME||'_'||curTblDate||' (direction COLLATE pg_catalog."default", caller_id_number COLLATE pg_catalog."default", caller_destination COLLATE pg_catalog."default", start_stamp, end_stamp, billsec);' ;
        EXECUTE strSQL;
        
    END IF;
 
    -- 插入数据到子分区!
    strSQL := 'INSERT INTO '||TG_RELNAME||'_'||curTblDate||' SELECT $1.*' ;
    EXECUTE strSQL USING NEW;
    RETURN NULL; 
END
$BODY$
  LANGUAGE plpgsql;
  
  -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
--  说明:
--(1) 代码中使用了 TG_ARGV[0] 来获取调用时传入的参数: 用于分区的时间字段名.
--(2) 代码中,通过内置参数 TG_RELNAME 获得了父表的表名称.
--(3) 首先根据插入时间, 判断对应分区表是否存在? 若存在, 直接插入对应分区子表
--(4) 若分区表还不存在, 先创建分区子表和索引, 然后插入数据到所建的子表中.
--以上代码, 在PostgreSQL v9.4 中调试通过. 理论上, v8.4以上均支持.

数据转移

INSERT INTO v_xml_cdr SELECT * FROM v_xml_cdr_old;

---

• 分类： 数据库 PostgreSQL
• 标签： postgresql