Connection error

Richard Sydney-Smith wrote:

An associate is using Postgresql 8 on a windows xp system. The
installation has been operating for several months and he has recently
tried installing some software and postgresql has begun refusing
connection " could not receive server response to SSL negotiation packet".

Unfortunately he is unable to tell me what was done! We have tried
turning off the firewall, opening connections to "trust" but so far have
not been able to connect.

Do you get different messages with SSL disabled?
Can you connect with psql locally?
Can you connect with psql via localhost?

Quickest solution is probably to run ethereal on each end (I think
there's a Windows version) and see what messages are going back and
fore. I'd still suspect a firewall or client library problem.

--
Richard Huxton
Archonet Ltd

Richard Sydney-Smith schrieb:

An associate is using Postgresql 8 on a windows xp system. The
installation has been operating for several months and he has recently
tried installing some software and postgresql has begun refusing
connection " could not receive server response to SSL negotiation packet".

Unfortunately he is unable to tell me what was done! We have tried
turning off the firewall, opening connections to "trust" but so far have
not been able to connect.

We can confirm that the postgresql service is operational. It starts /
stops cleanly.

Can someone point me on how to get this site operational again without
having to do a complete database re-install?

Eventually, is Windows XP updated to SP2?

--
Mario Guenterberg
mattheis. werbeagentur
IT Engineer / Projektleiter

Thanks for your advice:

Do you get different messages with SSL disabled?
Yes - "Error connecting to the server: server closed the connection
unexpectedly. This probably means the server terminated abnormally before
or while processing the request."
Can you connect with psql locally?
No
Can you connect with psql via localhost?
No

The program installed was a web excelerator. I think the Windows Firewall
exceptions were turned off for PG and the program calling it. They are now
on.

Will try the ethereal option.

All recommended Windows updates are installed.

Darren Meerwald
FlagDisplays.com
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Richard Huxton
Sent: Friday, 22 July 2005 15:31
To: Richard Sydney-Smith
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Connection error

Richard Sydney-Smith wrote:

An associate is using Postgresql 8 on a windows xp system. The
installation has been operating for several months and he has recently
tried installing some software and postgresql has begun refusing
connection " could not receive server response to SSL negotiation packet".

Unfortunately he is unable to tell me what was done! We have tried
turning off the firewall, opening connections to "trust" but so far have
not been able to connect.

Do you get different messages with SSL disabled?
Can you connect with psql locally?
Can you connect with psql via localhost?

Quickest solution is probably to run ethereal on each end (I think
there's a Windows version) and see what messages are going back and
fore. I'd still suspect a firewall or client library problem.

--
Richard Huxton
Archonet Ltd

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.9.2/55 - Release Date: 21/07/05

WA Pennant & Flag Displays - Darren wrote:

Thanks for your advice:

Do you get different messages with SSL disabled?
Yes - "Error connecting to the server: server closed the connection
unexpectedly. This probably means the server terminated abnormally before
or while processing the request."

Good - so it's not just SSL related.

Can you connect with psql locally?
No
Can you connect with psql via localhost?
No

Hmm - that makes the firewall options slightly less likely.

1. Make sure PG is listening on port 5432 and that log_connections is
set in your postgresql.conf. Then check your pg_hba.conf allows
connections from localhost and your local network. Restart PG.

2. Try telnetting to localhost, port 5432 (from the command-line in
Windows it should be "telnet localhost:5432" I think). If it connects,
hit return a couple of times and you should be disconnected.
PostgreSQL's logs should say something about an invalid startup packet.

If it doesn't even connect, then suspect a firewall.

The program installed was a web excelerator. I think the Windows Firewall
exceptions were turned off for PG and the program calling it. They are now
on.

Will try the ethereal option.

Seems less useful if you can't connect locally.

All recommended Windows updates are installed.

--
Richard Huxton
Archonet Ltd

WA Pennant & Flag Displays - Darren wrote:

Hi Richard,

Thanks for your advice. I implemented your tests as shown below and they
seem to indicate there's a firewall problem (but the firewall is off). I
found though that I can probably retrieve the data and have discussed what
I've been trying at the bottom of the email. Could you please let me know
your thoughts on what I have been doing wrong or should do in order to
retrieve the data.

Can you connect with psql locally?
No
Can you connect with psql via localhost?
No

Hmm - that makes the firewall options slightly less likely.

1. Make sure PG is listening on port 5432 and that log_connections is
set in your postgresql.conf.

Under 'Connections and Authentication' postgresql.conf displays:
listen_addresses = '*'
port = 5432
I've now also added:
log_connections = yes

Good. I take it you're not seeing any connections.

Then check your pg_hba.conf allows

connections from localhost and your local network. Restart PG.

pg_hba.conf shows (does this cover the localhost?):
local all all all trust

On a unix machine it covers connection over unix-sockets. Not sure about
Windows.

# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 md5
host all all 192.168.1.230/32 md5
host all all 192.168.1.240/32 md5
host all all 192.168.1.210/32 md5
host all all 192.168.1.211/32 md5
host all all 192.168.1.220/32 md5
host all remote all md5
hostnossl all all 127.0.0.1/32 md5

2. Try telnetting to localhost, port 5432 (from the command-line in
Windows it should be "telnet localhost:5432" I think). If it connects,
hit return a couple of times and you should be disconnected.
PostgreSQL's logs should say something about an invalid startup packet.

If it doesn't even connect, then suspect a firewall.

Connection failed (even with Microsoft's firewall off)

Well, if you're not getting *anything* then either:
1. There is nothing on that port
2. There is some sort of firewall interfering.

I did a backup on 5th July and this incident happened after the 19th July.
I've found http://www.postgresql.org/docs/8.0/interactive/backup-online.html
which seems to indicate that my data is retrievable, however I'm doing
something wrong. I have a new installation of postgres on another compter
and have restored the data of the 5th July. I have used the sample
recovery.conf and the two pertinent lines of code together with relevant
comments are below.

Well, there's no evidence your postgresql backend is having problems.
This means recovery is simple.

1. Make sure both the old and new machine have the same version of
PostgreSQL installed (i.e. both 8.0.x)
2. Stop Postgresql on the machine you can't connect to.
3. Zip up the entire contents of the "data" directory - you want all of
it pg_clog/xlog - all of it.
4. Unzip it to the right location on the new machine
5. Restart PG on the new machine

Done - the new machine might go through some recovery, depending whether
the backend on the old machine was shut down cleanly.

NOTE - you can't necessarily move data this way between different
architectures (e.g. x86 <=> PPC <=> 64-bit x86), nor between different
releases (e.g. 8.0.x => 8.1.x).

--
Richard Huxton
Archonet Ltd

unsubscribe

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Richard Huxton
Sent: Monday, July 25, 2005 10:43 AM
To: WA Pennant & Flag Displays - Darren
Cc: richard@ibisau.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Connection error

WA Pennant & Flag Displays - Darren wrote:

Hi Richard,

Thanks for your advice. I implemented your tests as shown below and

they

seem to indicate there's a firewall problem (but the firewall is off).

I

found though that I can probably retrieve the data and have discussed

what

I've been trying at the bottom of the email. Could you please let me

know

your thoughts on what I have been doing wrong or should do in order to
retrieve the data.

Can you connect with psql locally?
No
Can you connect with psql via localhost?
No

Hmm - that makes the firewall options slightly less likely.

1. Make sure PG is listening on port 5432 and that log_connections is
set in your postgresql.conf.

Under 'Connections and Authentication' postgresql.conf displays:
listen_addresses = '*'
port = 5432
I've now also added:
log_connections = yes

Good. I take it you're not seeing any connections.

Then check your pg_hba.conf allows

connections from localhost and your local network. Restart PG.

pg_hba.conf shows (does this cover the localhost?):
local all all all trust

On a unix machine it covers connection over unix-sockets. Not sure about

Windows.

# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 md5
host all all 192.168.1.230/32 md5
host all all 192.168.1.240/32 md5
host all all 192.168.1.210/32 md5
host all all 192.168.1.211/32 md5
host all all 192.168.1.220/32 md5
host all remote all md5
hostnossl all all 127.0.0.1/32 md5

2. Try telnetting to localhost, port 5432 (from the command-line in
Windows it should be "telnet localhost:5432" I think). If it connects,
hit return a couple of times and you should be disconnected.
PostgreSQL's logs should say something about an invalid startup

packet.

If it doesn't even connect, then suspect a firewall.

Connection failed (even with Microsoft's firewall off)

Well, if you're not getting *anything* then either:
1. There is nothing on that port
2. There is some sort of firewall interfering.

I did a backup on 5th July and this incident happened after the 19th

July.

I've found

http://www.postgresql.org/docs/8.0/interactive/backup-online.html

which seems to indicate that my data is retrievable, however I'm doing
something wrong. I have a new installation of postgres on another

compter

and have restored the data of the 5th July. I have used the sample
recovery.conf and the two pertinent lines of code together with

relevant

comments are below.

Well, there's no evidence your postgresql backend is having problems.
This means recovery is simple.

1. Make sure both the old and new machine have the same version of
PostgreSQL installed (i.e. both 8.0.x)
2. Stop Postgresql on the machine you can't connect to.
3. Zip up the entire contents of the "data" directory - you want all of
it pg_clog/xlog - all of it.
4. Unzip it to the right location on the new machine
5. Restart PG on the new machine

Done - the new machine might go through some recovery, depending whether

the backend on the old machine was shut down cleanly.

NOTE - you can't necessarily move data this way between different
architectures (e.g. x86 <=> PPC <=> 64-bit x86), nor between different
releases (e.g. 8.0.x => 8.1.x).

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly