3

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.

  • Try -h 127.0.0.1. The entry mentioned in /etc/hosts is probably not the only IP assigned to localhost. – Daniel Vérité Jul 12 '15 at 17:36
  • Tried that, same result. – iftheshoefritz Jul 12 '15 at 17:37
  • Then telnet 127.0.0.1 5432 in the hope of getting a more meaningful error message. – Daniel Vérité Jul 12 '15 at 17:40
  • Connects and then is closed by the other end. Output added at the end of the question. – iftheshoefritz Jul 12 '15 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. – Craig Ringer Jul 13 '15 at 0:08
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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