9 ms·

Because the actual process of upgrading Postgres is terrible. I say this as someone who absolutely loves using it, but the actual process of upgrading Postgres

Because the actual process of upgrading Postgres is terrible. I say this as someone who absolutely loves using it, but the actual process of upgrading Postgres is something that takes significant downtime, is error-prone and you're often better off just dumping all the databases and reimporting them in a new folder. (A good idea in general since it'll vacuum and compact indexes as well if you do it, combining a few maintenance routines in your upgrade path.) It requires having the previous version of Postgres installed, something which can mess with a number of distro policies (not to mention docker, which is the most popular way to deploy software that will typically rely on Postgres), and unlike most software with that issue, Postgres is software you want to be managed by your distro. Therefore, most people only upgrade by necessity - when their distro forces the upgrade or the version they're using reaches EOL.

MichaelZuo 2y ago
So the real question is, why is the upgrade process so incompetently designed, and why has no one fixed this?
- throwaway19972 2y ago
  There are many answers to this question and few are as satisfying as the question implies.
- phil21 2y ago
  My opinion is Postgres was designed by software developers for software developers. The split on “which relational database to use” in my career has almost always been perfectly split between SWE vehemently demanding pgsql for the feature set, and the sysadmins having to support maintenance and production availability preferring MySQL. One of the few things I’ve enjoyed with the move into devops and companies forcing previously “pure” developers into operational roles was their discovery that Postgres was utterly horrible to administer at a systems level. Apparently us lowly sysadmins may have had a point after all. This is a bit tongue in cheek but really not far from my lived reality. When the focus is on features and “correctness” at the near total expense of sane systems tooling folks can develop some myopia on the subject. So many arguments with devs on my teams over this subject that were utterly horrified to find we were running MySQL for a given service. Open source projects tend to fix the pain points its contributors experience, and I assume there were not too many contributors wanting to deal with the boring work of making administration and easy task - it’s thankless “sideways” work that won’t result in many accolades or personal satisfaction for most SWEs. The end users are almost always developers, most of whose experiences in production entail either the equivalent of a docker container level scale system, or are simply given a connection string and the rest is a black box to them. Under those contexts I’d personally prefer Postgres as well and it wouldn’t even be close. When you get into backups, clustering, upgrades, and high availability under extreme load? IMO the story falls apart real fast.
  - mxey 2y ago
    As someone who operates both I much prefer to run pg_upgrade every few years rather than dealing with mysqldump, replication issues and babysitting Orchestrator.
    - literalAardvark 2y ago
      just as long as you don't forget to reindex after
      - mxey 2y ago
        > All failure, rebuild, and reindex cases will be reported by pg_upgrade if they affect your installation; post-upgrade scripts to rebuild tables and indexes will be generated automatically.
        literalAardvark 2y ago
        Not true. Those scripts only cover problems caused by PostgreSQL itself, not, for example, a change in collations that will silently break your indexes, such as the one in Debian 12.
      - djbusby 2y ago
        It's a step of your script or runbook. Right?
        literalAardvark 2y ago
        It is, but it also takes a very long time during which production data integrity can be severely affected.
  - jeltz 2y ago
    That has not been my experience at all. The sysadmins I have worked with have strongly preferred PostgreSQL over running MySQL while the developers have cared, but less so. The reason is that when something goes wrong PostgreSQL is much easier to diagnose. So while some tasks like upgrades take less manual effort with MySQL PostgreSQL is more stable and gives better error messages for when you get paged in the middle of the night. PostgreSQL has prioritized correctness and stability which while it has made certain features more clunky to use or taken longer time for them to be implemented, when you get paged n the middle of the night you get the time you spent back. PostgreSQL also has spent a lot of time on improving DBA experience so mixed DBA/sysadmins also usually prefer PG.
- WJW 2y ago
  The answer to both is probably "because maintainer time is scarce and nobody is interested enough".
