Обсуждение: BUG #4339: The postgreSQL service stops abnormally

The following bug has been logged online:

Bug reference:      4339
Logged by:          Bhaskar Sirohi
Email address:      bhaskar.sirohi@druvaa.com
PostgreSQL version: 8.3.3
Operating system:   Windows 2003 Server
Description:        The postgreSQL service stops abnormally
Details:

Hi All,

The postgreSQL service stops abnormally,I can't restart it until I enter the
password for the \postgre login account. Once I do that, everything is fine
again.

Below are the snaps of pg_logs

2008-07-29 09:14:46 EDT LOG:  database system was interrupted; last known up
at 2008-07-28 23:13:20 EDT
2008-07-29 09:14:46 EDT LOG:  database system was not properly shut down;
automatic recovery in progress
2008-07-29 09:14:46 EDT LOG:  record with zero length at 2/D0E47B88
2008-07-29 09:14:46 EDT LOG:  redo is not required
2008-07-29 09:14:46 EDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-07-29 09:14:46 EDT FATAL:  the database system is starting up
2008-07-29 09:14:46 EDT LOG:  database system is ready to accept
connections
2008-07-29 09:14:46 EDT LOG:  autovacuum launcher started
2008-07-29 09:14:47 EDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-07-29 09:15:29 EDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-07-29 16:26:19 EDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-07-29 16:41:03 EDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-07-29 16:50:57 EDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-07-29 16:51:27 EDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-07-29 17:30:13 EDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"


2008-07-30 03:03:44 EDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-07-30 05:35:15 EDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-07-30 10:27:35 EDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-07-30 15:05:01 EDT LOG:  checkpoints are occurring too frequently (28
seconds apart)
2008-07-30 15:05:01 EDT HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2008-07-30 15:13:34 EDT LOG:  checkpoints are occurring too frequently (29
seconds apart)
2008-07-30 15:13:34 EDT HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2008-07-30 15:18:50 EDT LOG:  checkpoints are occurring too frequently (28
seconds apart)
2008-07-30 15:18:50 EDT HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2008-07-30 15:19:21 EDT LOG:  received fast shutdown request
2008-07-30 15:19:21 EDT LOG:  aborting any active transactions
2008-07-30 15:19:21 EDT ERROR:  canceling statement due to user request
2008-07-30 15:19:21 EDT STATEMENT:  COMMIT
2008-07-30 15:19:21 EDT ERROR:  canceling statement due to user request
2008-07-30 15:19:21 EDT STATEMENT:  ROLLBACK
2008-07-30 15:19:21 EDT ERROR:  current transaction is aborted, commands
ignored until end of transaction block
2008-07-30 15:19:21 EDT STATEMENT:  SELECT type, cino, ctime FROM folder
WHERE ino = 2 AND name = 'Michael H. Modee' AND dtime = 0
2008-07-30 15:19:21 EDT ERROR:  current transaction is aborted, commands
ignored until end of transaction block
2008-07-30 15:19:21 EDT STATEMENT:  SELECT type, cino, ctime FROM folder
WHERE ino = 2 AND name = 'Michael H. Modee' AND dtime = 0
2008-07-30 15:19:21 EDT ERROR:  canceling autovacuum task
2008-07-30 15:19:21 EDT CONTEXT:  automatic analyze of table
"notebookbkp.public.bmap"
2008-07-30 15:19:21 EDT FATAL:  terminating connection due to administrator
command
2008-07-30 15:19:21 EDT FATAL:  terminating connection due to administrator
command
2008-07-30 15:19:21 EDT LOG:  autovacuum launcher shutting down
2008-07-30 15:19:24 EDT LOG:  shutting down
2008-07-30 15:19:24 EDT LOG:  database system is shut down

Hi,

Bhaskar Sirohi wrote:
 > ...
> 2008-07-30 15:05:01 EDT LOG:  checkpoints are occurring too frequently (28
> seconds apart)
> 2008-07-30 15:05:01 EDT HINT:  Consider increasing the configuration
> parameter "checkpoint_segments".
> 2008-07-30 15:13:34 EDT LOG:  checkpoints are occurring too frequently (29
> seconds apart)
> 2008-07-30 15:13:34 EDT HINT:  Consider increasing the configuration
> parameter "checkpoint_segments".
> 2008-07-30 15:18:50 EDT LOG:  checkpoints are occurring too frequently (28
> seconds apart)
> 2008-07-30 15:18:50 EDT HINT:  Consider increasing the configuration
> parameter "checkpoint_segments".
> 2008-07-30 15:19:21 EDT LOG:  received fast shutdown request

These log lines look like your database is touching lots of tuples,
requiring it to checkpoint frequently. Then it receives a fast shutdown
request - from whoever.

What causes the workload? Did you check memory usage? (And uh.. does
Windows 2003 Server have an OOM Killer or some such?)

Regards

Markus Wanner

Hello Markus,

Thanks for the reply.

For the past one week postgreSQL service has been running absolutely fine
-:)

Also there is no OOM killer application on the windows 2003 server. I
haven't made any changes on the machine since first the service failed.

Nothing in the event logs also.

Can there be any another reason why the service failed ?

 --
Thanks and Regards

Bhaskar Sirohi
bhaskar.sirohi@druvaa.com
www.druvaa.com
(C) +91 986 094 6619


On Tue, Aug 5, 2008 at 5:50 PM, Markus Wanner <markus@bluegap.ch> wrote:

> Hi,
>
> Bhaskar Sirohi wrote:
> > ...
>
>> 2008-07-30 15:05:01 EDT LOG:  checkpoints are occurring too frequently (28
>> seconds apart)
>> 2008-07-30 15:05:01 EDT HINT:  Consider increasing the configuration
>> parameter "checkpoint_segments".
>> 2008-07-30 15:13:34 EDT LOG:  checkpoints are occurring too frequently (29
>> seconds apart)
>> 2008-07-30 15:13:34 EDT HINT:  Consider increasing the configuration
>> parameter "checkpoint_segments".
>> 2008-07-30 15:18:50 EDT LOG:  checkpoints are occurring too frequently (28
>> seconds apart)
>> 2008-07-30 15:18:50 EDT HINT:  Consider increasing the configuration
>> parameter "checkpoint_segments".
>> 2008-07-30 15:19:21 EDT LOG:  received fast shutdown request
>>
>
> These log lines look like your database is touching lots of tuples,
> requiring it to checkpoint frequently. Then it receives a fast shutdown
> request - from whoever.
>
> What causes the workload? Did you check memory usage? (And uh.. does
> Windows 2003 Server have an OOM Killer or some such?)
>
> Regards
>
> Markus Wanner
>

Hi,

Bhaskar Sirohi wrote:
> Also there is no OOM killer application on the windows 2003 server.

I also didn't think so, but who commanded the server to shutdown, then?

> Can there be any another reason why the service failed ?

Well, yes, sure. But if you don't answer the important questions, it's
hard to figure out what's wrong.

Regards

Markus Wanner