PostgreSQL数据清理策略_psql清理

---

一、创建用户

create user jdbc_etl_in with password '1';
1

二、授权schema tzq的使用权限

grant usage on schema tzq to jdbc_etl_in;
1

三、创建测试表

3.1、建t正式表

/*=================================================*/
/* 表名(Table) : tzq_bas_bank_account_pay_t        */
/* 效能(Efficacy) : 接收上游数据，TI表数据merge到T表 */
/* 释义(Definition) : 银行账号支付配置             */
/* 归档人(From) : tangzhiqiang                     */
/* 归档时间(Archived Time) : 20230717-2311         */
/*=================================================*/
create table tzq.tzq_bas_bank_account_pay_t (
	bank_account_pay_id	INT8	NOT NULL,
	bank_account_id	INT8,
	tzq_bank_account	VARCHAR(200),
	bank_account_pay_status	INT8,
	bank_short_name_id	INT8,
	delete_flag	INT8,
	comments	VARCHAR(4000),
	description	VARCHAR(1000),
	created_by	INT8	NOT NULL,
	creation_date	TIMESTAMP	NOT NULL,
	last_updated_by	INT8	NOT NULL,
	last_update_date	TIMESTAMP	NOT NULL,
	CONSTRAINT pk_tzq_bas_bank_account_pay_t PRIMARY KEY(bank_account_pay_id)
);
CREATE UNIQUE INDEX uk_tzq_bas_bank_account_pay_t_1 ON tzq_bas_bank_account_pay_t((CASE delete_flag WHEN 0 THEN tzq_bank_account ELSE NULL END));
CREATE INDEX       idx_tzq_bas_bank_account_pay_t_1 on tzq_bas_bank_account_pay_t(delete_flag);
CREATE INDEX       idx_tzq_bas_bank_account_pay_t_2 on tzq_bas_bank_account_pay_t(tzq_bank_account);
CREATE INDEX       idx_tzq_bas_bank_account_pay_t_3 on tzq_bas_bank_account_pay_t(tzq_bank_account,delete_flag);
COMMENT ON TABLE   tzq.tzq_bas_bank_account_pay_t IS '银行账号支付配置。';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.bank_account_pay_id IS 'IT主键，序列号。';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.bank_account_id IS '银行账号ID';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.tzq_bank_account IS '在tzq系统使用支付的账号';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.bank_account_pay_status IS '是否有效状态 0：有效；1：无效';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.bank_short_name_id IS 'tzq_bas_bank_short_name_t来源于账户对应的bank short name';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.delete_flag IS '删除标识 0：有效；1：无效';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.comments IS '其他信息说明';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.description IS '对本条记录的说明';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.created_by IS '创建人';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.creation_date IS '创建时间';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.last_updated_by IS '最后修改人';
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_t.last_update_date IS '最后修改时间';
CREATE  SEQUENCE   seq_tzq_bas_bank_account_pay_t;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE tzq_bas_bank_account_pay_t TO jdbc_etl_in;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

3.2、建ti接口表

/*=================================================*/
/* 表名(Table) : tzq_bas_bank_account_pay_t        */
/* 效能(Efficacy) : 接收上游数据，TI表数据merge到T表 */
/* 释义(Definition) : TI表。银行账号支付配置       */
/* 归档人(From) : tangzhiqiang                     */
/* 归档时间(Archived Time) : 20230717-2311         */
/*=================================================*/
create table tzq.tzq_bas_bank_account_pay_ti (
	bank_account_pay_id	INT8	NOT NULL,
	bank_account_id	INT8,
	tzq_bank_account	VARCHAR(200),
	bank_account_pay_status	INT8,
	bank_short_name_id	INT8,
	delete_flag	INT8,
	comments	VARCHAR(4000),
	description	VARCHAR(1000),
	created_by	INT8	NOT NULL,
	creation_date	TIMESTAMP	NOT NULL,
	last_updated_by	INT8	NOT NULL,
	last_update_date	TIMESTAMP	NOT NULL,
	process_date	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP,
	process_status	INT8	DEFAULT 2,
	process_error_message	VARCHAR(500)
)PARTITION BY list(process_status);
CREATE TABLE tzq_bas_bank_account_pay_ti_p2 PARTITION OF tzq_bas_bank_account_pay_ti FOR VALUES IN (2);
CREATE TABLE tzq_bas_bank_account_pay_ti_p4 PARTITION OF tzq_bas_bank_account_pay_ti FOR VALUES IN (4);
CREATE TABLE tzq_bas_bank_account_pay_ti_p5 PARTITION OF tzq_bas_bank_account_pay_ti FOR VALUES IN (5);
COMMENT ON COLUMN  tzq.tzq_bas_bank_account_pay_ti.process_status IS '数据处理状态：2-未处理；4-已处理；5-待清理';
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLE tzq_bas_bank_account_pay_ti TO jdbc_etl_in;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