- whizzter 2y ago
  I think one really BIG factor is that built-in logical replication wasn't introduced until PostgreSQL 10 in 2017, before that you only had physical replication for master-slave but iirc that didn't work between versions so doing a "hot" upgrade was more or less impossible without third-party tools iirc. So even if it's available these days, the amount of people still subjected to upgrades from older version still leaves an impression that it's really bad.
  - creshal 2y ago
    Yeah, everywhere I worked there's usually two clusters of postgres databases - whatever made it past the postgres 10 hump is on the most recent version and keeps getting migrated - whatever is still on 9.x is scheduled to remain that way until the product using it will be EOL'd
  - RegnisGnaw 2y ago
    There is still major issues with logical replication, mainly limited feature support.
    - whizzter 2y ago
      What's lacking? Specific types or schema changes?
      - RegnisGnaw 2y ago
        I don't care much about schema changes as I can setup logical replication just for the major version upgrade. The main issue is the lack of support for large objects, which the code bases I'm dealing with uses heavily.
- jeltz 2y ago
  To avoid having to slow down development of new PostgreSQL features. Improving upgrades in a way where PostgreSQL does not need to either maintain multiple different versions of parts of the code and/or lock down internal interfaces which now can change freely every major version so they cannot be refactored and improved in the future is not a trivial task, maybe even impossible. Even just the existence of pg_upgrade has to some degree limited what can be done to improve PostgreSQL. Obviously pg_upgrade is worth it, but hindering development even further might not be popular. The PostgreSQL team simply does not have the resources to do this. At least not without significantly slowing down development of everything else which there is no political will for. Maybe someone will come up with a genius idea which solves this but I am doubtful. Usually there is no free lunch. Maybe some core dev will correct me, I am quite familiar with the PostgreSQL project but not one of the core devs. :)
  - paulryanrogers 2y ago
    What is this holding back? A redo based alternative to MVCC?
    - jeltz 2y ago
      Nope, that is totally unrated. To support upgrade in place without an old version of PostgreSQL: 1. The new version of PostgreSQL would need to able to read all old catalog table formats and migrate them. 2. The new version of PostgreSQL would need to support all old versions of the parse tree to migrate views. 3. Likely a bunch of more things that I do not know of. I for example doubt it is trivial to just read an old catalog without having a fully up and running cluster which supports almost everything in that old cluster. The catalog has TOAST tables and indexes for example. Right now 1 and 2 are implemented in pg_dump plus by having pg_dump call functions in a running old version of PostgreSQL.
      - ttfkam 2y ago
        It is a PITA, but I've written scripts that pg_dump just the schema, load the schema into the new db with the new version, set up logical replication between the two, wait for them to sync, reset all the sequences, and rebuild indexes before doing the handover. It works with basically no downtime but I agree this kind of thing should definitely be easier, even turnkey.
tgv 2y ago
ADd to that: if it works, don't fix it.
- chefandy 2y ago
  And considering how many people use some sort of abstraction layer like an ORM, have databases installed as part of some deployed application, or just do basic CRUD stuff, it probably would have worked on a postgres install from the 90s. I keep an eye out for vulnerabilities, and that's about it.
