Passionate Development

Cannot find a running Postgres process in MacOSX

Mysterious case of dissapearing postgres process

Had a curious issue of not being able to connect to Postgres the other day.

it was quite baffling because listing the services via brew services list - it does indicate that postgres is running.

➜ brew services list
Name       Status  User           Plist
postgresql started FTjandrawibawa /Users/FTjandrawibawa/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
redis      started FTjandrawibawa /Users/FTjandrawibawa/Library/LaunchAgents/homebrew.mxcl.redis.plist

However ps -ef doesn’t show any postgres process.

➜ ps -ef|grep postgresql
  502  4764  1512   0  1:43pm ttys001    0:00.00 grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn postgresql

And sure enough psql couldn’t find a running server to connect to.

➜  psql
psql: error: could not connect to server: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

When in doubts, check the logs

Stumbled upon the following command from stackoverflow:

brew services restart -vvv postgresql

I have been using the restart command, but I haven’t come across the -vvv option which gives us a few more details about the process.

It spits out the generated plist for postgress and from there I saw the location of postgres log in /usr/local/var/log/postgres.log

:facepalm:, how could I forgot to check the logs?

So looking at the logs, it immediately obvious what the issue was.

2019-12-27 13:51:59.077 AEDT [5223] FATAL:  database files are incompatible with server
2019-12-27 13:51:59.077 AEDT [5223] DETAIL:  The data directory was initialized by PostgreSQL version 11, which is not compatible with this version 12.1.

I just did a brew upgrade earlier that day, and I must have upgraded postgres version from 11 to 12.

Upgrade postgres from 11 to 12

Fortunately, homebrew gives us a utitlity to upgrade postgres data directory, found this from running brew info postgres.

The command is:

brew postgresql-upgrade-database

And when it’s done

➜ ps -ef|grep postgres
  502  6744     1   0  1:53pm ??         0:00.04 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres
  502  6753  6744   0  1:53pm ??         0:00.00 postgres: checkpointer
  502  6754  6744   0  1:53pm ??         0:00.02 postgres: background writer
  502  6755  6744   0  1:53pm ??         0:00.02 postgres: walwriter
  502  6756  6744   0  1:53pm ??         0:00.01 postgres: autovacuum launcher
  502  6757  6744   0  1:53pm ??         0:00.05 postgres: stats collector
  502  6758  6744   0  1:53pm ??         0:00.00 postgres: logical replication launcher
  502  6837  1512   0  1:54pm ttys001    0:00.00 grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn postgres

Winning!