Homebrew-core: brew postgresql-upgrade-database fails from 10 -> 11

[maxnordlund]

Please note we will close your issue without comment if you delete, do not read or do not fill out the issue checklist below and provide ALL the requested information. If you repeatedly fail to use the issue template, we will block you from ever submitting issues to Homebrew again.

• [x] are reporting a bug others will be able to reproduce and not asking a question. If you're not sure or want to ask a question do so on our Discourse: https://discourse.brew.sh
• ~~ [ ] have a problem with brew install (or upgrade, reinstall) a single, official formula (not cask)? If it's a general brew problem please file this issue at Homebrew/brew: https://github.com/Homebrew/brew/issues/new/choose. If it's a brew cask problem please file this issue at https://github.com/Homebrew/homebrew-cask/issues/new/choose. If it's a tap (e.g. Homebrew/homebrew-php) problem please file this issue at the tap.~~
• [x] ran brew update and can still reproduce the problem?
• [x] ran brew doctor, fixed all issues and can still reproduce the problem?
• [ ] ran brew gist-logs <formula> (where <formula> is the name of the formula that failed) and included the output link?
• [x] if brew gist-logs didn't work: ran brew config and brew doctor and included their output with your issue? _Since this is not about a formula see the attached logs below_

To help us debug your issue please explain:

• What you were trying to do (and why)
• What happened (include command output)
• What you expected to happen
• Step-by-step reproduction instructions (by running brew install commands)

Hi, as some already may have experienced, I got this error when running brew postgresql-upgrade-database just a couple of minutes ago. I think it forgets to start the server, but I'm not sure.

I did run brew update; brew upgrade postgres just before, and brew doctor seems happy enough. It saw python greelet headers and a clingo/clasp linking conflict. Neither of which seem relevant to this though.

output of brew postgresql-upgrade-database

==> Upgrading postgresql data from 10 to 11...
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
==> Moving postgresql data from /usr/local/var/postgres to /usr/local/var/postgres.old...
The files belonging to this database system will be owned by user "maxno".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/var/postgres ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/opt/postgresql/bin/pg_ctl -D /usr/local/var/postgres -l logfile start

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/usr/local/var/log/.s.PGSQL.50432"?

