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.
- Mac OS
- Ruby on Rails
? $ 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”?
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
In my case the log file says:
[…]2018-12-18 03:50:26.129 JST  LOG: received smart shutdown request2018-12-18 03:50:26.166 JST  LOG: worker process: logical replication launcher (PID 1121) exited with exit code 12018-12-18 03:50:26.178 JST  LOG: shutting down2018-12-18 03:50:26.214 JST  LOG: database system is shut down2018-12-18 05:26:51.431 JST  FATAL: database files are incompatible with server2018-12-18 05:26:51.431 JST  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  LOG: listening on IPv6 address “::1”, port 54322018-12-18 18:40:48.751 JST  LOG: listening on IPv4 address “127.0.0.1”, port 54322018-12-18 18:40:48.752 JST  LOG: listening on Unix socket “/tmp/.s.PGSQL.5432″2018-12-18 18:40:48.765 JST  LOG: database system was shut down at 2018-12-18 18:40:47 JST2018-12-18 18:40:48.768 JST  LOG: database system is ready to accept connections
That?s promising, so I tried database migration again:
$ rails db:create$ rails db:migrate
Other possible solutions
Because of an incorrect shutting down?
Photo 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
In any case, check what log file exactly says and then think about the solution. 🙂