分享-在 K3S 中创建定时任务清理 Postgesql 分区
定义 Secret
最好使用 一个 Secret 来存储用户名和密码
apiVersion: v1
kind: Secret
metadata:
name: postgres-credentials
namespace: n100
type: Opaque
data:
DB_USER: foo= # 替换为base64编码的用户名
DB_PASSWORD: bar= # 替换为base64编码的密码定义 CronJob
这里定义了 7 days，根据自己的需求来定义日期即可
apiVersion: batch/v1
kind: CronJob
metadata:
name: cleanup-ts-kv-partitions
namespace: n100
spec:
schedule: "0 1 * * *" # 每天凌晨1点 (HKT)
jobTemplate:
spec:
template:
spec:
containers:
- name: cleanup-partitions
image: hub.yiqisoft.cn/library/postgres:12
command:
- /bin/sh
- -c
- |
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "
DO \$\$
DECLARE
r RECORD;
cutoff_date DATE := CURRENT_DATE - INTERVAL '7 days';
cutoff_str TEXT;
partition_count INTEGER := 0;
dropped_count INTEGER := 0;
BEGIN
SET TIME ZONE 'Asia/Hong_Kong';
RAISE NOTICE 'Starting partition cleanup at %', current_timestamp;
RAISE NOTICE 'Cutoff date: %', cutoff_date;
cutoff_str := to_char(cutoff_date, 'YYYY_MM_DD');
FOR r IN (
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
AND tablename LIKE 'ts_kv_%'
) LOOP
partition_count := partition_count + 1;
RAISE NOTICE 'Checking partition: %', r.tablename;
IF r.tablename < 'ts_kv_' || cutoff_str THEN
BEGIN
EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename);
dropped_count := dropped_count + 1;
RAISE NOTICE 'Successfully dropped partition: %', r.tablename;
EXCEPTION
WHEN OTHERS THEN
RAISE WARNING 'Failed to drop partition %: %', r.tablename, SQLERRM;
END;
ELSE
RAISE NOTICE 'Partition % is within retention period, keeping it.', r.tablename;
END IF;
END LOOP;
RAISE NOTICE 'Cleanup completed. Checked % partitions, dropped % partitions.', partition_count, dropped_count;
EXCEPTION
WHEN OTHERS THEN
RAISE WARNING 'Cleanup process failed: %', SQLERRM;
RAISE EXCEPTION 'Terminating due to error';
END \$\$;"
env:
- name: DB_HOST
value: "yiconnect-standalone-0-1749434022-postgres" # 替换为你的PostgreSQL服务名
- name: DB_NAME
value: "yiconnect" # 替换为你的数据库名
- name: DB_USER
valueFrom:
secretKeyRef:
name: postgres-credentials
key: DB_USER
- name: PGPASSWORD
valueFrom:
secretKeyRef:
name: postgres-credentials
key: DB_PASSWORD
restartPolicy: OnFailure手动执行 Job
日志如下：
NOTICE: Starting partition cleanup at 2025-06-09 10:43:47.469367+08
NOTICE: Cutoff date: 2025-06-02
NOTICE: Checking partition: ts_kv_latest
NOTICE: Partition ts_kv_latest is within retention period, keeping it.
NOTICE: Checking partition: ts_kv_dictionary
NOTICE: Partition ts_kv_dictionary is within retention period, keeping it.
NOTICE: Checking partition: ts_kv_indefinite
NOTICE: Partition ts_kv_indefinite is within retention period, keeping it.
NOTICE: Checking partition: ts_kv_2025_06_09
NOTICE: Partition ts_kv_2025_06_09 is within retention period, keeping it.
NOTICE: Checking partition: ts_kv_2025_06_02
NOTICE: Partition ts_kv_2025_06_02 is within retention period, keeping it.
NOTICE: Checking partition: ts_kv_2025_05_03
NOTICE: Successfully dropped partition: ts_kv_2025_05_03
NOTICE: Checking partition: ts_kv_2025_05_01
NOTICE: Successfully dropped partition: ts_kv_2025_05_01
NOTICE: Checking partition: ts_kv_2025_05_02
NOTICE: Successfully dropped partition: ts_kv_2025_05_02
NOTICE: Checking partition: ts_kv_2025_05_04
NOTICE: Successfully dropped partition: ts_kv_2025_05_04
NOTICE: Checking partition: ts_kv_2025_05_05
NOTICE: Successfully dropped partition: ts_kv_2025_05_05
NOTICE: Checking partition: ts_kv_2025_05_06
NOTICE: Successfully dropped partition: ts_kv_2025_05_06
NOTICE: Checking partition: ts_kv_2025_05_07
NOTICE: Successfully dropped partition: ts_kv_2025_05_07
NOTICE: Checking partition: ts_kv_2025_05_08
NOTICE: Successfully dropped partition: ts_kv_2025_05_08
NOTICE: Checking partition: ts_kv_2025_05_09
NOTICE: Successfully dropped partition: ts_kv_2025_05_09
NOTICE: Checking partition: ts_kv_2025_05_10
NOTICE: Successfully dropped partition: ts_kv_2025_05_10
NOTICE: Checking partition: ts_kv_2025_05_11
NOTICE: Successfully dropped partition: ts_kv_2025_05_11
NOTICE: Checking partition: ts_kv_2025_05_12
NOTICE: Successfully dropped partition: ts_kv_2025_05_12
NOTICE: Checking partition: ts_kv_2025_05_13
NOTICE: Successfully dropped partition: ts_kv_2025_05_13
NOTICE: Checking partition: ts_kv_2025_05_14
NOTICE: Successfully dropped partition: ts_kv_2025_05_14
NOTICE: Checking partition: ts_kv_2025_05_15
NOTICE: Successfully dropped partition: ts_kv_2025_05_15
NOTICE: Checking partition: ts_kv_2025_05_16
NOTICE: Successfully dropped partition: ts_kv_2025_05_16
NOTICE: Checking partition: ts_kv_2025_05_17
NOTICE: Successfully dropped partition: ts_kv_2025_05_17
NOTICE: Checking partition: ts_kv_2025_05_18
NOTICE: Successfully dropped partition: ts_kv_2025_05_18
NOTICE: Checking partition: ts_kv_2025_05_19
NOTICE: Successfully dropped partition: ts_kv_2025_05_19
NOTICE: Checking partition: ts_kv_2025_05_20
NOTICE: Successfully dropped partition: ts_kv_2025_05_20
NOTICE: Checking partition: ts_kv_2025_05_21
NOTICE: Successfully dropped partition: ts_kv_2025_05_21
NOTICE: Checking partition: ts_kv_2025_05_22
NOTICE: Successfully dropped partition: ts_kv_2025_05_22
NOTICE: Checking partition: ts_kv_2025_05_23
NOTICE: Successfully dropped partition: ts_kv_2025_05_23
NOTICE: Checking partition: ts_kv_2025_05_24
NOTICE: Successfully dropped partition: ts_kv_2025_05_24
NOTICE: Checking partition: ts_kv_2025_05_25
NOTICE: Successfully dropped partition: ts_kv_2025_05_25
NOTICE: Checking partition: ts_kv_2025_05_26
NOTICE: Successfully dropped partition: ts_kv_2025_05_26
NOTICE: Checking partition: ts_kv_2025_05_27
NOTICE: Successfully dropped partition: ts_kv_2025_05_27
NOTICE: Checking partition: ts_kv_2025_05_28
NOTICE: Successfully dropped partition: ts_kv_2025_05_28
NOTICE: Checking partition: ts_kv_2025_05_29
NOTICE: Successfully dropped partition: ts_kv_2025_05_29
NOTICE: Checking partition: ts_kv_2025_05_30
NOTICE: Successfully dropped partition: ts_kv_2025_05_30
NOTICE: Checking partition: ts_kv_2025_05_31
NOTICE: Successfully dropped partition: ts_kv_2025_05_31
NOTICE: Cleanup completed. Checked 36 partitions, dropped 31 partitions.