This morning I was involved in upgrading a PostgreSQL database on AWS RDS. We wanted to move from version 9.3.3 to version 9.4.4. We had "tested" the upgrade on a staging database, but the staging database is both much smaller, and doesn't use Multi-AZ. It turned out this test was pretty inadequate.
Our production database uses Multi-AZ. We've done minor version upgrades in the past, and in those cases RDS will upgrade the standby first and then promote it to master. Thus the only downtime incurred is ~60s during the failover.
We assumed the same would happen for the major version upgrade, but oh how wrong we were.
Some details about our setup:
- Provisioned IOPS (SSD)
- 300 GB storage, of which 139 GB is used
- We had RDS OS upgrades outstanding, we wanted to batch with this upgrade to minimise downtime
Here are the RDS events logged while we performed the upgrade:
Database CPU was maxed out between about 08:44 and 10:27. A lot of this time seemed to be occupied by RDS taking a pre-upgrade and post-upgrade snapshot.
The AWS docs don't warn of such repercussions, although from reading them it is clear that an obvious flaw in our approach is that we didn't create a copy of the production database in the Multi-AZ setup and try to upgrade it as a trial run
In general it was very frustrating because RDS gave us very little information about what it was doing and how long it was likely to take. (Again, doing a trial run would have helped...)
Apart from that, we want to learn from this incident so here are our questions:
- Is this kind of thing normal when doing a major version upgrade on RDS?
- If we wanted to do a major version upgrade in the future with minimal downtime, how would we go about it? Is there some kind of clever way to use replication to make it more seamless?