Upgrade to a newer postgres version

[neobooru]

In issue #213 we pinned postgres to version 11, which stops being supported after November 9, 2023 if understand the table correctly.

The easiest way I found to upgrade from 11 to 16 uses the docker-pgautoupgrade container.

1. In docker-compose.yml change image: postgres:11-alpine to image: pgautoupgrade/pgautoupgrade:16-dev
2. Run docker compose up sql and wait
3. In docker-compose.yml change image: pgautoupgrade/pgautoupgrade:16-dev to image: postgres:16-alpine
4. Run docker compose up to start the full szurubooru stack

We could ask our users to manually make the changes mentioned above, or we could just commit the change in docker-compose.yml to master. I personally don't like committing such a change to master because it makes us depend on that third-party image forever, instead of depending on the official postgres images.

Alternative ways to upgrade could use the pg_dumpall command or the pg_upgrade tool, for which we could make a script or something.

Ideas?

[phil-flip]

I think, writing a more detailed guide of what you provided might be the better idea. The only issues I can really observe is that the current docker-compose file is using latest tags. When people upgrade the latest tag, without reading the breaking change comment their DB won't work. There is also the issue that there is no proper versioning, to announce this breaking change on a GitHub-Release. So because of that, it might be a good idea to add a big message in the logs, if the DB version is outdated, reminding them to follow the instructions to upgrade first. Maybe even have a script ready they can run inside the containers. But not automatically, as I think that might break things with people having a custom setup.

[noirscape]

pg_dumpall is the most reliable choice I think.

That also effectively recreates the database, so that cleans out indexes.

[neobooru]

And how should we release a theoretical 3.0 version which uses a newer postgres version? Do we just keep the latest tag on version 2.x and ask everyone to change their dockerfiles to target version 3? We could even push a message to the latest v2 version with something like "This version is not the latest version, visit github.com/whatever to upgrade to the latest version"?

[404Fox]

Is the intent to require an upgrade to 16? I've been using 12 for years without issues, completely forgot about it even.

[phil-flip]

Is the intent to require an upgrade to 16? I've been using 12 for years without issues, completely forgot about it even.

Every version is going to go EOL at some point. So it only makes sense to upgrade to the newest version there is, as a possible automatic upgrade in the future is as unlikely to be done as now.

[Eskuero]

I did setup my instance a month ago and directly used version 16 for the setup to avoid future migrations. So far it's been rock solid.