Out of sync PostgreSQL - How to Fix?

[resistorsoftware]

brew info postgresql

 postgresql: stable 14.1 (bottled), HEAD
 Object-relational database system
 https://www.postgresql.org/
 /usr/local/Cellar/postgresql/13.2_1 (3,218 files, 42.6MB) *
 Poured from bottle on 2021-04-21 at 16:54:25

OK. So I have a working 13.2 and I want to go to 13.5

brew postgresql-upgrade-database

==> brew install postgresql@13
Error: postgresql@13: no bottle available!
You can try to install from source with:
  brew install --build-from-source postgresql@13
Please note building from source is unsupported. You will encounter build failures with some formulae. If you experience any issues please create pull requests instead of asking for help on Homebrew's GitHub, Twitter or any other official channels.
Error: No postgresql 13.* version installed!

So hang on? I have 13.2, poured from a bottle, yet the updater tells me I don't have the right stuff. So what should I be running to do one of two things I want to do.

1. I would accept to bump up to 14.1 provided I lose nothing in my current 13.2
2. I would accept to go from 13.2 to 13.5 just to get up to date there

I am open to any suggestions. Thanks

[gromgit]

What does brew info postgresql@13 show? If it starts with anything other than:

postgresql@13: stable 13.5 (bottled) [keg-only]
Object-relational database system
[...]

then you almost certainly haven't run brew update in a while.

If you're getting the right result from brew info, what's the output of brew config and brew doctor?

[resistorsoftware]

brew info postgresql@13

postgresql@13: stable 13.5 [keg-only]

brew config

HOMEBREW_VERSION: 3.3.6-29-gd7e6f19
ORIGIN: https://github.com/Homebrew/brew
HEAD: d7e6f197d794b732c790e9110667fb54f6059ba8
Last commit: 2 days ago
Core tap ORIGIN: https://github.com/Homebrew/homebrew-core
Core tap HEAD: 5ab7e217f110eef4f2a1843b6d470c066eaea88e
Core tap last commit: 34 hours ago
Core tap branch: master
HOMEBREW_PREFIX: /usr/local
HOMEBREW_CASK_OPTS: []
HOMEBREW_CORE_GIT_REMOTE: https://github.com/Homebrew/homebrew-core
HOMEBREW_DISPLAY: /private/tmp/com.apple.launchd.zM1qu0s8gv/org.macosforge.xquartz:0
HOMEBREW_EDITOR: /usr/local/bin/code
HOMEBREW_MAKE_JOBS: 8
Homebrew Ruby: 2.6.8 => /usr/local/Homebrew/Library/Homebrew/vendor/portable-ruby/2.6.8/bin/ruby
CPU: octa-core 64-bit skylake
Clang: 12.0.0 build 1200
Git: 2.34.0 => /usr/local/bin/git    
Curl: 7.64.1 => /usr/bin/curl
macOS: 10.15.6-x86_64
CLT: 12.4.0.0.1.1610135815
Xcode: 12.4

brew doctor

Nothing interesting, just some notes about some packages I never use.

[gromgit]

Hmmm, postgresql@13 was bottled for Catalina, if I'm reading this correctly, and yet:

$ brew cat postgresql@13
[...]
  bottle do
    sha256 arm64_monterey: "956d5e06c4d34fdd01b560114f57e9c7cd1e40f962c92a8b268fc6ff20ae60d3"
    sha256 arm64_big_sur:  "b3068d0cb543e2ee56900443259cd2607679d53433a97435cbfdbb37dc5011e4"
    sha256 monterey:       "134e0113a48dada73e298ab54a1082334c800873a544b43fa72fadd3ee993d88"
    sha256 big_sur:        "428d860e6c977e0071ae8e483c974b8a695f37f5edd86143beccc08754274ce3"
    sha256 x86_64_linux:   "4536ad299ce5d8305dfdcc2d2c9f558b7dd9e6a40266c95b3725198fdeb7e4c2"
  end
[...]

@Homebrew/core, any ideas?

[carlocab]

