保留策略 #

保留策略原理：

数据生命周期
├── 新数据
│   └── 写入超表
│
├── 活跃数据
│   └── 频繁查询
│
├── 历史数据
│   └── 偶尔查询
│
└── 过期数据
    └── 自动删除

保留策略优势：

自动化管理
├── 自动删除旧数据
├── 无需手动干预
├── 定期执行清理
└── 减少运维工作

存储优化
├── 释放磁盘空间
├── 降低存储成本
├── 减少备份大小
└── 提高查询性能

合规性
├── 满足数据保留要求
├── 自动清理敏感数据
├── 数据生命周期管理
└── 合规审计支持

对比手动删除：

特性              保留策略        手动删除
─────────────────────────────────────────
自动化            ✓              ✗
定时执行          ✓              ✗
分片级别删除      ✓              ✓
释放磁盘空间      ✓              需VACUUM
运维成本          低             高
出错风险          低             高

-- 创建超表
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION
);

SELECT create_hypertable('sensor_data', 'time');

-- 添加保留策略
SELECT add_retention_policy(
    'sensor_data',
    INTERVAL '90 days'  -- 保留90天数据
);

-- 查看保留策略
SELECT 
    job_id,
    schedule_interval,
    config
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 完整参数
SELECT add_retention_policy(
    'sensor_data',              -- 超表名
    INTERVAL '90 days',         -- 保留时间
    if_not_exists => true       -- 如果已存在不报错
);

-- 查看策略详情
SELECT 
    job_id,
    application_name,
    schedule_interval,
    max_runtime,
    scheduled,
    config
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 短期保留（7天）
SELECT add_retention_policy(
    'high_freq_data',
    INTERVAL '7 days'
);

-- 中期保留（90天）
SELECT add_retention_policy(
    'sensor_data',
    INTERVAL '90 days'
);

-- 长期保留（1年）
SELECT add_retention_policy(
    'important_data',
    INTERVAL '365 days'
);

-- 永久保留（不添加策略）
-- 或设置很长的保留时间
SELECT add_retention_policy(
    'permanent_data',
    INTERVAL '10 years'
);

-- 查看所有保留策略
SELECT 
    job_id,
    hypertable_name,
    schedule_interval,
    config->'drop_after' as retention_period
FROM timescaledb_information.jobs j
JOIN timescaledb_information.hypertables h
    ON j.hypertable_name = h.hypertable_name
WHERE proc_name = 'policy_retention';

-- 查看策略详情
SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 方法1：删除后重新添加
SELECT remove_retention_policy('sensor_data');

SELECT add_retention_policy(
    'sensor_data',
    INTERVAL '180 days'
);

-- 方法2：使用alter_job
SELECT alter_job(
    job_id,
    schedule_interval => INTERVAL '1 day',
    config => jsonb_set(
        config,
        '{drop_after}',
        '"180 days"'
    )
) FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention'
  AND hypertable_name = 'sensor_data';

-- 删除保留策略
SELECT remove_retention_policy('sensor_data');

-- 验证删除
SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention'
  AND hypertable_name = 'sensor_data';

-- 暂停保留策略
SELECT alter_job(
    job_id,
    scheduled => false
) FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention'
  AND hypertable_name = 'sensor_data';

-- 恢复保留策略
SELECT alter_job(
    job_id,
    scheduled => true
) FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention'
  AND hypertable_name = 'sensor_data';

-- 手动删除指定时间前的数据
SELECT drop_chunks(
    'sensor_data',
    older_than => INTERVAL '90 days'
);

-- 删除指定日期前的数据
SELECT drop_chunks(
    'sensor_data',
    older_than => '2024-01-01'::timestamptz
);

-- 删除指定时间范围
SELECT drop_chunks(
    'sensor_data',
    newer_than => INTERVAL '180 days',
    older_than => INTERVAL '90 days'
);

-- 手动运行保留策略作业
SELECT run_job(job_id)
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention'
  AND hypertable_name = 'sensor_data';

-- 查看执行结果
SELECT 
    job_id,
    last_run,
    last_run_success
FROM timescaledb_information.job_stats
WHERE job_id IN (
    SELECT job_id FROM timescaledb_information.jobs
    WHERE proc_name = 'policy_retention'
);

-- 查看保留策略执行历史
SELECT 
    job_id,
    last_run,
    next_run,
    last_run_success,
    total_runs,
    total_failures
FROM timescaledb_information.job_stats
WHERE job_id IN (
    SELECT job_id FROM timescaledb_information.jobs
    WHERE proc_name = 'policy_retention'
);

-- 查看最近执行时间
SELECT 
    hypertable_name,
    last_run,
    next_run
FROM timescaledb_information.jobs j
JOIN timescaledb_information.job_stats s ON j.job_id = s.job_id
WHERE proc_name = 'policy_retention';

