postgres: localhost not connecting

Question

My postgreSQL.conf looks like

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
port = 5432                             # (change requires restart)

and I also know that postgres is running

air:data postgres$ ps -aef | grep postgres
  504 16474 16473   0 11:34AM ??         0:00.00 postgres: logger process
  504 16476 16473   0 11:34AM ??         0:00.00 postgres: writer process
  504 16477 16473   0 11:34AM ??         0:00.00 postgres: wal writer process
  504 16478 16473   0 11:34AM ??         0:00.00 postgres: autovacuum launcher process
  504 16479 16473   0 11:34AM ??         0:00.00 postgres: stats collector process
    0 16087 16078   0 10:54AM ttys001    0:00.03 su - postgres
  504 16473     1   0 11:34AM ttys001    0:00.22 /Library/PostgreSQL/9.1/bin/postgres -D/Library/PostgreSQL/9.1/data
  504 16484 16088   0 11:34AM ttys001    0:00.00 grep postgres

But I am not able to connect

 psql -Uuser -W
Password for user user:
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

Also, when I run the following

 lsof -i tcp:5432
 ✘ me@air11:37:13 ⮀ ~ ⮀ netstat -a | grep postgres
tcp6       0      0  *.postgres             *.*                    LISTEN
tcp4       0      0  *.postgresql           *.*                    LISTEN

It says nothing running on port 5432 What am I missing?

UPDATE

My pg_hba.conf looks like

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Are you on a Mac OS X machine by any chance? (It's generally good to mention your platform) — Craig Ringer
– Craig Ringer, Commented Mar 26, 2013 at 23:38

1ac0 · Accepted Answer · 2013-03-26 19:22:28Z

5

In pg_hba.conf:

host    all             all             127.0.0.1/32            trust

last column change to trust

answered Mar 26, 2013 at 19:22

1ac0

2,9593 gold badges36 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel Vérité · Accepted Answer · 2013-03-26 19:16:14Z

You need to use the psql program that comes that the PostgreSQL package that you installed instead of the psql in the /usr/bin directory that is part of the Apple system.

For example, if using postgres.app, they say in their documentation:

Mac OS 10.7 ships with an older version of PostgreSQL, which can be started with the following command:

$ psql -h localhost PostgreSQL ships with a constellation of useful binaries, like pg_dump or pg_restore, that you will likely want to use. Go ahead and add the /bin directory that ships with Postgres.app to your PATH (preferably in .profile, .bashrc, .zshrc, or the like to make sure this gets set for every Terminal session):

PATH="/Applications/Postgres.app/Contents/MacOS/bin:$PATH"
Once your path is correctly set up, you should be able to run psql without a host. (If not, check that the correct version is being loaded in the PATH by doing which psql)

If using another source of pre-packaged postgresql, it's the same thing with different paths.

daydreamer · Accepted Answer · 2013-03-26 19:23:08Z

1

Not sure why this was happening, but I found postgresapp.com which is pretty good to use

I am using this with http://www.pgadmin.org/ and I am running it smoothly so far

answered Mar 26, 2013 at 19:23

daydreamer

92.9k204 gold badges477 silver badges754 bronze badges

Comments

Patrick · Accepted Answer · 2013-03-27 07:33:14Z

1

you can try to stop service and agian start the service. once you reload the configuration file. check again while it is working or not.

answered Mar 27, 2013 at 7:33

Patrick

732 silver badges8 bronze badges

Comments

Christoph Wolf · Accepted Answer · 2024-06-08 12:05:56Z

I had a similar issue in the pg_hba.conf but with trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

host all all all scram-sha-256

all but the last had trust as METHOD

and annoyingly enough that worked for my laptop but not the desktop despite all the purging I did.

So for anyone that will have a similar issue, give this a try ;-)

Collectives™ on Stack Overflow

postgres: localhost not connecting

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related