1.20. 恢复合规性数据

SELECT relname, relkind FROM pg_class WHERE relname = 'local_policies';

SELECT relname, relkind FROM pg_class WHERE relname = 'local_policies';

-- start a transaction
BEGIN;
-- Rename the legacy TABLE_NAME
ALTER TABLE event.local_policies RENAME TO local_policies_old;
-- Partition tables: https://github.com/stolostron/multicluster-global-hub/blob/main/operator/pkg/controllers/hubofhubs/database/2.tables.sql#L283-L318
CREATE TABLE IF NOT EXISTS event.local_policies (
    event_name character varying(63) NOT NULL,
    policy_id uuid NOT NULL,
    cluster_id uuid NOT NULL,
    leaf_hub_name character varying(63) NOT NULL,
    message text,
    reason text,
    count integer NOT NULL DEFAULT 0,
    source jsonb,
    created_at timestamp without time zone DEFAULT now() NOT NULL,
    compliance local_status.compliance_type NOT NULL,
    -- Rename the constraint to avoid conflicts
    CONSTRAINT local_policies_unique_partition_constraint UNIQUE (event_name, count, created_at)
) PARTITION BY RANGE (created_at);
-- Create partitions, load the old data to the previous partition table
CREATE TABLE IF NOT EXISTS event.local_policies_2023_08 PARTITION OF event.local_policies FOR VALUES FROM ('2023-08-01') TO ('2023-09-01');
CREATE TABLE IF NOT EXISTS event.local_policies_2023_07 PARTITION OF event.local_policies FOR VALUES FROM ('2000-01-01') TO ('2023-08-01');

-- Move the records from regular table to partition table
INSERT INTO event.local_policies SELECT * FROM event.local_polcies_old;
DROP TABLE IF EXISTS event.local_policies_old;
-- commit the transaction
COMMIT;

-- start a transaction
BEGIN;
-- Rename the legacy TABLE_NAME
ALTER TABLE event.local_policies RENAME TO local_policies_old;
-- Partition tables: https://github.com/stolostron/multicluster-global-hub/blob/main/operator/pkg/controllers/hubofhubs/database/2.tables.sql#L283-L318
CREATE TABLE IF NOT EXISTS event.local_policies (
    event_name character varying(63) NOT NULL,
    policy_id uuid NOT NULL,
    cluster_id uuid NOT NULL,
    leaf_hub_name character varying(63) NOT NULL,
    message text,
    reason text,
    count integer NOT NULL DEFAULT 0,
    source jsonb,
    created_at timestamp without time zone DEFAULT now() NOT NULL,
    compliance local_status.compliance_type NOT NULL,
    -- Rename the constraint to avoid conflicts
    CONSTRAINT local_policies_unique_partition_constraint UNIQUE (event_name, count, created_at)
) PARTITION BY RANGE (created_at);
-- Create partitions, load the old data to the previous partition table
CREATE TABLE IF NOT EXISTS event.local_policies_2023_08 PARTITION OF event.local_policies FOR VALUES FROM ('2023-08-01') TO ('2023-09-01');
CREATE TABLE IF NOT EXISTS event.local_policies_2023_07 PARTITION OF event.local_policies FOR VALUES FROM ('2000-01-01') TO ('2023-08-01');

-- Move the records from regular table to partition table
INSERT INTO event.local_policies SELECT * FROM event.local_polcies_old;
DROP TABLE IF EXISTS event.local_policies_old;
-- commit the transaction
COMMIT;