I've dispatched a bottle job at https://github.com/Homebrew/homebrew-core/actions/runs/1533243595.

@resistorsoftware, try doing brew edit postgresql@13 and then add this:

diff --git a/Formula/[email protected] b/Formula/[email protected]
index d4aee07a2b1..fbc38ea52d1 100644
--- a/Formula/[email protected]
+++ b/Formula/[email protected]
@@ -2,6 +2,7 @@ class PostgresqlAT13 < Formula
   desc "Object-relational database system"
   homepage "https://www.postgresql.org/"
   url "https://ftp.postgresql.org/pub/source/v13.5/postgresql-13.5.tar.bz2"
+  version "13.2"
   sha256 "9b81067a55edbaabc418aacef457dd8477642827499560b00615a6ea6c13f6b3"
   license "PostgreSQL"
   revision 1

and see if you can use brew postgresql-upgrade-database.

Make sure to do

git -C "$(brew --repository homebrew/core)" restore Formula/[email protected]

after attempting this.

[carlocab]

Also attempting a fix at Homebrew/homebrew-core#90319.

[carlocab]

I've merged the fix, so you shouldn't need the brew edit workaround above.

[resistorsoftware]

Absolute Magic! I ran brew update and then the Postgres update was able to run. Unfortunately, I was naive and forgot that this command is dangerous! So I am left with a borked system. I remember the last time this happened. I just walked away from my old DB and started with an empty new one. Hanging my head in shame.

==> Upgrading postgresql data from 13 to 14...
Stopping postgresql... (might take a while)
==> Successfully stopped postgresql (label: homebrew.mxcl.postgresql)
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2021-12-02 22:59:08.948 EST [5337] LOG: starting PostgreSQL 13.5 on x86_64-apple-darwin19.6.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit
2021-12-02 22:59:08.951 EST [5337] LOG: listening on IPv6 address "::1", port 5432
2021-12-02 22:59:08.951 EST [5337] LOG: listening on IPv4 address "127.0.0.1", port 5432
2021-12-02 22:59:08.952 EST [5337] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-12-02 22:59:08.972 EST [5338] LOG: database system was interrupted; last known up at 2021-12-02 14:20:35 EST
2021-12-02 22:59:09.367 EST [5338] LOG: database system was not properly shut down; automatic recovery in progress
2021-12-02 22:59:09.373 EST [5338] LOG: redo starts at 9/CD859770
2021-12-02 22:59:09.373 EST [5338] LOG: invalid record length at 9/CD859858: wanted 24, got 0
2021-12-02 22:59:09.373 EST [5338] LOG: redo done at 9/CD859820
2021-12-02 22:59:09.391 EST [5337] LOG: database system is ready to accept connections
done
server started
waiting for server to shut down....2021-12-02 22:59:10.306 EST [5337] LOG: received fast shutdown request
2021-12-02 22:59:10.306 EST [5337] LOG: aborting any active transactions
2021-12-02 22:59:10.307 EST [5337] LOG: background worker "logical replication launcher" (PID 5344) exited with exit code 1
2021-12-02 22:59:10.307 EST [5339] LOG: shutting down
2021-12-02 22:59:10.312 EST [5337] LOG: database system is shut down
done
server stopped
==> Moving postgresql data from /usr/local/var/postgres to /usr/local/var/postgres.old...
==> Creating database...
Error: Upgrading postgresql data from 13 to 14 failed!
==> Moving postgresql data back from /usr/local/var/postgres.old to /usr/local/var/postgres...
==> Successfully started postgresql (label: homebrew.mxcl.postgresql)
Error: Failure while executing; /usr/local/Cellar/postgresql/14.1_1/bin/initdb --lc-collate=C --lc-ctype=C --lc-messages=C --lc-monetary=C --lc-numeric=C --lc-time=C -E\ UTF8 /usr/local/var/postgres exited with 1.

[carlocab]

Oops. I don't use PostgreSQL, so I'm afraid I'm unable to help you there. We're happy to review pull requests that make the postgresql-upgrade-database command safer.

[resistorsoftware]

No trouble. I appreciate the effort!