crabbone 2y ago
> Postgres is software you want to be managed by your distro. Why? For production systems I generally try to avoid using anything not built internally. When it comes to PostgreSQL, for example, if you want to submit benchmarks, or you want to report bugs, an important or sometimes a mandatory component is to provide compilation flags. Also, seeing how every kind of storage can act in a different way, tuning of higher-level storage programs seems inevitable. Even though PostgreSQL has a lot of configurations that can be done to the compiled program, some (eg. block size) are compile-time that can only be changed by compiling the program.
- digitallis42 2y ago
  Depends on your scale. If you're a startup or even just a small side service, performance isn't going to be a bottleneck and you/the org wants the thing to be fire and forget, including bug fixes and especially security patches. A distro takes care of all of those generally and makes sure the dependencies have the same care taken.
  - throw0101c 2y ago
    > A distro takes care of all of those generally and makes sure the dependencies have the same care taken. So does using the vendor repos: * https://apt.postgresql.org/ https://apt.postgresql.org/ / https://yum.postgresql.org https://yum.postgresql.org * https://dev.mysql.com/downloads/repo/ https://dev.mysql.com/downloads/repo/ This way when you upgrade your OS you don't have to worry about suddenly getting a new version (which, in the case of MySQL, may take a long time to convert its database files to the new version format).
    - jamespo 2y ago
      How does that work for python/perl/ruby libs etc?
      - danudey 2y ago
        It's very rare that the client protocol changes dramatically and in an incompatible way, so typically nothing changes and no one notices. I've seen a few rare cases where an update to the client library is required for improvements to the protocol, e.g. when MySQL switched to the new password/authentication format, but that was also backwards-compatible: the old format was used until an account's password was changed, password changes could be told to use the legacy format instead, and you could set the legacy format by default. The only issue that occurred (and it did occur) was old client libraries + new server password hashes, but, again, in this case it didn't affect existing installations/users/passwords.
CrimsonRain 2y ago
I fully agree. That's why I use this: https://github.com/pgautoupgrade/docker-pgautoupgrade https://github.com/pgautoupgrade/docker-pgautoupgrade But obviously this is not suitable for all use cases. I don't know why pg doesn't invest in this sector.
- DavidPP 2y ago
  Thanks for the link, wasn't aware of this solution.
- elcritch 2y ago
  Financial incentives likely, because PG is developed by companies that make money via providing support. Doesn't have to be an insidious plot but just why work on something you know well and that makes you money. MSSQL wants people to pay to upgrade, it behooves them to make it seemless.
jeltz 2y ago
While I partially agree the solution is pretty simple to the multiple versions problem: use the official RPM and APT repos. https://apt.postgresql.org/ https://apt.postgresql.org/ https://yum.postgresql.org/ https://yum.postgresql.org/
forinti 2y ago
I have always compiled from source so that I can have more than one version in place. The installation takes less than 1GB and is quite easy and fast to compile and I've never had any issues with upgrades. If you forget to compile an extension, you can do it later, copy the .so to the right folder, and you don't even have to restart the cluster. If you don't want to use pg_upgrade, you can dump from one cluster and pipe directly into another, with no need for a temporary file. It couldn't be easier. Can't say the same for Oracle. Even applying a patch can ruin several weekends.
- literalAardvark 2y ago
  > It couldn't be easier If you have a trivial database, yes. If you have a large, complicated database that takes hours/days to restore and has an HA SLA, very hell no.
  - deleted 2y ago
    [deleted]
  - forinti 2y ago
    That's not going to be easy with any database.
    - Too 2y ago
      With mongodb in a HA setup you can perform a staggered upgrade of individual nodes in the cluster with no downtime. Very smooth in my experience.
      - water9 2y ago
        Yeah, but what is your dataset is partitioned horizontally? It’s very smooth until it’s not. For example, if you are using the postGIS extensions and something changes with the new version that could be a showstopper
    - danudey 2y ago
      MySQL, for all its faults, actually handles this quite well. Generally speaking, assuming there aren't any major backwards-incompatibilities (which there haven't often been in my experience), you can start up the old server in place of the new server and you're good to go. Some table-rebuilding can be done in-place if you need to do things like recreate indices, but _generally_ you're good to go from there. For more complex deployments, cases of backwards-incompatibilities in the data structure, or where I had reason to rebuild many large tables, you set up a replication secondary, do pre-upgrade migrations (e.g. in the case of deprecated functionality), upgrade the secondary, do post-upgrade migrations (e.g. in the case of new functionality), and wait for replication to catch up 100%. Then, after sending traffic to it for a bit to ensure it works correctly, you swap the primary and secondary and do it again.
kardianos 2y ago
There are two primary issues with PostgreSQL: * Upgrades * Database Protocol MS SQL Server does better on both of these fronts: Upgrades, when running MS SQL Server in a container, is rebooting the database with the new database engine version. The DBMS sees the old files, upgrades them in place and starts the DB completely seamlessly 100% of the time. The MS SQL Server protocol, TDS, supports native named parameters, executes the T-SQL PL inline with SQL. TDS also supports inline query cancellations because the protocol is framed, and both T-SQL and TDS supports returning multiple result sets from a single query. But yes, whenever I see PG docs saying, major versions require an export and re-load, I shudder.
- Tostino 2y ago
  As someone who has a decade of professional postgres experience (and built my company on it), and 6 years of SqlServer experience, I couldn't agree more with your comment. I really, really like the TDS wire protocol and what it enables. I also really miss their query optimizer. It was just so damn smart.
  - chasil 2y ago
    I've only done it once, but doing an "rpm -Uvh" on the SQL Server binaries in Linux, then starting up the database did an immediate upgrade of the database files. Oracle's DBUA is so much more painful.
  - whartung 2y ago
    I'm sorry, not to quibble about the quality of the TDS protocol, but what does it have to do with the upgradability of SqlServer?
    - Tostino 2y ago
      Nothing, they're unrelated. However, I agree with OP that SQL Server does have a solid upgrade story and it's generally pain free. They are just two aspects I really wish Postgres could do better on, and I miss working with those parts of SQL Server.
ktosobcy 2y ago
This! I already mentioned this a while back and was basically berated (by someone that seemed like a dev/close to dev) that current setup is just fine and because of postgress complexity (extensions) it has to be done that way... and while I like postgress a lot it's quite annoying that the upgrade is such a terrible experience... :|
- lazide 2y ago
  I suspect it’s also a bit of stockhold syndrome on behalf of admins haha
  - ktosobcy 2y ago
    Or better yet - "guarding the job", i.e. making their job very important ;)
    - lazide 2y ago
      Very true. At a prior company, we rolled out a simpler and easier to use admin console for our enterprise product and all the customer admins hated it. Because now it was easy to use hah. We ended up rolling it back because the hate was so consistent. (And no, this wasn’t us guessing or making this up - a number of them were quite explicit that we were threatening their roles, and to stop it.)
thomaslord 2y ago
I'll confess - I have a project that uses Heroku's managed Postgres and my preferred upgrade method is to set the maintenance window to the middle of the night, create a backup, and be awake at 1am to make sure that nothing is broken after they force the upgrade. Their auto-upgrade process hasn't failed me so far, but there's no way to manually trigger it.
- daleswanson 2y ago
  What do you mean? You can manually run Heroku database maintenance. We just did this recently at work. https://devcenter.heroku.com/articles/heroku-postgres-maintenance#performing-maintenance-manually https://devcenter.heroku.com/articles/heroku-postgres-mainte...
anymouse123456 2y ago
Also a fan of Postgresql, but compiling and installing this tool is also a complete nightmare. Every bit of the experience before getting it working, feels like it was designed by an MBA who owns a Postgres hosting service. Other than that, it's amazing.
beeboobaa3 2y ago
This has been solved by logical replication a few versions ago. 1. Leave your old database running 2. Start your new database with new version 3. Run logical replication and let it catch up 4. Decide you want to switch over. 5. Using a script: Stop your apps writing to the database (or switch to read only mode), let logical replication catch up one final time, stop logical replication, run a procedure to fix your sequences, and switch your apps over to the new database. You are now done. Sure, it requires a one-time investment to work out the exact steps needed for your situation, but it's easy to do with minimal downtime. Of course if you don't care about downtime just shut down your database server, make a backup(!), and then do pg_upgrade.
- RegnisGnaw 2y ago
  This has not been solved by logical replication. There are features in PostgreSQL not supported by logical replication. One of the issues I'm dealing with is our product switched from Oracle to PostgreSQL and the ORM stuff love LOBs, and you know LOBs can't be logical replicated.
