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?
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
Conclusion
In any case, check what log file exactly says and then think about the solution. 🙂
