I have two PostgreSQL clusters deployed on AWS in two regions, ap-southeast-2 and eu-west-2. Each cluster has one partition table temperatures which has two partitions.

In ap-southest-2, there is a master table temperatures which has two partitions temperatures_2017 and temperatures_2018.

In eu-west-2, there is a master table temperatures which has two partitions temperatures_2015 and temperatures_2016.

Then I created FDW between this two clusters. So each master table includes 4 partitions where 2 of them are from local and the other 2 are from remote server. So the structure becomes:

In ap-southest-2, there is a master table temperatures which has two local partitions temperatures_2017 and temperatures_2018 and two remote partitions temperatures_2015 and temperatures_2016 which are from eu-west-2.

In eu-west-2, there is a master table temperatures which has two local partitions temperatures_2015 and temperatures_2016 and two remote partitions temperatures_2017 and temperatures_2018 which are from ap-southeast-2.

And now I want to update one of the column’s type. As table schema change can’t be done on partition level, I have to run it against the master table. I tried to run it from either of the two clusters but failed. For example, I run below command on ap-southeast-2,

alter table temperatures alter column name type int using name::int;

which gives me an error: [42809] ERROR: "temperatures_2016" is not a table.

Since I run the command on ap-southest-2 region, the partition temperatures_2016 is a remove partition which can’t be changed. If I do the same change on the other cluster I will get the same error for the same reason.

My question is how I can update table schema in this case.