Solving Issues in PostgreSQL. (Lessons in Debugging)

#postgres #database #unix #debugging

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

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)

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 postmaster.pid 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 postmaster.pid file needs to be deleted in order for PostgreSQL to get up and running again, So we did the following,

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:

Run cd /usr/local/var/postgres

Run ls You should see a postmaster.pid file.

Run rm ./postmaster.pid. You shouldn’t have any trouble deleting this file, but if you do, run sudo rm ./postmaster.pid.

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

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

Finally we saw our issue! The postmaster.pid 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!

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

$ brew install postgresql
==> Installing dependencies for postgresql: openssl and readline
==> Installing postgresql dependency: openssl
==> Downloading https://homebrew.bintray.com/bottles/openssl-1.0.2s.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/c4/c4a762d719c2be74ac686f1aafabb32f3c5d5ff3a98935c4925a1ddb9c750ee1?__gda__=exp=1566501434~hmac=b709fcc5ffd01de96a07acdb294be279
######################################################################## 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

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!

Top comments (3)

Jasmine Anderson • Feb 8 '21

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!