1. 테이블 스페이스 생성
CREATE TABLESPACE bc_stat_history_tbs OWNER postgres LOCATION 'C:\Program Files\PostgreSQL\14\data\bc_stat_history';
2. 파티션 테이블 생성 ( 컬럼명은 추가/보완필요)
CREATE TABLE bc_stat_history (
agent_id int4,
object_name VARCHAR(200),
event_name VARCHAR(200),
value int4,
script_id int4,
stat_time timestamptz,
stat_desc VARCHAR(200),
mon_id VARCHAR(50),
id uuid,
PRIMARY KEY (id, stat_time)
) PARTITION BY RANGE (stat_time) TABLESPACE bc_stat_history_tbs;
기본 키 제약 조건에 PostgreSQL의 파티션 키 열(stat_time)이 포함되어야 함을 나타냅니다. 이 문제를 해결하려면 기본 키에 stat_time을 포함하면 됩니다.
CREATE TABLE bc_stat_history_01 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-01 00:00:00+09') TO ('2024-08-02 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_02 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-02 00:00:00+09') TO ('2024-08-03 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_03 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-03 00:00:00+09') TO ('2024-08-04 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_04 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-04 00:00:00+09') TO ('2024-08-05 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_05 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-05 00:00:00+09') TO ('2024-08-06 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_06 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-06 00:00:00+09') TO ('2024-08-07 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_07 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-07 00:00:00+09') TO ('2024-08-08 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_08 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-08 00:00:00+09') TO ('2024-08-09 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_09 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-09 00:00:00+09') TO ('2024-08-10 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_10 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-10 00:00:00+09') TO ('2024-08-11 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_11 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-11 00:00:00+09') TO ('2024-08-12 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_12 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-12 00:00:00+09') TO ('2024-08-13 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_13 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-13 00:00:00+09') TO ('2024-08-14 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_14 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-14 00:00:00+09') TO ('2024-08-15 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_15 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-15 00:00:00+09') TO ('2024-08-16 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_16 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-16 00:00:00+09') TO ('2024-08-17 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_17 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-17 00:00:00+09') TO ('2024-08-18 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_18 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-18 00:00:00+09') TO ('2024-08-19 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_19 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-19 00:00:00+09') TO ('2024-08-20 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_20 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-20 00:00:00+09') TO ('2024-08-21 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_21 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-21 00:00:00+09') TO ('2024-08-22 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_22 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-22 00:00:00+09') TO ('2024-08-23 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_23 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-23 00:00:00+09') TO ('2024-08-24 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_24 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-24 00:00:00+09') TO ('2024-08-25 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_25 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-25 00:00:00+09') TO ('2024-08-26 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_26 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-26 00:00:00+09') TO ('2024-08-27 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_27 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-27 00:00:00+09') TO ('2024-08-28 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_28 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-28 00:00:00+09') TO ('2024-08-29 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_29 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-29 00:00:00+09') TO ('2024-08-30 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_30 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-30 00:00:00+09') TO ('2024-08-31 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_31 PARTITION OF bc_stat_history FOR VALUES FROM ('2024-08-31 00:00:00+09') TO ('2024-09-01 00:00:00+09') TABLESPACE bc_stat_history_tbs;
CREATE TABLE bc_stat_history_def PARTITION OF bc_stat_history DEFAULT;
--ALTER TABLE bc_stat_history RENAME TO bc_stat_history_20240809;
--ALTER TABLE bc_stat_history RENAME TO bc_stat_history;
SELECT tablename FROM pg_tables
WHERE schemaname = 'public'
AND tablename LIKE 'bc_stat_history_%'
AND to_timestamp(substring(tablename from 8) || '-01', 'YYYY_MM-DD') < current_date - interval '0 days'
---------------------------------------------------------------------------------------------------------
3. 권한부여
-- auto-generated definition
create user 소유주명
superuser
createdb
createrole;
comment on role 소유주명 is '이 DB 소유주';
-- auto-generated definition
create user 사용자명;
alter user 사용자명 set search_path = 소유주명;
comment on role 사용자명 is '이 DB 사용자';
create schema 소유주명;
alter schema 소유주명 owner to 소유주명;
grant usage on schema 소유주명 to 사용자명;
GRANT SELECT, insert, update, delete ON ALL TABLES IN SCHEMA 소유주명 TO 사용자명;
4. 롤링파티션 관리
* 매일 아침 08:30 한달 지난 파티션 테이블은 삭제하고 새로 생성한다.
. 백업테이블 관리는 별도로 하며 관리 로직은 현재로선 불필요하여 뺴놨음
1) 파티션 테이블
=====================================================================
이력 한달관리 및 한달지난 파티션테이블 STOP/RECREATE
* 아래 FUNCTION생성후 스케줄러에 등록함
-- DROP FUNCTION public.drop_and_create_partition();
CREATE OR REPLACE FUNCTION public.drop_and_create_partition()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
partition_name VARCHAR(25);
partition_day CHAR(2);
partition_start_date TIMESTAMP;
partition_end_date TIMESTAMP;
drop_query TEXT;
create_query TEXT;
BEGIN
-- 내일의 날짜 계산
partition_start_date := DATE_TRUNC('day', CURRENT_DATE + INTERVAL '1 day');
partition_end_date := partition_start_date + INTERVAL '1 day';
partition_day := TO_CHAR(partition_start_date, 'DD');
partition_name := 'bc_stat_history_' || partition_day;
-- 파티션 드롭 쿼리 생성
drop_query := 'DROP TABLE IF EXISTS ' || partition_name;
-- 새로운 파티션 테이블 생성 쿼리 생성
create_query := 'CREATE TABLE ' || partition_name ||
' PARTITION OF bc_stat_history ' ||
' FOR VALUES FROM (''' || TO_CHAR(partition_start_date, 'YYYY-MM-DD HH24:MI:SS+09') || ''') ' ||
' TO (''' || TO_CHAR(partition_end_date, 'YYYY-MM-DD HH24:MI:SS+09') || ''') ' ||
' TABLESPACE bc_stat_history_tbs ;';
-- 생성된 쿼리를 출력하여 디버깅
RAISE NOTICE 'Executing DROP Query: %', drop_query;
RAISE NOTICE 'Executing CREATE Query: %', create_query;
-- 드롭 쿼리 실행
BEGIN
EXECUTE drop_query;
RAISE NOTICE 'Table % dropped successfully.', partition_name;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'Drop Query Error: %', SQLERRM;
END;
-- 생성 쿼리 실행
BEGIN
EXECUTE create_query;
RAISE NOTICE 'Table % created successfully.', partition_name;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'Create Query Error: %', SQLERRM;
END;
-- 성공 메시지 출력
RAISE NOTICE 'Partition % dropped and recreated successfully.', partition_name;
END;
$function$;
[매일아침 생성결과]
=====================================================================
daily_01 테이블에 move_data_to_history라는 함수를 트리거로 생성하여,
데이터를 삽입할 때마다 31일 지난 데이터를 history_daily 테이블에 백업하고 삭제합니다.
move_data_to_history 함수는 INSERT 트리거로 동작하며, daily_01 테이블에 데이터를 삽입할 때마다 호출됩니다.
함수 내부에서는 INSERT INTO 문을 사용하여 31일 지난 데이터를 history_daily 테이블에 백업합니다.
DELETE 문을 사용하여 31일 지난 데이터를 daily_01 테이블에서 삭제합니다.
CURRENT_DATE 함수를 사용하여 현재 날짜를 가져옵니다.
INTERVAL '31 DAY'를 사용하여 31일 전 날짜를 계산합니다.
트리거를 생성할 때는 테이블 이름과 트리거 이름을 지정해야 합니다.
FOR EACH ROW 옵션을 사용하여 각 행에 대해 트리거가 실행되도록 합니다.
EXECUTE FUNCTION 문을 사용하여 트리거에서 함수를 호출합니다.
-> crontab을 이용하여 매일 정해진 시간에 트리거를 실행하도록 설정