Superuser connect during smart shutdown

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Jim Nasby <Jim.Nasby@BlueTreble.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Tom Lane (tgl@sss.pgh.pa.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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Oct 19, 2014 4:34 AM, "Robert Haas" <robertmhaas@gmail.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@gmail.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane <tgl@sss.pgh.pa.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Oct 20, 2014 at 03:10:50PM -0400, Tom Lane wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane <tgl@sss.pgh.pa.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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Mon, Oct 20, 2014 at 03:10:50PM -0400, Tom Lane wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane <tgl@sss.pgh.pa.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Mon, Oct 20, 2014 at 03:10:50PM -0400, Tom Lane wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane <tgl@sss.pgh.pa.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 3/20/15 9:44 AM, Kevin Grittner wrote:

Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Mon, Oct 20, 2014 at 03:10:50PM -0400, Tom Lane wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Sun, Oct 19, 2014 at 12:27 PM, Tom Lane <tgl@sss.pgh.pa.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Jim Nasby <Jim.Nasby@BlueTreble.com> wrote:

On 3/20/15 9:44 AM, Kevin Grittner wrote:

Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian <bruce@momjian.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Mar 23, 2015 at 11:02 AM, Kevin Grittner <kgrittn@ymail.com> wrote:

Jim Nasby <Jim.Nasby@BlueTreble.com> wrote:

On 3/20/15 9:44 AM, Kevin Grittner wrote:

Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian <bruce@momjian.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers