Ops: Prepare for postgres upgrade

[jtgeibel]

I see no urgency for this migration, but we should start planning for an eventual upgrade. We are currently on PG11 and PG13 is available on Heroku for production use.

Heroku's pg:upgrade serves as a guide. We will want to document our own detailed steps and test them out on staging. In particular, we may want to temporarily setup a real leader/follower configuration on staging. (Currently, both connection pools point to the same database on staging.)

We will also need to make sure the new download tracking logic works well in read-only mode (dropping download counts for the duration of the migration).

To minimize the time we operate in read-only mode, we will probably want to land #3479 first.

[Turbo87]

#3886 (comment) might be a good reason to do this in the near-term future

[Turbo87]

we discussed this topic in our weekly team meeting today:

• it would be good to resolve Archive old entries in version_downloads table #3479 before the upgrade, but it is not strictly required. resolving it after the upgrade means that the upgrade might take a little bit longer since more data needs to be migrated.
• it should be possible to create a temporary follower database and upgrade that to get a feel for how long the migration would take, and thus the amount of time we would have to spend in read-only mode.
• @pietroalbini will look into this on the weekend and write down some instructions
• the actual upgrade should happen during a Zoom call with @jtgeibel @pietroalbini and @Turbo87 to immediately address any issues that come up

[pietroalbini]

So, I created a throwaway follower and performed the upgrade on it, and it took between 7 and 8 minutes for the upgrade to finish. This would allow us to have a 10 minute read-only window, which is not bad.

The main worry I have is about the read-only follower used for the more heavy queries: when the database is upgraded the old followers are detached, so they can't be used with the new primary. This would mean we'd need to first upgrade the primary from PostgreSQL 11 to PostgreSQL 13, and then create a new read-only follower.

Unfortunately followers can't be created straight away from the upgraded database, as the capability to create a follower database is marked as "temporarily unavailable". According to the Heroku documentation a backup is required before being able to create the follower, and backups are hard-capped at 10MB/s. With the current database size (8.2 GB) this would mean waiting at least 13 minutes before we can start creating the new follower, leaving a window when all the queries must go to the primary database. 13 minutes is not that bad though.

[pietroalbini]

it would be good to resolve #3479 before the upgrade, but it is not strictly required. resolving it after the upgrade means that the upgrade might take a little bit longer since more data needs to be migrated.

An advantage of resolving #3479 before the upgrade would be actually shrinking our database: the version_downloads table currently takes more than 6 GB, but even if we delete the data we can't do a VACUUM FULL in production, otherwise we'd break the website until it finishes. Running the upgrade with pg:copy instead of pg:upgrade would allow us to compact the table and perform the upgrade at the same time without breaking the website.

[Turbo87]

given that we've updated to Postgres 13 a while ago, I guess we can close this issue.