Thread: PostgreSQL doesn't accept connections when Windows Server is rebooted

>
> Everything runs as expected.
>
> Until the Windows Server is rebooted.  When this occurs, the
> PostgreSQL service starts as expected, but won't accept connections.
>
> If the PostgreSQL service is then restarted manually (via
> Administrative Tools > Component Services > Restart), the service runs
> as expected and everything is normal.
>
> How can I correct this?

By providing us with some logging output that tells us what PostgreSQL
is doing. :)

Sincerely,

Joshua D. Drake


>
> Thanks in advance,
> George
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

George Weaver wrote:
>
> I then updated the postgresql.conf file with "listen_addresses = '*'
> and changed the port from 5432 (currently being used for a 7.3 server)
> to 5435.
>
Have you tried setting the port back to default and see what happens?
Is it possible for you to stop the 7.3 server for a moment to test this
theory?

The only thing you have different is the port, so I am thinking that it
has something to do with that.

Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

George,

I guess your service is not running after rebooting? You can check this via services.msc

Most propable cause: the postgres user does not have logon_as_service privilege.


--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

George,

Okay, that is a different situation. I was helping for the "serice not running after reboot" :)

Can you please check the log files produced by the server? If PostgreSQL started up successfully, there should be a new file in <data>\pg_log

(standard stderr redirection assumed)

whats in that ?

Harald


--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

On Nov 27, 2006, at 10:41 AM, Tony Caduto wrote:
>> I then updated the postgresql.conf file with "listen_addresses =
>> '*' and changed the port from 5432 (currently being used for a 7.3
>> server) to 5435.
>>
> Have you tried setting the port back to default and see what happens?
> Is it possible for you to stop the 7.3 server for a moment to test
> this theory?
>
> The only thing you have different is the port, so I am thinking
> that it has something to do with that.

Hmm... are you sure there isn't already something listening on port
5435?
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

From: "Jim Nasby"

> Hmm... are you sure there isn't already something listening on port  5435?

Hi Jim,

How might I determine this?

Would this make a difference when the system is rebooted vs manually
restarting the server?

Thanks,
George

> How might I determine this?

netstat -a -n

this will list you all current listened ports
you could also try and

telnet localhost 5432

to see if something is listening.

> Would this make a difference when the system is rebooted vs manually
> restarting the server?

when another program is occupying the port, it would probabyl not make much
difference, except if the pgsql service is started before the other tool.

maybe pgsql is already running? ;-)

cheers,
thomas

Hi Thomas

> netstat -a -n

Cool!

And -o gives me the pid of the process which I can match with processes
through the Task Manager...

> when another program is occupying the port, it would probabyl not make
> much difference, except if the pgsql service is started before the other
> tool.
>
> maybe pgsql is already running? ;-)

The problem I'm having is that if the server (Windows 2000) is rebooted, the
PostgreSQL service starts as expected, but won't accept connections.  Bur if
the PostgreSQL service is then restarted manually (via Administrative Tools
 > Component Services > Restart), the service runs as expected and everything
is normal.

If I understand what you're suggesting, perhaps upon reboot PostgreSQL is
starting before another app that listens on the same port, which could cause
the problem, but if I then restart the PostgreSQL service this solves the
problem?

Regards,
George

> If I understand what you're suggesting, perhaps upon reboot PostgreSQL is
> starting before another app that listens on the same port, which could
> cause the problem, but if I then restart the PostgreSQL service this
> solves the problem?

if you can start it manually, then its probably not a problem with another
tool using the same port.

a) do you have a software firewall installed on that machine that might be
interfering here?

b) is your windows in a domain?
if so, make sure you manually set "log on as a service" priviledges on the
domain controller for the local postgres user. the installed only sets local
priviledges that are overwritten upon reboot.

you find the setting here: start -> run -> mmc -> file -> add snapin ->
add -> group policy editor -> browse -> default domain policy
then browse for computer configuration -> windows settings ->  security
settings -> local policies -> user rights -> log on as a service. add the
user there.

regards,
- thomas

----- Original Message From: "Thomas H.", etc.

Thank you all for your replies and suggestions.  I apologize for not
replying earlier but the client is located several hours away and I have not
been back to the site until yesterday.

Here's an update of the situation (PostgreSQL 8.1.5 on Windows 2000 Server
system).

The server is not in a domain, but I followed Thomas' suggestion just the
same (>"make sure you manually set "log on as a service" priviledges on the
domain controller for the local postgres user").

Upon reboot of the Windows 2000 server:

    1.     PostgeSQL service starts without problem.
    2.     Server is listening on port 5435 as per postgresql.conf file
(verified by netstat).
    3.     No other application using port 5435.
    3.     (PostgreSQL 7.3 version listening on port 5432 - no problems)
    4.     Attempts to connect to PostgreSQL 8.1 result in the following
error message:

    C:\Program Files\PostgreSQL\8.1\bin>psql -h name.local -p 5435
feb01-06-01
        psql: server closed the connection unexpectedly
            This probably means the server terminated abnormally
            before or while processing the request.

    5.    There are no related entries in pg_log
    6.    The following error is in the Windows Event log:

    Event Type: Error
    Event Source: PostgreSQL
    Event Category: None
    Event ID: 0
    Date:  14/12/2006
    Time:  1:06:13 PM
    User:  N/A
    Computer: SERVER
    Description:
    FATAL:  could not reattach to shared memory (key=5435001,
addr=01590000): Invalid argument

    7.     If PostgreSQL Service is restarted via Control Panel >
Administrative Tools > Component Services, everything runs normally and
there are no problems connecting.

Any further thoughts?

Thanks in advance,
George