Can not connect to local PostgreSQL

Question

I ve managed to bork my local development environment   All my local Rails apps are now giving the error   PGError could not connect to server  Permission denied     Is the server running locally and accepting     connections on Unix domain socket   var pgsql socket  s PGSQL 5432     I ve no idea what s caused this    While searching for a solution I ve updated all bundled gems  updated system gems  updated MacPorts  No joy   Others have reported this issue when upgrading from OSX Leopard to Lion  due to confusion over which version of Postgres should be used  i e   OSX version or MacPorts version   I ve been running Lion for several months  so it seems strange that this should happen now   I m reluctant to mess around too much without first understanding what the problem is  How can I debug this methodically    How can I determine how many versions of PostgreSQL are on my system  which one is being accessed  and where it is located  How do I fix this if the wrong PostgreSQL is being used   Sorry for the noob questions  I m still learning how this works  Thanks for any pointers   EDIT   Some updates based on suggestions and comments below   I tried to run pg lsclusters which returned a command not found error    I then tried to local my pg hba conf file and found these three sample files    opt local share postgresql84 pg hba conf sample  opt local var macports software postgresql84 8 4 7 0 opt local share postgresql84 pg hba conf sample  usr share postgresql pg hba conf sample   So I assume 3 versions of PSQL are installed  Macports  OSX default and        I then did a search for the launchctl startup script ps -ef   grep postgres which returned  0    56     1   0 11 41AM            0 00 02  opt local bin daemondo --label postgresql84-server --start-cmd  opt local etc LaunchDaemons org macports postgresql84-server postgresql84-server wrapper start   --stop-cmd  opt local etc LaunchDaemons org macports postgresql84-server postgresql84-server wrapper stop   --restart-cmd  opt local etc LaunchDaemons org macports postgresql84-server postgresql84-server wrapper restart   --pid none   500   372     1   0 11 42AM            0 00 17  opt local lib postgresql84 bin postgres -D  opt local var db postgresql84 defaultdb   500   766   372   0 11 43AM            0 00 37 postgres  writer process                                                                                                                                                                                                                                                                                                                      500   767   372   0 11 43AM            0 00 24 postgres  wal writer process                                                                                                                                                                                                                                                                                                                  500   768   372   0 11 43AM            0 00 16 postgres  autovacuum launcher process                                                                                                                                                                                                                                                                                                         500   769   372   0 11 43AM            0 00 08 postgres  stats collector process                                                                                                                                                                                                                                                                                                             501  4497  1016   0 12 36PM ttys000    0 00 00 grep postgres   I ve posted the contents of postgresql84-server wrapper at http   pastebin com Gj5TpP62   I tried to run port load postgresql184-server but received an error Error  Port postgresql184-server not found   I m still very confused how to fix this  and appreciate any  for dummies  pointers   Thanks   EDIT2   This issue began after I had some problems with daemondo  My local Rails apps were crashing with an application error along the lines of  daemondo gem can not be found   I then went through a series of bundle updates  gem updates  port updates and brew updates to try and find the issue   Could this error be an issue with daemondo

User · Answer

I read many topics about this error and the solution to me was to simply restart the postgres with   sudo service postgresql restart   Which is not mentioned here

User · Answer

I had similar problem when trying to use postgresql with rails  Updating my Gemfile to use new version of gem pg solve this problem for me   gem pg version 0 16 0 works   In the Gemfile use   gem  pg    0 16 0    then run the following to update the gem  bundle install --without production bundle update bundle install

User · Answer

I was getting this same error  it turns out it was an error with postmaster pid  Here s how I got postgres up and running again  credit to Ricardo Burillo for the fix      rm  usr local var postgres postmaster pid    pg resetxlog -f  usr local var postgres

User · Answer

My gut feeling is that this is  again  a mac OSX-thing  the front end and the back end assume a different location for the unix-domain socket  which functions as a rendezvous point    Checklist    Is postgres running  ps aux   grep postgres   grep -v grep should do the trick Where is the socket located  find   -name  s PGSQL 5432 -ls  the socket used to be in  tmp  you could start looking there  even if you locate the  unix-domain  socket  the client could use a different location   this happens if you mix distributions  or of you have a distribution installed someplace and have another  eg from source  installation elsewhere   with client and server using different rendez-vous addresses    If postgres is running  and the socket actually exists  you could use    psql -h  the directory where the socket was found mydbname    which attempts to connect to the unix-domain socket     you should now get the psql prompt  try  d and then  q to quit  You could also  try     psql -h localhost mydbname     which attempts to connect to localhost  127 0 0 1   If these attempts fail because of insufficient authorisation  you could alter pg hba conf  and SIGHUP or restart  In this case  also check the logs   A similar question  Can  39 t get Postgres started  Note  If you can get to the psql prompt  the quick fix to this problem is just to change your config database yml  add   host  localhost  or you could try adding   host   the directory where the socket was found  In my case  host   tmp

