Solve PSQL error: could not connect to server

I recently started pairing with, Kori Roys a Senior Developer contributing to JOGL, an open-source non-profit that decentralizes open research and innovation laboratory. We've been working on getting the clones of the backend and the frontend to my computer so I can pair with him and drive with access to make pull requests. Following the install instructions, we came across an issue when we were trying to create a postgres user so that I could have access to the development database.

Error: 
     psql: 
     error: 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"

Turns out this is a very common problem that crops up if your postgres server is shut down incorrectly or if your postgres went through an upgrade. I saw multiple solves where people were removing the pid such as in this stackoverflow by using the following commands:

$ rm /usr/local/var/postgres/postmaster.pid
$ brew services restart postgresql


I was curious as to what the pid actually was so I did a little investigating after the fact. Reading the postgres docs led me to the discovery that the existence of the postmaster.pid file in the data directory is used to help pg_ctl determine if the server is currently running or not. pg_ctl is a utility for starting, stopping, or restarting the PostgreSQL backend server (postmaster), or displaying the status of a running server.

Following what many previous users with the same error did, we searched for the postmaster.pid in the location indicated above. However, there wasn't a pid. The closest I saw was /usr/local/var/postgres/postmaster.opts and try as we might we could not find a pid anywhere.

The Senior Dev used his experience to suggest that we check the postgres logs to see what was actually happening under the hood:

$ tail -f -n 100 /usr/local/var/log/postgres.log

which gave us the most recent hundred logs from postgres, and we saw that my PostgresSQL databases and versions were incompatible.

2021-02-17 16:13:50.885 CST [12699] FATAL: database files are incompatible with server
2021-02-17 16:13:50.885 CST [12699] DETAIL: The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 13.2.


We first tried changing the PostgreSQL version to 12, but we still had the same error and we were not able to locate a pid. So we reverted back to version 13.2 and instead upgraded the databases:

$ brew postgresql-upgrade-database

Then after starting and stopping postgres a few times using:

$ brew services stop postgres
$ brew services start postgres

and checking the logs

$ tail -f -n 100 /usr/local/var/log/postgres.log

we finally got a new error!

2021-02-17 16:22:37.524 CST [17926] HINT: Is another postmaster (PID 17890) running in data directory "/usr/local/var/postgres"?
2021-02-17 16:22:47.547 CST [18050] FATAL: lock file "postmaster.pid" already exists


Which meant when we checked the files in the postgres folder:

There was a pid we could remove! Which we did using:

$ rm postmaster.pid
$ brew services stop postgres
$ brew services start postgres

We then were able to successfully use postgres and the logs confirmed this:

2021-02-17 16:25:04.453 CST [19214] LOG: listening on IPv6 address "::1", port 5432
2021-02-17 16:25:04.453 CST [19214] LOG: listening on IPv4 address "127.0.0.1", port 5432
2021-02-17 16:25:04.454 CST [19214] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"

And we were able to continue following the JOGL install instructions and create a postgres user for the database.

Quick Tip

When writing up this post in the days following this troubleshooting experience, I had to go and ask the Senior Dev what was the command he used to access the postgres logs. Which he graciously showed me, but he also taught me another trick for when you are trying to remember the commands you ran. You can enter $history [option] and in option provide the number of lines you want to see. In this way, I was able to follow every single step we took while troubleshooting.

$ history 100

This image is a summary of all the steps we took to resolve the issue. And it took two different sessions to get through this.

Lesson learned: the logs can be incredibly helpful when the error isn't enough to resolve your issue.

Happy coding!