4 ms·

I upgraded my development database from 12 to 15-beta this week. It was super painful, even with pg_upgrade, I cant understand why newer Postgres release can in

by olavgg 4y ago

I upgraded my development database from 12 to 15-beta this week. It was super painful, even with pg_upgrade, I cant understand why newer Postgres release can include compatibility for older storage formats. This is the number 1 weakness in Postgresql. I prefer to not upgrade major version for years because its so painful.

mattashii 4y ago
That seems strange, the only incompatibilities between major versions are generally only the catalogs. I don't remember any version whose storage format is cannot be read by the next versions (none since 2010). Could you add some more info what issues you had?
- 411111111111111 4y ago
  In not the person you've responded to but I've never been able to get `pg_dump -Fc` and restore to work across versions. It always errors out for me, saying it's an invalid/broken backup. Super annoying because now the workflow is 1. Start old version just to restore, remove old version and start new version for upgrade.
  - castorp 4y ago
    You need to use pg_dump from the new version, not from the old version.
    - jasfi 4y ago
      I think you found a common stumbling block. The tutorial people follow may say to run pg_dump and many people would assume you're running it from the old version.
    - rapind 4y ago
      Wait, what? Explain please. OK upon further thought, what you're saying is to use the pg_dump tool that comes with the pg version you are upgrading to. I read it as "use pg_dump instead of pg_restore"...
      - mh- 4y ago
        Yes. Put concisely: Use the client tools from the newest version involved for all steps.
        oblio 4y ago
        Isn't that impractical for most Linux distros? You'd need to manually install the new client.
        mh- 4y ago
        Probably? Certainly if you rely on distro packaging for it, yes. I was just trying to simplify the guidance here. :)
        BGZq7 4y ago
        On Debian (and probably most of its derivatives), the package manager will not remove any running PostgreSQL versions and will let you have multiple versions side by side. It also has its own tool pg_upgradecluster, which can perform the upgrade using pg_dump+pg_restore or pg_upgrade (optionally with link or clone options).
    - autra 4y ago
      Yes, and usually, it means you need to use the full binary path unfortunately (because debian wrappers embedded in the package select the version from your 5432 cluster).
  - forinti 4y ago
    I've used dumps as backups and never experienced this issue, although they would stop prematurely from time to time. The solution was just to check the string at the end and repeat the dump if it wasn't there.
    - 411111111111111 4y ago
      Considering that -Fc is a binary format, how would i check a string at the end of it?
      - forinti 4y ago
        If you do an SQL dump, it will end with "-- PostgreSQL database dump complete". If you use -Fc, you can make an SQL file from the dump using pg_restore like this: pg_restore -f db.sql db.dmp Or straight to stdout like this: pg_restore -f - db.dmp | grep "database dump complete" Although I guess if it's corrupted pg_restore won't work. It is really fast, but might not be practical if your database is too big.
        411111111111111 4y ago
        My issue was indeed the mishmash of versions, as the restore succeeded with the mentioned steps (installing old version, restoring, upgrading). nonetheless, interesting way to validate a backup, though it would probably take too long on most dumps
forinti 4y ago
I'm curious as to what could have gone wrong. A few weeks ago I upgraded from 9 to 14 using pg_upgrade and had no issue whatsoever.
- Macha 4y ago
  Doesn't this entail going 9.4-10, 10-11, 11-12, 12-13 and 13-14? (and possibly across various 9.x versions) That would be enough to qualify as "super painful" for me
  - forinti 4y ago
    No. To be precise, it was 9.6.6 or 9.5.3 (I don't recall which one exactly because both were installed) to 14.0. And then I upgraded to 14.4, but that only required a compilation.
- nicoburns 4y ago
  I think the issue is if you want zero-downtime (or close to zero downtime). pg_upgrade can take upwards of 10 minutes to run even for relatively small databases. It can potentially be hours for large ones.
  - aidos 4y ago
    If you use —-link it uses the existing data files instead of copying, which is significantly faster (though I’m not sure what your definition of relatively small is).

by olavgg 4y ago

mattashii 4y ago

That seems strange, the only incompatibilities between major versions are generally only the catalogs. I don't remember any version whose storage format is cannot be read by the next versions (none since 2010). Could you add some more info what issues you had?

411111111111111 4y ago
In not the person you've responded to but I've never been able to get `pg_dump -Fc` and restore to work across versions. It always errors out for me, saying it's an invalid/broken backup. Super annoying because now the workflow is 1. Start old version just to restore, remove old version and start new version for upgrade.
- castorp 4y ago
  You need to use pg_dump from the new version, not from the old version.
  - jasfi 4y ago
    I think you found a common stumbling block. The tutorial people follow may say to run pg_dump and many people would assume you're running it from the old version.
  - rapind 4y ago
    Wait, what? Explain please. OK upon further thought, what you're saying is to use the pg_dump tool that comes with the pg version you are upgrading to. I read it as "use pg_dump instead of pg_restore"...
    - mh- 4y ago
      Yes. Put concisely: Use the client tools from the newest version involved for all steps.
      - oblio 4y ago
        Isn't that impractical for most Linux distros? You'd need to manually install the new client.
        mh- 4y ago
        Probably? Certainly if you rely on distro packaging for it, yes. I was just trying to simplify the guidance here. :)
        BGZq7 4y ago
        On Debian (and probably most of its derivatives), the package manager will not remove any running PostgreSQL versions and will let you have multiple versions side by side. It also has its own tool pg_upgradecluster, which can perform the upgrade using pg_dump+pg_restore or pg_upgrade (optionally with link or clone options).
  - autra 4y ago
    Yes, and usually, it means you need to use the full binary path unfortunately (because debian wrappers embedded in the package select the version from your 5432 cluster).
- forinti 4y ago
  I've used dumps as backups and never experienced this issue, although they would stop prematurely from time to time. The solution was just to check the string at the end and repeat the dump if it wasn't there.
  - 411111111111111 4y ago
    Considering that -Fc is a binary format, how would i check a string at the end of it?
    - forinti 4y ago
      If you do an SQL dump, it will end with "-- PostgreSQL database dump complete". If you use -Fc, you can make an SQL file from the dump using pg_restore like this: pg_restore -f db.sql db.dmp Or straight to stdout like this: pg_restore -f - db.dmp | grep "database dump complete" Although I guess if it's corrupted pg_restore won't work. It is really fast, but might not be practical if your database is too big.
      - 411111111111111 4y ago
        My issue was indeed the mishmash of versions, as the restore succeeded with the mentioned steps (installing old version, restoring, upgrading). nonetheless, interesting way to validate a backup, though it would probably take too long on most dumps

forinti 4y ago

I'm curious as to what could have gone wrong. A few weeks ago I upgraded from 9 to 14 using pg_upgrade and had no issue whatsoever.

Macha 4y ago
Doesn't this entail going 9.4-10, 10-11, 11-12, 12-13 and 13-14? (and possibly across various 9.x versions) That would be enough to qualify as "super painful" for me
- forinti 4y ago
  No. To be precise, it was 9.6.6 or 9.5.3 (I don't recall which one exactly because both were installed) to 14.0. And then I upgraded to 14.4, but that only required a compilation.
nicoburns 4y ago
I think the issue is if you want zero-downtime (or close to zero downtime). pg_upgrade can take upwards of 10 minutes to run even for relatively small databases. It can potentially be hours for large ones.
- aidos 4y ago
  If you use —-link it uses the existing data files instead of copying, which is significantly faster (though I’m not sure what your definition of relatively small is).