After I did brew update and brew upgrade, my postgres got some problem. I tried to uninstall postgres and install it again, but it didn't work as well.
This is the error message. (I also got this error message when I try to do rake db:migrate)
$ psql psql: 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"? How can I solve it?
Mac version: Mountain lion.
homebrew version: 0.9.3
postgres version: psql (PostgreSQL) 9.2.1
And this is what I did:
$ brew uninstall postgresql Uninstalling /usr/local/Cellar/postgresql/9.2.1... $ brew uninstall postgresql Uninstalling /usr/local/Cellar/postgresql/9.1.4... $ psql --version bash: /usr/local/bin/psql: No such file or directory $ brew install postgresql ==> Downloading Already downloaded: /Library/Caches/Homebrew/postgresql-9.2.1.tar.bz2 ...... ...... ==> Summary /usr/local/Cellar/postgresql/9.2.1: 2814 files, 38M, built in 2.7 minutes $ initdb /usr/local/var/postgres -E utf8 The files belonging to this database system will be owned by user "laigary". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default text search configuration will be set to "english". initdb: directory "/usr/local/var/postgres" exists but is not empty If you want to create a new database system, either remove or empty the directory "/usr/local/var/postgres" or run initdb with an argument other than "/usr/local/var/postgres". $ mkdir -p ~/Library/LaunchAgents $ cp /usr/local/Cellar/postgresql/9.2.1/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/ $ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist homebrew.mxcl.postgresql: Already loaded $ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start server starting $ env ARCHFLAGS="-arch x86_64" gem install pg Building native extensions. This could take a while... Successfully installed pg-0.14.1 1 gem installed $ psql --version psql (PostgreSQL) 9.2.1 $ psql psql: 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"?Now, after I reinstalled homebrew, when I use $ psql, it doesn't show any error message.
But I run rake db:migrate in my Rails app, it shows:
could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"? /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:1213:in `initialize' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:1213:in `new' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:1213:in `connect' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:329:in `initialize' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:28:in `new' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:28:in `postgresql_connection' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:309:in `new_connection' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:319:in `checkout_new_connection' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:241:in `block (2 levels) in checkout' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:236:in `loop' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:236:in `block in checkout' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:233:in `checkout' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:96:in `block in connection' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:95:in `connection' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:404:in `retrieve_connection' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_specification.rb:170:in `retrieve_connection' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_specification.rb:144:in `connection' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/railties/databases.rake:107:in `rescue in create_database' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/railties/databases.rake:51:in `create_database' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/railties/databases.rake:40:in `block (3 levels) in <top (required)>' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/railties/databases.rake:40:in `each' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.8/lib/active_record/railties/databases.rake:40:in `block (2 levels) in <top (required)>' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:205:in `call' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:205:in `block in execute' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:200:in `each' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:200:in `execute' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:158:in `block in invoke_with_call_chain' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:151:in `invoke_with_call_chain' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/task.rb:144:in `invoke' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:116:in `invoke_task' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:94:in `block (2 levels) in top_level' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:94:in `each' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:94:in `block in top_level' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:133:in `standard_exception_handling' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:88:in `top_level' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:66:in `block in run' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:133:in `standard_exception_handling' /usr/local/Cellar/ruby/1.9.3-p327/lib/ruby/1.9.1/rake/application.rb:63:in `run' /usr/local/bin/rake:32:in `<main>' Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"riy_development", "pool"=>5, "username"=>nil, "password"=>nil}Finally I've found a solution.
sudo mkdir /var/pgsql_socket/ sudo ln -s /private/tmp/.s.PGSQL.5432 /var/pgsql_socket/ This solution is a little tricky, but it works. Hope anyone has a better solution
Update
This works for me as well.
rm /usr/local/var/postgres/postmaster.pid 60 Answers
Had a similar problem; a pid file was blocking postgres from starting up. To fix it:
$ rm /usr/local/var/postgres/postmaster.pid $ brew services restart postgresql and then all is well.
UPDATE:
For Apple M1 (Big Sur) users, do this instead:
$ rm /opt/homebrew/var/postgres/postmaster.pid $ brew services restart postgresql This sometimes happens when brew does a postgres upgrade, causing the data files to become incompatible with the new server.
In my case, it happened when upgrading from 9.3 to 9.4.
OS X/Homebrew:
Try running postgres -D /usr/local/var/postgres -- it will give you a much more verbose output if postgres fails to start. Or open the log file at /usr/local/var/log/postgres.log (/opt/homebrew/var/log/postgres.log on Mac M1) and look for the line "FATAL: database files are incompatible with server"
In my case, running rm -rf /usr/local/var/postgres && initdb /usr/local/var/postgres -E utf8 removed my old databases and then reinitialized the postgres db schema. (THIS WILL DESTROY YOUR DATA)
Thanks to for that solution. Full instructions for a thorough re-install can be found here: How to completely uninstall and reinstall Homebrew Postgres - Test Double Blog (Again, if you're on an M1 Mac then substitute the /opt/homebrew/var path wherever it says /usr/local/var)
After regenerating my databases (with rake db:create) everything worked fine again.
Finally, links in the comments point to this possible solution that preserves your data, but I haven't tried it: How to upgrade PostgreSQL from version 9.6 to version 10.1 without losing data - Stack Overflow
14Found a solution that worked for me here:
You basically run the following command to manually start the server:
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start If installing and uninstalling postgres with brew doesn't work for you, look at the logs of your postgresql installation or:
postgres -D /usr/local/var/postgres if you see this kind of output:
LOG: skipping missing configuration file "/usr/local/var/postgres/postgresql.auto.conf" FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 9.4, which is not compatible with this version 9.6.1. Then try the following:
rm -rf /usr/local/var/postgres && initdb /usr/local/var/postgres -E utf8 Then start the server:
pg_ctl -D /usr/local/var/postgres -l logfile start Upgrading the database works for me
brew postgresql-upgrade-database
On Yosemite, if the pid file is blocking Postgres from starting and you have a launchctl daemon trying (and failing) to load the database daemons, then you'll need to unload the plist file:
$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist Then remove the pid file
$ rm /usr/local/var/postgres/postmaster.pid Then reload the launchctl daemon
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist For anyone reading this and using Postgres.app, you may need host: localhost in your database.yml.
brew services start postgres worked for me!
0The problem can also be attributed to a crashed process that left postmaster.pid file behind. I do this and work's:
$ brew services stop postgresql $ rm /usr/local/var/postgres/postmaster.pid # adjust path accordingly to your install $ brew services start postgresql Check that the socket file exists.
$ ls -l /tmp/.s.PGSQL.5432 srwxrwxrwx 1 you wheel 0 Nov 16 09:22 /tmp/.s.PGSQL.5432 If it doesn't then check your postgresql.conf for unix_socket_directory change.
$ grep unix_socket /usr/local/var/postgres/postgresql.conf #unix_socket_directory = '' # (change requires restart) #unix_socket_group = '' # (change requires restart) #unix_socket_permissions = 0777 # begin with 0 to use octal notation The problem is because there is already a running service on the port 5432 and we cannot establish psql socket connection through this port.
I removed the socket file
rm -rf /tmp/.s.PGSQL.5432/ Then I reinitialized postgres services
postgres -D /usr/local/var/postgres This worked for me.
The most fool proof way around this is to do
brew reinstall postgresql This will keep your user permission etc all intact and everything is reset to new. Works all the time !
1In 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.
1This is actually what you are supposed to do:
you should instead look at /usr/local/var/postgres/postmaster.pid
and then look at the first line of the file - this is the bad PID
Run
ps aux | grep <PID> for example:
ps aux | grep 12345 then do
kill <PID> for example
kill 12345 Assuming it's still running
do not listen to the accepted answer it is bad and will corrupt your data!!!
1This worked for me . I didn't have to remove any file
brew postgresql-upgrade-database
Changing postresql or database.yml config settings, changing $PATH, or creating symlinks were all unnecessary for me. All I needed to do was gem uninstall pg and then bundle (or gem install pg).
The issue was that the pg gem had been installed before homebrew postgres, so was picking up the settings from the version of postgres that comes with MacOS. Reinstalling it (and thus rebuilding the native extension) fixed the problem.
1This happened to me when I upgraded from 9.3.4 to 9.5 as the databases are incompatible without upgrading.
I used pg_upgrade as follows:
Stop postgres
$ brew services stop postgresql Upgrade the databases:
$ pg_upgrade \ -d /usr/local/var/postgres \ -D /usr/local/var/postgres9.5 \ -b /usr/local/Cellar/postgresql/9.3.4/bin/ \ -B /usr/local/Cellar/postgresql/9.5.0/bin/ \ -v Archive the old databases:
$ mv /usr/local/var/postgres /usr/local/var/postgres9.3.save $ mv /usr/local/var/postgres9.5 /usr/local/var/postgres Restart postgres:
$ brew services start postgresql Updated Gems (for rails / active record) :
$ gem uninstall pg $ gem uninstall activerecord-postgresql-adapter $ bundle install For MacOS BigSur, homebrew postgres:
rm /opt/homebrew/var/postgres/postmaster.pidbrew services restart postgresql
Psql option
-h hostname --host=hostname
: Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix-domain socket.
$ grep "port\|unix_socket" /etc/postgresql/9.1/main/postgresql.conf port = 5433 # (change requires restart) unix_socket_directory = '/var/run/postgresql' # (change requires resta $ netstat -nalp | grep postgres unix 2 [ ACC ] STREAM LISTENING 106753 4349/postgres /tmp/.s.PGSQL.5432 unix 2 [ ACC ] STREAM LISTENING 10377 1031/postgres /var/run/postgresql/.s.PGSQL.5433 Run psql with -host Option
$ psql -p 5433 -h /var/run/postgresql No need to make a soft link
If postgres was installed using homebrew, you can fix this by running:
brew link postgres This worked for me (as a mix of previous answers):
$ rm /usr/local/var/postgres/postmaster.pid $ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start I faced the same problem for psql (PostgreSQL) 9.6.11.
what worked for me -
remove postmaster.pid -- rm /usr/local/var/postgresql@9.6/postmaster.pid
restart postgres -- brew services restart postgresql@9.6
If this also doesn't work then run -- sudo chmod 700 /usr/local/var/postgresql@9.6
Came across this issue too on MacOS Sierra and when we ran pg_ctl as described above we then had the following error pg_ctl: no database directory specified and environment variable PGDATA unset. So we followed the steps here which solved our issue, namely:
mkdir ~/.postgres
initdb ~/.postgres
pg_ctl -D ~/.postgres start
I got same issue because I'm using a wrong Postgres's username in code. I logged into postgres psql -d postgres and enter \du to take role name and correct Postgres's username.
So when you guys face this issue, you guys need to make sure you're using correct Postgres username, password, hostname and database...
Hope this will help anyone
If you shut down your system without quitting psql, postgres would not have removed some files.
I didn't find the file postmaster.pid in the location usr/local/var/postgres
So I did the below:
brew services start postgresql
The above command should let you start postgres
So i stubled upon this after the rails db:create command. Setting up the environment in a macOS Catalina 10.15.3.
First thing that i checked was the flow that got me here. After ensuring that that all things had went smoothly and there was no error that might had escaped my mind i tried the most popular solutions from here but none of the seemed to work.
So far the only error i was seeing was the
$ psql psql: 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"?
So i needed some more specific information about what was happening. Due to that reason i decided to look at the postgres logfile which is located at
/usr/local/var/log/postgres.log
So after opening the log i saw this error
LOG: starting PostgreSQL 12.2 on x86_64-apple-darwin19.3.0, compiled by Apple clang version 11.0.0 (clang-1100.0.33.17), 64-bit LOG: could not translate host name "localhost", service "5432" to address: nodename nor servname provided, or not known WARNING: could not create listen socket for "localhost" FATAL: could not create any TCP/IP sockets LOG: database system is shut down
So this is a bit more explanatory and specific. The problem is something about that PostgreSQL cannot "see" and resolve the localhost server.
So next thing i did was to check the /etc/hosts file whose default contents should look like this:
## # Host Database # # localhost is used to configure the loopback interface # when the system is booting. Do not change this entry. ## 127.0.0.1 localhost 255.255.255.255 broadcasthost ::1 localhost After comparing the above with mine i saw that in mine this line was different and commented (!).
#::1 localhost So i removed the # symbol in front of the line saved the file and re run the
rails db:create and the database was succesfully initiated.
1I ran into this issue after trying to restore/drop/create a db while other processes were accessing them. MacOSX/Homebrew fix was:
- close all other accessing processes
rails server,rails console,guard, etc... - load/unload using the commands found in
brew info postgres - run restore/drop/create from before
FWIW this happened to me today, but what happened was that I was running Ubuntu updates at the time, which were likely updating Postgres. Once the update completed, I was able to connect without a hitch.
For completeness' sake, I was trying to retrieve records from the database from a Rails console:
development (main):0 > a = MyModel.find 73694 PG::ConnectionBad: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? It looks like your psql doesn't run. You should run it before connect. You can do that using Postgres.app for Mac OS only. (Download and install this app ) Open the app, and you have a PostgreSQL server ready and awaiting new connections. Close the app, and the server shuts down. You also can find this info here . Hope this will help you.
2It is because a previous server a still running, try closing everything down and re-running your application.
