Would be awesome if PostgreSQL would finally add support for seamless major version upgrade…
- [1] https://github.com/LemmyNet/lemmy-ansible/blob/main/UPGRADIN...
https://github.com/docker-library/postgres/issues/37#issueco...
Btw, what do you find convoluted about our project (pgautoupgrade)?
It started out as super simple like you mention above... but it also started out using Alpine images so didn't work so well for people coming from Debian based ones. We've added support for Debian based ones now too, but that's introduced more complexity to the README (as has other options). o_O
Unless something has radically changed with this last release, then the PostgreSQL database needs to be offline while pg_upgrade is running.
What's more, even outside of docker running `pg_upgrade` requires both version to be present (or having older binary handy). Honestly, having the previous version logic to load and process the database seems like it would be little hassle but would improve upgrading significantly...
WTF?
It was only implied to be able to do migration between major versions, not all of them (which doesn't make any sense).
I don't expect PostgreSQL changing that significantly between major version the upgrade would be such a huge hassle…
TBH PostgreSQL seem to be the only tool/software that has such a bonkers upgrade path between major versions…
> I don't expect PostgreSQL changing that significantly between major version the upgrade would be such a huge hassle…
PostgreSQL would need code which transforms between the old AST to the new AST for example to support views and check constraints so PostgreSQL would need to keep around a version of the AST for every major version of PostgreSQL plus the code to deparse it. It would also need to keep around the code to read every version of the catalog tables (it partially does this in pg_dump, but only partially since parts of pg_dump is implemented using server side functions).
There are also likely a bunch more complications caused by the pluggable nature of PostgreSQL: e.g. custom plan nodes which might make the whole project a non-starter.
So this would at least mean basically a total rewrite of pg_dump plus maintaining 5 to 10 versions of the AST. But likely much more work since I have likely forgot a bunch of stuff which needs to change. A huge project which increases the maintenance burden for a relatively small gain.
> TBH PostgreSQL seem to be the only tool/software that has such a bonkers upgrade path between major versions…
This is caused mostly by PostgreSQL being heavily extensible unlike its competitors.
No, it would be for a huge gain for the people who run PostgreSQL. Every long running PostgreSQL installation use has to go through the process and/or potential downtime figuring out a (potentially forced) upgrade every few years.
Instead of that, PG being able to transparently (or at least automatically) upgrade major versions as needed would remove that absolutely huge pain point.
Other databases have recognised this as being a major problem, then put the time and effort into to solving it. The PostgreSQL project should too. :)
It's not perfect, as it doesn't (yet) recreate indexes or automatically run `vacuum analyze` afterwards. We're working on those though. :)
You do make backups of your database though yeah?
Works well for a few years as PG is super stable. But we tend to see people when they've run their systems longer than that and it's gotten to the point of their PG install is end of life (or similar circumstance), so they're looking at their upgrade options.
The pgautoupgrade approach is one potential way to fix that, and (in theory) should mean that once they're using it they don't have to worry about major version upgrades ever again (in a positive way).