Too long don't read
I wanted to upgrade some things through HomeBrew, but it seems like it broke my Postgres.
I'm on MacOS. I need to be able to run my Postgres again. Deleting without backups isn't much of a problem: this is a local dev setup.
Long sequence of operations for upgrading and debugging
I ran:
brew updatebrew upgrade
Which output:
==> Upgrading 10 outdated packages:
postgresql 13.3 -> 14.1_1
==> Upgrading postgresql
13.3 -> 14.1_1
==> Pouring postgresql--14.1_1.monterey.bottle.tar.gz
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
brew postgresql-upgrade-database
This formula has created a default database cluster with:
initdb --locale=C -E UTF-8 /usr/local/var/postgres
For more details, read:
https://www.postgresql.org/docs/14/app-initdb.html
To restart postgresql after an upgrade:
brew services restart postgresql
Or, if you don't want/need a background service you can just run:
/usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres
==> Summary
/usr/local/Cellar/postgresql/14.1_1: 3,304 files, 43.9MB
==> Running `brew cleanup postgresql`...
Removing: /usr/local/Cellar/postgresql/13.3... (3,230 files, 42.7MB)
==> Casks with 'auto_updates true' or 'version :latest' will not be upgraded; pass `--greedy` to upgrade them.
==> Upgrading 2 outdated packages:
pgadmin4 5.2 -> 6.2
visualvm 2.0.7 -> 2.1.1
==> Upgrading pgadmin4
==> Downloading https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v6.2/macos/pgadmin4-6.2.dmg
######################################################################## 100.0%
==> Backing App 'pgAdmin 4.app' up to '/usr/local/Caskroom/pgadmin4/5.2/pgAdmin 4.app'
==> Removing App '/Applications/pgAdmin 4.app'
==> Moving App 'pgAdmin 4.app' to '/Applications/pgAdmin 4.app'
==> Purging files for version 5.2 of Cask pgadmin4
pgadmin4 was successfully upgraded!
==> Upgrading visualvm
==> Caveats
visualvm requires Java. You can install the latest version with:
brew install --cask temurin
==> Downloading https://github.com/oracle/visualvm/releases/download/2.1.1/VisualVM_211.dmg
==> Downloading from https://objects.githubusercontent.com/github-production-release-asset-2e65be/68017978/68ae1458-d0e0-40f3-b429-1dec7eb5b703?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20211208%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20211208T200243Z&X-Amz-Expires=300&X-Amz-Signature=e33c4cfaaba1afeb72a08a34daed39b82de9ebc342
######################################################################## 100.0%
==> Backing App 'VisualVM.app' up to '/usr/local/Caskroom/visualvm/2.0.7/VisualVM.app'
==> Removing App '/Applications/VisualVM.app'
==> Moving App 'VisualVM.app' to '/Applications/VisualVM.app'
==> Purging files for version 2.0.7 of Cask visualvm
visualvm was successfully upgraded!
==> Caveats
==> postgresql
To migrate existing data from a previous major version of PostgreSQL run:
brew postgresql-upgrade-database
This formula has created a default database cluster with:
initdb --locale=C -E UTF-8 /usr/local/var/postgres
For more details, read:
https://www.postgresql.org/docs/14/app-initdb.html
To restart postgresql after an upgrade:
brew services restart postgresql
Or, if you don't want/need a background service you can just run:
/usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres
==> visualvm
visualvm requires Java. You can install the latest version with:
brew install --cask temurin
Then when I'd try to run my Postgres server with the usual
pg_ctl -D /usr/local/var/postgres start
I would get
waiting for server to start....2021-12-08 23:44:24.182 EST [17879] FATAL: database files are incompatible with server
2021-12-08 23:44:24.182 EST [17879] DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.
stopped waiting
pg_ctl: could not start server
Examine the log output.
Then using
tail -n 10 /usr/local/var/log/postgres.log
I see
2021-12-08 23:30:30.897 EST [15687] FATAL: database files are incompatible with server
2021-12-08 23:30:30.897 EST [15687] DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.
2021-12-08 23:30:40.982 EST [15949] FATAL: database files are incompatible with server
2021-12-08 23:30:40.982 EST [15949] DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.
2021-12-08 23:30:51.066 EST [15954] FATAL: database files are incompatible with server
2021-12-08 23:30:51.066 EST [15954] DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.
2021-12-08 23:31:01.154 EST [15960] FATAL: database files are incompatible with server
2021-12-08 23:31:01.154 EST [15960] DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.
2021-12-08 23:31:11.227 EST [16453] FATAL: lock file "postmaster.pid" already exists
2021-12-08 23:31:11.227 EST [16453] HINT: Is another postgres (PID 16451) running in data directory "/usr/local/var/postgres"?
As you can see, the timestamp don't match. That's actually because I've also been trying to start the service with
brew services start postgres
And that is the process that's been outputting logs there.
If I look into /usr/local/Cellar I see:
drwxr-xr-x 3 i548042 admin 96 8 Dec 23:15 postgresql
drwxr-xr-x 3 i548042 admin 96 8 Dec 23:29 postgresql@13
So something seems wrong.
psql --version
does output
psql (PostgreSQL) 14.1
And if I run the HomeBrew's suggested
brew postgresql-upgrade-database
I get:
==> Upgrading postgresql data from 13 to 14...
waiting for server to start....2021-12-08 23:48:02.918 EST [18573] LOG: starting PostgreSQL 13.5 on x86_64-apple-darwin21.1.0, compiled by Apple clang version 13.0.0 (clang-1300.0.29.3), 64-bit
2021-12-08 23:48:02.920 EST [18573] LOG: listening on IPv6 address "::1", port 5432
2021-12-08 23:48:02.920 EST [18573] LOG: listening on IPv4 address "127.0.0.1", port 5432
2021-12-08 23:48:02.921 EST [18573] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-12-08 23:48:02.933 EST [18574] LOG: database system was shut down at 2021-12-08 23:31:28 EST
2021-12-08 23:48:02.939 EST [18573] LOG: database system is ready to accept connections
done
server started
waiting for server to shut down...2021-12-08 23:48:03.275 EST [18573] LOG: received fast shutdown request
.2021-12-08 23:48:03.275 EST [18573] LOG: aborting any active transactions
2021-12-08 23:48:03.276 EST [18573] LOG: background worker "logical replication launcher" (PID 18580) exited with exit code 1
2021-12-08 23:48:03.276 EST [18575] LOG: shutting down
2021-12-08 23:48:03.285 EST [18573] 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...
The files belonging to this database system will be owned by user "i548042".
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 ... America/Toronto
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 system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
loadable_libraries.txt
Failure, exiting
Error: Upgrading postgresql data from 13 to 14 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@13/13.5_1/bin -B /usr/local/opt/postgresql/bin -d /usr/local/var/postgres.old -D /usr/local/var/postgres -j 16` exited with 1.
Doing the suggested
/usr/local/opt/postgresql/bin/pg_ctl -D /usr/local/var/postgres -l logfile start
gives me
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
So I use again the command to look into logs. Same thing:
2021-12-08 23:49:45.779 EST [18758] FATAL: database files are incompatible with server
2021-12-08 23:49:45.779 EST [18758] DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.
I also tried many different other solutions like combinations of brew uninstall and brew install for postgres and postgresql@13, and also looked online for a way to manually install psql on version 13 since that seems to be the culprit here.
Then I went ahead and tried to mess with the symlink in ~/Library/LaunchAgents by removing and recreating it (and I've most probably messed something up at that point too, but things were already broken beforehand).
Goal
Anyways, all of this to say: this is extremely annoying, and I don't understand what I can do to finally be able to start Postgres again.
I don't care about the version, and I don't care about losing the data on the DB. If I need to delete folders, I will. I already have dumps of the relevant info, and anyways this is a local dev environment, not a prod one.