mastodon postgres upgrade fun

on benharri's blog

#sysadmin #tilde #social

howdy friends!

if you’re a mastodon user on (the tildeverse mastodon instance), you might’ve noticed some downtime recently.

here’s a quick recap of what went down during the upgrade process.

we run the current stable version of postgresql from the postgres apt repos. postgres 13 was released recently and the apt upgrades automatically created a new cluster running 13.

the database for mastodon has gotten quite large (about 16gb) which complicates this upgrade a bit. this was my inital plan:

these steps appeared to work fine, but closer inspection afterwards led me to discover that the new cluster had ended up with SQL_ASCII encoding somehow. this is not a situation we want to be in. time to fix it.

here’s the new plan:

i’m still not 100% sure how the encoding reverted to ascii but it seems that the locale was not correctly set while running the apt upgrades…

if this happens to you, hopefully this helps you wade out while keeping all your data :)