- ellisv 2y ago
  > Of course if you don't care about downtime just shut down your database server, make a backup(!), and then do pg_upgrade. I think this is the right answer for 99% of companies. Yes, everyone wants zero downtime, but I see incredibly few cases where it is actually worth the cost and effort.

MichaelZuo 2y ago

So the real question is, why is the upgrade process so incompetently designed, and why has no one fixed this?

throwaway19972 2y ago
There are many answers to this question and few are as satisfying as the question implies.
phil21 2y ago
My opinion is Postgres was designed by software developers for software developers. The split on “which relational database to use” in my career has almost always been perfectly split between SWE vehemently demanding pgsql for the feature set, and the sysadmins having to support maintenance and production availability preferring MySQL. One of the few things I’ve enjoyed with the move into devops and companies forcing previously “pure” developers into operational roles was their discovery that Postgres was utterly horrible to administer at a systems level. Apparently us lowly sysadmins may have had a point after all. This is a bit tongue in cheek but really not far from my lived reality. When the focus is on features and “correctness” at the near total expense of sane systems tooling folks can develop some myopia on the subject. So many arguments with devs on my teams over this subject that were utterly horrified to find we were running MySQL for a given service. Open source projects tend to fix the pain points its contributors experience, and I assume there were not too many contributors wanting to deal with the boring work of making administration and easy task - it’s thankless “sideways” work that won’t result in many accolades or personal satisfaction for most SWEs. The end users are almost always developers, most of whose experiences in production entail either the equivalent of a docker container level scale system, or are simply given a connection string and the rest is a black box to them. Under those contexts I’d personally prefer Postgres as well and it wouldn’t even be close. When you get into backups, clustering, upgrades, and high availability under extreme load? IMO the story falls apart real fast.
- mxey 2y ago
  As someone who operates both I much prefer to run pg_upgrade every few years rather than dealing with mysqldump, replication issues and babysitting Orchestrator.
  - literalAardvark 2y ago
    just as long as you don't forget to reindex after
    - mxey 2y ago
      > All failure, rebuild, and reindex cases will be reported by pg_upgrade if they affect your installation; post-upgrade scripts to rebuild tables and indexes will be generated automatically.
      - literalAardvark 2y ago
        Not true. Those scripts only cover problems caused by PostgreSQL itself, not, for example, a change in collations that will silently break your indexes, such as the one in Debian 12.
    - djbusby 2y ago
      It's a step of your script or runbook. Right?
      - literalAardvark 2y ago
        It is, but it also takes a very long time during which production data integrity can be severely affected.
- jeltz 2y ago
  That has not been my experience at all. The sysadmins I have worked with have strongly preferred PostgreSQL over running MySQL while the developers have cared, but less so. The reason is that when something goes wrong PostgreSQL is much easier to diagnose. So while some tasks like upgrades take less manual effort with MySQL PostgreSQL is more stable and gives better error messages for when you get paged in the middle of the night. PostgreSQL has prioritized correctness and stability which while it has made certain features more clunky to use or taken longer time for them to be implemented, when you get paged n the middle of the night you get the time you spent back. PostgreSQL also has spent a lot of time on improving DBA experience so mixed DBA/sysadmins also usually prefer PG.
WJW 2y ago
The answer to both is probably "because maintainer time is scarce and nobody is interested enough".
whizzter 2y ago
I think one really BIG factor is that built-in logical replication wasn't introduced until PostgreSQL 10 in 2017, before that you only had physical replication for master-slave but iirc that didn't work between versions so doing a "hot" upgrade was more or less impossible without third-party tools iirc. So even if it's available these days, the amount of people still subjected to upgrades from older version still leaves an impression that it's really bad.
- creshal 2y ago
  Yeah, everywhere I worked there's usually two clusters of postgres databases - whatever made it past the postgres 10 hump is on the most recent version and keeps getting migrated - whatever is still on 9.x is scheduled to remain that way until the product using it will be EOL'd
