Question : I am trying to upgrade my RDS Postgresql/Aurora Postgresql to major version(example: 14.10->15.13 or 15.13->16.13) using Blue-Green deployment, but I keep getting incompatible parameter error like (Creation of blue/green deployment failed due to incompatible parameter(s): max_replication_slots, and max_logical_replication_workers), is there a way to find the cause and fix it?

Explanation:

We have the below Aurora postgresql cluster and would like to upgrade it to 16.13 from 15.13 using Blue Green deployment method.

Source cluster name	Source Cluster version	Target Cluster version
apg15-source	15.13	16.13

Blue green deployment in RDS Postgresql/Aurora Postgresql uses Logical Replication to create the green db and the respective configurations.

We tried to upgrade to 16.13 using Bluegreen deployment method, but it ended up in issue.

Below we can see that Blue green deployment with name bg-deployment-2 is in Invalid configuration state. We can also see respective Blue (aka source) Aurora postgresql cluster. And green cluster is in Incompatible-create state.

When you click on Logs & events section on BlueGreen deployment(in our case it is bg-deployment-2), you can see events related to actual error. It is as below. It shows mesage as

Full event message is as below.

Creation of blue/green deployment failed due to incompatible parameter(s): max_replication_slots, and max_logical_replication_workers. See https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/blue-green-deployments-creating.html#blue-green-deployments-creating-preparing-postgres to help resolve the issues, then delete and recreate the blue/green deployment.

We can see that two parameters max_replication_slots and max_logical_replication_workers are having issue here.

Let's see what values are allocated to these parameters now.

postgres=> SELECT name, setting, unit, context FROM pg_settings where name IN ('max_replication_slots', 'max_logical_replication_workers');
              name               | setting | unit |  context   
---------------------------------+---------+------+------------
 max_logical_replication_workers | 4       |      | postmaster
 max_replication_slots           | 20      |      | postmaster
(2 rows)

Why the above values are not enough?

A Blue/Green Deployment keeps the green (staging) environment in sync with blue using logical replication. On PostgreSQL, logical replication consumes one replication slot per database being replicated — so if your cluster has N databases, the sync needs N replication slots. If max_replication_slots is lower than the number of databases, some databases can't get a slot, and the Blue/Green setup fails or replication for those databases won't start.

So if you have 20 databases on the instance, the Blue environment needs 20 slots just for this — hence the guidance that max_replication_slots ≥ number of databases.

Let's check how many databases are there in cluster.

select count(*) from pg_database WHERE datistemplate = false;
 count 
-------
    52
(1 row)

Note:

There won't be any replication slots are required/created for template0 and template1 databases.

There are 52 databases in the cluster. So max_replication_slots value should be equal to or higher than that. Prefer to keep it higher than the number of databases.

max_logical_replication_workers - Increase max_logical_replication_workers as well as it will limit actual sync/apply workers.

So just like slots, the worker requirement scales with the number of databases being synced. If there aren't enough workers, some databases' changes won't get applied on the green side. This is why this strict requirement for Blue-Green deployment.

max_worker_processes After Increasing max_logical_replication_workers , make sure to increase max_worker_processes because max_logical_replication_workers value is derived from max_worker_processes.

Make the changes to Source cluster parameter group with above changes and restart the Writer instance because these are static parameters.

After making aforementioned changes, the new parameters are as below.

postgres=> SELECT name, setting, unit, context FROM pg_settings where name IN ('max_replication_slots', 'max_logical_replication_workers','max_worker_processes');
              name               | setting | unit |  context   
---------------------------------+---------+------+------------
 max_logical_replication_workers | 66      |      | postmaster
 max_replication_slots           | 66      |      | postmaster
 max_worker_processes            | 200     |      | postmaster
(3 rows)

So now, let's delete and recreate new blue-green deployment.

Deleting prior failed blue green deployment.

Delete blue green deployment: Highlight the BlueGreen Deployment and select Delete to delete it.

Create new blue green deployment

Now we can see blue green deployment creating successfully without issues and it passed incompatible parameter step.

Summary:

Below is the minimum Blue(Source) Db/Cluster parameter requirement for RDS Postgresql/Aurora Postgresql BlueGreen deployment to succeed the major version upgrade.

1. Make sure max_replication_slots value ≥ number of databases.
2. max_logical_replication_workers > number of databases.
3. max_worker_processes >> max_logical_replication_workers.
4. rds.logical_replication=1