Обсуждение: Problem to connect to the Windows Port
Hello everybody
I have installed the Windows-Port on a the machine 'mini' in the local
network.
In postgresql.conf on mini listen_adresses is set to '*'
When trying to connect to this installation from the machine 'mach1' I
get the following error:
conni@mach1 ~
$ psql -h mini -U postgres -d minitest
psql: could not connect to server: Connection refused
Is the server running on host "mini" and accepting
TCP/IP connections on port 5432?
I shut down the database server on mini and restarted it with the
option -i.
Then the error looks like this:
conni@mach1 ~
$ psql -h mini -U postgres -d minitest
psql: FATAL: no pg_hba.conf entry for host "192.168.1.2", user
"postgres", data
base "minitest"
All these errormessages appear on both machines.
The IP for mach1 is 192.168.1.2
The pg_hba.conf on mini contains the entries:
host all all 192.168.1.2
192.168.255.255 password
host template1 postgres 192.168.1.2 192.168.255.255
password
host minitest postgres 192.168.1.2 192.168.255.255
password
Do I miss something?
Any help is appreciated.
Regards
Conni
>Hello everybody > >I have installed the Windows-Port on a the machine 'mini' in the local >network. >In postgresql.conf on mini listen_adresses is set to '*' >When trying to connect to this installation from the machine 'mach1' I >get the following error: > >conni@mach1 ~ >$ psql -h mini -U postgres -d minitest >psql: could not connect to server: Connection refused > Is the server running on host "mini" and accepting > TCP/IP connections on port 5432? > >I shut down the database server on mini and restarted it with the >option -i. > >Then the error looks like this: > >conni@mach1 ~ >$ psql -h mini -U postgres -d minitest >psql: FATAL: no pg_hba.conf entry for host "192.168.1.2", user >"postgres", data >base "minitest" > >All these errormessages appear on both machines. > >The IP for mach1 is 192.168.1.2 >The pg_hba.conf on mini contains the entries: > >host all all 192.168.1.2 >192.168.255.255 password >host template1 postgres 192.168.1.2 192.168.255.255 >password >host minitest postgres 192.168.1.2 192.168.255.255 >password > >Do I miss something? The pg_hba lines should be: host all all 192.168.1.2 255.255.255.255 password host template1 postgres 192.168.1.2 255.255.255.255 password host minitest postgres 192.168.1.2 255.255.255.255 password 192.168.255.255 is not a valid netmask. The mask above will match one host. If you meant to match the entire subnet (192.168.*.*), instead put host minitest postgres 192.168.0.0 255.255.0.0 password //Magnus
Hi Magnus
Thanks.
> If you meant to match the entire subnet (192.168.*.*), instead put
> host minitest postgres 192.168.0.0 255.255.0.0 password
Ok. I changed the line in pg_hba.conf to
host all all 192.168.0.0 255.255.0.0 password
then rebooted the computer and tried again:
C:\>psql -h 192.168.1.8 -U postgres -d minitest
psql: could not connect to server: Connection refused
Is the server running on host "192.168.1.8" and accepting
TCP/IP connections on port 5432?
The server on 192.168.1.8 is running, (I can connect on this machine
to localhost/minitest.).
I can ping 192.168.1.8
Regards
Conni
"Cornelia Boenigk" <poppcorn@cornelia-boenigk.de> writes:
> C:\>psql -h 192.168.1.8 -U postgres -d minitest
> psql: could not connect to server: Connection refused
> Is the server running on host "192.168.1.8" and accepting
> TCP/IP connections on port 5432?
"Connection refused" suggests that you've got a firewall-type problem,
ie the operating system is rejecting the connection rather than letting
the postmaster receive it. Check packet filtering rules...
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > "Cornelia Boenigk" <poppcorn@cornelia-boenigk.de> writes: >> C:\>psql -h 192.168.1.8 -U postgres -d minitest >> psql: could not connect to server: Connection refused >> Is the server running on host "192.168.1.8" and accepting >> TCP/IP connections on port 5432? > > "Connection refused" suggests that you've got a firewall-type problem, > ie the operating system is rejecting the connection rather than letting > the postmaster receive it. Check packet filtering rules... It's also possible that the postmaster is listening on a port other than 5342... -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
Cornelia Boenigk wrote: > Hi Magnus > > Thanks. > > >>If you meant to match the entire subnet (192.168.*.*), instead put >>host minitest postgres 192.168.0.0 255.255.0.0 password > > > Ok. I changed the line in pg_hba.conf to > > host all all 192.168.0.0 255.255.0.0 password > > then rebooted the computer and tried again: > > C:\>psql -h 192.168.1.8 -U postgres -d minitest > psql: could not connect to server: Connection refused > Is the server running on host "192.168.1.8" and accepting > TCP/IP connections on port 5432? > > The server on 192.168.1.8 is running, (I can connect on this machine > to localhost/minitest.). > I can ping 192.168.1.8 I could you execute on your server the following command: netstat -an and look for the row that say LISTENING on port 5432 ? Regards Gaetano Mendola
Hi, I'm a newbie at postgreSQL, although i have years of experience with MySQL. My question is (and i tried to found the answer): why this gives an error ? SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM urls_negados; Any help would be appreciated. Sorry for the basic answer :( Warm Regards, Mário Gamito
On Aug 25, 2004, at 8:37 PM, Mário Gamito wrote: > why this gives an error ? > > SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM > urls_negados; I'm not sure whether this is a MySQLism or part of the SQL spec not implemented in PostgreSQL, but I suspect the former. INTO OUTFILE is not valid in PostgreSQL. You might want to look at the COPY command <http://www.postgresql.org/docs/current/static/sql-copy.html> , or perhaps using \o in psql to redirect results to a file. <http://www.postgresql.org/docs/current/static/app-psql.html> The PostgreSQL SQL command reference lists valid SQL syntax. I think you'll find it useful. Michael Glaesemann grzm myrealbox com
Mário Gamito wrote: > Hi, > > I'm a newbie at postgreSQL, although i have years of experience with MySQL. Hi Mario. No problem, but try not to reply to existing messages to post a new question. > My question is (and i tried to found the answer): > why this gives an error ? > > SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM urls_negados; INTO OUTFILE ... is not SQL. From psql you can use: \o MYFILE.TXT SELECT * FROM ... SQL syntax is in the "SQL COMMANDS" section of the manuals. See the "client applications" section for details on psql. HTH -- Richard Huxton Archonet Ltd
On Wed, 2004-08-25 at 12:37, Mário Gamito wrote: > Hi, > > I'm a newbie at postgreSQL, although i have years of experience with MySQL. > > My question is (and i tried to found the answer): > why this gives an error ? > > SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM urls_negados; > > Any help would be appreciated. It isn't valid PostgreSQL syntax. PostgreSQL tries to stick to SQL standards as far as possible. Use SELECT INTO to create a new table. To dump to a flat file, use COPY: COPY urls_negado (url_negado) TO '/tmp/urls_negados.txt'; Nulls will be represented as \N -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== "But the day of the Lord will come as a thief in the night. The heavens shall pass away with a great noise, and the elements shall melt with fervent heat, and the earth and the works that are therein shall be burned up." II Peter 3:10
Mário Gamito wrote: > Hi, > > I'm a newbie at postgreSQL, although i have years of experience with MySQL. > > My question is (and i tried to found the answer): > why this gives an error ? > > SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM urls_negados; > > Any help would be appreciated. You can not do it in postgres in that way. Try with this: \o '/tmp/urls_negados.txt' select url_negado from urls_negados; \o Regards Gaetano Mendola
Hi all > "Connection refused" suggests that you've got a firewall-type problem, The port 5432 is open in bith directions. I can connect to remote databases from all machines and all OS without any problem. Inside the local network I can access the databases in a Cygwin-installation on another machine from the Windows Port but not vice versa. > netstat -an and look for the row that say LISTENING on port 5432 ? On both machines there is the line TCP 0.0.0.0:5432 0.0.0.0:0 LISTENING Regards Conni
On Wed, 25 Aug 2004, [ISO-8859-1] Mário Gamito wrote: > Hi, > > I'm a newbie at postgreSQL, although i have years of experience with MySQL. > > My question is (and i tried to found the answer): > why this gives an error ? > > SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM urls_negados; > > Any help would be appreciated. > > Sorry for the basic answer :( > > Warm Regards, > Mário Gamito > Check out COPY and \copy. COPY is server-side, thus it's executed on the host the server runs on, with the permissions of the user the server is running as. \copy is a psql command, thus client-side. It's executed on the client host and with the permissions of the user running psql (you). If you are writing your own client application, you can use COPY ... FROM STDIN and COPY ... TO STDOUT (which is what psql does). http://www.postgresql.org/docs/7.4/interactive/sql-copy.html http://www.postgresql.org/docs/7.4/interactive/app-psql.html .TM. -- ____/ ____/ / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _____/ _____/ _/ Colombo@ESI.it
On 8/25/2004 3:11 PM, Cornelia Boenigk wrote: > Hi all > >> "Connection refused" suggests that you've got a firewall-type > problem, Hi Conni, Gruß aus Philadelphia connection refused means at least that the machine responded to the connection attempt, but that (it pretended?) nobody is listening on the port. The firewall I would set up would not respond at all, but simply drop the packet. So I doubt you're facing a firewall problem here. Are you sure that you are trying to contact the right host? Jan > > The port 5432 is open in bith directions. > I can connect to remote databases from all machines and all OS without > any problem. Inside the local network I can access the databases in a > Cygwin-installation on another machine from the Windows Port but not > vice versa. > >> netstat -an and look for the row that say LISTENING on port 5432 ? > > On both machines there is the line > TCP 0.0.0.0:5432 0.0.0.0:0 LISTENING > > Regards > Conni > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
I've been watching this thread because I am experience exactly the same thing. I can also repeat all the diagnostic results of Conni. Postmaster resides on a Win 2K box. I can telnet to the server no problem. My pg_hba file line entries will allow access from all hosts, all users, all databases on ip 192.168.0.0 with a mask of 0.0.0.0 method=trust. Connection to the postmaster on the local machine works just fine, but I just cannot get it to connect remotely. -----Original Message----- From: Jan Wieck [mailto:JanWieck@Yahoo.com] Sent: August 25, 2004 8:27 PM To: Cornelia Boenigk Cc: pgsql-general Subject: Re: Problem to connect to the Windows Port On 8/25/2004 3:11 PM, Cornelia Boenigk wrote: > Hi all > >> "Connection refused" suggests that you've got a firewall-type > problem, Hi Conni, Gruß aus Philadelphia connection refused means at least that the machine responded to the connection attempt, but that (it pretended?) nobody is listening on the port. The firewall I would set up would not respond at all, but simply drop the packet. So I doubt you're facing a firewall problem here. Are you sure that you are trying to contact the right host? Jan > > The port 5432 is open in bith directions. > I can connect to remote databases from all machines and all OS without > any problem. Inside the local network I can access the databases in a > Cygwin-installation on another machine from the Windows Port but not > vice versa. > >> netstat -an and look for the row that say LISTENING on port 5432 ? > > On both machines there is the line > TCP 0.0.0.0:5432 0.0.0.0:0 LISTENING > > Regards > Conni > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
OK, I've found what worked for me. By default, the pg_hba file installs like so: # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD local all all trust # IPv4-style local connections: host all all 127.0.0.1 255.255.255.255 trust # IPv6-style local connections: host all all ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff trust Once I removed the IPv6-style line, then I could add my own IP + Mask combination and it worked fine (in my case this is 192.168.0.0 255.255.0.0) Perhaps upon installation the IPv6 line should be "remmed" out by default? -----Original Message----- From: Anony Mous [mailto:A.Mous@shaw.ca] Sent: August 26, 2004 5:55 AM To: 'Jan Wieck'; 'Cornelia Boenigk' Cc: 'pgsql-general' Subject: RE: Problem to connect to the Windows Port I've been watching this thread because I am experience exactly the same thing. I can also repeat all the diagnostic results of Conni. Postmaster resides on a Win 2K box. I can telnet to the server no problem. My pg_hba file line entries will allow access from all hosts, all users, all databases on ip 192.168.0.0 with a mask of 0.0.0.0 method=trust. Connection to the postmaster on the local machine works just fine, but I just cannot get it to connect remotely. -----Original Message----- From: Jan Wieck [mailto:JanWieck@Yahoo.com] Sent: August 25, 2004 8:27 PM To: Cornelia Boenigk Cc: pgsql-general Subject: Re: Problem to connect to the Windows Port On 8/25/2004 3:11 PM, Cornelia Boenigk wrote: > Hi all > >> "Connection refused" suggests that you've got a firewall-type > problem, Hi Conni, Gruß aus Philadelphia connection refused means at least that the machine responded to the connection attempt, but that (it pretended?) nobody is listening on the port. The firewall I would set up would not respond at all, but simply drop the packet. So I doubt you're facing a firewall problem here. Are you sure that you are trying to contact the right host? Jan > > The port 5432 is open in bith directions. > I can connect to remote databases from all machines and all OS without > any problem. Inside the local network I can access the databases in a > Cygwin-installation on another machine from the Windows Port but not > vice versa. > >> netstat -an and look for the row that say LISTENING on port 5432 ? > > On both machines there is the line > TCP 0.0.0.0:5432 0.0.0.0:0 LISTENING > > Regards > Conni > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Hi Jan > but that (it pretended?) nobody is listening on the > port. netstat says it is listening on port 5432. > Are you > sure that you are trying to contact the right host? Of course ;-) Trying to connect from another machine running Win98 I get a socket error with no description available ?? Regards Conni
Hi Anony > Once I removed the IPv6-style line, then I could add my own IP + Mask > combination and it worked fine (in my case this is 192.168.0.0 > 255.255.0.0) This doesn't help ;-( The connection is still refused. Regards Conni
On 8/26/2004 2:18 PM, Cornelia Boenigk wrote: > Hi Jan > >> but that (it pretended?) nobody is listening on the >> port. > > netstat says it is listening on port 5432. > >> Are you >> sure that you are trying to contact the right host? > > Of course ;-) > > Trying to connect from another machine running Win98 I get a socket > error with no description available ?? oh wait ... this isn't by chance Windows-XP or some 3rd party firewall that is blocking the traffic? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Hi Jan > oh wait ... this isn't by chance Windows-XP or some 3rd party firewall > that is blocking the traffic? No, it's Win2k on both computers and the firewall is built in in the DSL router. If it was a problem with the firewall I couldn't connect from the Win Port to Cygwin PostgreSQL either, isnt'it? Regards Conni