Repairing Postgresql after upgrading to OSX 10.7 Lion

Question

I recently upgraded to OSX 10.7, at which point my rails installation completely borked when trying to connect to the psql server. When I do it from the command line using

psql -U postgres

it works totally fine, but when I try to run the rails server or console with the same username and password, I get this error

...activerecord-3.0.9/lib/active_record/connection_adapters/postgresql_adapter.rb:950:in `initialize': could not connect to server: Permission denied (PGError) 
Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

Any ideas what might be going on would be super helpful! Thanks!

Knitting

This also hit someone in my office; we had a similar problem with getting the wrong binary, an additional problem with trying to connect to domain sockets in a different directory with different permissions, and it looks like the upgrade ate all the data on the local database. Fortunately this was just a development box, so it's not a huge deal, but mildly obnoxious. :) – Kobarid

I hit this one myself today and remembered reading your question yesterday. Good to see @John Wang has come out and explained it :) – Metopic

Answer 1

It's a PATH issue. Mac OSX Lion includes Postgresql in the system now. If you do a which psql you'll likely see usr/bin/psql instead of usr/local/bin/psql which is HomeBrew's correct one. If you run brew doctor you should get a message stating that you need to add usr/local/bin to the head of your PATH env variable.

Editing your .bash_profile or .profile, or whichever shell you're using and adding: export PATH=/usr/local/bin:$PATH

as the first export for the PATH then either quit you shell session or source your file with source ~/.bash_profile and it should now be OK again.

Spectacled

This fixed it. You can also edit /etc/paths and make sure /usr/local/bin is on the top – Card

Also, note that if you installed the pg gem BEFORE fixing your path, it will use the wrong psql. If so, uninstall the pg gem and then reinstall it (gem uninstall pg && gem install pg). – Nolen

Is this for homebrew? Ports seems to put it in: /opt/local/lib/postgresql91 So make sure you use export PATH=/opt/local/lib/postgresql91/bin:$PATH – Enamor

My path was reporting correctly, but Troy's solution of uninstalling the pg gem, then letting bundler reinstall did the trick for me. – Echolocation

Just to clarify - it looks as if you are best to setup your path correctly, then uninstall/reinstall the pg gem – Kneehole

Troy's comment worked for me. Running Mountain Lion, and had just installed homebrew's postgres. – Pontefract

Also note for the Python users; you have to reinstall your PostgreSQL driver (that is likely to be psycopg2) after you fix your path issue explained above. To do that: pip uninstall psycopg2 && pip install psycopg2 within your virtualenv. – Katheryn

uninstall pg gem? I have installed rubymine and postgres and induction. This error comes up when trying to connect to postgres through induction. How can I uninstall pg gem? – Tooley

Answer 2

For those of you who are interested, I pieced together the solution. All I needed was to add

host: localhost

to the database.yml for my environment and all was gravy.

Knitting

be careful with this: This setting changes the access from the domain socket to a TCP connection. While it probably works, you might lose a bit of performance and usable ports on your machine which can be a problem depending on your setup. The solution provided by John is correct. – Constraint

'gem uninstall pg' (choose all versions), then 'bundle' again to install the pg version from your Gemfile worked for me. – Libradalibrarian

Thanks Dave G, this worked for me too. I installed 10.7.3 update and rake db:migrate complained. This fixed it. – Latonialatoniah

This worked for me too. However i left the password and the username blank. – Unjust

I'd imagine this would work because it forces a TCP/IP connection. – Pontefract

Answer 3

I had this very problem with Mountain Lion but the only thing that worked for me was this fix:

Check where the actual target is:

sudo find / -name .s.PGSQL.5432

I needed to create this directory:

mkdir /var/pgsql_socket/

Then using the result from the find above create this symlink:

ln -s /private/tmp/.s.PGSQL.5432 /var/pgsql_socket/

I suspect that for most people on Mountain Lion you can just create the dir and do the symlink and not waste time doing the find unless the symlink doesn't work.

PS - my PostgreSQL was installed through the official installer.

Armament

I have a feeling it might be it for me but can't make it work. I'm getting this : ln: /var/pgsql_socket/: No such file or directory – Emaemaciate

Sorry, I forgot that I ran into that too. Added extra step to answer. – Armament

Bless you Ben. Greatly appreciated. – Altdorf

Answer 4

If the problem persists past changing the path (as it did for me), also try this...

gem pristine pg

It appears that the problem (partially) lies in the pg gem itself. When it builds, it figures out where the domain socket should be. If you change the location of the domain socket after the fact it doesn't seem to take effect until you rebuild the gem.

Ursas

This worked for me, even after I fixed the PATH and reinstalled the pg gem without pristine. – Muscatel

Answer 5

For those who installed direct from the official installer, just adding the host to the command works with no path changes:

psql -h localhost -U postgres

Annorah

Answer 6

I had the same issue and was having problems making John Wang's solution work. As Darren noted there's an issue with the pg gem. To get it work I needed to:

gem uninstall pg

Then reinstall.

Which got it working.

Soothe

I had to run this twice...odd. I did uninstall pg then bundle install and it failed. Then just did gem install pg and it worked. Thanks! – Malt

Answer 7

I ran into this as well, but I had installed postgres myself (not with homebrew). If that's the case, you need to find the old path to psql (which may be /usr/local/bin, but for me was /usr/local/pgsql/bin) and prepend that to your $PATH.

(before) which psql => /usr/bin/psql

(fix) export PATH=/usr/local/psql/bin:$PATH

(after) `which psql' => /usr/local/psql/bin

