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?

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&gt;

, or perhaps using \o in psql to redirect results to a file.

<http://www.postgresql.org/docs/current/static/app-psql.html&gt;

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

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

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

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

From pgsql-general-owner@postgresql.org Wed Aug 25 22:51:37 2004

X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id 8BE195E37D0
for <pgsql-general-postgresql.org@localhost.postgresql.org>; Wed, 25 Aug 2004 22:51:36 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 36014-01
for <pgsql-general-postgresql.org@localhost.postgresql.org>;
Thu, 26 Aug 2004 01:51:31 +0000 (GMT)
Received: from sccrmhc11.comcast.net (sccrmhc11.comcast.net [204.127.202.55])
by svr1.postgresql.org (Postfix) with ESMTP id 7978A5E3632
for <pgsql-general@postgresql.org>; Wed, 25 Aug 2004 22:51:28 -0300 (ADT)
Received: from kenny.ugholf.net ([67.161.200.254])
by comcast.net (sccrmhc11) with ESMTP
id <2004082601513301100c3tmhe>; Thu, 26 Aug 2004 01:51:33 +0000
Received: from jandr.org (rjo-200-170-42-140.dial-up.vento.com.br [200.170.42.140] (may be forged))
(authenticated bits=0)
by kenny.ugholf.net (8.12.10/8.12.10) with ESMTP id i7Q1pNtv029795
(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO)
for <pgsql-general@postgresql.org>; Wed, 25 Aug 2004 19:51:29 -0600
Message-ID: <412D431F.2050908@jandr.org>
Date: Wed, 25 Aug 2004 22:55:43 -0300
From: Jon Lapham <lapham@jandr.org>
Reply-To: lapham@jandr.org
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040510
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: EXPLAIN ANALYZE total runtime != walltime
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.6 tagged_above=0.0 required=5.0
tests=RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL
X-Spam-Level: *
X-Archive-Number: 200408/1335
X-Sequence-Number: 64890

I have been using the EXPLAIN ANALYZE command to debug some performance
bottlenecks in my database. In doing so, I have found an oddity (to me
anyway). The "19ms" total runtime reported below actually takes 25
seconds on my computer (no other CPU intensive processes running). Is
this normal for EXPLAIN ANALYZE to report a total runtime so vastly
different from wall clock time?

During the "explain ANALYZE delete from msgid;" the CPU is pegged at
100% for the postmaster process, and the HD light only flashes
intermittently, so I do not think it is HD I/O.

I tossed in a "EXPLAIN ANALYZE VERBOSE" at the end of this email, in
case that helps anyone.

Thanks for any help!
-Jon

translator=> SELECT version();
version

---------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.2 on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 3.3.3 20040216 (Red Hat Linux 3.3.3-2.1)
(1 row)

translator=> VACUUM ANALYZE ;
WARNING: skipping "pg_shadow" --- only table or database owner can
vacuum it
WARNING: skipping "pg_database" --- only table or database owner can
vacuum it
WARNING: skipping "pg_group" --- only table or database owner can vacuum it
VACUUM
translator=> BEGIN ;
BEGIN
translator=> explain ANALYZE delete from msgid;
QUERY PLAN

-----------------------------------------------------------------------------------------------------
Seq Scan on msgid (cost=0.00..23.81 rows=981 width=6) (actual
time=0.029..10.151 rows=981 loops=1)
Total runtime: 19.755 ms
(2 rows)
translator=> \d msgid
Table "public.msgid"
Column | Type | Modifiers

-----------+---------------+-------------------------------------------------------
id | integer | not null default
nextval('public.msgid_id_seq'::text)
projectid | integer | not null
msgid | text | not null
msgidmd5 | character(32) | not null
Indexes:
"msgid_pkey" primary key, btree (id)
Foreign-key constraints:
"$1" FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE
translator=> rollback;
ROLLBACK
translator=> explain ANALYZE VERBOSE delete from msgid;
QUERY PLAN

----------------------------------------------------------------------------------------------------
{SEQSCAN
:startup_cost 0.00
:total_cost 23.81
:plan_rows 981
:plan_width 6
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 27
:restypmod -1
:resname ctid
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk true
}
:expr
{VAR
:varno 1
:varattno -1
:vartype 27
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno -1
}
}
)

:qual <>
:lefttree <>
:righttree <>
:initPlan <>
:extParam ()

:allParam ()

:nParamExec 0
:scanrelid 1
}

Seq Scan on msgid (cost=0.00..23.81 rows=981 width=6) (actual
time=0.031..6.444 rows=981 loops=1)
Total runtime: 35.760 ms
(46 rows)

--
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham <lapham@jandr.org> Rio de Janeiro, Brasil
Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------

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 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