3 ms·

From my perspective, the other side of the coin is that major version upgrades are a major big deal. You can't upgrade in place. You can't even upgrade with dow

From my perspective, the other side of the coin is that major version upgrades are a major big deal. You can't upgrade in place. You can't even upgrade with downtime (afaik). You've got to create a new database and migrate everything to it.

chasil 5y ago
In contrast, Oracle's database upgrade agent (DBUA, graphical Java app) will perform an upgrade in place, usually taking less than 30 minutes. The one place where I've found DBUA to fail is an upgrade of a 32-bit database to 64-bit (version 11.2.0.4 was the last offered for 32-bit). This type of upgrade requires the command-line dbupgrade utility, and it's a bit more complex. Complete export/import is only required when switching architectures for Oracle. Postgres should try for this sometime. I've also seen MS SQL Server quietly upgrade an imported database.
- jordanthoms 5y ago
  Is this zero-downtime? pg_upgrade with the link option is pretty fast, usually only taking a few minutes even with our >10TB database, but it does require taking the server down while it's running which is a major issue for many modern apps. I'm loving the cockroachdb upgrade process for our cluster - you just do a zero-downtime rolling restart to the next major version, with the ability to roll back to the old version if you see issues, and then when ready allow the database to apply it's migrations internally to upgrade the data structures, which is also zero-downtime.
  - chasil 5y ago
    No, it is not zero downtime. I think that "Real Application Cluster" (RAC) databases, where multiple Oracle database instances are connected to the storage table spaces, are able to perform a rolling upgrade, but I have not researched it in depth. I am certain that quarterly patches can be deployed in this way.
zozbot234 5y ago
> You can't upgrade in place. The Postgres documentation (which is generally excellent) describes workflows for upgrading across major releases, including what can be fairly described as an "in-place" upgrade, via pg_upgrade and the "--link" option. Upgrades without downtime can be achieved by using logical replication to create a standby instance running the new major release, then switching the new instance to primary.
- hn_throwaway_99 5y ago
  Yeah, was just about to post this about pg_upgrade. In addition, there was a front page post on HN recently about Adyen upgrading their 50 terabyte postgres instance with minimal downtime: https://news.ycombinator.com/item?id=29923303 https://news.ycombinator.com/item?id=29923303

chasil 5y ago

In contrast, Oracle's database upgrade agent (DBUA, graphical Java app) will perform an upgrade in place, usually taking less than 30 minutes. The one place where I've found DBUA to fail is an upgrade of a 32-bit database to 64-bit (version 11.2.0.4 was the last offered for 32-bit). This type of upgrade requires the command-line dbupgrade utility, and it's a bit more complex. Complete export/import is only required when switching architectures for Oracle. Postgres should try for this sometime. I've also seen MS SQL Server quietly upgrade an imported database.

jordanthoms 5y ago
Is this zero-downtime? pg_upgrade with the link option is pretty fast, usually only taking a few minutes even with our >10TB database, but it does require taking the server down while it's running which is a major issue for many modern apps. I'm loving the cockroachdb upgrade process for our cluster - you just do a zero-downtime rolling restart to the next major version, with the ability to roll back to the old version if you see issues, and then when ready allow the database to apply it's migrations internally to upgrade the data structures, which is also zero-downtime.
- chasil 5y ago
  No, it is not zero downtime. I think that "Real Application Cluster" (RAC) databases, where multiple Oracle database instances are connected to the storage table spaces, are able to perform a rolling upgrade, but I have not researched it in depth. I am certain that quarterly patches can be deployed in this way.

zozbot234 5y ago

> You can't upgrade in place. The Postgres documentation (which is generally excellent) describes workflows for upgrading across major releases, including what can be fairly described as an "in-place" upgrade, via pg_upgrade and the "--link" option. Upgrades without downtime can be achieved by using logical replication to create a standby instance running the new major release, then switching the new instance to primary.

hn_throwaway_99 5y ago
Yeah, was just about to post this about pg_upgrade. In addition, there was a front page post on HN recently about Adyen upgrading their 50 terabyte postgres instance with minimal downtime: https://news.ycombinator.com/item?id=29923303 https://news.ycombinator.com/item?id=29923303