John Wang's suggestion to source ~/.bash_rc afterward you add that to your bash_rc is golden.

Karakorum

Answer 8

Is this for homebrew? Ports seems to put it in:

/opt/local/lib/postgresql91 

So make sure you use export

PATH=/opt/local/lib/postgresql91/bin:$PATH

Mac ports issue: https://trac.macports.org/ticket/30125

Enamor

Answer 9

I'm not happy with the most upvoted answers as they are either OS-user specific or remap Postgres to use TCP instead of domain sockets, as pointed out by @pilif. I've seen another solution that involves re-ordering the default paths at the system level to check Brew's path before a core system path, but this seems hazardous as it could affect all other application name-collisions like this one.

This site details a solution my coworker found. It comes down to executing a single shell script that will

1. back up the Postgres 8.4 files in a separate directory
2. symlink the brew's installation of Postgres in place

This comes with the caveat that the system default Postgres is whatever brew has installed, so you have to make a judgment call about whether that's right for you. I don't see myself needing Postgres 8.4 specifically over 9.x, but YMMV

Palstave

Answer 10

Another possible solution that worked for me is resetting the postmaster file by deleting it. Simply run:

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

It's worth checking the log for errors which you can find here:

/usr/local/var/postgres/server.log

The error message I was having was:

FATAL:  lock file "postmaster.pid" already exists
HINT:  Is another postmaster (PID 161) running in data directory 
"/usr/local/var/postgres"?

Everything worked great afterwards.

Concessive

I had the issue only from the command line. Removing the file /usr/local/var/postgres/postmaster.pid solved my issue. – Doukhobor

Answer 11

In my case the server didn't start because of wrong shared memory settings. At first I was confused because there were several postgres processes running, but those were standard system processes. Look for postmaster processes!

All I needed to do was to change the shared memory settings. Fiddling around with the path settings wasn't needed in my case.

Moria

Answer 12

If you like a permanent change in your $PATH try this:

defaults write $HOME/.MacOSX/environment PATH "/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/X11/bin:/opt/local/bin"

this will rewrite your ~/.MacOSX/environment.plist.

Crow

Answer 13

You may need to specify the host of your database.

Higdon

Answer 14

I'm new to Rails, but adding the following to database.yml worked for me:

host: localhost

port: 5432

Not sure why Rails defaults to domain sockets instead of TCP, while PostgreSQL does not set up domain sockets by default.

Hairstreak

Answer 15

My PostgreSQL is installed in /Library/PostgreSQL so that /usr/var stuff didn't work for me.

It appears that Woz is correct because everytime I close my macbook pro's lid it crashes... Here is what worked post-crash for me:

sudo su postgres -c "/Library/PostgreSQL/9.2/bin/pg_ctl -m fast -D /Library/PostgreSQL/9.2/data restart"

Penal