User · Answer

The location of the socket file is baked into the gem at compile time   Thus you need to rebuild your pg gem   gem pristine pg   or bundle exec gem pristine pg   This should resolve that particular issue

User · Answer

Try uninstalling the pg gem  gem uninstall pg  then reinstalling -- if you use bundler  then bundle install  else gem install pg   Also  make sure path picks up the right version  Lion has a version of posgresql  prior versions didn t  and it may be in the path before your locally installed version  e g  MacPorts  homebrew    In my case  homebrew install of postgresql  updated postgresql  rails  etc  and then got this error   Uninstalling and reinstalling the pg gem did it for me

User · Answer

This really looks like a file permissions error   Unix domain sockets are files and have user permissions just like any other   It looks as though the OSX user attempting to access the database does not have file permissions to access the socket file   To confirm this I ve done some tests on Ubuntu and psql to try to generate the same error  included below    You need to check the permissions on the socket file and its directories  var and  var pgsql socket   Your Rails app  OSX user  must have execute  x  permissions on these directories  preferably grant everyone permissions  and the socket should have full permissions  wrx   You can use ls -lAd  lt file gt  to check these  and if any of them are a symlink you need to check the file or dir the link points to    You can change the permissions on the dir for youself  but the socket is configured by postgres in postgresql conf  This can be found in the same directory as pg hba conf  You ll have to figure out which one    Once you ve set the permissions you will need to restart postgresql     postgresql conf should contain    unix socket directory     var run postgresql          dont worry if yours is different  unix socket group                                    default is fine here  unix socket permissions   0777                       check this one and uncomment if necessary      EDIT   I ve done a quick search on google which you may wish to look into to see if it is relavent  This might well result in any attempt to find your config file failing   http   www postgresqlformac com server howto edit postgresql confi html    Error messages   User not found in pg hba conf  psql  FATAL   no pg hba conf entry for host   local    user  couling   database  main   SSL off   User failed password auth   psql  FATAL   password authentication failed for user  couling    Missing unix socket file   psql  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     Unix socket exists  but server not listening to it   psql  could not connect to server  Connection refused     Is the server running locally and accepting     connections on Unix domain socket   var run postgresql  s PGSQL 5432     Bad file permissions on unix socket file   psql  could not connect to server  Permission denied     Is the server running locally and accepting     connections on Unix domain socket   var run postgresql  s PGSQL 5432

User · Answer

gem uninstall pg   On OS X with Homebrew   gem install pg -- --with-pg-config  usr local bin pg config

User · Answer

Hello world   The best but strange way for me was to do next things  1  Download postgres93 app or other version  Add this app into  Applications  folder   2  Add a row  command  into the file  bash profile  which is in my home directory     export PATH  Applications Postgres93 app Contents MacOS bin   PATH It s a PATH to psql from Postgres93 app  The row  command  runs every time console is started   3  Launch Postgres93 app from  Applications  folder  It starts a local server  port is  5432  and host is  localhost     4  After all of this manipulations I was glad to run   createuser -SRDP user name and other commands and to see that it worked  Postgres93 app can be made to run every time your system starts   5  Also if you wanna see your databases graphically you should install PG Commander app  It s good way to see your postgres DB as pretty data-tables  Of  course  it s helpful only for local server  I will be glad if this instructions help others who has faced with this problem

User · Answer

In my case none of previous solutions was good  Instead of using socket  you can use TCP host   port number in Rails config file  So in database yml file just add two lines like here       adapter  postgresql encoding  unicode pool  5 host  localhost port  5432   This solved my problem     Before I used this fix   sudo mkdir  var run postgresql sudo ln -s  tmp  s PGSQL 5432  var run postgresql  s PGSQL 5432   But after each reboot  tmp  s PGSQL 5432 was deleted and I had to repeat these commands  Solution works  but it is horrible  so better just modify Rails database config file

User · Answer

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      I searching the solution for a while  So  this one fixed the issue for me as well  reinit db      rm -r  usr local var postgres   initdb  usr local var postgres -E utf8   pg ctl -D  usr local var postgres -l logfile start   I use OS X 10 11 3 with brew

User · Answer

This is how I solved that error message  based partly on wildplasser s answer   find   -name  s PGSQL 5432 -ls 2 gt   dev null     gt       tmp  s PGSQL 5432   So  there s my socket or whatever  but the client looks for it at    var run postgresql  s PGSQL 5432   So quite simply make a symbolic link to the  tmp  s PGSQL 5432   sudo ln -s  tmp  s PGSQL 5432  var run postgresql  s PGSQL 5432   Hope this helps to anyone  The seems kind of wrong  but hey  it works

User · Answer

MacOSX here   I had the same problem after upgrading my postresql install from a pre-9 1 to 9 1 2 using homebrew    By the way  remember to dump databases before your upgrade with pg dump  pre-9 1 databases are incompatible    Same problem  same error messages   Uninstalling the pg gem did the trick for me   I actually had to do quite a bit of dancing to discover the issue   First I did a global gem uninstall  clearing the deck of all the old gems  there were a few    Then I removed pg from my Gemfile  rebundled  restored the pg reference  and rebounded once more   After that  it worked like a charm

User · Answer

Just confirming I had a similar issue on PSQL and Django   Looked like because my psql server was not shut down correctly and the postmaster pid file was still present  should be deleted on proper shutdown automatically  in my postgres folder   Deleted this and all good

User · Answer

This happened to me today after my Macbook s battery died  I think this can be caused by improper shutdown  All you have to do in cases such as mine is delete postmaster pid  Navigate to the folder  cd  usr local var postgres   Check to see if postmaster pid is present  ls   Remove postmaster pid  rm postmaster pid

User · Answer

If you re getting a similar error   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     This might do the trick  it did for me    initdb  usr local var postgres -E utf8   The directory specified should be different if you re not using OSX Brew   Note  This is not the exact error message seen above  but this thread is the first result for that error message

User · Answer

Got this error when I was setting up Posgtres with Django  I m using Back Track and it comes with Postgres installed  I assume the settings are the issue  I fixed it by removing it completely then reinstalling like so   sudo apt-get remove postgresql sudo apt-get purge postgresql   Now run   apt-get --purge remove postgresql     to remove everything PostgreSQL from your system  Just purging the postgres package isn t enough since it s just an empty meta-package   Once all PostgreSQL packages have been removed  run   rm -r  etc postgresql  rm -r  etc postgresql-common  rm -r  var lib postgresql  userdel -r postgres groupdel postgres   You should now be able to   apt-get install postgresql

User · Answer

what resolved this error for me was deleting a file called postmaster pid in the postgres directory  please see my question answer using the following link for step by step instructions  my issue was not related to file permissions   psql  could not connect to server  No such file or directory  Mac OS X   the people answering this question dropped a lot of game though  thanks for that  i upvoted all i could

User · Answer

I had this problem plaguing me  and upon further investigation  running rake db setup   I saw that rails was trying to connect to a previously used postgres instance - one which was stored in env variables as DATABASE URL    The fix  unset DATABASE URL  source  https   stackoverflow com a 17420624 2577622

User · Answer

I started getting this after upgrading to a new postgres - I didn t realize I had hold data files    First I tried to start the postgres server   postgres -D  usr local var postgres   which is how I saw this error  FATAL   database files are incompatible with server DETAIL   The data directory was initialized by PostgreSQL version 9 0  which is not compatible with this version 9 3 5    So then I found this answer on SO - related to an incompatibility error  https   serverfault com questions 342626 how-do-i-upgrade-postgresl-database-incompatibility-error  This is what fixed it  mv  usr local var postgres  usr local var postgres old initdb -D  usr local var postgres

User · Answer

I tried most of the solutions to this problem but couldn t get any to work    I ran lsof -P   grep   5432    awk   print  2   which showed the PID of the process running  However I couldn t kill it with kill -9  lt pid gt    When I ran pkill postgresql the process finally stopped  Hope this helps

[ruby-on-rails] Can not connect to local PostgreSQL

Examples related to ruby-on-rails

Examples related to macos

Examples related to postgresql