6

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.

  • Assuming Postgres is running on 5432, does specifying the host make a difference? eg. psql -h 127.0.0.1 -d postgres. /tmp/.s.PGSQL.5432 exists on my mac (still on Sierra, fwiw), but I also started it using pg_ctl -D $DATADIR start, not via the brew services start command. – bma Dec 6 '17 at 13:59
  • Is the only PostgreSQL installed on your system through brew? – Evan Carroll Dec 6 '17 at 17:37
6
+50

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.

  1. Backing up

    launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
    sudo mv /usr/local/var/postgres /usr/local/var/.postgres_bak;
    
  2. Making sure you have the latest version

    brew update
    brew upgrade postgresql
    
  3. Either, not both reinitialize or try to upgrade your data-dir

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

      initdb /usr/local/var/postgres -E utf8
      
    2. 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
      
  4. Restart server.

    brew services start postgresql
    
  5. Delete the backup, if the server restarts and everything works remove the backup

    sudo rm -rf /usr/local/var/.postgres_bak
    
  • 1
    Ultimately I went with "nuke it" because the old version binaries were no longer present. – workerjoe Dec 8 '17 at 2:56
  • 2
    You got a typo in /usr/local/bar/.postgres_bak;. Should be /usr/local/var/.postgres_bak;. I'm too annoyed by the SO validation wanting me to edit more than 6 chars so I won't try to work around it and leave a comment instead – Benjamin Crouzier Jul 3 '18 at 14:16
1

Hopefully you haven't run brew cleanup postgresql yet. brew info gives all the relevant info, including if you still have the previous version and the fact you might need to run pg_upgrade (shortened output below).

This will tell you what version your db is on: cat /usr/local/var/postgres/PG_VERSION

If you didn't mean to upgrade to PostgreSQL 10.1, and assuming your previous version is 9.6.6, this could be enough: brew switch postgresql 9.6.6

If you need to reinstall an older version, this could work: https://stackoverflow.com/a/9832084/279726

It looks like the data directory is /usr/local/var/postgres by default (as I haven't changed mine), see last line of output below.

$ brew info postgresql
postgresql: stable 10.1 (bottled), HEAD
[...]
==> Caveats
To migrate existing data from a previous major version of PostgreSQL, see:
  https://www.postgresql.org/docs/10/static/upgrading.html

  You will need your previous PostgreSQL installation from brew to perform
  `pg_upgrade` or `pg_dumpall` depending on your upgrade method.

  Do not run `brew cleanup postgresql` until you have performed the migration.

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start
$

But if you do want to use 10.1, basically you could run something like this, still assuming you were running 9.6.6 before (change directory name based on your actual previous PostgreSQL version). Run one command at a time and check the output before proceeding with the next:

mv -i /usr/local/var/postgres /usr/local/var/postgres96
mkdir /usr/local/var/postgres
pg_upgrade -b /usr/local/Cellar/postgresql/9.6.6/bin -B /usr/local/Cellar/postgresql/10.1/bin -d /usr/local/var/postgres96 -D /usr/local/var/postgres

See here for details: https://www.postgresql.org/docs/10/static/pgupgrade.html

********************************************************
* PLEASE MAKE BACKUPS BEFORE DOING ANY OF THE ABOVE!!! *
********************************************************

The funny thing is, if I try to install the latest PostgreSQL I get:

$ brew install postgresql
Error: postgresql 9.5.5 is already installed
To upgrade to 10.1, run `brew upgrade postgresql`

So I'm not sure how you got from 9.6.x to 10.1 without seeing that message...

  • Oh I've been through at least a few version upgrades before this one, that didn't give me the current error. I'll try your recommendations. – workerjoe Dec 7 '17 at 15:39
  • Problem: the binaries for 9.6.6 (or any old version) don't exist on my system, and the pg_upgrade command doesn't work without the -b argument. I probably used brew uninstall and brew install a few times in trying to troubleshoot this problem. Am I stuck? If I just want to start over with an empty local server, how would I do that? – workerjoe Dec 7 '17 at 15:41
  • If you need to reinstall an older version, this could work: stackoverflow.com/a/9832084/279726 – Kamal Dec 7 '17 at 21:52
  • How is "nuke it" a better answer, when I gave you options to reinstall 9.6.6? Meh – Kamal Dec 8 '17 at 21:39
0
  • Do you have any other version of PostgreSQL installed, or just the one from brew?
  • Is the server itself running for you connect to it check sudo ps aux?
  • Are you connecting to the right port. Check netstat see this awesome post by Craig.
  • Do you use brew to upgrade to a new version of PostgreSQL? If so you may want to check out this hella-popular question on Stackoverflow

You may also want to see this post, and start the server manually.

  • 1
    (1) just the one from brew (2) no! that's odd (3-4) skipped these ones... (5) I tried that 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 2017-12-06 14:35:33.309 EST [1318] DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.1. – workerjoe Dec 6 '17 at 19:36
  • @Joe sudo updatedb; sudo locate postgres | grep bin – Evan Carroll Dec 6 '17 at 19:37
  • updatedb gets me a command not found error – workerjoe Dec 6 '17 at 19:46
  • 'locate' finds only one postgres on my system, and it's v.10.1 in /usr/local/Cellar/postgresql/10.1/ – workerjoe Dec 6 '17 at 19:48
  • looks like there's a utility called pg_upgrade ... it needs my data directory, how do I know where that is? – workerjoe Dec 6 '17 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.