-- 查看数据保留情况
SELECT 
    hypertable_name,
    count(*) as chunk_count,
    min(range_start) as oldest_data,
    max(range_end) as newest_data,
    max(range_end) - min(range_start) as data_span
FROM timescaledb_information.chunks
GROUP BY hypertable_name;

-- 查看即将删除的分片
SELECT 
    chunk_name,
    range_start,
    range_end,
    pg_size_pretty(total_bytes) as size
FROM timescaledb_information.chunks c
JOIN chunk_relation_size(c.hypertable_name) s ON c.chunk_name = s.chunk_name
WHERE c.hypertable_name = 'sensor_data'
  AND c.range_end < NOW() - INTERVAL '90 days'
ORDER BY range_start;

-- 查看保留策略错误
SELECT 
    job_id,
    err_time,
    err_message
FROM timescaledb_information.job_errors
WHERE job_id IN (
    SELECT job_id FROM timescaledb_information.jobs
    WHERE proc_name = 'policy_retention'
)
ORDER BY err_time DESC
LIMIT 10;

-- 保留策略可以删除压缩数据
-- drop_chunks会自动处理压缩分片

-- 添加压缩策略
SELECT add_compression_policy(
    'sensor_data',
    INTERVAL '7 days'
);

-- 添加保留策略
SELECT add_retention_policy(
    'sensor_data',
    INTERVAL '90 days'
);

-- 数据生命周期
-- 0-7天: 原始数据
-- 7-90天: 压缩数据
-- 90天后: 自动删除

-- 配置分层存储
-- 热数据（0-7天）：原始数据，SSD存储
-- 温数据（7-30天）：压缩数据，SSD存储
-- 冷数据（30-90天）：压缩数据，HDD存储
-- 过期数据（90天后）：自动删除

-- 添加压缩策略
SELECT add_compression_policy(
    'sensor_data',
    INTERVAL '7 days'
);

-- 添加保留策略
SELECT add_retention_policy(
    'sensor_data',
    INTERVAL '90 days'
);

保留时间选择指南：

数据类型          推荐保留时间
─────────────────────────────
日志数据          7-30天
监控数据          30-90天
业务数据          90-365天
重要数据          1-3年
合规数据          根据法规要求

-- 配置执行时间
-- 默认每天执行一次

-- 修改执行间隔
SELECT alter_job(
    job_id,
    schedule_interval => INTERVAL '12 hours'
) FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention'
  AND hypertable_name = 'sensor_data';

-- 配置最大运行时间
SELECT alter_job(
    job_id,
    max_runtime => INTERVAL '1 hour'
) FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 为多个表添加保留策略
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');
SELECT add_retention_policy('event_logs', INTERVAL '30 days');
SELECT add_retention_policy('metrics', INTERVAL '7 days');

-- 查看所有保留策略
SELECT 
    hypertable_name,
    config->'drop_after' as retention_period,
    schedule_interval
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 在删除前备份数据
-- 使用pg_dump备份即将删除的数据

-- 查看即将删除的数据
SELECT 
    chunk_name,
    range_start,
    range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data'
  AND range_end < NOW() - INTERVAL '90 days';

-- 备份数据
pg_dump -t sensor_data -f backup.sql tsdb

-- 或导出CSV
COPY (
    SELECT * FROM sensor_data
    WHERE time < NOW() - INTERVAL '90 days'
) TO '/backup/old_data.csv' WITH (FORMAT csv, HEADER true);

-- 创建归档表
CREATE TABLE sensor_data_archive (LIKE sensor_data INCLUDING ALL);
SELECT create_hypertable('sensor_data_archive', 'time');

-- 归档旧数据
INSERT INTO sensor_data_archive
SELECT * FROM sensor_data
WHERE time < NOW() - INTERVAL '90 days';

-- 删除已归档数据
SELECT drop_chunks(
    'sensor_data',
    older_than => INTERVAL '90 days'
);

-- 问题：数据被意外删除
-- 预防措施

-- 1. 定期备份
pg_dump -Fc tsdb > backup.dump

-- 2. 修改保留时间
SELECT remove_retention_policy('sensor_data');
SELECT add_retention_policy('sensor_data', INTERVAL '180 days');

-- 3. 暂停保留策略
SELECT alter_job(job_id, scheduled => false)
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 问题：保留策略未自动执行
-- 检查策略状态

-- 1. 查看策略是否启用
SELECT job_id, scheduled
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 2. 查看执行错误
SELECT * FROM timescaledb_information.job_errors
WHERE job_id IN (
    SELECT job_id FROM timescaledb_information.jobs
    WHERE proc_name = 'policy_retention'
);

-- 3. 手动执行测试
SELECT run_job(job_id)
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 问题：删除数据后磁盘空间未释放
-- 原因：使用DELETE而不是drop_chunks

-- 解决方案：使用drop_chunks
SELECT drop_chunks(
    'sensor_data',
    older_than => INTERVAL '90 days'
);

-- 或对DELETE的数据执行VACUUM
VACUUM sensor_data;