Re: Superuser connect during smart shutdown

Over in the "Log notice that checkpoint is to be written on shutdown" thread...

On 10/16/14, 2:31 PM, Michael Banck wrote:
> There were some comments that this might not actually be the case and/or
> that the postmaster was simply waiting for clients to disconnect due to
> smart shutdown being invoked.

Something else mentioned was that once you start a smart shutdown you have no good way (other than limited ps output) to see what the shutdown is waiting on. I'd like to have some way to get back into the database to see what's going on. Perhaps we could allow superusers to connect while waiting for shutdown. A big warning that we're in shutdown would be nice, and maybe it would make sense to further restrict this to only local connections.

It would also be good to be able to abort a smart shutdown if you determine it was a bad idea. Perhaps that's an acceptable way to solve both problems: if your smart shutdown is hung, cancel it and connect to see what's going on.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 10/17/2014 03:59 AM, Jim Nasby wrote:
> Over in the "Log notice that checkpoint is to be written on shutdown"
> thread...
>
> On 10/16/14, 2:31 PM, Michael Banck wrote:
>> There were some comments that this might not actually be the case and/or
>> that the postmaster was simply waiting for clients to disconnect due to
>> smart shutdown being invoked.
>
> Something else mentioned was that once you start a smart shutdown you
> have no good way (other than limited ps output) to see what the shutdown
> is waiting on. I'd like to have some way to get back into the database
> to see what's going on. Perhaps we could allow superusers to connect
> while waiting for shutdown. A big warning that we're in shutdown would
> be nice, and maybe it would make sense to further restrict this to only
> local connections.

You'd also want to flag this connection so it's ignored by the smart
shutdown check, allowing the server to shut down even if it's active.

That'd be a pretty useful thing to have anyway, so monitoring tools,
long-running reports that can be restarted ,etc can mark their
connections as ignored for the purpose of smart shutdown.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> writes:
> Something else mentioned was that once you start a smart shutdown you
> have no good way (other than limited ps output) to see what the shutdown
> is waiting on. I'd like to have some way to get back into the database
> to see what's going on. Perhaps we could allow superusers to connect
> while waiting for shutdown.

I think this idea is going to founder on the fact that the postmaster
has no way to tell whether an incoming connection is for a superuser.
You don't find that out until you've connected to a database and run
a transaction (so you can read pg_authid). And by that point, you've
already had a catastrophic impact on any attempt to shut things down.

> It would also be good to be able to abort a smart shutdown if you
> determine it was a bad idea.

That sounds possibly more feasible.

But TBH I suspect 95% of the problems here would vanish if smart
shutdown weren't the default ...

regards, tom lane

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> But TBH I suspect 95% of the problems here would vanish if smart
> shutdown weren't the default ...

+1000 ...

Thanks!

Stephen

Tom Lane-2 wrote
> Jim Nasby &lt;

> Jim.Nasby@

> &gt; writes:
>> Something else mentioned was that once you start a smart shutdown you
>> have no good way (other than limited ps output) to see what the shutdown
>> is waiting on. I'd like to have some way to get back into the database
>> to see what's going on. Perhaps we could allow superusers to connect
>> while waiting for shutdown.
>
> I think this idea is going to founder on the fact that the postmaster
> has no way to tell whether an incoming connection is for a superuser.
> You don't find that out until you've connected to a database and run
> a transaction (so you can read pg_authid). And by that point, you've
> already had a catastrophic impact on any attempt to shut things down.

This quote from the documentation seems suspect in light of your comment...

"While backup mode is active, new connections will still be allowed, but
only to superusers (this exception allows a superuser to connect to
terminate online backup mode)."

http://www.postgresql.org/docs/9.3/interactive/server-shutdown.html

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Superuser-connect-during-smart-shutdown-tp5823332p5823367.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 10/16/14, 11:46 PM, David G Johnston wrote:
> Tom Lane-2 wrote
>>> Something else mentioned was that once you start a smart shutdown you
>>> have no good way (other than limited ps output) to see what the shutdown
>>> is waiting on. I'd like to have some way to get back into the database
>>> to see what's going on. Perhaps we could allow superusers to connect
>>> while waiting for shutdown.
>>
>> I think this idea is going to founder on the fact that the postmaster
>> has no way to tell whether an incoming connection is for a superuser.
>> You don't find that out until you've connected to a database and run
>> a transaction (so you can read pg_authid). And by that point, you've
>> already had a catastrophic impact on any attempt to shut things down.
>
> This quote from the documentation seems suspect in light of your comment...
>
> "While backup mode is active, new connections will still be allowed, but
> only to superusers (this exception allows a superuser to connect to
> terminate online backup mode)."
>
> http://www.postgresql.org/docs/9.3/interactive/server-shutdown.html

check_hba() does

if (!check_role(port->user_name, roleid, hba->roles))
continue;

And check_role(char **newval, void **extra, GucSource source) does

is_superuser = ((Form_pg_authid) GETSTRUCT(roleTup))->rolsuper;
...
myextra->roleid = roleid;
myextra->is_superuser = is_superuser;
*extra = (void *) myextra;

So presumably with some changes to how we're calling check_role() we could determine if port->user_name is a superuser.

I also like the idea of specifying that a connection should be terminated by a smart shutdown; I agree that'd be useful for monitoring tools and what-not. If folks agree with that I can take a stab at implementing it.

Since I tend to be paranoid, I like smart being the default, but seems I'm in the minority there.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

> But TBH I suspect 95% of the problems here would vanish if smart
> shutdown weren't the default ...

But for your repeated objections, we would have changed the default to fast years ago. AFAICT everyone else is in favor of that.

...Robert

