Connection Refused Error

JohnD <lists@johndubchak.com> writes:

I can ssh into the server and do a psql <db-name> from the
/var/lib/pgsql command prompt, as user postgres. But, when I try to use
a different user (psql -U user -p <db-name>), from the same prompt, I get:

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

"-p" doesn't seem like the switch you meant to use here. I think it's
trying to feed the dbname to atoi() and getting a zero for the port
number.

regards, tom lane

On Monday 09 March 2009 4:11:49 pm JohnD wrote:

Hi,

I have two "identical" servers running CentOS 5.2 with PostgreSQL 8.3.5
installed on both. Prior to a reboot this morning, I was able to
connect, remotely, to both of them and doing telnet <serve-rname> 5432
brought up a prompt for them as well.

However, I am now in the unfortunate situation of not being able to
connect remotely to one particular server and cannot for the life of me
figure out why I am getting a connection refused:

Connection refused. Check that the hostname and port are correct and
that the postmaster is accepting TCP/IP connections.

I can ssh into the server and do a psql <db-name> from the
/var/lib/pgsql command prompt, as user postgres. But, when I try to use
a different user (psql -U user -p <db-name>), from the same prompt, I get:

This psql -U user -p <db-name> should be psql -U user -d <db-name>

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

When I look into the /tmp directory for the domain socket, I see:

srwxrwxrwx 1 postgres postgres 0 Mar 9 17:44 .s.PGSQL.5432
-rw------- 1 postgres postgres 25 Mar 9 17:44 .s.PGSQL.5432.lock

Also, here is the relevant piece of my pg_hba.conf file:

local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 192.168.0.0/16 md5
host all all 172.16.0.0/16 password # for a
VMWare instance

# IPv6 local connections:
host all all ::1/128 trust

And, lastly, I use the following script as the postgres user to start
PostgreSQL from the command prompt, manually:

#!/bin/bash

ARGV=$1
PG_HOME=/var/lib/pgsql
PG_WORK_DIR=$PG_HOME/data

if [ "$1" = "start" ]
then
pg_ctl -D $PG_WORK_DIR -l logfile start
elif [ "$1" = "stop" ]
then
pg_ctl -D $PG_WORK_DIR stop
fi

Nothing, that I am aware of, has changed on this server that would
prevent the remote connection. I have both SELinux and iptables
disabled (off by default) since this is inside a firewall on a home
network and is not available to the outside world.

Any idea why I am no longer able to connect?

Thanks for any and all help.

John

--
Adrian Klaver
aklaver@comcast.net

On Mon, 2009-03-09 at 18:11 -0500, JohnD wrote:

Hi,

Any idea why I am no longer able to connect?

What does your listen_addresses say on the affected server? Also just to
be safe do a /sbin/iptables -L and make sure you aren't blocking.

Joshua D. Drake

Thanks for any and all help.

John

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

Joshua D. Drake wrote:

What does your listen_addresses say on the affected server? Also just to
be safe do a /sbin/iptables -L and make sure you aren't blocking.

Joshua,

Thank you so much - that was it. My postgresql.conf listen_addresses
was commented out which defaulted to 'localhost'. Changing it to '*'
and restarting cleared the problem up. Not sure why this "stopped" working.

Thanks again - I was beating myself up about this all day.

John

On Mon, 2009-03-09 at 19:09 -0500, JohnD wrote:

Joshua D. Drake wrote:

What does your listen_addresses say on the affected server? Also just to
be safe do a /sbin/iptables -L and make sure you aren't blocking.

Joshua,

Thank you so much - that was it. My postgresql.conf listen_addresses
was commented out which defaulted to 'localhost'. Changing it to '*'
and restarting cleared the problem up. Not sure why this "stopped" working.

Thanks again - I was beating myself up about this all day.

:)

Joshua D. Drake

John

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997