Some notes on upgrading PostgreSQL in Docker

Here are some quick notes from helping Jim upgrade his bava.tv Peertube instance from PostgreSQL 10 to PostgreSQL 13. bava.tv uses Docker so actually changing Postgres versions is dead simple (basically just edit a number in the docker-compose.yml file and restart the containers) but we found out pretty quickly that you can’t just change the version of Postgres and expect the database to still work.

First I cloned the environment so I could do some testing and added Adminer to the docker-compose.yml file. This way I could poke around the database and try exporting and importing stuff, but I had all kinds of errors related to overwriting tables that already existed. It seems like Postgres is very protective about what things you can overwrite or tables you can drop because it has a concept of what tables are related to each other.¹

After reading the docs a bit it seemed like the most straightforward way is to dump the database on the old version using pg_dumpall, upgrade the version of Postgres, then restore the database. pg_dumpall will dump not only the public schema that has all the data we care about, but also the other schemas that mostly seem to contain metadata.

After finding out about pg_dumpall I also stumbled upon this blog post about upgrading Postgres in Docker, which was pretty useful:

How to Upgrade Your PostgreSQL Version Using Docker

I did tweak things slightly when it came to restoring the database, as their method was a bit more complicated than mine.

Here was my actual procedure for doing this with bava.tv, after I figured out what I was doing:

cd to the folder PeerTube is running from

In this particular case /home/peertube.²

cd /home/peertube

Dump the database using pg_dumpall

docker-compose exec postgres pg_dumpall -U DATABASE_USERNAME > dump.sql

docker-compose exec postgres tells Docker to execute the command pg_dumpall -U bavatube inside our postgres container, and then > dump.sql redirects that output to a file so we can restore it later. You can find the username, and other databse info in the .env file for PeerTube.

Stop all containers and delete the database

docker-compose down
rm -rf docker-volume/db/

Edit the docker-compose.yml file to upgrade PostgreSQL

vim docker-compose.yml

We just need to change the tag for the postgres container, so we replace 10 with 13 in the image: postgres:10-alpine line.

Start up the Postgres container (and nothing else)

docker-compose up -d postgres

We need to start up just the database but not rest of the Peertube containers, so we have a completely empty database to work with. If we allow the PeerTube container to also start up, it will see an empty database and automatically populate it with some data before we get a chance to restore anything.

Restore the database dump

docker-compose exec -T postgres psql -U DATABSE_USERNAME -d DATABASE_NAME < dump.sql

We’re done! Start up all the other containers for PeerTube!

docker-compose up -d

That was the problem as best as I can tell. I’m very very far from a DB admin 🤷‍♂️ ↩︎

For most installs made with our installer it will be at /root. bava.tv predates our PeerTube installer. ↩︎