Solving Issues in PostgreSQL. (Lessons in Debugging)
Tags: postgres, database, unix, debugging
Published: 2019-08-22 23:41:35

---
title: Solving Issues in PostgreSQL. (Lessons in Debugging)
published: true
description: Fixing "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.✨'?" on Mac OS X and Homebrew
tags: postgreSQL, 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:

```bash
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,

```bash
$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,

>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 `postmaster.pid` file.
>4. Run `rm ./postmaster.pid`. You shouldn’t have any trouble deleting 
> this file, but if you do, run `sudo rm ./postmaster.pid`. 
>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.

```bash
$ 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.

```bash
$ 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`:

```bash
$ 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!