- RegnisGnaw 2y ago
  There is still major issues with logical replication, mainly limited feature support.
  - whizzter 2y ago
    What's lacking? Specific types or schema changes?
    - RegnisGnaw 2y ago
      I don't care much about schema changes as I can setup logical replication just for the major version upgrade. The main issue is the lack of support for large objects, which the code bases I'm dealing with uses heavily.
jeltz 2y ago
To avoid having to slow down development of new PostgreSQL features. Improving upgrades in a way where PostgreSQL does not need to either maintain multiple different versions of parts of the code and/or lock down internal interfaces which now can change freely every major version so they cannot be refactored and improved in the future is not a trivial task, maybe even impossible. Even just the existence of pg_upgrade has to some degree limited what can be done to improve PostgreSQL. Obviously pg_upgrade is worth it, but hindering development even further might not be popular. The PostgreSQL team simply does not have the resources to do this. At least not without significantly slowing down development of everything else which there is no political will for. Maybe someone will come up with a genius idea which solves this but I am doubtful. Usually there is no free lunch. Maybe some core dev will correct me, I am quite familiar with the PostgreSQL project but not one of the core devs. :)
- paulryanrogers 2y ago
  What is this holding back? A redo based alternative to MVCC?
  - jeltz 2y ago
    Nope, that is totally unrated. To support upgrade in place without an old version of PostgreSQL: 1. The new version of PostgreSQL would need to able to read all old catalog table formats and migrate them. 2. The new version of PostgreSQL would need to support all old versions of the parse tree to migrate views. 3. Likely a bunch of more things that I do not know of. I for example doubt it is trivial to just read an old catalog without having a fully up and running cluster which supports almost everything in that old cluster. The catalog has TOAST tables and indexes for example. Right now 1 and 2 are implemented in pg_dump plus by having pg_dump call functions in a running old version of PostgreSQL.
    - ttfkam 2y ago
      It is a PITA, but I've written scripts that pg_dump just the schema, load the schema into the new db with the new version, set up logical replication between the two, wait for them to sync, reset all the sequences, and rebuild indexes before doing the handover. It works with basically no downtime but I agree this kind of thing should definitely be easier, even turnkey.

tgv 2y ago

ADd to that: if it works, don't fix it.

chefandy 2y ago
And considering how many people use some sort of abstraction layer like an ORM, have databases installed as part of some deployed application, or just do basic CRUD stuff, it probably would have worked on a postgres install from the 90s. I keep an eye out for vulnerabilities, and that's about it.

crabbone 2y ago

> Postgres is software you want to be managed by your distro. Why? For production systems I generally try to avoid using anything not built internally. When it comes to PostgreSQL, for example, if you want to submit benchmarks, or you want to report bugs, an important or sometimes a mandatory component is to provide compilation flags. Also, seeing how every kind of storage can act in a different way, tuning of higher-level storage programs seems inevitable. Even though PostgreSQL has a lot of configurations that can be done to the compiled program, some (eg. block size) are compile-time that can only be changed by compiling the program.

