Re: pg_ctl -m fast failing?

PG-People,

Under what circumstances should pg_ctl -m fast fail to shut down the database?
I've been having some problems on one server with it not shutting down on
command.

--
Josh Berkus
josh(at)agliodbs(dot)com
Aglio Database Solutions
San Francisco

On Tue, Mar 04, 2003 at 09:18:07AM -0800, Josh Berkus wrote:
> PG-People,
>
> Under what circumstances should pg_ctl -m fast fail to shut down the database?
> I've been having some problems on one server with it not shutting down on
> command.

This is a problem I've had a couple of times, too. In spare moments
(ha!) I am trying to find a way of reproducing it consistently.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Under what circumstances should pg_ctl -m fast fail to shut down the database?

One cause was discovered and fixed about two weeks ago:

2003-02-17 21:53 tgl

* src/backend/commands/async.c (REL7_3_STABLE): Async_NotifyHandler
must save and restore ImmediateInterruptOK. Fixes known problem
with failure to respond to 'pg_ctl stop -m fast', and probable
problems if SIGINT or SIGTERM arrives while processing a SIGUSR2
interrupt that arrived while waiting for a new client query.

regards, tom lane

Tom,

> 2003-02-17 21:53 tgl
>
> * src/backend/commands/async.c (REL7_3_STABLE): Async_NotifyHandler
> must save and restore ImmediateInterruptOK. Fixes known problem
> with failure to respond to 'pg_ctl stop -m fast', and probable
> problems if SIGINT or SIGTERM arrives while processing a SIGUSR2
> interrupt that arrived while waiting for a new client query.

So ... if I had a PSQL terminal session open on a remote client, and
did:

pg_ctl -m fast stop
pg_ctl start
pg_ctl -m fast stop

... the second stop would fail? Am I understanding correctly?
Because that's the behavior I am observing.

-Josh

"Josh Berkus" <josh(at)agliodbs(dot)com> writes:
> So ... if I had a PSQL terminal session open on a remote client, and
> did:
> pg_ctl -m fast stop
> pg_ctl start
> pg_ctl -m fast stop
> ... the second stop would fail? Am I understanding correctly?
> Because that's the behavior I am observing.

No, I'd not expect the second stop to fail; and I can't reproduce any
such problem here.

The known bug might cause the *first* stop to fail; it basically
triggers if you have a client that sits idle for a long time (while
other sessions are doing work) and still remains idle after the shutdown
command comes.

regards, tom lane

On Tue, Mar 04, 2003 at 05:52:15PM -0500, Tom Lane wrote:
> "Josh Berkus" <josh(at)agliodbs(dot)com> writes:

> > ... the second stop would fail? Am I understanding correctly?
> > Because that's the behavior I am observing.
>
> No, I'd not expect the second stop to fail; and I can't reproduce any
> such problem here.