On Oct 19, 2014 4:34 AM, "Robert Haas" <robertmhaas(at)gmail(dot)com> wrote:
>
>
> > But TBH I suspect 95% of the problems here would vanish if smart
> > shutdown weren't the default ...
>
> But for your repeated objections, we would have changed the default to
fast years ago. AFAICT everyone else is in favor of that.
>

Yes, most others even seemed more than happy to change the behaviour of
smart to be that of fast, and rename the old "smart" method to "silly".

no, that's not something I'd recommend, for compatibility reasons, but
definitely +<all current quota> to not have the silly be the default..

/Magnus

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> But TBH I suspect 95% of the problems here would vanish if smart
>> shutdown weren't the default ...

> But for your repeated objections, we would have changed the default to fast years ago. AFAICT everyone else is in favor of that.

I've certainly objected to it in the past, but I don't believe
I was the only one objecting.

regards, tom lane

On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> But TBH I suspect 95% of the problems here would vanish if smart
>>> shutdown weren't the default ...
>
>> But for your repeated objections, we would have changed the default to fast years ago. AFAICT everyone else is in favor of that.
>
> I've certainly objected to it in the past, but I don't believe
> I was the only one objecting.

What's your feeling now?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I've certainly objected to it in the past, but I don't believe
>> I was the only one objecting.

> What's your feeling now?

I'm prepared to yield on the point.

regards, tom lane

On Mon, Oct 20, 2014 at 03:10:50PM -0400, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> I've certainly objected to it in the past, but I don't believe
> >> I was the only one objecting.
>
> > What's your feeling now?
>
> I'm prepared to yield on the point.

OK, are we up for changing the default pg_ctl shutdown method for 9.5,
("smart" to "fast"), or should we wait for 9.6?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Mon, Oct 20, 2014 at 03:10:50PM -0400, Tom Lane wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> > On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> >> I've certainly objected to it in the past, but I don't believe
>> >> I was the only one objecting.
>>
>> > What's your feeling now?
>>
>> I'm prepared to yield on the point.
>
> OK, are we up for changing the default pg_ctl shutdown method for 9.5,
> ("smart" to "fast"), or should we wait for 9.6?

I'm up for it. I think it's long overdue.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> On Mon, Oct 20, 2014 at 03:10:50PM -0400, Tom Lane wrote:
>>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>>> On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>>> I've certainly objected to it in the past, but I don't
>>>>> believe I was the only one objecting.
>>>>
>>>> What's your feeling now?
>>>
>>> I'm prepared to yield on the point.
>>
>> OK, are we up for changing the default pg_ctl shutdown method for
>> 9.5, ("smart" to "fast"), or should we wait for 9.6?
>
> I'm up for it. I think it's long overdue.

+1

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 3/20/15 9:44 AM, Kevin Grittner wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>>> On Mon, Oct 20, 2014 at 03:10:50PM -0400, Tom Lane wrote:
>>>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>>>> On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>>>> I've certainly objected to it in the past, but I don't
>>>>>> believe I was the only one objecting.
>>>>>
>>>>> What's your feeling now?
>>>>
>>>> I'm prepared to yield on the point.
>>>
>>> OK, are we up for changing the default pg_ctl shutdown method for
>>> 9.5, ("smart" to "fast"), or should we wait for 9.6?
>>
>> I'm up for it. I think it's long overdue.
>
> +1

+1, but I also like the idea of allowing SU to connect during a smart
shutdown. Even if you've intentionally chosen smart instead of fast it
still sucks that you can't find out what's actually holding things up
(and ps isn't that great a solution).
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> wrote:
> On 3/20/15 9:44 AM, Kevin Grittner wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

>>>> OK, are we up for changing the default pg_ctl shutdown method
>>>> for 9.5, ("smart" to "fast"), [...]?
>>>
>>> I'm up for it. I think it's long overdue.
>>
>> +1
>
> +1, but I also like the idea of allowing SU to connect during a
> smart shutdown. Even if you've intentionally chosen smart
> instead of fast it still sucks that you can't find out what's
> actually holding things up (and ps isn't that great a solution).

I like that idea a lot, too. Having been in the position of
remotely administering about 80 database servers, and getting a
call that the building containing one of them was on fire, and the
fire department would be arriving in two or three minutes to cut
power to the building and start spraying water on everything, I
found current behavior rather nervous-making as I struggled to get
a clean shutdown of PostgreSQL followed by a clean shutdown and
power-off of the server before that happened. The ability to make
an SU connection during either "fast" or "smart" shutdown can be
useful in a world of connection pools and long-running report
queries. And fires.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Mar 23, 2015 at 11:02 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> wrote:
>> On 3/20/15 9:44 AM, Kevin Grittner wrote:
>>> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>>> On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
>>>>> OK, are we up for changing the default pg_ctl shutdown method
>>>>> for 9.5, ("smart" to "fast"), [...]?
>>>>
>>>> I'm up for it. I think it's long overdue.
>>>
>>> +1
>>
>> +1, but I also like the idea of allowing SU to connect during a
>> smart shutdown. Even if you've intentionally chosen smart
>> instead of fast it still sucks that you can't find out what's
>> actually holding things up (and ps isn't that great a solution).
>
> I like that idea a lot, too. Having been in the position of
> remotely administering about 80 database servers, and getting a
> call that the building containing one of them was on fire, and the
> fire department would be arriving in two or three minutes to cut
> power to the building and start spraying water on everything, I
> found current behavior rather nervous-making as I struggled to get
> a clean shutdown of PostgreSQL followed by a clean shutdown and
> power-off of the server before that happened. The ability to make
> an SU connection during either "fast" or "smart" shutdown can be
> useful in a world of connection pools and long-running report
> queries. And fires.

That, however, is a separate issue from changing the default shutdown mode.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company