How to do a major version upgrade for Aurora Postgres in CloudFormation with custom Parameter Groups?

Question

Issue

I am trying to do a major version upgrade from Aurora Postgres 10.14 to 11.9 using Cloudformation. My template creates a DBCluster, a DBInstance, a DBClusterParameterGroup and a DBParameterGroup.

The problem is that when I try to update the stack and change the EngineVersion property for DBCluster from 10.14 to 11.9 and change the Family property for DBClusterParameterGroup and DBParameterGroup from aurora-postgresql10 to aurora-postgresql11, I get this error in CloudFormation:

Error

The following parameters are not defined for the specified group: enable_partitionwise_aggregate, enable_parallel_append, enable_partition_pruning, vacuum_cleanup_index_scale_factor, pg_bigm.last_update, apg_enable_semijoin_push_down, parallel_leader_participation, pg_bigm.enable_recheck, pg_bigm.gin_key_limit, max_parallel_maintenance_workers, pg_bigm.similarity_limit, enable_parallel_hash, enable_partitionwise_join (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue)

I think this is because, according to the AWS Documentation for RDS Parameter Groups:

The DB cluster parameter group family can't be changed when updating a DB cluster parameter group

So even though I am trying to update the Family property for DBClusterParameterGroup and DBParameterGroup, CloudFormation simply ignores that, and so it is trying to apply the aurora-postgresql10 Parameter group Family aurora-postgresql10 to a database now trying to run Aurora Postgres 11.9

What I've tried

