PG::ConnectionBad: could not connect to server: No such file or directory

PG::ConnectionBad: could not connect to server: No such file or directory

When Postgresql doesn?t work.

I was creating a new project using Rails, and I came across an error at the very beginning when I tried to run rails db:migrate after I added the first model.

Project Configuration

  • Mac OS
  • PostgresQL
  • Ruby on Rails

Error production

? $ rails db:migrate –trace** Invoke db:migrate (first_time)** Invoke db:load_config (first_time)** Invoke environment (first_time)** Execute environment** Execute db:load_config** Execute db:migraterails aborted!PG::ConnectionBad: could not connect to server: No such file or directoryIs the server running locally and acceptingconnections on Unix domain socket “/tmp/.s.PGSQL.5432”?

Solution

In my case, the trouble was caused by Mac OS updating. Upgrading PostgresQL solved the issue.

# upgrade database version solved the trouble$ brew postgresql-upgrade-database

However, this error is quite common and has multiple possible reasons, you shouldn?t fully rely on my solution above.

The first thing

Take a look at your postgresql logfile. It is located at/usr/local/var/log/postgres.log .

Chances are that log file is in /usr/local/var/postgres/ .

$ cd /usr/local/var/log/$ cat postgres.log

Case study

In my case the log file says:

[…]2018-12-18 03:50:26.129 JST [1097] LOG: received smart shutdown request2018-12-18 03:50:26.166 JST [1097] LOG: worker process: logical replication launcher (PID 1121) exited with exit code 12018-12-18 03:50:26.178 JST [1116] LOG: shutting down2018-12-18 03:50:26.214 JST [1097] LOG: database system is shut down2018-12-18 05:26:51.431 JST [1042] FATAL: database files are incompatible with server2018-12-18 05:26:51.431 JST [1042] DETAIL: The data directory was initialized by PostgreSQL version 10, which is not compatible with this version 11.1.[…](same errors)

The error didn?t happen first when I run rails db:migrate . It started 3 am this morning when I was sleeping. It seems like the version management was the factor to this problem (current data directory looks older than the pg version itself). So I run this command:

$ brew postgresql-upgrade-database

Now .log file looks like:

2018-12-18 18:40:48.751 JST [27321] LOG: listening on IPv6 address “::1”, port 54322018-12-18 18:40:48.751 JST [27321] LOG: listening on IPv4 address “127.0.0.1”, port 54322018-12-18 18:40:48.752 JST [27321] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432″2018-12-18 18:40:48.765 JST [27324] LOG: database system was shut down at 2018-12-18 18:40:47 JST2018-12-18 18:40:48.768 JST [27321] LOG: database system is ready to accept connections

That?s promising, so I tried database migration again:

$ rails db:create$ rails db:migrate

It worked!!

Other possible solutions

Because of an incorrect shutting down?

Image for postPhoto by Sergey Zolkin on Unsplash

The common reason for this has something to do with shut down. When you shut down your PC or terminate your console(like Terminal in Mac) with your local server that using pg open, the error will occur.

As this stack-over-flow says, first try to remove PID file.

$ rm /usr/local/var/postgres/postmaster.pid

There?s another way to kill the database server(CPU process) manually.

# for mac os$ sudo ps ax | grep rails# -> will show you a list# if there is some task that is running local server, run this code$ sudo kill -9 5_DIGIT_NUMBER

Uninstall then install again

If you still have the problem, uninstalled pg. (credit to this stack-over-flow)

$ gem uninstall pg$ brew uninstall postgresql$ brew install postgresql$ ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist$ gem install pg — –with-pg-config=/usr/local/bin/pg_config

Conclusion

In any case, check what log file exactly says and then think about the solution. 🙂

24