digitallis42 2y ago
Depends on your scale. If you're a startup or even just a small side service, performance isn't going to be a bottleneck and you/the org wants the thing to be fire and forget, including bug fixes and especially security patches. A distro takes care of all of those generally and makes sure the dependencies have the same care taken.
- throw0101c 2y ago
  > A distro takes care of all of those generally and makes sure the dependencies have the same care taken. So does using the vendor repos: * https://apt.postgresql.org/ https://apt.postgresql.org/ / https://yum.postgresql.org https://yum.postgresql.org * https://dev.mysql.com/downloads/repo/ https://dev.mysql.com/downloads/repo/ This way when you upgrade your OS you don't have to worry about suddenly getting a new version (which, in the case of MySQL, may take a long time to convert its database files to the new version format).
  - jamespo 2y ago
    How does that work for python/perl/ruby libs etc?
    - danudey 2y ago
      It's very rare that the client protocol changes dramatically and in an incompatible way, so typically nothing changes and no one notices. I've seen a few rare cases where an update to the client library is required for improvements to the protocol, e.g. when MySQL switched to the new password/authentication format, but that was also backwards-compatible: the old format was used until an account's password was changed, password changes could be told to use the legacy format instead, and you could set the legacy format by default. The only issue that occurred (and it did occur) was old client libraries + new server password hashes, but, again, in this case it didn't affect existing installations/users/passwords.

CrimsonRain 2y ago

I fully agree. That's why I use this: https://github.com/pgautoupgrade/docker-pgautoupgrade https://github.com/pgautoupgrade/docker-pgautoupgrade But obviously this is not suitable for all use cases. I don't know why pg doesn't invest in this sector.

DavidPP 2y ago
Thanks for the link, wasn't aware of this solution.
elcritch 2y ago
Financial incentives likely, because PG is developed by companies that make money via providing support. Doesn't have to be an insidious plot but just why work on something you know well and that makes you money. MSSQL wants people to pay to upgrade, it behooves them to make it seemless.

jeltz 2y ago

While I partially agree the solution is pretty simple to the multiple versions problem: use the official RPM and APT repos. https://apt.postgresql.org/ https://apt.postgresql.org/ https://yum.postgresql.org/ https://yum.postgresql.org/

forinti 2y ago

I have always compiled from source so that I can have more than one version in place. The installation takes less than 1GB and is quite easy and fast to compile and I've never had any issues with upgrades. If you forget to compile an extension, you can do it later, copy the .so to the right folder, and you don't even have to restart the cluster. If you don't want to use pg_upgrade, you can dump from one cluster and pipe directly into another, with no need for a temporary file. It couldn't be easier. Can't say the same for Oracle. Even applying a patch can ruin several weekends.

literalAardvark 2y ago
> It couldn't be easier If you have a trivial database, yes. If you have a large, complicated database that takes hours/days to restore and has an HA SLA, very hell no.
- deleted 2y ago
  [deleted]
- forinti 2y ago
  That's not going to be easy with any database.
  - Too 2y ago
    With mongodb in a HA setup you can perform a staggered upgrade of individual nodes in the cluster with no downtime. Very smooth in my experience.
    - water9 2y ago
      Yeah, but what is your dataset is partitioned horizontally? It’s very smooth until it’s not. For example, if you are using the postGIS extensions and something changes with the new version that could be a showstopper
  - danudey 2y ago
    MySQL, for all its faults, actually handles this quite well. Generally speaking, assuming there aren't any major backwards-incompatibilities (which there haven't often been in my experience), you can start up the old server in place of the new server and you're good to go. Some table-rebuilding can be done in-place if you need to do things like recreate indices, but _generally_ you're good to go from there. For more complex deployments, cases of backwards-incompatibilities in the data structure, or where I had reason to rebuild many large tables, you set up a replication secondary, do pre-upgrade migrations (e.g. in the case of deprecated functionality), upgrade the secondary, do post-upgrade migrations (e.g. in the case of new functionality), and wait for replication to catch up 100%. Then, after sending traffic to it for a bit to ensure it works correctly, you swap the primary and secondary and do it again.

kardianos 2y ago

There are two primary issues with PostgreSQL: * Upgrades * Database Protocol MS SQL Server does better on both of these fronts: Upgrades, when running MS SQL Server in a container, is rebooting the database with the new database engine version. The DBMS sees the old files, upgrades them in place and starts the DB completely seamlessly 100% of the time. The MS SQL Server protocol, TDS, supports native named parameters, executes the T-SQL PL inline with SQL. TDS also supports inline query cancellations because the protocol is framed, and both T-SQL and TDS supports returning multiple result sets from a single query. But yes, whenever I see PG docs saying, major versions require an export and re-load, I shudder.

