Sample Header Ad - 728x90

psql breaks if I specify host localhost

5 votes
2 answers
4243 views
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 .
Asked by iftheshoefritz (161 rep)
Jul 12, 2015, 01:01 PM
Last activity: Nov 21, 2023, 12:08 AM