5

I am aware that this or similar has been asked in other places. None of the other answers have worked for me.

Machine is a Macbook Pro 2014 / OS X Yosemite 10.10 with Postgres 9.3.5 installed via Homebrew.

I can only connect to my local postgres instance if I do NOT specify a host. This works:

psql -Umyuser -d mydb
sql (9.4.4)
Type "help" for help.

myuser=# 

-h localhost does not:

psql -Umyuser -d mydb -h localhost
psql: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

It pauses for a long time before I get this message.

I am trying to set up a third party tool that seems to want to specify localhost, so it is not an option just to leave that off.

If I understand what I've read correctly elsewhere, postgres behaves differently if you're using TCP/IP, and specifying -h localhost forces that. Accordingly I have added the following in pg_hba.conf:

local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             localhost               trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

(127.0.0.1 AND localhost seems redundant but desperation drives superstition) ... and in postgresql.conf:

listen_addresses = 'localhost'          
port = 5432                             

netstat confirms that Postgres is listening to that port:

☁  ~  netstat -an | grep 5432
tcp4       0      0  127.0.0.1.5432         *.*                    LISTEN     
tcp6       0      0  ::1.5432               *.*                    LISTEN     
d8d7c0e31d5add1d stream 0 0 d8d7c0e326b18a1d 0 0 0 /tmp/.s.PGSQL.5432

All permissions are set on the socket file:

~ ls -l /tmp/.s.PGSQL.5432
srwxrwxrwx  1 fritz  wheel  0 12 Jul 13:27 /tmp/.s.PGSQL.5432

/etc/hosts has an entry for localhost:

127.0.0.1   localhost
::1         localhost

psql and pg_ctl are pointing to the same installations:

~ which psql               
/usr/local/bin/psql
~ ll /usr/local/bin/psql
/usr/local/bin/psql -> ../Cellar/postgresql/9.4.4/bin/psql
~ which pg_ctl
/usr/local/bin/pg_ctl
~  ll /usr/local/bin/pg_ctl
/usr/local/bin/pg_ctl -> ../Cellar/postgresql/9.4.4/bin/pg_ctl

There's nothing named psql elsewhere on my system that seems like it could be hijacking this process:

~  sudo find / -name psql
/Applications/pgAdmin3.app/Contents/SharedSupport/psql
/Users/fritz/devtools/phantomjs/src/qt/config.tests/unix/psql
/Users/fritz/Qt/5.3/Src/qtbase/config.tests/unix/psql
/Users/fritz/Qt/5.3/Src/qtbase/src/plugins/sqldrivers/psql
/Users/fritz/Qt/5.3/Src/qtbase/src/sql/drivers/psql
/usr/local/bin/psql
/usr/local/Cellar/postgresql/9.4.4/bin/psql

UPDATE: telnet connects to localhost 5432 for a while and then is disconnected:

~  telnet localhost 5432
Trying ::1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

UPDATE 2: I used wireshark to spy on tcp localhost port 5432. Trouble is, I don't know what I should see and whether/how the results are wrong. Hoping someone out there can make something of this capture file.

9
  • Try -h 127.0.0.1. The entry mentioned in /etc/hosts is probably not the only IP assigned to localhost. Jul 12, 2015 at 17:36
  • Tried that, same result. Jul 12, 2015 at 17:37
  • Then telnet 127.0.0.1 5432 in the hope of getting a more meaningful error message. Jul 12, 2015 at 17:40
  • Connects and then is closed by the other end. Output added at the end of the question. Jul 12, 2015 at 20:15
  • That's a very weird issue. psql doesn't just exit silently like that. If the connection is closed unexpectedly by the other end it'll print a message when you try the next query, not just exit. Similarly on authentication failures. I have a hard time guessing what might be going on here. In your position I'd be attaching a debugger, and/or doing network traffic capture, to figure out what's going on. Jul 13, 2015 at 0:08

2 Answers 2

1

Thanks to the suggestion on pgsql-general that I check TCP/IP with netcat I discovered that the first few bytes sent to localhost were being swallowed by a process that was intended to keep an audit trail of Internet access. Uninstalled and everything works as I expect.

0

There seems to be a special case in the PostgreSQL client libraries for localhost and 127.0.0.1. Instead of trying to hit the TCP port, it looks for the UNIX domain socket instead.

I was able to work around this by specifying localhost. as the hostname. (I was also able to get the IPv6 ::1 address to work.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.