ThingsBoard--使用K3s CronJob自动清理PostgreSQL按日分区表

在管理PostgreSQL数据库时，按日分区是处理时间序列数据的常见策略。然而，随着数据量增长，旧分区会占用大量存储空间，需要定期清理。本文将介绍如何在K3s集群中配置一个CronJob，每天自动清理ts_kv表中30天前的按日分区（分区命名格式为ts_kv_YYYY_MM_DD，如ts_kv_2025_06_01），并确保日志输出详细以便监控。

问题背景
解决方案
技术栈
实现步骤
注意事项
总结

问题背景

本文的前提是你的 ThingsBoard 已经部署在 K3s 集群中，并且已经安装了 PostgreSQL 数据库。

在我们的场景中，ts_kv表存储时间序列数据，按日分区以提高查询效率和数据管理能力。分区名称遵循ts_kv_YYYY_MM_DD格式，例如ts_kv_2025_06_01。为了避免存储空间浪费，我们需要每天删除30天前的分区，同时记录详细的清理日志以便排查问题。

我们选择K3s（轻量级Kubernetes发行版）来运行CronJob，利用psql命令直接与PostgreSQL交互，执行清理逻辑。这种方法轻量、可靠，适合生产环境。

解决方案

我们将创建一个K3s CronJob，每天凌晨1点（上海时间）运行，执行以下任务：

连接到PostgreSQL数据库。
查询ts_kv表的所有分区（ts_kv_%）。
删除早于30天的分区（基于ts_kv_YYYY_MM_DD命名）。
输出详细日志，记录每个分区的检查和删除操作。

技术栈

K3s：运行CronJob的轻量级Kubernetes集群。
PostgreSQL：存储ts_kv表的数据库，使用官方postgres:12镜像运行psql。
Secret：通过K3s Secret管理数据库凭据。
SQL：使用PL/pgSQL脚本处理分区清理逻辑。

实现步骤

创建数据库凭据Secret

为了安全存储PostgreSQL的用户名和密码，我们使用K3s Secret。以下是Secret的YAML定义：

apiVersion: v1
kind: Secret
metadata:
  name: postgres-credentials
  namespace: default
type: Opaque
data:
  DB_USER: cG9zdGdyZXM=         # base64编码的用户名，例如 'postgres'
  DB_PASSWORD: eW91cl9wYXNzd29yZA==  # base64编码的密码

生成base64编码：

echo -n 'your_user' | base64
echo -n 'your_password' | base64

应用Secret：

kubectl apply -f postgres-credentials.yaml

创建CronJob

我们使用postgres:12镜像运行CronJob，通过psql执行SQL脚本清理分区。以下是CronJob的YAML定义：

apiVersion: batch/v1
kind: CronJob
metadata:
  name: cleanup-ts-kv-partitions
  namespace: default # 替换为你的命名空间
spec:
  schedule: "0 1 * * *"  # 每天凌晨1点
  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 '30 days'; # 30天前的日期
                  cutoff_str TEXT;
                  partition_count INTEGER := 0;
                  dropped_count INTEGER := 0;
              BEGIN
                  SET TIME ZONE 'Asia/Shanghai';
                  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: "postgres-service"  # 替换为你的PostgreSQL服务名
            - name: DB_NAME
              value: "your_database"     # 替换为你的数据库名
            - name: DB_USER
              valueFrom:
                secretKeyRef:
                  name: postgres-credentials
                  key: DB_USER
            - name: PGPASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-credentials
                  key: DB_PASSWORD
          restartPolicy: OnFailure

关键点：

调度：schedule: "0 1 * * *"表示每天凌晨1点（HKT）运行。
时区：SET TIME ZONE 'Asia/Shanghai';确保日期计算和日志使用上海时区。
分区逻辑：使用to_char(cutoff_date, 'YYYY_MM_DD')生成YYYY_MM_DD格式，匹配ts_kv_YYYY_MM_DD。
日志：记录清理开始时间、截止日期、每个分区的状态（删除或保留）、总计检查和删除的分区数。

应用CronJob：

kubectl apply -f cleanup-ts-kv-cronjob.yaml

验证执行结果

检查CronJob：

kubectl get cronjobs

手动触发Job（测试）：

kubectl create job --from=cronjob/cleanup-ts-kv-partitions manual-test

查看日志：

kubectl logs <pod-name>

示例日志：

NOTICE: Starting partition cleanup at 2025-06-09 01:00:00.123456+08
NOTICE: Cutoff date: 2025-05-10
NOTICE: Checking partition: ts_kv_2025_05_01
NOTICE: Successfully dropped partition: ts_kv_2025_05_01
NOTICE: Checking partition: ts_kv_2025_06_01
NOTICE: Partition ts_kv_2025_06_01 is within retention period, keeping it.
NOTICE: Cleanup completed. Checked 2 partitions, dropped 1 partitions.

如果发生错误：

NOTICE: Checking partition: ts_kv_2025_05_01
WARNING: Failed to drop partition ts_kv_2025_05_01: permission denied

注意事项

分区命名：脚本假设分区名为ts_kv_YYYY_MM_DD。如果格式不同，需调整to_char和比较逻辑。
权限：确保数据库用户有删除分区的权限，否则会记录permission denied错误。
备份：生产环境中，建议在删除分区前备份数据。
日志监控：通过kubectl logs查看详细日志。考虑集成日志收集工具（如Fluentd或Loki）以便长期监控。
网络：确保CronJob pod能通过DB_HOST访问PostgreSQL服务。
调试：若日志不完整，可在psql命令加-e选项回显SQL语句。

总结

通过K3s CronJob和psql，我们可以高效地自动化清理PostgreSQL按日分区表，释放存储空间，同时通过详细日志监控执行情况。这种方案轻量、可靠，适合在K3s集群中管理时间序列数据。如果你需要进一步优化（例如集成通知、调整保留天数），可以扩展SQL脚本或添加外部工具。

希望这篇文章对你管理PostgreSQL分区表有所帮助！如果有其他问题，欢迎在评论区交流。