PostgreSQL中如何将SSD上的旧时序数据表迁移至HDD并保留逻辑结构

结论 #

你的需求完全可以实现，PostgreSQL的tablespace特性刚好适配你的场景——既能将旧数据迁移到HDD，又能保留原有表的逻辑结构，第三方监控应用无需任何修改即可正常访问数据。

具体操作步骤 #

1. 在HDD上创建专用表空间 #

首先在系统层面为HDD上的PostgreSQL存储创建目录并设置权限：

# 假设HDD挂载在/mnt/hdd，根据你的实际挂载路径调整
sudo mkdir /mnt/hdd/postgres_ts
sudo chown postgres:postgres /mnt/hdd/postgres_ts
sudo chmod 700 /mnt/hdd/postgres_ts

然后登录PostgreSQL控制台（psql），创建表空间：

CREATE TABLESPACE hdd_ts LOCATION '/mnt/hdd/postgres_ts';

2. 迁移旧数据表到HDD表空间 #

针对所有超过1个月的sqlt_data_1_YYYY_XX表，执行表空间迁移命令：

# 示例：迁移2023年9月的表
ALTER TABLE sqlt_data_1_2023_09 SET TABLESPACE hdd_ts;

如果表数量较多，可以用SQL批量生成迁移语句，避免手动逐个操作：

SELECT 'ALTER TABLE ' || tablename || ' SET TABLESPACE hdd_ts;'
FROM pg_tables
WHERE schemaname = 'public' -- 替换为你的表所在的schema名称
  AND tablename LIKE 'sqlt_data_1_%'
  -- 筛选出早于1个月前的表（根据表名中的年月判断）
  AND to_date(substring(tablename from 'sqlt_data_1_(\d{4}_\d{2})'), 'YYYY_MM') < current_date - interval '1 month';

将上述查询输出的所有ALTER TABLE语句复制到控制台执行即可完成批量迁移。

3. 自动化后续迁移 #

为了避免每月手动操作，可以写一个简单的Shell脚本，结合Ubuntu的cron定时任务，每月自动迁移上个月的表：

#!/bin/bash
# 脚本路径：/usr/local/bin/migrate_old_tables.sh
psql -U postgres -d your_database_name << EOF
SELECT 'ALTER TABLE ' || tablename || ' SET TABLESPACE hdd_ts;'
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename LIKE 'sqlt_data_1_%'
  AND to_date(substring(tablename from 'sqlt_data_1_(\d{4}_\d{2})'), 'YYYY_MM') < current_date - interval '1 month';
EOF | grep -v "ALTER TABLE" | psql -U postgres -d your_database_name

给脚本加执行权限：

sudo chmod +x /usr/local/bin/migrate_old_tables.sh

然后添加到cron，每月1号执行：

sudo crontab -u postgres -e
# 添加以下内容
0 0 1 * * /usr/local/bin/migrate_old_tables.sh

4. 验证迁移结果 #

登录psql执行以下语句，确认旧表已迁移到HDD表空间：

SELECT tablename, tablespace
FROM pg_tables
WHERE tablename LIKE 'sqlt_data_1_%';

第三方监控应用无需任何调整，因为表的名称、结构、访问路径完全没有变化，PostgreSQL会自动处理底层存储的读取请求。

可选优化方向 #

如果后续想简化表管理，可以将现有按月分表改造成分区表：创建一个主表（如sqlt_data_1），将每个月的数据作为分区，新分区默认放在SSD表空间，旧分区迁移到HDD表空间。这种方式下，应用只需访问主表即可查询所有数据，无需关心具体的分表名称，但当前的tablespace方案已经能满足你的需求，且无需修改应用逻辑，更适合快速落地。

内容的提问来源于stack exchange，提问作者ConceptBug