Upgrade postgresql from 11.4-alpine to 17.4-alpine

[jessesimpson36]

Greetings,

According to
https://www.postgresql.org/support/versioning/

v11.4 support was dropped on November 9, 2023. I propose that we upgrade it to v17.4 which drops support in 2029. The benefit in upgrading is potentially reducing CVE's in postgresql, and possibly even fixing bugs that might exist in earlier versions. As part of this issue, I will share some steps on how to do the upgrade so that it minimizes the actual time needed for an admin to perform these actions.

Why do I think upgrading is a safe operation

According to Buildbot's Database Overview,

All the connectors use SQLAlchemy Core to achieve (almost) database-independent operation. Note that the SQLAlchemy ORM is not used in Buildbot. Database queries are carried out in threads, and report their results back to the main thread via Twisted Deferreds.

From this, I see that making use of SQLAlchemy is database-independent, which means it's not going to use vendor-specific logic such as tsvectors or postgresql plugins. And not making use of the SQLAlchemy ORM means that raw queries are executed on the server, which reduces the possibility of postgresql accepting one query from buildbot in v11 but rejecting a query in v17.

How to perform the upgrade

1. Bring down all services except postgresql

docker compose down buildbot worker fileserver cron

2. Take a backup of the existing database with pg_dump.

docker exec -it gkci-main-db-1 pg_dump -U buildbot > dump.psql

3. Read from the dump.psql file to ensure that it looks like a full database dump. If it is a small file size or doesn't start with

--
-- PostgreSQL database dump
--

then something may have went wrong.

3. Bring down postgresql

docker compose down db

5. Delete the postgresql volume

docker volume rm gkci-main_postgres_data

6. Change the version in docker-compose.yml in db.image to 17.4-alpine

7. Bring up just the db service

docker compose up db -d 

8. Restore from the database dump

cat dump.psql | docker exec -i gkci-main-db-1 psql -U buildbot -f -

9. Bring up the rest of the services

docker compose up -d

How to rollback if the upgrade is not smooth

1. Bring down each of the services

docker compose down

2. Change the version in docker-compose.yml in db.image to 11.4-alpine
3. Remove the database volume

docker volume rm gkci-main_postgres_data

5. Bring up just the database

docker compose up db -d 

6. Restore from the database dump

cat dump.psql | docker exec -i gkci-main-db-1 psql -U buildbot -f -

7. Bring the rest of the components back up.

docker compose up -d 

Recordings of me testing this locally

Asciinema recording of the upgrade procedure:
https://asciinema.org/a/Swd3CpN3N20EbJ0pUkaldbQkc

Asciinema recording of the rollback procedure:
https://asciinema.org/a/vvpd9D54NIMIKcnN6N8uXymgN

Hope this helps!