From: Kenneth Marshall <ktm@rice.edu>
To: David Schnur <dnschnur@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Shutdown fails with both 'fast' and 'immediate'
Date: Wed, 12 May 2010 09:45:25 -0500
Message-ID: <20100512144525.GT1610@aart.is.rice.edu> (raw)
In-Reply-To: <AANLkTindCeLyRmRbgV4ICxEBGRLwyBbm5yO2oLBHUnSc@mail.gmail.com>
References: <AANLkTindCeLyRmRbgV4ICxEBGRLwyBbm5yO2oLBHUnSc@mail.gmail.com>
On Wed, May 12, 2010 at 10:22:14AM -0400, David Schnur wrote:
> I develop an app that uses a back-end Postgres database, currently 8.3.9.
> The database is started when the app starts up, and stopped when it shuts
> down. Shutdown uses pg_ctl with -m fast, and waits two minutes for the
> process to complete. If it doesn't, it tries -m immediate, and waits two
> more minutes before logging an error and giving up.
>
> One user, on OSX 10.5.8, has a script that stops the app each morning, to
> upgrade to the newest build. In his case, both the fast and immediate
> shutdowns time out, and Postgres continues running for at least 2-4 hours.
> At that point he brings up the terminal to kill all the back-ends manually,
> so we haven't seen it finish shutting down on its own yet. It is in fact
> shutting down, because all queries fail with the 'database system is
> shutting down' error.
>
> The query running during this time is a DELETE that runs as part of the
> application's daily maintenance. The size of the DELETE varies, and in his
> case happened to be unusually large one day, which is apparently what
> triggered the problem. Since the DELETE never gets a chance to finish, the
> problem recurs every morning.
>
> I'll obviously need to deal with that query, but I'm concerned that Postgres
> is unable to interrupt it. Why might this be happening? Thanks,
>
> David
In many cases, I/O requests are not interruptable until they complete
and DELETE causes a lot of I/O. Check to see if the processes are in
device-wait, D in top or ps. The solution is to fix the DELETE processing.
One option would be to batch it in smaller numbers of rows which should
allow the quit to squeeze in between one of the batches.
Cheers,
Ken
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ktm@rice.edu, dnschnur@gmail.com
Subject: Re: Shutdown fails with both 'fast' and 'immediate'
In-Reply-To: <20100512144525.GT1610@aart.is.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox