Thread: Connection error

Connection error

From
Richard Sydney-Smith
Date:
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?

thanks muchly

Richard Sydney-Smith
ibisau.com



Re: Connection error

From
Richard Huxton
Date:
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

Re: Connection error

From
Mario Guenterberg
Date:
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

Re: Connection error

From
"WA Pennant & Flag Displays - Darren"
Date:
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



Re: Connection error

From
Richard Huxton
Date:
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

Re: Connection error

From
Richard Huxton
Date:
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

Re: Connection error

From
"Jonathan Lam"
Date:
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