Problem upgrading Postgres to V13 on Big Sur

[SteveHawes]

I'm trying to upgrade Postgres to V13 and am getting the following error:

`stevehawes@Steves-MBP postgres % brew postgresql-upgrade-database
==> Upgrading postgresql data from 12 to 13...
waiting for server to start....2020-12-31 17:46:10.254 WET [13927] LOG: starting PostgreSQL 12.5 on x86_64-apple-darwin20.1.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.27), 64-bit
2020-12-31 17:46:10.255 WET [13927] LOG: listening on IPv6 address "::", port 5432
2020-12-31 17:46:10.255 WET [13927] LOG: listening on IPv4 address "0.0.0.0", port 5432
2020-12-31 17:46:10.266 WET [13927] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-31 17:46:10.299 WET [13927] LOG: redirecting log output to logging collector process
2020-12-31 17:46:10.299 WET [13927] HINT: Future log output will appear in directory "log".
done
server started
waiting for server to shut down.... done
server stopped
==> Moving postgresql data from /usr/local/var/postgres to /usr/local/var/postgres.old...
==> Creating database...
The files belonging to this database system will be owned by user "stevehawes".
This user must also own the server process.

The database cluster will be initialized with locale "C".
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 dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Atlantic/Canary
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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

==> Migrating and upgrading data...
Performing Consistency Checks

Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating dump of global objects ok
Creating dump of database schemas
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 target postmaster started with the command:
"/usr/local/opt/postgresql/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/usr/local/var/postgres" -o "-p 50432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0 -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/usr/local/var/log'" start
Failure, exiting
Error: Upgrading postgresql data from 12 to 13 failed!
==> Removing empty postgresql initdb database...
==> Moving postgresql data back from /usr/local/var/postgres.old to /usr/local/var/postgres...
Error: Failure while executing; /usr/local/opt/postgresql/bin/pg_upgrade -r -b /usr/local/Cellar/postgresql@12/12.5/bin -B /usr/local/opt/postgresql/bin -d /usr/local/var/postgres.old -D /usr/local/var/postgres -j 12 exited with 1.
`

Any ideas anyone?

[SMillerDev]

Did you try the suggestion?

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

[SteveHawes]

I thought I had - I wrongly assumed was in the log I posted as:

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"?

I have now located the log file and the actual error is:

-----------------------------------------------------------------
  pg_upgrade run on Fri Jan  1 11:45:57 2021
-----------------------------------------------------------------

command: "/usr/local/Cellar/postgresql@12/12.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
waiting for server to start....2021-01-01 11:45:57.858 WET [29915] LOG:  starting PostgreSQL 12.5 on x86_64-apple-darwin20.1.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.27), 64-bit
2021-01-01 11:45:57.859 WET [29915] LOG:  listening on Unix socket "/usr/local/var/log/.s.PGSQL.50432"
2021-01-01 11:45:57.885 WET [29915] LOG:  redirecting log output to logging collector process
2021-01-01 11:45:57.885 WET [29915] HINT:  Future log output will appear in directory "log".
 done
server started


command: "/usr/local/Cellar/postgresql@12/12.5/bin/pg_ctl" -w -D "/usr/local/var/postgres.old" -o "" -m smart stop >> "pg_upgrade_server.log" 2>&1
waiting for server to shut down.... done
server stopped


command: "/usr/local/opt/postgresql/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/usr/local/var/postgres" -o "-p 50432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/usr/local/var/log'" start >> "pg_upgrade_server.log" 2>&1
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2021-01-01 11:46:12.022 WET [30205] FATAL:  lock file "postmaster.pid" already exists
2021-01-01 11:46:12.022 WET [30205] HINT:  Is another postmaster (PID 29929) running in data directory "/usr/local/var/postgres"?
 stopped waiting
pg_ctl: could not start server
Examine the log output.

I have checked the relevant directories for the postmaster.pid file (even done a sudo find / -name postmaster.pid -print) and nothing is found, the PID mentioned is not a running process and if I try the upgrade again the PID always changes and is close to the the PID for the first server started so I'm guessing it is a process that is being started by the upgrade script.

Any ideas on how to debug further?

TIA
Steve

[SteveHawes]

OK - after A LOT of trial and error I have found the problem. The brew postgresql-upgrade-database script calls the pg_upgrade command and one of the parameters it passes is related to the number of simultaneous processes or threads to use (-j or --jobs). In my particular case it was using -j 12.

Running the upgrade process manually without this parameter allowed the upgrade to proceed smoothly without error. No idea why this would be the case but hopefully this post will help someone else in this situation.

Thanks
Steve

[whatcould]

I had the same issue; I was not able to manually run the upgrade process without the -j parameter (as @SteveHawes suggested), because it seemed to depend on some series of commands.

But I was able to resolve the error by stopping the postgres service (grrrrr), eg brew services stop postgresql, before running the upgrade command (brew postgresql-upgrade-database).

[Hermanverschooten]

I just ran into the same issue when upgrading to 14, they might mention this...

[harismh]

Ditto for me when upgrading 14. Had to stop, upgrade, then start in order for psql and other connections to work again, ex.

brew services stop postgresql
brew postgresql-upgrade-database
brew services start postgresql

[foreverLoveWisdom]

This works for me. I have installed a 3-party lib on Mac and Brew automatically updated PostgreSQL on my machine and it got stuck, and kept throwing at me the connection error at port 5432. I tried different ways to check if there were any other connections to the port 5432, and I saw none. Took me a while to figure this out. Thanks again

[will]

I upgraded today from 13.something to 14.1 . I also needed to manually run brew services stop postgresql to get it to work after finding this issue.

Would it be possible for brew postgresql-upgrade-database to take care of this automatically?

[gromgit]

According to https://github.com/Homebrew/homebrew-core/blob/cc9c50003a58b1e8ce42dbed8bbd57c6d8087902/cmd/postgresql-upgrade-database.rb#L70-L81, that should already be happening for both new and old instances.

What was the output from brew postgresql-upgrade-database, before manually doing a services stop?