postgres_fdw使用CREATE TABLE LIKE无法同步索引的解决方法

问题根源在于外部表（postgres_fdw导入的表）不会存储远程表的索引元数据，所以CREATE TABLE ... LIKE ... INCLUDING INDEXES基于外部表创建本地表时，无法复制索引。下面是两种可行的调整方案：

方案一：从源库直接导出索引DDL到目标库执行 #

在测试环境（RDS_1）生成表结构和索引的DDL，然后在生产环境（RDS_2）的辅助表上执行，再导入数据：

1. 在RDS_1执行，导出表结构和索引的DDL：

-- 生成表结构DDL
SELECT pg_get_tabledef('test_schema.test_table');

-- 生成所有索引的DDL
SELECT pg_get_indexdef(indexrelid)
FROM pg_index
WHERE indrelid = 'test_schema.test_table'::regclass;

2. 在RDS_2执行：
   先创建空的辅助表（不带索引），然后执行上面导出的索引DDL，最后导入数据：

-- 创建空辅助表（仅复制结构，不包含索引）
CREATE TABLE test_schema.test_table_aux (LIKE test_schema_remote.test_table);

-- 执行从RDS_1导出的索引DDL，替换表名为test_table_aux
CREATE INDEX idx_test_table_name ON test_schema.test_table_aux(name);
CREATE INDEX idx_test_table_value ON test_schema.test_table_aux(value);

-- 导入数据
INSERT INTO test_schema.test_table_aux SELECT * FROM test_schema_remote.test_table;

方案二：通过FDW查询源库系统表，动态生成索引DDL #

在RDS_2直接查询RDS_1的系统表，自动生成索引创建语句，无需手动导出：

1. 在RDS_2创建指向源库系统表的外部表：

-- 导入源库的核心系统表到临时外部 schema
CREATE SCHEMA IF NOT EXISTS remote_sys;
IMPORT FOREIGN SCHEMA pg_catalog LIMIT TO (pg_index, pg_class, pg_namespace, pg_attribute)
FROM SERVER staging_server INTO remote_sys;

2. 生成并执行索引DDL：

-- 生成索引创建语句，自动替换表名为本地辅助表
SELECT format(
    'CREATE INDEX %I ON test_schema.test_table_aux(%s);',
    c.relname,
    array_to_string(array_agg(a.attname), ', ')
) AS index_ddl
FROM remote_sys.pg_index i
JOIN remote_sys.pg_class c ON c.oid = i.indexrelid
JOIN remote_sys.pg_class t ON t.oid = i.indrelid
JOIN remote_sys.pg_namespace ns ON ns.oid = t.relnamespace
JOIN remote_sys.pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(i.indkey)
WHERE ns.nspname = 'test_schema'
  AND t.relname = 'test_table'
  AND c.relname NOT LIKE 'pg_%' -- 排除系统自带索引
GROUP BY c.relname;

-- 将查询结果中的DDL语句复制执行即可

优化后的蓝绿部署完整流程 #

1. 在RDS_1构建table_1（包含结构、索引、数据）
2. 在RDS_2通过FDW导入RDS_1的table_1为外部表
3. 在RDS_2创建空的table_1_aux（仅复制源表基础结构）
4. 用上述任意方案同步RDS_1的索引到table_1_aux
5. 通过FDW将RDS_1的table_1数据复制到table_1_aux
6. 在RDS_2删除原table_1，将table_1_aux重命名为table_1

内容的提问来源于stack exchange，提问作者pau.ferrer