PostgreSQL Mountain Lion socket issue

问题:

I've been trying to set up PostgreSQL on my system (OSX 10.8, clean install), but I'm running into trouble with using psql, createdb, etc. I've tried various solutions and none seem to work.

The install was successful, and I proceeded to fix the known sockets issue using the following:

mkdir /var/pgsql_socket 
sudo chown $USER /var/pgsql_socket

Then I edited postgresql.conf, set unix_socket_directory to

unix_socket_directory = '/var/pgsql_socket'

and restarted Pg.

That should apparently have fixed the socket issue, but I'm still getting:

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"?

Also, I've checked the status of the server, and it appears to be running, but I still get 'no such file or directory'

Any ideas?

回答1:

According to the error message, the psql command that appears first in the $PATH has /tmp as the hard-coded default unix socket directory.

Since the actual directory is in fact /var/pgsql_socket, you should tell it explicitly rather than relying on the default:

$ psql -h /var/pgsql_socket [other options]

The same applies to other client-side commands like createdb, dropdb, createuser...

If you don't want to specify -h each time, it can be put into the PGHOST environment variable.

Some people also solve this by using TCP connections to localhost rather than using the Unix socket directory.

The root cause of this issue would be that after installing PostgreSQL on Mac OS X, the system ends up having two different instances of the postgres client set (the libpq library, psql and other associated utilities), one that is bundled with MacOS and the other that comes with the PostgreSQL installer.

Therefore yet another method is to change your $PATH so that the psql installed with PostgreSQL gets choosen before the one installed with the system (presumably /usr/bin/psql).

回答2:

I encountered this same issue when installing PostgreSQL 9.2 via Homebrew. psql that comes with this build looks to /tmp for a socket when called without any options.

I didn't feel like adding any new environment variables like PGHOST or creating aliases for psql. There's nothing wrong with doing any of these things, but I just didn't feel like adding to the clutter of my environment.

So, why not just set unix_socket_directory in postgresql.conf to /tmp? I did:

unix_socket_directory = '/tmp'      # (change requires restart)
#unix_socket_group = ''             # (change requires restart)
#unix_socket_permissions = 0777     # begin with 0 to use octal notation

After a reload, I can just run $ psql with no -h option, without having added an alias or environment variable.

回答3:

I had the same problem, and after doing what you'd done I also had to change my path so that /usr/local/bin (where Homebrew puts everything) comes before /usr/bin. I don't know why this isn't the default anymore, but it's easy to change.

sudo vi /etc/paths

then edit the file to have the /usr/local/bin line at the top.

Or alternately if you just want this change to happen for your specific user account, edit your ~/.bash_profile file:

touch ~/.bash_profile
vi ~/.bash_profile

and add this line: export PATH="/usr/local/bin:$PATH"

then run source ~/.bash_profile for the changes to take effect.

回答4:

See accepted answer for question/link below. Worked for me, but note the setting to change is unix_socket_directories (plural) not unix_socket_directory as stated.

https://dba.stackexchange.com/questions/21587/postgresql-is-running-locally-but-i-cannot-connect-why