Right. This is the occasional problem that I have been trying, in
vain, to reproduce lately. Indeed, if I get it into the right state,
"-m i" will issue complaints as well. But darned if I can make it
happen again :-(

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110

Andrew Sullivan <andrew(at)libertyrms(dot)info> writes:
> On Tue, Mar 04, 2003 at 05:52:15PM -0500, Tom Lane wrote:
>> "Josh Berkus" <josh(at)agliodbs(dot)com> writes:
>>> ... the second stop would fail? Am I understanding correctly?
>>> Because that's the behavior I am observing.
>>
>> No, I'd not expect the second stop to fail; and I can't reproduce any
>> such problem here.

> Right. This is the occasional problem that I have been trying, in
> vain, to reproduce lately. Indeed, if I get it into the right state,
> "-m i" will issue complaints as well. But darned if I can make it
> happen again :-(

Hm, so a start immediately followed by a stop sometimes fails? Is the
stop issued too quickly to allow any clients to get in?

regards, tom lane

On Wed, Mar 05, 2003 at 11:00:08AM -0500, Tom Lane wrote:
>
> Hm, so a start immediately followed by a stop sometimes fails? Is the
> stop issued too quickly to allow any clients to get in?

That does indeed fail, and I expect the stop is issued too quickly to
allow any clients in. But I can -- or rather, have, but can't now --
cause the problem by issuing two stops in a row.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110

Tom,

> No, I'd not expect the second stop to fail; and I can't reproduce any
> such problem here.

I've had it happen once.

> The known bug might cause the *first* stop to fail; it basically
> triggers if you have a client that sits idle for a long time (while
> other sessions are doing work) and still remains idle after the shutdown
> command comes.

So ... the workaround for 7.2.4 would be to have a server-side script hunt for
idle connections after shutdown and kill -9 them?

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> So ... the workaround for 7.2.4 would be to have a server-side script hunt for
> idle connections after shutdown and kill -9 them?

Yuck. I'd recommend back-porting the patch to 7.2 instead; should be
easy enough:
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/commands/async.c.diff?r1=1.91&r2=1.92

Now, if you can reproduce the problem even with that patch in place, I'm
very interested in looking at the state of the system ...

regards, tom lane

On Friday March 7 2003 11:56, Josh Berkus wrote:
> Tom,
>
> > No, I'd not expect the second stop to fail; and I can't reproduce any
> > such problem here.
>
> I've had it happen once.

I've seen this many times when a bug like that described below was in play
during shutdown. It's never been convenient to pin it down.

> > The known bug might cause the *first* stop to fail; it basically
> > triggers if you have a client that sits idle for a long time (while
> > other sessions are doing work) and still remains idle after the
> > shutdown command comes.
>
> So ... the workaround for 7.2.4 would be to have a server-side script
> hunt for idle connections after shutdown and kill -9 them?

There's a very small patch posted recently that fixes the problem, assuming
you can apply it.

Ed

Can anyone comment on the status of point-in-time recovery (PITR?) I
am looking to move an application over to PostgreSQL onto a Mac (I'll
try to avoid the divide-by-zero issue :-) ) and PITR would really help
the backup/recovery plan to accept PostgreSQL as a solution with fast
recovery times.

Thanks,

Charlie

On Friday, March 7, 2003, at 02:43 PM, Tom Lane wrote:

> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> So ... the workaround for 7.2.4 would be to have a server-side script
>> hunt for
>> idle connections after shutdown and kill -9 them?
>
> Yuck. I'd recommend back-porting the patch to 7.2 instead; should be
> easy enough:
> http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/
> commands/async.c.diff?r1=1.91&r2=1.92
>
> Now, if you can reproduce the problem even with that patch in place,
> I'm
> very interested in looking at the state of the system ...
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org

PITR is months away.

---------------------------------------------------------------------------

Charlie Woloszynski wrote:
> Can anyone comment on the status of point-in-time recovery (PITR?) I
> am looking to move an application over to PostgreSQL onto a Mac (I'll
> try to avoid the divide-by-zero issue :-) ) and PITR would really help
> the backup/recovery plan to accept PostgreSQL as a solution with fast
> recovery times.
>
> Thanks,
>
> Charlie
>
>
> On Friday, March 7, 2003, at 02:43 PM, Tom Lane wrote:
>
> > Josh Berkus <josh(at)agliodbs(dot)com> writes:
> >> So ... the workaround for 7.2.4 would be to have a server-side script
> >> hunt for
> >> idle connections after shutdown and kill -9 them?
> >
> > Yuck. I'd recommend back-porting the patch to 7.2 instead; should be
> > easy enough:
> > http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/
> > commands/async.c.diff?r1=1.91&r2=1.92
> >
> > Now, if you can reproduce the problem even with that patch in place,
> > I'm
> > very interested in looking at the state of the system ...
> >
> > regards, tom lane
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> > majordomo(at)postgresql(dot)org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

I cannot subscribe to this list in the digest mode. I have twice tried on
the mailing list subscription form, but it does not seem to work.

Can anybody tell me how to do this?

Thanks,
Tanu

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/