Superuser connect during smart shutdown

[Jim Nasby]

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

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.

[Craig Ringer]

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

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.

[Tom Lane]

Post by Jim Nasby
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.

Post by Jim Nasby
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

[Stephen Frost]

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

+1000 ...

Thanks!

Stephen

[David G Johnston]

Jim Nasby <

Post by Jim Nasby
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.

[Jim Nasby]

Post by David G Johnston

Post by Tom Lane

Post by Jim Nasby
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.

[Robert Haas]

Post by Tom Lane
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

[Magnus Hagander]

Post by Robert Haas

Post by Tom Lane
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

[Tom Lane]

Post by Robert Haas

Post by Tom Lane
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

[Robert Haas]

Post by Tom Lane

Post by Robert Haas

Post by Tom Lane
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

[Tom Lane]

Post by Robert Haas

Post by Tom Lane
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