Cannot connect remotely to postgresql

On 19/01/2012 17:27, Willem Buitendyk wrote:

I have 8.2 installed on 64bit windows 7. I have no problem making a
local connection. However, when I make changes to pg_hba.conf such
as add:

local all all trust

What is the exact error message you're getting?

Did you restart the server after changing pg_hba.conf?

Also, I don't think "local" rules do anything on windows - you need to
add a "host" rule as the connections are over TCP/IP (though I could be
wrong).

I still cannot connect through a VPN. On a hunch that my pg server
was not using the config files in "C:\Program Files
(x86)\PostgreSQL\8.3\data" I changed the port in postgresql.conf to
5433 and restarted the server. After doing this I am still able to
connect the server using "psql -h localhost -U postgres -d xxx" I am
assuming (perhaps incorrectly) that I shouldn't be able to do this.

That does seem odd - you should need the -p option for anything other
than the standard port.

Is there any chance that you have more than one installation running on
the machine, and the other one is listening on port 5432?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 2012-01-19, at 10:10 AM, Raymond O'Donnell wrote:

On 19/01/2012 17:27, Willem Buitendyk wrote:

I have 8.2 installed on 64bit windows 7. I have no problem making a
local connection. However, when I make changes to pg_hba.conf such
as add:

local all all trust

What is the exact error message you're getting?

Did you restart the server after changing pg_hba.conf?

Also, I don't think "local" rules do anything on windows - you need to
add a "host" rule as the connections are over TCP/IP (though I could be
wrong).

I still cannot connect through a VPN. On a hunch that my pg server
was not using the config files in "C:\Program Files
(x86)\PostgreSQL\8.3\data" I changed the port in postgresql.conf to
5433 and restarted the server. After doing this I am still able to
connect the server using "psql -h localhost -U postgres -d xxx" I am
assuming (perhaps incorrectly) that I shouldn't be able to do this.

That does seem odd - you should need the -p option for anything other
than the standard port.

Is there any chance that you have more than one installation running on
the machine, and the other one is listening on port 5432?

There is only one service listed. If I try the following:

C:\Users\Willem>postgres -D "C:\Program Files (x86)\PostgreSQL\8.3\data"

I get:

2012-01-19 10:48:06 PST LOG: loaded library "$libdir/plugins/plugin_debugger.dl
l"
2012-01-19 10:48:06 PST LOG: could not bind IPv4 socket: No error
2012-01-19 10:48:06 PST HINT: Is another postmaster already running on port 543
3? If not, wait a few seconds and retry.
2012-01-19 10:48:06 PST WARNING: could not create listen socket for "10.0.1.7"

There appears to be no other instance of postgresql running on my system other then the one.
I will try a restart without the service starting automatically and try a manual start next.

I tried manually starting without the service automatically running using pg_ctl start -D "c:\program files (x86)\etc etc" which reported back that i might have another postmaster running. I then did pg_ctl reload -D "c:\program files (x86)\etc etc" and it sent a signal and voila it worked. I have since put everything back to having the postgresql service start automatically upon machine startup and its back to not working. In fact, when I run pg_ctl status from a fresh boot with the postgresql service automatically starting I get the return message of: pg_ctl: no server running.

So perhaps there is something with 8.3 and windows 64 specifically in that the configuration files are loading from somewhere else. Very peculiar behaviour. I have some resolve from my madness. At least I can manually start the service and have it running properly.

On 2012-01-19, at 10:10 AM, Raymond O'Donnell wrote:

On 19/01/2012 20:40, Willem Buitendyk wrote:

I tried manually starting without the service automatically running
using pg_ctl start -D "c:\program files (x86)\etc etc" which
reported back that i might have another postmaster running. I then
did pg_ctl reload -D "c:\program files (x86)\etc etc" and it sent a
signal and voila it worked. I have since put everything back to
having the postgresql service start automatically upon machine
startup and its back to not working. In fact, when I run pg_ctl
status from a fresh boot with the postgresql service automatically
starting I get the return message of: pg_ctl: no server running.

So are you saying that the PostgreSQL service isn't starting up
automatically on system boot, even though it's set to? If so, you need
to check the Windows event log and the Postgres logs to find the reason.

The fact that it works for you when logged in, but not at system boot,
smells to me like a permissions problem... but I'm not an expert.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie