PostgreSQL 13.2如何将company2模式的表和索引迁移至I盘

一、前期准备 #

• 在I盘创建用于存储表空间的空目录，例如 I:\pg_tablespaces\company2
• 给PostgreSQL服务运行的账号（通常是postgres用户）分配该目录的完全控制权限，避免后续操作出现权限错误

二、创建目标表空间 #

登录PostgreSQL数据库，执行以下SQL创建对应表空间：

CREATE TABLESPACE ts_company2
LOCATION 'I:/pg_tablespaces/company2'; -- Windows路径可使用正斜杠，或双反斜杠转义：'I:\\pg_tablespaces\\company2'

三、批量生成并执行迁移语句 #

由于模式下可能有大量表、索引、序列，可通过查询自动生成迁移SQL，避免手动逐个操作：

1. 生成表迁移语句 #

SELECT 'ALTER TABLE company2.' || table_name || ' SET TABLESPACE ts_company2;'
FROM information_schema.tables
WHERE table_schema = 'company2' AND table_type = 'BASE TABLE';

将查询结果中的SQL语句复制出来执行，完成所有表的迁移。

2. 生成索引迁移语句 #

SELECT 'ALTER INDEX company2.' || index_name || ' SET TABLESPACE ts_company2;'
FROM information_schema.indexes
WHERE table_schema = 'company2';

执行生成的SQL，迁移所有索引。

3. 生成序列迁移语句（若模式下存在序列） #

SELECT 'ALTER SEQUENCE company2.' || sequence_name || ' SET TABLESPACE ts_company2;'
FROM information_schema.sequences
WHERE sequence_schema = 'company2';

执行生成的SQL迁移序列。

四、验证迁移结果 #

执行以下SQL确认所有对象已迁移到目标表空间：

-- 检查表的存储位置
SELECT table_name, tablespace_name
FROM information_schema.tables
WHERE table_schema = 'company2';

-- 检查索引的存储位置
SELECT index_name, tablespace_name
FROM information_schema.indexes
WHERE table_schema = 'company2';

-- 检查序列的存储位置（若有）
SELECT sequence_name, tablespace_name
FROM information_schema.sequences
WHERE sequence_schema = 'company2';

确认所有结果中的tablespace_name均为ts_company2即可。

注意事项 #

• 迁移操作会对表加排他锁，务必在业务低峰期执行，避免影响正常业务
• 若模式下存在带外键的表，无需单独处理约束，表迁移后约束会自动关联
• 迁移完成后，PostgreSQL会自动将原数据目录下的对应文件删除，无需手动清理

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