could not connect to source postmaster started with the command:
"/usr/local/Cellar/postgresql/10.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/usr/local/var/postgres.old" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/usr/local/var/log'" start
Failure, exiting
Error: Upgrading postgresql data from 10 to 11 failed!
==> Removing empty postgresql initdb database...
==> 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/opt/postgresql/bin/pg_upgrade -r -b /usr/local/Cellar/postgresql/10.5/bin -B /usr/local/opt/postgresql/bin -d /usr/local/var/postgres.old -D /usr/local/var/postgres -j 4` exited with 1.

brew {dotor,config}

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: Unbrewed header files were found in /usr/local/include.
If you didn't put them there on purpose they could cause problems when
building Homebrew formulae, and may need to be deleted.

Unexpected header files:
  /usr/local/include/python2.7/greenlet/greenlet.h
  /usr/local/include/python3.6m/greenlet/greenlet.h
  /usr/local/include/python3.7m/greenlet/greenlet.h

Warning: You have unlinked kegs in your Cellar
Leaving kegs unlinked can lead to build-trouble and cause brews that depend on
those kegs to fail to run properly once built. Run `brew link` on these:
  clingo
HOMEBREW_VERSION: 1.8.4-47-g1755797
ORIGIN: https://github.com/Homebrew/brew.git
HEAD: 175579785d623021ecb985135f665e15264c9b67
Last commit: 3 hours ago
Core tap ORIGIN: https://github.com/Homebrew/homebrew-core
Core tap HEAD: fd240072b57313f9dd9b87904f89de4452599ae1
Core tap last commit: 4 hours ago
HOMEBREW_PREFIX: /usr/local
HOMEBREW_DEV_CMD_RUN: 1
HOMEBREW_GITHUB_API_TOKEN: set
HOMEBREW_NO_ANALYTICS: 1
CPU: quad-core 64-bit haswell
Homebrew Ruby: 2.3.7 => /System/Library/Frameworks/Ruby.framework/Versions/2.3/usr/bin/ruby
Clang: 10.0 build 1000
Git: 2.19.2 => /usr/local/bin/git
Curl: 7.54.0 => /usr/bin/curl
Java: 1.8.0_66, 1.8.0_25, 1.8.0_11, 1.8.0_05, 1.7.0_67
macOS: 10.13.6-x86_64
CLT: 10.1.0.0.1.1539992718
Xcode: 10.1

[superscott]

My upgrade issue is unrelated to [email protected] not being available via Homebrew.
Sorry for the distraction.

TL,DR; I think if homebrew allows us to download our 10.x version we should be able to download the psql 10.x version separately, which _might_ allow brew postgresql-upgrade-database to correctly grab the old bin and allow the upgrade from 10.x to 11.x. Either way, being able to download 10.x from homebrew would let us manually run the pg_upgrade utility.

+1 For having this issue as well.
I _think_ there are two things at play here.

1. brew postgresql-upgrade-database requires the bin from the "old" version as well as the "new" version. This homebrew command is using the pg_upgrade functionality which is further explained in the Postgresql docs.

2. Homebrew _somehow_ is not showing any of the postgresql versions between 11.x and 9.x, and therefore we are not able to get a bin for the 10.x version that was previously installed.

┌─[~] [2018-12-07 11:28:51]
└─[0] <> brew search postgresql
==> Formulae
postgresql ✔                                              [email protected]                                            [email protected]                                            [email protected]

==> Casks
navicat-for-postgresql

Additional anecdotes
- I upgraded by running this which I'm sure did not help my personal situation:

brew update && brew upgrade && brew cleanup && brew prune

Where brew cleanup caused my 10.6 data to be removed before I could run brew postgresql-upgrade-database

Removing: /usr/local/Cellar/postgresql/10.6... (1,706 files, 20.8MB)
Removing: /Users/bishop/Library/Caches/Homebrew/postgresql--10.6.high_sierra.bottle.tar.gz... (7.0MB)

-I was running Postgresql 10.6 prior to updating today.

[samgranieri]

I had to rebuild my postgres cluster on my work laptop because brew postgresql-upgrade-database. I didn't mind, but it was a bit inconvenient. I've usually used instructions from articles like this: https://olivierlacan.com/posts/migrating-homebrew-postgres-to-a-new-version/

[MikeMcQuaid]

I had to rebuild my postgres cluster on my work laptop because brew postgresql-upgrade-database. I didn't mind, but it was a bit inconvenient.

@samgranieri Please consider submitting a pull request to address these issues.

Consult the last few lines of "pg_upgrade_server.log"

@maxnordlund It looks like pg_upgrade is the command that failed. We don't control that.

[alastair]

It looks like this may be related to a simultaneous upgrade of icu4c from v62 to v63. Postgres 10 requires 62 while postgres 11 requires 63.

From pg_upgrade_server.log:

command: "/usr/local/Cellar/postgresql/10.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/usr/local/var/postgres.old" -o "-p 50432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/usr/local/var/log'" start >> "pg_upgrade_server.log" 2>&1
dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.62.dylib
Referenced from: /usr/local/Cellar/postgresql/10.5/bin/postgres
Reason: image not found

Changing to the old version of icu4c allows postgres 10 to be run:

$ brew switch icu4c 62.1
$ /usr/local/Cellar/postgresql/10.5/bin/postgres
  postgres does not know where to find the server configuration file.
  You must specify the --config-file or -D invocation option or set the PGDATA environment variable.

However then when running brew postgresql-upgrade-database again it fails to run the postgres 11 commands (in this case when running initdb for v11 to create the new cluster):

dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.63.dylib
Referenced from: /usr/local/Cellar/postgresql/11.1/bin/postgres
Reason: image not found
no data was returned by command ""/usr/local/Cellar/postgresql/11.1/bin/postgres" -V"
The program "postgres" is needed by initdb but was not found in the
same directory as "/usr/local/Cellar/postgresql/11.1/bin/initdb".
Check your installation.

I'm not sure if it's possible to link both versions of icu4c into /usr/local/opt/icu4c/lib/ at the same time, or for postgres to use the specific versions in /usr/local/Cellar/icu4c/62.1 instead?

[MikeMcQuaid]

The issue is that a postgresql@10 formula needs to be created. I'm adding a brew audit in https://github.com/Homebrew/brew/pull/5390 to ensure that happens in future. https://github.com/Homebrew/homebrew-core/pull/34930 should address this when merged.

[CamJN]

Having just blown up my postgres install this doesn't seem to have fixed the icu4c issue.

[MikeMcQuaid]

It might not but the versioned formula should render it unnecessary. If desired support for using the Cellar versions could be removed entirely.

[RazerM]

I also had problems with brew postgresql-upgrade-database, with either the old or new version complaining about the wrong icu4c version depending on which I switched to.

In the meantime I'm using postgresql@10:

brew unlink postgresql
brew link --force postgresql@10

[acrogenesis]

That didn't work for me I ran

brew switch postgresql 10.6 

and it worked.

[Mazorius]

I found out it's a permission problem. After I set the permission from the 10th version postgres data folder to 0700 (recursively) it worked.