PostgreSQL partitions, stored procedures, bash, cronjob

CREATE TABLE history_uint_new (
    id SERIAL PRIMARY KEY,
    created_at DATE NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE history_uint_new (
        itemid                   bigint                                    NOT NULL,
        clock                    integer         DEFAULT '0'               NOT NULL,
        value                    numeric(20)     DEFAULT '0'               NOT NULL,
        ns                       integer         DEFAULT '0'               NOT NULL,
        PRIMARY KEY (itemid,clock,ns)
) PARTITION BY RANGE (clock);

ALTER TABLE history_uint_new OWNER TO zabbix;


CREATE TABLE history_uint_2025_04_01 PARTITION OF history_uint_new 
FOR VALUES FROM (EXTRACT(EPOCH FROM TIMESTAMP '2025-04-01 00:00:00'))
             TO (EXTRACT(EPOCH FROM TIMESTAMP '2025-04-02 00:00:00'));

ALTER TABLE history_uint_2025_04_01 OWNER TO zabbix;


CREATE TABLE history_uint_2025_04_02 PARTITION OF history_uint_new 
FOR VALUES FROM (EXTRACT(EPOCH FROM TIMESTAMP '2025-04-02 00:00:00'))
             TO (EXTRACT(EPOCH FROM TIMESTAMP '2025-04-03 00:00:00'));
			 
ALTER TABLE history_uint_2025_04_02 OWNER TO zabbix;

ALTER TABLE history_uint RENAME TO history_uint_old; ALTER TABLE history_uint_new RENAME TO history_uint;



CREATE TABLE auditlog_new (
        auditid                  varchar(25)                               NOT NULL,
        userid                   bigint                                    NULL,
        username                 varchar(100)    DEFAULT ''                NOT NULL,
        clock                    integer         DEFAULT '0'               NOT NULL,
        ip                       varchar(39)     DEFAULT ''                NOT NULL,
        action                   integer         DEFAULT '0'               NOT NULL,
        resourcetype             integer         DEFAULT '0'               NOT NULL,
        resourceid               bigint                                    NULL,
        resource_cuid            varchar(25)                               NULL,
        resourcename             varchar(255)    DEFAULT ''                NOT NULL,
        recordsetid              varchar(25)                               NOT NULL,
        details                  text            DEFAULT ''                NOT NULL,
        PRIMARY KEY (auditid,clock)
) PARTITION BY RANGE (clock);
CREATE INDEX auditlog_new_1 ON auditlog_new (userid,clock);
CREATE INDEX auditlog_new_2 ON auditlog_new (clock);
CREATE INDEX auditlog_new_3 ON auditlog_new (resourcetype,resourceid);
CREATE INDEX auditlog_new_4 ON auditlog_new (recordsetid);
CREATE INDEX auditlog_new_5 ON auditlog_new (ip);


CREATE TABLE auditlog_new_2025_04_01 PARTITION OF auditlog_new 
FOR VALUES FROM (EXTRACT(EPOCH FROM TIMESTAMP '2025-04-01 00:00:00'))
             TO (EXTRACT(EPOCH FROM TIMESTAMP '2025-04-02 00:00:00'));
			 
			 CREATE TABLE auditlog_new_2025_04_02 PARTITION OF auditlog_new 
FOR VALUES FROM (EXTRACT(EPOCH FROM TIMESTAMP '2025-04-02 00:00:00'))
             TO (EXTRACT(EPOCH FROM TIMESTAMP '2025-04-03 00:00:00'));


ALTER TABLE auditlog_new OWNER TO zabbix;
ALTER TABLE auditlog_new_2025_04_01 OWNER TO zabbix;
ALTER TABLE auditlog_new_2025_04_02 OWNER TO zabbix;


ALTER TABLE auditlog RENAME TO auditlog_old; ALTER TABLE auditlog_new RENAME TO auditlog;