PostgreSQL：指定列复制至目标表并自动留存7天数据求助

一、复制源表指定列到目标表 #

通用思路 #

先确保目标表结构与要复制的列匹配（数据类型、长度等一致），再用INSERT...SELECT语句完成数据复制。若需避免重复插入，可基于时间戳或自增ID做增量过滤。

MySQL 示例

-- 首次全量复制指定列
INSERT INTO target_table(col1, col2, col3, record_time)
SELECT src_col1, src_col2, src_col3, NOW()
FROM source_table;

-- 后续增量复制（仅同步新增数据）
INSERT INTO target_table(col1, col2, col3, record_time)
SELECT src_col1, src_col2, src_col3, NOW()
FROM source_table
WHERE source_table.update_time > COALESCE((SELECT MAX(record_time) FROM target_table), '1970-01-01');

PostgreSQL 示例

-- 首次全量复制指定列
INSERT INTO target_table(col1, col2, col3, record_time)
SELECT src_col1, src_col2, src_col3, CURRENT_TIMESTAMP
FROM source_table;

-- 后续增量复制（仅同步新增数据）
INSERT INTO target_table(col1, col2, col3, record_time)
SELECT src_col1, src_col2, src_col3, CURRENT_TIMESTAMP
FROM source_table
WHERE source_table.update_time > COALESCE((SELECT MAX(record_time) FROM target_table), '1970-01-01'::timestamp);

二、自动清理7天以上旧数据 #

清理数据SQL #

MySQL

DELETE FROM target_table
WHERE record_time < DATE_SUB(NOW(), INTERVAL 7 DAY);

PostgreSQL

DELETE FROM target_table
WHERE record_time < CURRENT_TIMESTAMP - INTERVAL '7 days';

设置定时任务实现自动执行 #

MySQL（事件调度器）

1. 开启事件调度器：

SET GLOBAL event_scheduler = ON;

2. 创建每天凌晨执行的清理事件：

CREATE EVENT clean_target_table_old_data
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
DO
BEGIN
  DELETE FROM target_table WHERE record_time < DATE_SUB(NOW(), INTERVAL 7 DAY);
END;

PostgreSQL（pg_cron扩展）

1. 安装pg_cron扩展（需超级权限）：

CREATE EXTENSION IF NOT EXISTS pg_cron;

2. 创建每天凌晨执行的定时任务：

SELECT cron.schedule('daily-clean-target-table', '0 0 * * *', $$DELETE FROM target_table WHERE record_time < CURRENT_TIMESTAMP - INTERVAL '7 days'$$);

实用建议 #

• 所有操作先在测试环境验证，确认数据正确、性能无影响后再部署到生产
• 若源表数据量较大，优先用增量同步，避免全量插入导致数据库负载过高
• 清理大量旧数据时，不要一次性执行大DELETE，可分批删除（如每次删1000条加LIMIT），防止长时间锁表
• 给目标表的record_time列添加索引，大幅提升DELETE和增量查询的效率
• 定期检查定时任务的执行日志，确保清理和同步任务正常运行

内容的提问来源于stack exchange，提问作者Utpal DEBSARMA Debsarma