Thread: Can't connect

Bob Pawley wrote:
> Hi
>
> My copy of PostgreSQL version 8.3 has decided not to receive a
> connection after an idle time measured in hours.

Odd.

> It acceptes the connection one I stop the server and then restart. At
> this point, it always asks for the password.
>
> Here is the log of the event -
>
> FATAL:  could not reattach to shared memory (key=1804,
> addr=01700000): 487 2009-10-29 00:19:20 PDT WARNING:  worker took too
> long to start; cancelled

That "worker took too long..." message is from autovaccuum, so I'm not
sure it's directly responsible. The "could not reattach to shared
memory" error looks familiar though.

Are you:
1. On Windows?
2. Running some sort of anti-virus?

Also, you might want to read this news item from September:
  http://www.postgresql.org/about/news.1135

Are you already on 8.3.8?

--
  Richard Huxton
  Archonet Ltd

I am on Windows and am running an anti virus program. But I was running the
same programs on Windows before without this problem.

Bob
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, October 29, 2009 10:24 AM
Subject: Re: [GENERAL] Can't connect


> Bob Pawley wrote:
>> Hi
>>
>> My copy of PostgreSQL version 8.3 has decided not to receive a
>> connection after an idle time measured in hours.
>
> Odd.
>
>> It acceptes the connection one I stop the server and then restart. At
>> this point, it always asks for the password.
>>
>> Here is the log of the event -
>>
>> FATAL:  could not reattach to shared memory (key=1804,
>> addr=01700000): 487 2009-10-29 00:19:20 PDT WARNING:  worker took too
>> long to start; cancelled
>
> That "worker took too long..." message is from autovaccuum, so I'm not
> sure it's directly responsible. The "could not reattach to shared
> memory" error looks familiar though.
>
> Are you:
> 1. On Windows?
> 2. Running some sort of anti-virus?
>
> Also, you might want to read this news item from September:
>  http://www.postgresql.org/about/news.1135
>
> Are you already on 8.3.8?
>
> --
>  Richard Huxton
>  Archonet Ltd
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Bob Pawley wrote:

> FATAL:  could not reattach to shared memory (key=1804, addr=01700000): 487
> 2009-10-29 00:19:20 PDT WARNING:  worker took too long to start; cancelled
>
> Is there some way of ensuring that the server always accepts a connection?

This is a known bug, supposedly fixed in 8.3.8.  Update and let us know
if it reocurrs.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Bob Pawley wrote:
> I am on Windows and am running an anti virus program. But I was running
> the same programs on Windows before without this problem.

Well, as long as you're happy you've ruled out your anti-virus, and
you're running 8.3.8 then you'll want to monitor it and next time it
happens record:
1. What connections are already open
2. Whether PG receives any connection attempt at all
3. Whether your a/v+firewall sees the connection attempt

Items 2,3 will require you to check that you are logging the right
information (PG can log connection and disconnection). You'll need to
consult your a/v manuals for details on its logging.

If PostgreSQL isn't accepting any connections then you can't connect and
list what others it has. You should be able to see each in Task Manager,
but probably something like sysinternals' "process explorer" will be
more useful. Check how many connections and whether any are unreasonably
large, hung etc. Ideally the developers would probably want to see
output from a debugger, but I'm guessing that's not straightforward on
Windows.

It's possible there is a corner case that the fix in 8.3.8 doesn't
handle and it would be useful to know.

--
  Richard Huxton
  Archonet Ltd