DEV Community


Solving Issues in PostgreSQL. (Lessons in Debugging)

Andrew Ackerman
I'm a Kiwi Carpenter becoming an American Software Developer. 🇳🇿
・4 min read

Last week I had the joy of spilling water on my computer, which turned it off rather quickly. Thankfully I was able to get it into an Apple Store and they dried it out and the biggest loss was the screen, meaning I have to have it eternally plugged into a monitor now, but it could have been far worse. Once I was back into work, I got ready to begin, opened my terminal, and tried running my Rails app, only to have this issue appear:

Started GET "/" for ::1 at 2019-08-21 09:15:43 -0700
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"?
 excluded from capture: DSN not set

PG::ConnectionBad (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"?

Enter fullscreen mode Exit fullscreen mode

As PG is an abbreviation for Postgres, My first thought is that there’s an issue with how PostgreSQL (my database) is working, so after a quick trip to Stack Overflow to relearn Postgres commands I thought I’d try the ol’ “Have you tried turning it off and on again?” So I restarted PostgreSQL, like so,

$brew services restart postgresql
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)

Enter fullscreen mode Exit fullscreen mode

Unfortunately, this did not work, And I was greeted with the same error. I headed back to Stack Overflow and tried the first rwo pages of results, all to no avail. So I decided then to seek help from my employer, (@andrewculver ) and he reminded me of a previous PostgreSQL issue I had encountered, in which PostgreSQL creates a file, which doesn't get dealt with when a computer experiences a hard shutdown, which in turn stops PostgreSQL from running again, because it believes it's already running. Thus the file needs to be deleted in order for PostgreSQL to get up and running again, So we did the following,

  1. Run brew info postgresql. You should see a reference to pg_ctl -D >/usr/local/var/postgres start. Don’t run that, You just want to confirm that the path is /usr/local/var/postgres. Actually do this:
  2. Run cd /usr/local/var/postgres
  3. Run ls You should see a file.
  4. Run rm ./ You shouldn’t have any trouble deleting this file, but if you do, run sudo rm ./
  5. Run brew services start postgresql.

While this may have helped the previous issue I'd had before, unfortunately, the same error message appeared, so it was time to do some deeper digging.

The Solution

Firstly we made our way into cd /usr/local/var/log/, once inside, we listed the contents like so.

$ ls
postgres.log    redis.log
Enter fullscreen mode Exit fullscreen mode

Next wanted to check some of the recent log entries without displaying everything, so used tail to print out the last 10 lines.

$ tail ./postgres.log 
  Reason: image not found
dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.63.dylib
  Referenced from: /usr/local/opt/postgresql/bin/postgres
  Reason: image not found
dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.63.dylib
  Referenced from: /usr/local/opt/postgresql/bin/postgres
  Reason: image not found
dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.63.dylib
  Referenced from: /usr/local/opt/postgresql/bin/postgres
  Reason: image not found
Enter fullscreen mode Exit fullscreen mode

Finally we saw our issue! The wasn't the issue, but instead a dynamic library for Unicode and globalization called icu4c, which was affecting our PostgreSQL processes. So, using Homebrew we uninstalled postgresql and reinstalled icu4c:

$ brew uninstall postgresql
Uninstalling /usr/local/Cellar/postgresql/11.2... (3,186 files, 35.4MB)
$ brew install icu4c
Updating Homebrew...

#I'm not going to past all the output because it goes on forever, but icu4c is reinstalled!
Enter fullscreen mode Exit fullscreen mode

Finally, we installed PostgreSQL and restarted it's processes:

$ brew install postgresql
==> Installing dependencies for postgresql: openssl and readline
==> Installing postgresql dependency: openssl
==> Downloading
==> Downloading from
######################################################################## 100.0%

#Again, a lot more output then I'm going to post here, but it ends with these two options.

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
Enter fullscreen mode Exit fullscreen mode

The first option sounded like what we wanted, so we went with that.

So that's it! Andrew said that this has happened to him every few months for years, so we wanted to post something that would help anyone else who is seeing the same uninformative error message. Even if a mis-linked dynamic library isn't your specific issue, the contents of that logfile are likely to tell you what's wrong. If you need help debugging this issue, and the steps above didn't help, just post the output of your log file in the comments below! In fact, even if you understand the error message right away, please paste it in the comments anyway just so we can build a catalog of all the different reasons PostgreSQL isn't automatically restarting for different people. It might help someone else! Hopefully this helps someone else having the same issue! If you have any pointers or suggestions for other ways of solving the problem, I'd love to know!

Discussion (2)

jasmineea profile image
Jasmine Anderson

Thank you so much for this!!! I spent hours trying to figure out what was wrong with my database. I didn't even consider checking the tail log smh. Thanks again!

ibrahimahmed443 profile image
Ibrahim Ahmed

After spending hours on Stackoverflow trying to fix this issue in vain, this article finally helped me solve it. Thanks for sharing