四、创建“表清理规则临时表”

CREATE TABLE tzq_table_clear_rule_tmp(
  schema_name  varchar(50),
  table_name  varchar(100),
  clear_days  int8,
  last_clear_date TIMESTAMP(0),
  creation_date TIMESTAMP(0),
  last_update_date TIMESTAMP(0),
  status int4,
  execution_clear_sql text,
  description varchar(1000)
);
1
2
3
4
5
6
7
8
9
10
11

五、“表清理规则临时表”制定规则数据 - 定时清理接口表数据

INSERT INTO tzq_table_clear_rule_tmp("shema_name", "table_name", "clear_days", "last_clear_date", "creation_date", "last_update_date", "status", "execution_clear_sql", "discription") VALUES ('tzq', 'tzq_bas_bank_account_pay_ti', 1, '2023-07-03 11:45:15', '2023-07-03 09:34:54', '2023-07-03 09:34:54', 1, 'TRUNCATE TABLE tzq.tzq_bas_bank_account_pay_ti_p5;
	UPDATE tzq.tzq_bas_bank_account_pay_ti t
	   SET process_status  = 5
	where t.process_status = 4;
	TRUNCATE TABLE tzq.tzq_bas_bank_account_pay_ti_p4;', '定时清理集成TI表');
1
2
3
4
5

六、创建测试表2：users表

CREATE TABLE tzq.users (
  id int4 NOT NULL DEFAULT nextval('users_id_seq'),
  username varchar(40),
  email varchar(100),
  created_by int8,
  creation_date timestamp(6)
);
INSERT INTO users (id, username, email, created_by, creation_date) VALUES (1, 'paul', 'hans@qq.com', -1, '2023-07-11 23:54:34.262521+08');
INSERT INTO users (id, username, email, created_by, creation_date) VALUES (4, 'tzq2', 'tzq2@qq.com', -1, '2023-07-11 23:54:34.262521+08');
INSERT INTO users (id, username, email, created_by, creation_date) VALUES (5, 'tzq', 'tzq@qq.com', -1, '2023-07-11 23:54:34.262521+08');
INSERT INTO users (id, username, email, created_by, creation_date) VALUES (6, 'tzq2', 'tzq2@qq.com', -1, '2023-07-11 23:54:34.262521+08');
1
2
3
4
5
6
7
8
9
10
11

七、“表清理规则临时表”-创建制定规则数据2

-- truncate table tzq_table_clear_rule_tmp;
insert into tzq_table_clear_rule_tmp
select 'tzq','users',1,'2023-07-08','2023-07-09',1
      ,'
DROP TABLE if exists users_tmp cascade;
CREATE TABLE users_tmp as
SELECT id,username,email
      ,-1 as created_by
      ,CURRENT_TIMESTAMP as creation_date
  FROM users;
','把users表最新数据备份到users_tmp表';
-- select * from tzq_table_clear_rule_tmp;
1
2
3
4
5
6
7
8
9
10
11
12

八、创建定时任务,一天一执行

-- 建立ROMA任务,一天一执行
DO $$
  DECLARE
    v_sql varchar;
    cur record;
BEGIN
  FOR cur IN (
    SELECT string_to_array(t.execution_clear_sql,';') as sqls
          ,t.table_name
          ,t.last_clear_date
          ,now() + (-t.clear_days ||' day')::INTERVAL
      FROM tzq_table_clear_rule_tmp t
     WHERE t.last_clear_date <= now() + (-t.clear_days ||' day')::INTERVAL
       AND status = 1) LOOP
    FOR i IN 1 .. array_length(cur.sqls, 1) LOOP
      v_sql := cur.sqls[i];
      IF v_sql IS NOT NULL THEN
        EXECUTE v_sql;
        UPDATE tzq.tzq_table_clear_rule_tmp t
           SET last_clear_date = CURRENT_TIMESTAMP
         WHERE t.table_name = cur.table_name;
      END IF;
    END LOOP;
  END LOOP;
END $$;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25