Re: PostgreSQL doesn't accept connections when Windows

Hi everyone,

I recently installed PostgreSQL (Version 8.1.5.1) using the MSI installer on a Windows Server 2000 system.

I ran the installer logged in under an Administrator account, and set the service account to postgres.

During the installation process, when it came time to run initdb, the installer would stop with the error message:

"Failed to run initdb: 128. Please see the logfile in 'C:\Program Files\PostgreSQL\8.1\tmp\initdb.log.
Note! You must read/copy this logfile before you click OK, or it will be automatically removed."

I surmized that the problem was that I was attempting to run the install as an Administrator, so I addressed the problem by running the install, but not having the installer run initdb step to intialize the database cluster. When the install finished without error, I then logged onto the server under the postgres account and manually ran initdb. I was able to initialize the cluster.

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.

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?

Thanks in advance,
George

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

I ran the installer logged in under an Administrator account, and set the
> service account to postgres.
>

That is the recommended way. The installer will create an low-priv postgres
service account and run initdb as that user.

During the installation process, when it came time to run initdb,
> the installer would stop with the error message:
>
> "Failed to run initdb: 128. Please see the logfile in 'C:\Program
> Files\PostgreSQL\8.1\tmp\initdb.log.
> Note! You must read/copy this logfile before you click OK, or it
> will be automatically removed."
>

Did you read that log? Read and copy it befor clicking OK, just as the
message stated?

BECAUSE: running THE INSTALLER as Administrator is recommended AND necessary
to create the postgres user and to give correct rights to this user.

SO: in running initdb.exe something else went wrong, it is NOT because you
ran the installer as Administrator.

(guessing .... you installed with port 5432, and initdb tried to contact
5432, failing, because it is an 7.3 ?)

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

One try to repair this: login as Administrator, open services.msc, and
from the postgreSQL service refill the login information. That does reset
the LOGON_AS_SERVICE privilege.

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.

----- Original Message -----
From: Harald Armin Massa

Hi Harald,

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

This is what is confusing me - the service IS running after rebooting, but does not appear to be listening on port 5435. If I simply restart the service in the Administrative Tools > Component Services section everything is fine.

>>During the installation process, when it came time to run initdb, the installer would stop with the error message:

>> "Failed to run initdb: 128. Please see the logfile in 'C:\Program Files\PostgreSQL\8.1\tmp\initdb.log.
> > Note! You must read/copy this logfile before you click OK, or it will be automatically removed."

> Did you read that log? Read and copy it befor clicking OK, just as the message stated?

When I tried to (before clicking OK), there was no initdb.log file - or any other file - in the 'C:\Program Files\PostgreSQL\8.1\tmp\ directory.

>One try to repair this: login as Administrator, open services.msc, and from the postgreSQL service refill the login information. That does reset the LOGON_AS_SERVICE privilege.

I'll give this a try and see what happens.

George

George,

>I guess your service is not running after rebooting? You can check this via
> services.msc
>
> This is what is confusing me - the service IS running after rebooting, but
> does not appear to be listening on port 5435. If I simply restart the
> service in the Administrative Tools > Component Services section everything
> is fine.
>
> 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(at)nasby(dot)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