K3S 中清理

定义 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.

独立部署中清理

清理脚本 shell

more /usr/local/bin/cleanup_ts_kv_partition.sh
#!/bin/bash

# 数据库连接参数
DB_HOST="127.0.0.1"  # 替换为你的PostgreSQL主机
DB_PORT="5432"
DB_NAME="yiconnect"  # 替换为你的数据库名
DB_USER="postgres"      # 替换为你的数据库用户
DB_PASSWORD="admin"  # 替换为你的数据库密码
LOG_FILE="/var/log/postgres_cleanup.log"

# 设置PGPASSWORD环境变量
export PGPASSWORD="$DB_PASSWORD"

# SQL脚本，内嵌在Shell中
SQL_SCRIPT="
SET TIME ZONE 'Asia/Shanghai';
DO \$\$
DECLARE
    r RECORD;
    cutoff_date DATE := CURRENT_DATE - INTERVAL '30 days';
    cutoff_str TEXT;
    partition_count INTEGER := 0;
    dropped_count INTEGER := 0;
BEGIN
    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 \$\$;"

# 执行清理脚本并记录日志
echo "=== Cleanup started at $(date '+%Y-%m-%d %H:%M:%S %Z') ===" >> "$LOG_FILE"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "$SQL_SCRIPT" >> "$LOG_FILE" 2>&1
EXIT_CODE=$?

if [ $EXIT_CODE -eq 0 ]; then
    echo "Cleanup completed successfully." >> "$LOG_FILE"
else
    echo "Cleanup failed with exit code $EXIT_CODE." >> "$LOG_FILE"
fi
echo "=== Cleanup ended at $(date '+%Y-%m-%d %H:%M:%S %Z') ===" >> "$LOG_FILE"
echo "" >> "$LOG_FILE"

# 清理旧日志（可选，保留最近30天的日志）
find /var/log -name "postgres_cleanup.log" -mtime +30 -delete

Crontab 任务

这里，我们需要每天凌晨执行，使用 docker 容器

0 1 * * * docker run --rm --network host -v /usr/local/bin/cleanup_ts_kv_partition.sh:/cleanup_ts_kv_partition.sh -v /var/log:/var/log hub.yiqisoft.cn/library/postgres:12 /bin/bash /cleanup_ts_kv_partition.sh

执行日志

=== Cleanup started at 2025-07-08 05:24:03 UTC ===
NOTICE:  Starting partition cleanup at 2025-07-08 13:24:03.287644+08
NOTICE:  Cutoff date: 2025-06-08
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_11
NOTICE:  Partition ts_kv_2025_06_11 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_02
NOTICE:  Successfully dropped partition: ts_kv_2025_06_02
NOTICE:  Checking partition: ts_kv_2025_06_03
NOTICE:  Successfully dropped partition: ts_kv_2025_06_03
NOTICE:  Checking partition: ts_kv_2025_06_04
NOTICE:  Successfully dropped partition: ts_kv_2025_06_04
NOTICE:  Checking partition: ts_kv_2025_06_05
NOTICE:  Successfully dropped partition: ts_kv_2025_06_05
NOTICE:  Checking partition: ts_kv_2025_06_12
NOTICE:  Partition ts_kv_2025_06_12 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_06
NOTICE:  Successfully dropped partition: ts_kv_2025_06_06
NOTICE:  Checking partition: ts_kv_2025_06_07
NOTICE:  Successfully dropped partition: ts_kv_2025_06_07
NOTICE:  Checking partition: ts_kv_2025_06_08
NOTICE:  Partition ts_kv_2025_06_08 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_10
NOTICE:  Partition ts_kv_2025_06_10 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_13
NOTICE:  Partition ts_kv_2025_06_13 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_14
NOTICE:  Partition ts_kv_2025_06_14 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_15
NOTICE:  Partition ts_kv_2025_06_15 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_16
NOTICE:  Partition ts_kv_2025_06_16 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_17
NOTICE:  Partition ts_kv_2025_06_17 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_18
NOTICE:  Partition ts_kv_2025_06_18 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_19
NOTICE:  Partition ts_kv_2025_06_19 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_20
NOTICE:  Partition ts_kv_2025_06_20 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_21
NOTICE:  Partition ts_kv_2025_06_21 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_22
NOTICE:  Partition ts_kv_2025_06_22 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_23
NOTICE:  Partition ts_kv_2025_06_23 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_24
NOTICE:  Partition ts_kv_2025_06_24 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_25
NOTICE:  Partition ts_kv_2025_06_25 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_26
NOTICE:  Partition ts_kv_2025_06_26 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_27
NOTICE:  Partition ts_kv_2025_06_27 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_28
NOTICE:  Partition ts_kv_2025_06_28 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_29
NOTICE:  Partition ts_kv_2025_06_29 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_06_30
NOTICE:  Partition ts_kv_2025_06_30 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_07_01
NOTICE:  Partition ts_kv_2025_07_01 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_07_02
NOTICE:  Partition ts_kv_2025_07_02 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_07_03
NOTICE:  Partition ts_kv_2025_07_03 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_07_04
NOTICE:  Partition ts_kv_2025_07_04 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_07_05
NOTICE:  Partition ts_kv_2025_07_05 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_07_06
NOTICE:  Partition ts_kv_2025_07_06 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_07_07
NOTICE:  Partition ts_kv_2025_07_07 is within retention period, keeping it.
NOTICE:  Checking partition: ts_kv_2025_07_08
NOTICE:  Partition ts_kv_2025_07_08 is within retention period, keeping it.
NOTICE:  Cleanup completed. Checked 111 partitions, dropped 77 partitions.
DO
Cleanup completed successfully.
=== Cleanup ended at 2025-07-08 05:24:06 UTC ===

分享-在 K3S 中创建定时任务清理 Postgesql 分区，独立部署中清理

K3S 中清理

定义 Secret

定义 CronJob

手动执行 Job

独立部署中清理

清理脚本 shell

Crontab 任务

执行日志

添加新评论

最新文章

分类