Tostino 2y ago
As someone who has a decade of professional postgres experience (and built my company on it), and 6 years of SqlServer experience, I couldn't agree more with your comment. I really, really like the TDS wire protocol and what it enables. I also really miss their query optimizer. It was just so damn smart.
- chasil 2y ago
  I've only done it once, but doing an "rpm -Uvh" on the SQL Server binaries in Linux, then starting up the database did an immediate upgrade of the database files. Oracle's DBUA is so much more painful.
- whartung 2y ago
  I'm sorry, not to quibble about the quality of the TDS protocol, but what does it have to do with the upgradability of SqlServer?
  - Tostino 2y ago
    Nothing, they're unrelated. However, I agree with OP that SQL Server does have a solid upgrade story and it's generally pain free. They are just two aspects I really wish Postgres could do better on, and I miss working with those parts of SQL Server.

ktosobcy 2y ago

This! I already mentioned this a while back and was basically berated (by someone that seemed like a dev/close to dev) that current setup is just fine and because of postgress complexity (extensions) it has to be done that way... and while I like postgress a lot it's quite annoying that the upgrade is such a terrible experience... :|

lazide 2y ago
I suspect it’s also a bit of stockhold syndrome on behalf of admins haha
- ktosobcy 2y ago
  Or better yet - "guarding the job", i.e. making their job very important ;)
  - lazide 2y ago
    Very true. At a prior company, we rolled out a simpler and easier to use admin console for our enterprise product and all the customer admins hated it. Because now it was easy to use hah. We ended up rolling it back because the hate was so consistent. (And no, this wasn’t us guessing or making this up - a number of them were quite explicit that we were threatening their roles, and to stop it.)

thomaslord 2y ago

I'll confess - I have a project that uses Heroku's managed Postgres and my preferred upgrade method is to set the maintenance window to the middle of the night, create a backup, and be awake at 1am to make sure that nothing is broken after they force the upgrade. Their auto-upgrade process hasn't failed me so far, but there's no way to manually trigger it.

daleswanson 2y ago
What do you mean? You can manually run Heroku database maintenance. We just did this recently at work. https://devcenter.heroku.com/articles/heroku-postgres-maintenance#performing-maintenance-manually https://devcenter.heroku.com/articles/heroku-postgres-mainte...

anymouse123456 2y ago

Also a fan of Postgresql, but compiling and installing this tool is also a complete nightmare. Every bit of the experience before getting it working, feels like it was designed by an MBA who owns a Postgres hosting service. Other than that, it's amazing.

beeboobaa3 2y ago

This has been solved by logical replication a few versions ago. 1. Leave your old database running 2. Start your new database with new version 3. Run logical replication and let it catch up 4. Decide you want to switch over. 5. Using a script: Stop your apps writing to the database (or switch to read only mode), let logical replication catch up one final time, stop logical replication, run a procedure to fix your sequences, and switch your apps over to the new database. You are now done. Sure, it requires a one-time investment to work out the exact steps needed for your situation, but it's easy to do with minimal downtime. Of course if you don't care about downtime just shut down your database server, make a backup(!), and then do pg_upgrade.

RegnisGnaw 2y ago
This has not been solved by logical replication. There are features in PostgreSQL not supported by logical replication. One of the issues I'm dealing with is our product switched from Oracle to PostgreSQL and the ORM stuff love LOBs, and you know LOBs can't be logical replicated.
ellisv 2y ago
> Of course if you don't care about downtime just shut down your database server, make a backup(!), and then do pg_upgrade. I think this is the right answer for 99% of companies. Yes, everyone wants zero downtime, but I see incredibly few cases where it is actually worth the cost and effort.