Hi @m.w, we went through this process a month ago. It can be a difficult process. We had a lot of trial and error. The key to this upgrade is that you have upgrade the Postgres database before you do anything. Before you do anything, take a snapshot of the database and the Ec2 instance. Next, stop your Matillion Ec2 Instance.
First go into the Postgres RDS instance in AWS and do any updates if it shows updates are required. These are OS updates for your RDS instance not Postgres version updates. The next steps are key to a successful upgrade using a Cloudformation (CF) template. When you upgrade your Postgres RDS you have to ensure that the version you are upgrading to supports the instance size you are currently running on. For example, Postgres 9.6.x may have run on a t2.micro but the smallest instance Postgres 13.3 will run on is a t3.small. If you want to be sure that the version you are on and the version you are going to support a particular instance size, check out the table on this page: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html
For the next step, change your Postgres RDS instance size in your CF and apply just that change. Once this is done you will be able to upgrade to Postgres 13.3 because it will be compatible with the instance size. So, now change the CF but only change the Postgres version from 9.6.x to 13.x (I went to 13.3) and then apply that change. Both the instance size and version change take a very long time.
Once completed, you can then upgrade the AMI ID in the CF for the Matillion image. I personally choose the highest AMI available because it's usually a little behind the current release anyway. Either way you need to choose an AMI that has Matillion 1.56 or higher for it to be supported with the new version of Postgres. Once you change the CF, go ahead an apply it.
When you boil it down, this multi-step process is all due to AWS not knowing how to apply the order of operation based on dependencies. If you look real close at the CloudWatch logs you will see that it's trying to do multiple changes at the same time which will fail because there are dependencies on instance sizes and Postgres versions. You can't simply do both of those at the same time.