Is brew postgresql-upgrade-database still relevant?

[swrobel]

Output of brew config

HOMEBREW_VERSION: 4.1.3-6-g1b6c46f
ORIGIN: https://github.com/Homebrew/brew
HEAD: 1b6c46ff12aace0f268303888ecd5495c95bdeaa
Last commit: 3 hours ago
Core tap origin: https://github.com/Homebrew/homebrew-core
Core tap HEAD: f357e4c65c5a923db687100ac4167232299b3983
Core tap last commit: 2 hours ago
Core tap branch: master
Core tap JSON: 31 Jul 21:02 UTC
HOMEBREW_PREFIX: /opt/homebrew
HOMEBREW_CASK_OPTS: []
HOMEBREW_EDITOR: subl -w
HOMEBREW_GITHUB_API_TOKEN: set
HOMEBREW_MAKE_JOBS: 10
Homebrew Ruby: 2.6.10 => /System/Library/Frameworks/Ruby.framework/Versions/2.6/usr/bin/ruby
CPU: 10-core 64-bit arm_firestorm_icestorm
Clang: 14.0.3 build 1403
Git: 2.41.0 => /opt/homebrew/bin/git
Curl: 8.1.2 => /usr/bin/curl
macOS: 13.5-arm64
CLT: 14.3.1.0.1.1683849156
Xcode: 14.3.1
Rosetta 2: false

Output of brew doctor

Please note that these warnings are just used to help the Homebrew maintainers
with debugging if you file an issue. If everything you use Homebrew for is
working fine: please don't worry or file an issue; just ignore this. Thanks!

Warning: Some installed formulae are deprecated or disabled.
You should find replacements for the following formulae:
  [email protected]

Warning: You have unlinked kegs in your Cellar.
Leaving kegs unlinked can lead to build-trouble and cause formulae that depend on
those kegs to fail to run properly once built. Run `brew link` on these:
  six

Description of issue

Is brew postgresql-upgrade-database still relevant? I attempted to use it to upgrade from postgresql@14 to postgresql@15, and first it made me brew tap homebrew/core, which I didn't think was necessary anymore since taps were moved to API endpoints, but after doing so, it still errors:

$ brew postgresql-upgrade-database -d
Warning: Formula postgresql was renamed to postgresql@14.
/opt/homebrew/Library/Homebrew/brew.rb (Formulary::TapLoader): loading /opt/homebrew/Library/Taps/homebrew/homebrew-core/Formula/[email protected]
Error: No such file or directory @ rb_sysopen - /opt/homebrew/var/postgres/PG_VERSION
/opt/homebrew/Library/Homebrew/cmd/postgresql-upgrade-database.rb:33:in `read'
/opt/homebrew/Library/Homebrew/cmd/postgresql-upgrade-database.rb:33:in `read'
/opt/homebrew/Library/Homebrew/cmd/postgresql-upgrade-database.rb:33:in `postgresql_upgrade_database'
/opt/homebrew/Library/Homebrew/brew.rb:94:in `<main>'

It should be noted that both are versioned postgres installs. I don't have a postgresql unversioned formula installed, and haven't for a long time.

Is this no longer the recommended way to do major version postgres upgrades? If not, what is?

[SMillerDev]

I know @MikeMcQuaid worked on it a couple of times, but as far as I know it's not needed anymore and you can just use https://www.postgresql.org/docs/current/pgupgrade.html

[MikeMcQuaid]

This script is only needed if you're migrating between major versions that both use var/postgres rather than e.g. var/postgres@14. As @SMillerDev mentioned, you should be able to use pg_upgrade instead if that's your situation.

@Homebrew/core should we deprecate this script and also the old_datadir usage?

[Bo98]

Worked fine when I used it a month ago with API enabled, but I believe that was when migrating an old pre-versioned data directory to the new format (the mv -v instruction wasn't sufficient for my old install).

But yeah the usefulness has perhaps reduced a lot there.

[MikeMcQuaid]

@Bo98 Yeh, I think it's unclear that for the "happy path" it shouldn't really be needed at all any more. If you want to fix it up for API case and undeprecate: definitely game for that 👍🏻

[Bo98]

What's broken with API enabled? I never had to set HOMEBREW_NO_INSTALL_FROM_API.

The broken bit really is that it only supports the unversioned var directory still, and I agree that supporting versioned var directories probably isn't worth it given the new structure works a lot better with pg_upgrade directly, where you can precisely choose which version to migrate to, at the small cost of path passing boilerplate (e.g. /opt/homebrew/opt/postgresql@15/bin/pg_upgrade -b /opt/homebrew/opt/postgresql@14/bin -d /opt/homebrew/var/postgresql@14 -D /opt/homebrew/var/postgresql@15). Unless you think it's useful being able to do brew postgresql-upgrade-database 14 15 or something.

In any case, it may be worth modifying the mv -v instruction in postgresql@14 to suggest that a pg_upgrade may be needed.

[MikeMcQuaid]

In any case, it may be worth modifying the mv -v instruction in postgresql@14 to suggest that a pg_upgrade may be needed.

Agreed, definitely room for caveats improvements I think 👍🏻

[ArturT]

@Bo98 Thank you for the command. I encountered a few issues because I was using PostgreSQL 14 universal version and postgresql@14 at the same time. I prepared a gist with instructions if anyone ends up here and wants to upgrade from PostgreSQL 14 to 15, to 16, to 17.
https://gist.github.com/ArturT/7a96be5988b3bfab4e10287f7c216219