HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Homebrew installed PostgreSQL could not connect, database files are incompatible with server

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlconnectarewithcoulddatabasehomebrewfilesserverinstalled

Problem

Here's the error message I'm getting today when I attempt to log in to my localhost PosgreSQL databases using psql:

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


I installed PostgreSQL using Homebrew and this morning I've uninstalled and reinstalled using that utility, with no change observable. The psql client works fine, and I can still use it to connect to databases over the network. I only connect to my local databases once every month or two, and it worked fine before, so I'm assuming this is due to some automatic update but I don't know which. Mac OS upgraded itself to High Sierra (10.13) recently.

The same error message popped up on here in 2014 (link) but that was a case where a guy had trouble starting the server. In my case I believe it's running -- I've used brew services start postgresql and yes, I've restarted a few times today. However, the file mentioned in the error, /tmp/.s.PGSQL.5432, does not exist on my system. I think this is a case of some configuration mistake. Anyone have a clue how to fix?

Update: I tried starting the server manually (as recommended by Evan) and I think I made a breakthrough. Here's the error I get when I try to start it manually:

FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.1.


Update 2: Ultimately I solved this problem by replacing my data directory and re-initializing it with an empty database using initdb as in the answer marked "correct". To be fair, all three answers were helpful. I was unable to update the database from the old version to the new one because I no longer had the binaries for the older version of Postgres.

Solution

If you don't have any data, then it seems like a problem with Brew. Did you run brew upgrade, there are two answers on StackOverflow that address this, I've merged both of them together to try the best of both worlds.

-
Backing up

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
sudo mv /usr/local/var/postgres /usr/local/var/.postgres_bak;


-
Making sure you have the latest version

brew update
brew upgrade postgresql


-
Either, not both reinitialize or try to upgrade your data-dir

-
Nuke it (start from scratch, leaving the old back up)

initdb /usr/local/var/postgres -E utf8


-
Try to upgrade it

pg_upgrade -b /usr/local/Cellar/postgresql/$myOldVersion/bin/ -B /usr/local/Cellar/postgresql/$myNewVersion/bin -d /usr/local/var/.postgres_bak -D /usr/local/var/postgres


-
Restart server.

brew services start postgresql


-
Delete the backup, if the server restarts and everything works remove the backup

sudo rm -rf /usr/local/var/.postgres_bak

Code Snippets

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
sudo mv /usr/local/var/postgres /usr/local/var/.postgres_bak;
brew update
brew upgrade postgresql
initdb /usr/local/var/postgres -E utf8
pg_upgrade -b /usr/local/Cellar/postgresql/$myOldVersion/bin/ -B /usr/local/Cellar/postgresql/$myNewVersion/bin -d /usr/local/var/.postgres_bak -D /usr/local/var/postgres
brew services start postgresql

Context

StackExchange Database Administrators Q#191328, answer score: 21

Revisions (0)

No revisions yet.