如何更新含远程分区的跨PostgreSQL集群分区表列类型

因为主表包含远程FDW分区时，直接执行ALTER TABLE会尝试修改远程分区（而FDW分区不支持结构修改），所以需要分四步操作：

操作步骤 #

1. 从主表中移除远程分区 #

在两个集群分别执行命令，将不属于本地的远程分区从主表中分离：

• ap-southeast-2集群：

  ALTER TABLE temperatures DETACH PARTITION temperatures_2015;
  ALTER TABLE temperatures DETACH PARTITION temperatures_2016;
  

• eu-west-2集群：

  ALTER TABLE temperatures DETACH PARTITION temperatures_2017;
  ALTER TABLE temperatures DETACH PARTITION temperatures_2018;
  

2. 修改本地主表及本地分区的列类型 #

此时每个集群的主表仅包含本地分区，执行ALTER命令会自动同步所有本地分区的结构：

• ap-southeast-2集群：

  ALTER TABLE temperatures ALTER COLUMN name TYPE int USING name::int;
  

• eu-west-2集群：

  ALTER TABLE temperatures ALTER COLUMN name TYPE int USING name::int;
  

3. 同步本地FDW外部表的列类型 #

远程集群的表结构已修改，需更新本地用来映射远程分区的FDW外部表结构，保证结构一致：

• ap-southeast-2集群：

  ALTER FOREIGN TABLE temperatures_2015 ALTER COLUMN name TYPE int;
  ALTER FOREIGN TABLE temperatures_2016 ALTER COLUMN name TYPE int;
  

• eu-west-2集群：

  ALTER FOREIGN TABLE temperatures_2017 ALTER COLUMN name TYPE int;
  ALTER FOREIGN TABLE temperatures_2018 ALTER COLUMN name TYPE int;
  

4. 将远程分区重新挂载回主表 #

按照原分区的范围规则，将远程分区重新附加到主表（请根据实际分区键调整范围值）：

• ap-southeast-2集群：

  ALTER TABLE temperatures ATTACH PARTITION temperatures_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01');
  ALTER TABLE temperatures ATTACH PARTITION temperatures_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01');
  

• eu-west-2集群：

  ALTER TABLE temperatures ATTACH PARTITION temperatures_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');
  ALTER TABLE temperatures ATTACH PARTITION temperatures_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
  

注意事项 #

• 操作前务必备份所有数据，避免数据丢失。
• 建议在业务低峰的维护窗口执行操作，避免影响正常读写。
• 确保执行操作的账号拥有足够权限（ALTER TABLE、ALTER FOREIGN TABLE等权限）。
• 每一步操作完成后，可通过\d temperatures验证列类型，\d+ temperatures验证分区列表是否正确。

内容的提问来源于stack exchange，提问作者Joey Yi Zhao