Updating the Description property for DBClusterParameterGroupandDBParameterGroupto include a reference to thepEngineVersion` parameter, as the AWS Documentation says that will trigger a Replacement, but it does not actually do this, and so I get the same error
Manually adding the parameters listed in the error to DBParameterGroup before running update. Got error "Unmodifiable DB Parameter: pg_bigm.last_update"

The only workaround I have found is clunky:

Manually update the database version in the console from 10.14 to 11.9, and change the DB cluster parameter group and Parameter group both to default.aurora-postgresql11 as well
Comment out the code for DBClusterParameterGroup and DBParameterGroup and update the stack with the updated EngineVersion 11.9 for DBCluster
Uncomment out the code for DBClusterParameterGroup and DBParameterGroup and update the stack again with the correct Family property aurora-postgresql11 on DBClusterParameterGroup and DBParameterGroup. Now the database is updated, it is using the custom parameter groups, and the stack is not drifting

Code

Parameters:

  pEngineVersion:
    Type: String
    #currently '10.14'
    #trying to change to '11.9'
  pFamily:
    Type: String
    #currently 'aurora-postgresql10'
    #trying to change to 'aurora-postgresql11'

Resources:

  DBClusterParamGroup:
    Type: AWS::RDS::DBClusterParameterGroup
    Properties:
      Description: !Sub 'AuroraDBClusterParamGroup-${AWS::Region}'
      Family: !Ref pFamily
      Parameters:
        application_name: "App name"
        log_statement: all
        log_min_duration_statement: 0

  DBParamGroup:
    Type: AWS::RDS::DBParameterGroup
    Properties:
      Description: !Sub 'AuroraDBParamGroup-${AWS::Region}'
      Family: !Ref pFamily
      Parameters:
        max_connections: 1000

  AuroraDBCluster:
    Type: AWS::RDS::DBCluster
    Properties:
      EngineVersion: !Ref pEngineVersion
      Engine: aurora-postgresql
      DBClusterParameterGroupName: !Ref 'DBClusterParamGroup'
      #snipping unneccesary code#

  AuroraDBInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      Engine: aurora-postgresql
      DBParameterGroupName: !Ref 'DBParamGroup'
      DBClusterIdentifier: !Ref 'AuroraDBCluster'
      #snipping unneccesary code#

Any help would be very appreciated

Mike N · Accepted Answer

Solution

This method worked for me (I've modified some things slightly to post in a public forum) EDIT: I have updated this to use the Blue/Green solution now that I have had the chance to test it.

Parameters:
[...]
  DBEngineVersion:
    Type: String
    Description: "Choices are engine versions from Postgres 9/10/11, only newest and legacy deployed in Prod versions"
    Default: 11.7
    AllowedValues:
      - 9.6.18
      - 10.7
      - 10.14
      - 11.7
  MajorVersionUpgrade:
    Type: String
    Description: Swap this between 'Blue' or 'Green' if we are doing a Major version upgrade
    Default: Blue
    AllowedValues:
      - Blue
      - Green

Conditions:
  BlueDeployment: !Equals [!Ref MajorVersionUpgrade, "Blue"]
  GreenDeployment: !Equals [!Ref MajorVersionUpgrade, "Green"]
  
Mappings:
  EngineToPGFamily:
    "9.6.18":
      PGFamily: aurora-postgresql9.6
    "10.7":
      PGFamily: aurora-postgresql10
    "10.14":
      PGFamily: aurora-postgresql10
    "11.7":
      PGFamily: aurora-postgresql11

  ##### Keep parameters of both versions in sync ####
  DBClusterParameterGroupBlue: #Keep parameters of both versions in sync
    Type: "AWS::RDS::DBClusterParameterGroup"
    Condition: BlueDeployment
    Properties:
      Description: !Sub "Postgres Cluster Parameter Group for ${InfrastructureStackName}"
      Family: !FindInMap ['EngineToPGFamily', !Ref 'DBEngineVersion', 'PGFamily']
      Parameters:
        client_encoding: UTF8
        idle_in_transaction_session_timeout: 60000
  DBClusterParameterGroupGreen:
    Type: "AWS::RDS::DBClusterParameterGroup"
    Condition: GreenDeployment
    Properties:
      Description: !Sub "Postgres Cluster Parameter Group for ${InfrastructureStackName}"
      Family: !FindInMap ['EngineToPGFamily', !Ref 'DBEngineVersion', 'PGFamily']
      Parameters:
        client_encoding: UTF8
        idle_in_transaction_session_timeout: 60000

  DBCluster:
    Type: "AWS::RDS::DBCluster"
    Properties:
      DBClusterIdentifier: !Sub "${AWS::StackName}-cluster"
      DBClusterParameterGroupName: !If [GreenDeployment, !Ref DBClusterParameterGroupGreen, !Ref DBClusterParameterGroupBlue]
      Engine: aurora-postgresql
[...]
  DBInstance1:
    Type: "AWS::RDS::DBInstance"
    Properties:
      DBClusterIdentifier: !Ref DBCluster
      EngineVersion: !Ref DBEngineVersion
      Engine: aurora-postgresql
[...]

  DBInstance2:
    Type: "AWS::RDS::DBInstance"
    Properties:
      DBClusterIdentifier: !Ref DBCluster
      EngineVersion: !Ref DBEngineVersion
      Engine: aurora-postgresql
[...]

Usage and Methodology

This method will create a new ClusterParameterGroup that will be used during the upgrade (when doing a Major version upgrade), and clean up the original group when the upgrade is done. The advantage to this solution is that there is no version hardcoding in the group and the same template can be used for continuous lifecycle updates without additional changes needed (aside from desired Parameters updates).

Initial Setup

Initially, leave MajorVersionUpgrade set to Blue and the conditions will ensure that only the Blue ParameterGroup is created using the proper DB family.

Performing a Major Version Upgrade

Update the stack and set the DBEngineVersion to the new major version and MajorVersionUpgrade to Green if it is currently Blue (or Blue if it is currently Green). CloudFormation will:

Start by creating the new resources (which is the new parameter group referencing the new version)
Then it will update the Cluster and point to that new group
Finally, it will delete the old ParameterGroup (referncing the old version) during the cleanup phase since the Blue/Green Condition for that group no longer evaluates to true.

Important notes

The Parameters in both parameter groups must manually kept in sync
Obviously, update the DBEngineVersion AllowedValues list with versions as needed, just be sure to update the EngineToPGFamily Mappings to have a valid Postgres family value for that Engine version
Special care must be paid when implementing Parameters that are new to a DB Major version and don't exist in an older version, I have not tested this