Creating an Aurora Postgresql blue/green deployment with publish_via_partition_root=true

Question

I'm trying to use an Aurora Postgres blue/green deployment to modify a partitioned table without downtime. My goal is to split a large partition into several smaller partitions by running the following transaction on the new ('green') environment:

```
BEGIN;
ALTER TABLE my_table detach table my_table_large_partition;
ALTER TABLE my_table attach partition my_table_small_partition_1 FOR VALUES FROM ('some_start_1') TO ('some_end_1');
...
ALTER TABLE my_table attach partition my_table_small_partition_N FOR VALUES FROM ('some_start_N') TO ('some_end_N');
END;
```

My understanding is that this will require the Postgres publication on the source (blue) database to have `publish_via_partition_root=True`, so that changes made to 'my_table_large_partition' in the blue environment are replicated to the correct 'my_table_small_partition_K' partition in the green environment.

After creating a blue-green deploy through the AWS console, I inspected the publication in the green database with 'select * from pg_publication'. This showed 'pubviaroot=false' for the created publication.

Is it safe to manually set this to 'true' via `ALTER PUBLICATION bluegreen_publication_name SET publish_via_partition_root` before modifying the partitions on the green instance? If not, is it there another way of using blue-green deployments to resize partitions?

Saad · Answer

You're correct that using publish_via_partition_root=true is necessary to replicate partition changes properly during a blue/green deployment with Aurora PostgreSQL.

Manually updating the publication to set publish_via_partition_root=true via ALTER PUBLICATION is the right approach. This is safe to do on the green environment after the blue/green deployment is created.

The key considerations when doing this:

- Make the ALTER PUBLICATION change on the green environment before making any partition changes.

- Verify the publication change is replicated back to the blue environment.

- Double check replication is working between environments after altering the publication.

- Be cautious of making other DDL/DML changes on the partitioned table during the switch over.

- Test the process thoroughly before performing it on a production system.

So in summary, yes you can and should modify the publication to enable partition root replication for your blue/green partitioning scenario. Just be very careful and test it out to validate smooth switchover. Let me know if any other questions come up!

Creating an Aurora Postgresql blue/green deployment with publish_via_partition_root=true

Relevant content