PostgreSQL通过声明式分区实现按天分区

PostgreSQL通过声明式分区实现按天分区

PostgreSQL按天分区可以通过声明式分区实现，以下是具体实现方法和注意事项：

1.创建父表（分区主表）

CREATE TABLE log_data (
    id SERIAL,
    log_time TIMESTAMP NOT NULL,
    content TEXT
) PARTITION BY RANGE (log_time);

2.手动创建分区子表

-- 创建2025年6月10日的分区
CREATE TABLE log_data_20250610 PARTITION OF log_data
    FOR VALUES FROM ('2025-06-10 00:00:00') TO ('2025-06-11 00:00:00');

-- 创建2025年6月11日的分区
CREATE TABLE log_data_20250611 PARTITION OF log_data
    FOR VALUES FROM ('2025-06-11 00:00:00') TO ('2025-06-12 00:00:00');

3.自动创建分区的函数（推荐）

这是一个触发器触发的函数

CREATE OR REPLACE FUNCTION create_daily_partition()
RETURNS TRIGGER AS $$
DECLARE
    partition_date TEXT;
    partition_name TEXT;
    start_date TIMESTAMP;
    end_date TIMESTAMP;
BEGIN
    -- 获取下一天的日期
    partition_date := TO_CHAR(CURRENT_DATE + 1, 'YYYYMMDD');
    partition_name := 'log_data_' || partition_date;
    
    -- 设置分区时间范围（下一天）
    start_date := DATE_TRUNC('day', CURRENT_DATE + 1);
    end_date := start_date + INTERVAL '1 day';
    
    -- 如果分区不存在则创建
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF log_data 
                   FOR VALUES FROM (%L) TO (%L)', 
                   partition_name, start_date, end_date);
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

4.创建触发器（每天自动检查）

CREATE TRIGGER trg_create_daily_partition
BEFORE INSERT ON log_data
EXECUTE FUNCTION create_daily_partition();

关键注意事项：
分区字段必须是主键或唯一约束的一部分
每个分区可以单独创建索引提升查询性能9
范围分区的上限是不包含的（如6月10日分区包含10日0点至11日0点前的数据）
建议提前创建未来几天的分区以避免插入失败
可以使用pg_cron扩展设置定时任务自动创建分区
对于历史数据迁移，可以使用ALTER TABLE…ATTACH PARTITION将现有表附加为分区。按天分区特别适合日志类时间序列数据，能显著提升查询和维护效率。

如果不想通过触发器触发，可以创建一个单独调用的函数，通过Linux定时器触发或者‌pg_cron内置调度器触发

5.自动创建分区的存储过程

CREATE OR REPLACE PROCEDURE create_daily_partition(
    p_days_ahead INTEGER DEFAULT 1,
    p_table_name TEXT DEFAULT 'log_data'
)
LANGUAGE plpgsql
AS $$
DECLARE
    partition_date TEXT;
    partition_name TEXT;
    start_date TIMESTAMP;
    end_date TIMESTAMP;
BEGIN
    -- 计算目标日期
    partition_date := TO_CHAR(CURRENT_DATE + p_days_ahead, 'YYYYMMDD');
    partition_name := p_table_name || '_' || partition_date;
    
    -- 设置分区时间范围
    start_date := DATE_TRUNC('day', CURRENT_DATE + p_days_ahead);
    end_date := start_date + INTERVAL '1 day';
    
    -- 创建分区（IF NOT EXISTS避免重复创建）
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF %I 
                   FOR VALUES FROM (%L) TO (%L)', 
                   partition_name, p_table_name, start_date, end_date);
    
    RAISE NOTICE '已创建分区: % (日期范围: % 至 %)', 
        partition_name, start_date, end_date;
END;
$$;

6.Linux定时任务触发

sh脚本:run_pgsql.sh

#!/bin/bash
echo "[$(date)] 开始执行存储过程" >> /var/log/pgsql_proc.log
PGPASSWORD="your_password" /usr/bin/psql -h localhost -U postgres -d your_db -c "CALL create_daily_partition();"
echo "[$(date)] 执行完成" >> /var/log/pgsql_proc.log

赋权

chmod +x run_pgsql.sh
chown postgres:postgres run_pgsql.sh

添加定时任务

切换用户：
su - postgres
编辑定时任务：
crontab -e
添加定时任务：
0 1 * * * /opt/pgsql_jobs/run_proc.sh >> /var/log/pgsql_proc.log 2>&1

7.pg_cron内置调度器触发

以下操作需要在数据库执行

安装扩展

CREATE EXTENSION pg_cron;

配置凌晨1点任务

SELECT cron.schedule(
    'daily_1am_job',       -- 任务名称
    '0 1 * * *',           -- cron表达式（与Linux格式相同）
    'CALL create_daily_partition()'  -- 执行的SQL命令
);

支持所有标准cron表达式语法
任务元数据存储在 cron.job 表中，可通过 cron.unschedule() 取消任务