Thread: Cannot connect remotely to postgresql
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.confsuch as add: local all all trust 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 amstill able to connect the server using "psql -h localhost -U postgres -d xxx" I am assuming (perhaps incorrectly) thatI shouldn't be able to do this. So now I'm completely stumped. I've searched my computer and can't find any other conffiles. I recently set $PGDATA to "C:\Program Files (x86)\PostgreSQL\8.3\data\" and the same in my $PATH for bin. Ido notice that lib is not installed in my $PATH but assume that would not affect my connection. Any ideas?
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. > > Ray. > > -- > Raymond O'Donnell :: Galway :: Ireland > rod@iol.ie
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)\etcetc" and it sent a signal and voila it worked. I have since put everything back to having the postgresql servicestart automatically upon machine startup and its back to not working. In fact, when I run pg_ctl status from a freshboot 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 somewhereelse. Very peculiar behaviour. I have some resolve from my madness. At least I can manually start the serviceand have it running properly. 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? > > Ray. > > -- > Raymond O'Donnell :: Galway :: Ireland > rod@iol.ie
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