FB

Solution for Homebrew PostgreSQL Service Not Starting

If you’re on a Mac and trying to run Postgres using Homebrew, you might have problems starting the service.

In this post, I’ll explain the issue and the solution.

Starting Postgres

So you’ve installed Postgres using Homebrew.

You’ve started Postgres using this command:

brew services start postgresql

Here’s the output of that command.

==> Successfully started postgresql (label: homebrew.mxcl.postgresql)

Let’s see all of the Homebrew services that are running:

brew services list

The output could look something like this:

Name       Status   User File
mysql      started  BB ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
postgresql error    BB ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

I’ve got both mysql and PostgreSQL listed as I have installed both using Homebrew.

However, the PostgreSQL service is showing an error.

That’s strange.

If I try to run the start command again, I’ll get a different output:

brew services start postgresql

Bootstrap failed: 5: Input/output error
Try re-running the command as root for richer errors.
Error: Failure while executing; /bin/launchctl bootstrap gui/501 /Users/BB/Library/LaunchAgents/homebrew.mxcl.postgresql.plist exited with 5.

 

The Error

The status says an error has occurred.

I can try to connect to Postgres anyway to see what happens. I’ve read that sometimes it still works even though the service shows an error.

I’ll run this psql command to try to connect to the postgres database.

psql -d postgres

Here’s the output:

psql: error: connection to server on socket “/tmp/.s.PGSQL.5432” failed: No such file or directory Is the server running locally and accepting connections on that socket?

This output indicates the database is not running.

Is there something else running on post 5432?

I can run the lsof command to check:

lsof -i tcp:5432

There’s no output from this command, so no other services are running.

Let’s look into the Postgres service further. I can run the info command in Homebrew to see more about the Postgres service.

brew services info postgresql

Running: ✘
Loaded: ✔
Schedulable: ✘

This shows that the PostgreSQL service is loaded but not running.

Let’s see the list of services again.

brew services list

Name       Status  User File
mysql      started BB ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
postgresql error   BB ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

In the User File column is a plist file, which has a set of configuration options for the service.

Let’s open it using the cat command. I found this easier than trying to browse to the file using Finder.

cat ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Here’s the output:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "<http://www.apple.com/DTDs/PropertyList-1.0.dtd>">
<plist version="1.0">
<dict>
	<key>KeepAlive</key>
	<true/>
	<key>Label</key>
	<string>homebrew.mxcl.postgresql</string>
	<key>ProgramArguments</key>
	<array>
		<string>/usr/local/opt/postgresql/bin/postgres</string>
		<string>-D</string>
		<string>/usr/local/var/postgres</string>
	</array>
	<key>RunAtLoad</key>
	<true/>
	<key>StandardErrorPath</key>
	<string>/usr/local/var/log/postgres.log</string>
	<key>StandardOutPath</key>
	<string>/usr/local/var/log/postgres.log</string>
	<key>WorkingDirectory</key>
	<string>/usr/local</string>
</dict>
</plist>

It’s an XML file. One of the attributes near the bottom is called StandardErrorPath, and refers to an error file:

<key>StandardErrorPath</key>
<string>/usr/local/var/log/postgres.log</string>

Let’s look into this file to see what it says.

cat /usr/local/var/log/postgres.log

Here’s the output:

2023-08-10 16:43:31.204 AEST [42321] FATAL: database files are incompatible with server
2023-08-10 16:43:31.204 AEST [42321] DETAIL: The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 14.2.

This seems to indicate there is an incompatibility with the versions of Postgres.

Let’s check what version of PostgreSQL we have installed.

psql — version

psql (PostgreSQL) 14.2

This is helpful to know.

 

The Solution

The error indicates that there is a directory that is set up for Postgres 12, but the version I have just installed using Homebrew is version 14.2. This new Postgres version can’t be started because the versions don’t match.

This could have happened because I have previously installed older versions of Postgres. Perhaps this new installation is using the same directory and files and is causing an issue.

To resolve this, we can use a Homebrew command to migrate the files and update the version:

brew postgresql-upgrade-database

If I run this command, I’ll see a lot of output. Here are a few of the things it shows:

==> brew install postgresql@12

==> Downloading https://ghcr.io/v2/homebrew/core/postgresql/12/manifests/12.10_1 ######################################################################## 100.0%

==> Running brew cleanup postgresql@12...

==> Upgrading postgresql data from 12 to 14...

==> Successfully stopped postgresql (label: homebrew.mxcl.postgresql)

==> Moving postgresql data from /usr/local/var/postgres to /usr/local/var/postgres.old

==> Creating database... ==> Migrating and upgrading data...

==> Upgraded postgresql data from 12 to 14!

==> Your postgresql 12 data remains at /usr/local/var/postgres.old

==> Successfully started postgresql (label: homebrew.mxcl.postgresql)

This has been completed successfully.

This process includes migrating the old Postgres service to the postgres.old directory, and upgrading the existing folder to version 14. It also starts the Postgres service.

We can list the services again to check:

brew services list

Here’s the output.

Name       Status  User File
mysql      none
postgresql started BB   ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

PostgreSQL now shows “started”. This is good news.

Let’s try to connect.

psql -d postgres
Ben-2022-MBP:~ BB$ psql -d postgres psql (14.2) Type “help” for help.
postgres=#

This is the PostgreSQL prompt, which means we have connected and can run queries.

Let’s run a simple SELECT query.

postgres=# SELECT ‘x’;
?column?
———-
x
(1 row)
postgres=#

The query has run and shows a value of ‘x’.

Our issue has been resolved!

In summary, we had a version conflict issue when attempting to start the PostgreSQL service on Homebrew. We checked the logs and ran an upgrade command, which upgraded the database.

If you get any other errors during this process, put them into Google and check some StackOverflow answers. I found those useful when attempting to fix this issue.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.