Upgrading postgres from 10 to 12 using terraform

Introduction

I was switching the instance type of a postgres instance on AWS RDS to the new graviton processors for better performance for the cost. This post is to walk through how it went. Spoiler alert: I did not break production :)

Background

The postgres instance was on version 10 but graviton processors are only supported for version 12 so I had to update the postgres version first before switching the instance type.

Walkthrough

The very first thing I did was create a copy of the database using the latest backups that AWS had created. This was my test instance that I performed all the steps on first to see how everything would go before touching the real database. Also, I created a backup of the production database before doing performing any of the steps to have a rollback plan in case something didn’t work.

All the steps on the test database were doing via the console and the same steps were done via terraform for the production database.

To upgrade via console was simple. Use the modify instance to change the engine version from 10 to 12 and then wait. I did have to create a new parameter group for version 12 as I could not use the original parameter group of version 12 but other than that it was just selecting the new version from the drop down menu and getting coffee while it upgraded.

Once the upgrade finished - I verified that the DB was still intact by doing some selects via psql.

Then it was changing the instance type - that was also simply clicking the modify instance button and then selecting the new instance type. Took some time but worked flawlessly.

Once the instance type was changed - used psql again to make sure everything was still good.

With the newly gained confidence from upgrading the test instance it was time to do it to the production instance.

The production instance was provisioned via terraform so I did the update and instance switch directly from TF. Did it in two separate steps.

There is an apply immediately argument that can be set to either true or false. If set to false the updates will be applied during the next maintenance window else they are applied right now.

1
2
3
4
resource "aws_db_instance" "pg-db" {
  engine            = "postgres"
  engine_version    = "12" <--- changed this to 12 from 10
}

Run terraform apply and get lunch.

Once that is done - verify via psql again.

1
2
3
4
5
resource "aws_db_instance" "pg-db" {
  engine            = "postgres"
  engine_version    = "12"
  instance_class    = "db.m6g.xyz" <-- change this to the desired instance type
}

Run terraform apply and wait.

Once that is done - verify via psql again.

Once the database was done, something that did pop up was query performance had degraded. PG 12 should have better performance than PG 10 so what gives?

After a bit of debugging using explains and analyze - noticed that the query planner was doing sequential scans even though there were indices present.

The workaround was to run an analyze on every table and commit the result. After doing that the queries were working as expected again.

Conclusion

Most posts around upgrades tend to revolve around negative impact and outages but I wanted to share this that not all updates break things :)