I've heard a saying "just say no to dot oh" -- encouraging one to skip the .0 release and wait until .1 or .2 before doing the upgrade.

This will stop working when not enough people adopt .0 versions anymore, because errors from regular usage will not pop up. .0 will become the new beta release that nobody cares about.

I typically update to .0 on my dev machine ASAP, and if there are no problems after a bit I'll update the server to .0 (or usually .1, since that'll usually be out by then).

I think that's a nice compromise; although not a "real" test as the load is a lot less. Also things may work in dev but not production, but unless you explicitly use new features that's rare.

For larger setups you can us canary servers and whatnot, but I prefer to work at small companies and keep my sanity.

For context my ideal setup has at least 3 environments - prod, qa/testing before release, and dev. With dev being the most likely to break, and the qa/testing for when we need to be absolutely certain that differences between prod and dev aren't masking any bugs.

I do like the idea of "canary versioning" some stuff. It's a bit of a game theory issue isn't it, since we all need prod to work at all times and can't risk a .0, and a major version difference between dev and prod can mask some nasty issues in my experience.

Luckily nowdays we have enough brave persons who just take whatever is postgres:latest image from Docker hub.

Unluckily, chances they will report bugs not that high - at least I don't expect bugs related to any moderate size/loaded databases be found by this way.

If only they made the upgrade automatic.

I use it in a container mainly, and MariaDB has an option to enable auto upgrades, but postgres requires you to manually upgrade every time.

For small databases with lax uptime requirements I find it pretty easy to upgrade. Just stop the database, run pg_upgrade_cluster (a Debian tool) and create a new replica.

For large databases where you care about uptime upgrading is usually a huge project anyway, since you have to make sure that nothing breaks due to the new version (check query plans, check if the new version changed anything, etc). You also probably want to use logical replication for the upgrade. There are things that PostgreSQL can improve of course.

As an aside I recently was involved in an upgrade from 9.6 where we used logical replication and pg_dump (yes, we really used pg_dump on a 20+ TB database). The reason for using pg_dump was because we have some tables which bloated very fast meaning we could not hold a snapshot open on the master long enough to do the initial data copy so the initial copy was done with pg_dump from a paused replica. And to not lose any data during pg_dump/pg_restore we kept a logical replication slot alive (but unused until pg_restore was complete). That was an interesting project, especially since Googling did not turn up anyone who had done the same.

Isn’t keeping a logical slot alive holding a snapshot?

Not normally, no. A real snapshot is held during the initial creation of the logical slot. After that the slot's "catalog xmin" prevents old catalog (but not user table!) row versions from being removed, but that's somewhat different from a snapshot.

Exactly. We had to disable some cron jobs which abuse temporary tables (and functions!) to avoid catalog bloat but there were no long lived snapshots on the master during the upgrade.

I'd genuinely love to read about that if you're able to share more?

Major version upgrades by definition contain breaking changes. Automatic upgrades across major versions will break things. Still, some distributions do handle this somewhat sanely. For example, Debian and derivatives such as Ubuntu let you have multiple versions installed at the same time and provide a pg_upgradecluster tool to handle the upgrade automatically in most cases. It just won't do it while you are asleep (thankfully).