4 ms·

Would be awesome if PostgreSQL would finally add support for seamless major version upgrade…

by ktosobcy 2y ago

ellisv 2y ago
I'm curious what you feel is specifically missing.
- levkk 2y ago
  pg_upgrade is a bit manual at the moment. If the database could just be pointed to a data directory and update it automatically on startup, that would be great.
  - olavgg 2y ago
    I agree, why is this still needed? It can run pg_upgrade in the background.
    - vbezhenar 2y ago
      It needs binaries for both old and new versions for some reason.
    - justinclift 2y ago
      When you say "in the background" what are you meaning? Unless something has radically changed with this last release, then the PostgreSQL database needs to be offline while pg_upgrade is running.
- ktosobcy 2y ago
  Being able to simply switch from "postgres:15" to "postgres:16" in docker for example (I'm aware about pg_autoupdate but it's external and I'm a bit iffy about using it) 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...
  - jeltz 2y ago
    It would be a major hassle to do so since it would mean keeping around major parts of the code for all old versions of PostgreSQL. Possible to do? Yes. A hassle for the dev team? Yes, a huge one. Worth it? Doubtful.
    - ktosobcy 2y ago
      > It would be a major hassle to do so since it would mean keeping around major parts of the code for *all old versions of PostgreSQL.* 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…
      - jeltz 2y ago
        Patches welcome since you obviously know more than me about the code base. And I obviously meant only major versions when I said all versions. PostgreSQL releases one new major version per year so that adds up quickly. > 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.
dewey 2y ago
Maybe https://github.com/pgautoupgrade/docker-pgautoupgrade https://github.com/pgautoupgrade/docker-pgautoupgrade is interesting for you.
- ticoombs 2y ago
  I use this for my Lemmy instance & lemmy-ansible and it's been great! No longer having to support upgrade scripts and write a complete upgrade process[1] for people to follow has made my life a lot easier! Amazing product - [1] https://github.com/LemmyNet/lemmy-ansible/blob/main/UPGRADING.md#postgres-upgrade-from-v15-to-v16 https://github.com/LemmyNet/lemmy-ansible/blob/main/UPGRADIN...
- ktosobcy 2y ago
  I heard about that project but it still somewhat convoluted. Imagine being able to simply use "postgres:latest" or better yet "postgres:15" and switch to "postgres:16" and it would just update (like any other minor version does, or any other db, like mysql, does)
  - justinclift 2y ago
    Yeah, we've offered to make that happen but there's been zero response: https://github.com/docker-library/postgres/issues/37#issuecomment-2228396318 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

Would be awesome if PostgreSQL would finally add support for seamless major version upgrade…

ellisv 2y ago

I'm curious what you feel is specifically missing.

levkk 2y ago
pg_upgrade is a bit manual at the moment. If the database could just be pointed to a data directory and update it automatically on startup, that would be great.
- olavgg 2y ago
  I agree, why is this still needed? It can run pg_upgrade in the background.
  - vbezhenar 2y ago
    It needs binaries for both old and new versions for some reason.
  - justinclift 2y ago
    When you say "in the background" what are you meaning? Unless something has radically changed with this last release, then the PostgreSQL database needs to be offline while pg_upgrade is running.
ktosobcy 2y ago
Being able to simply switch from "postgres:15" to "postgres:16" in docker for example (I'm aware about pg_autoupdate but it's external and I'm a bit iffy about using it) 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...
- jeltz 2y ago
  It would be a major hassle to do so since it would mean keeping around major parts of the code for all old versions of PostgreSQL. Possible to do? Yes. A hassle for the dev team? Yes, a huge one. Worth it? Doubtful.
  - ktosobcy 2y ago
    > It would be a major hassle to do so since it would mean keeping around major parts of the code for *all old versions of PostgreSQL.* 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…
    - jeltz 2y ago
      Patches welcome since you obviously know more than me about the code base. And I obviously meant only major versions when I said all versions. PostgreSQL releases one new major version per year so that adds up quickly. > 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.

dewey 2y ago

Maybe https://github.com/pgautoupgrade/docker-pgautoupgrade https://github.com/pgautoupgrade/docker-pgautoupgrade is interesting for you.

ticoombs 2y ago
I use this for my Lemmy instance & lemmy-ansible and it's been great! No longer having to support upgrade scripts and write a complete upgrade process[1] for people to follow has made my life a lot easier! Amazing product - [1] https://github.com/LemmyNet/lemmy-ansible/blob/main/UPGRADING.md#postgres-upgrade-from-v15-to-v16 https://github.com/LemmyNet/lemmy-ansible/blob/main/UPGRADIN...
ktosobcy 2y ago
I heard about that project but it still somewhat convoluted. Imagine being able to simply use "postgres:latest" or better yet "postgres:15" and switch to "postgres:16" and it would just update (like any other minor version does, or any other db, like mysql, does)
- justinclift 2y ago
  Yeah, we've offered to make that happen but there's been zero response: https://github.com/docker-library/postgres/issues/37#issuecomment-2228396318 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