TimescaleDB 2.27.2：压缩策略与刷新策略冲突的修复，建议尽快升级

# 1. 更新软件包（以 Ubuntu/Debian 为例）
sudo apt update
sudo apt install timescaledb-2-postgresql-16=2.27.2

# 2. 在每个数据库中更新扩展
psql -d your_timeseries_db -c "ALTER EXTENSION timescaledb UPDATE TO '2.27.2';"

# 3. 验证版本
psql -d your_timeseries_db -c "SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';"
# 期望输出: 2.27.2

# 拉取新镜像并重启容器
docker pull timescale/timescaledb-ha:pg16-ts2.27.2
docker stop tsdb && docker rm tsdb
docker run -d --name tsdb \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=yourpassword \
  -v tsdb_data:/var/lib/postgresql/data \
  timescale/timescaledb-ha:pg16-ts2.27.2

# 容器启动后同样需要 ALTER EXTENSION
docker exec tsdb psql -U postgres -d your_timeseries_db \
  -c "ALTER EXTENSION timescaledb UPDATE TO '2.27.2';"

-- 查看所有 hypertable 上的策略
SELECT hypertable_name, job_id, proc_name, schedule_interval, max_runtime
FROM timescaledb_information.jobs
WHERE proc_name IN ('policy_refresh_continuous_aggregate', 'policy_compression');

-- 如果发现某个持续聚合缺少刷新策略，手动重建：
-- 假设持续聚合视图名为 metrics_hourly，刷新间隔 1 小时
SELECT add_continuous_aggregate_policy(
  'metrics_hourly',
  start_offset    => INTERVAL '3 hours',
  end_offset      => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour'
);

-- 添加压缩策略（2.27.2 中不再会误删刷新策略）
SELECT add_compression_policy(
  'metrics',
  compress_after => INTERVAL '7 days'
);

-- 创建 hypertable
CREATE TABLE metrics (
  time        TIMESTAMPTZ       NOT NULL,
  device_id   TEXT              NOT NULL,
  value       DOUBLE PRECISION  NULL
);
SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day');

-- 创建持续聚合
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       device_id,
       avg(value) AS avg_value,
       count(*)   AS sample_count
FROM metrics
GROUP BY bucket, device_id;

-- 先加刷新策略
SELECT add_continuous_aggregate_policy(
  'metrics_hourly',
  start_offset      => INTERVAL '3 hours',
  end_offset        => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour'
);

-- 再加压缩策略（2.27.2 安全，2.27.1 会误删上面的刷新策略）
SELECT add_compression_policy(
  'metrics',
  compress_after => INTERVAL '7 days'
);

-- 验证两个策略都存在
SELECT hypertable_name, proc_name
FROM timescaledb_information.jobs
WHERE hypertable_name IN ('metrics', '_timescaledb_internal._hyper_1_1_chunk')
  AND proc_name IN ('policy_refresh_continuous_aggregate', 'policy_compression');