Unable to get postgres running after long time no vacuum

"Leon Mergen" <leon@solatis.com> writes:

Now, I enter a 'vacuum verbose;' and wait for about a day (the
database is 1TB in size) -- it completes, and I start the database
again; it starts replaying the +- 1K WAL log files for a while, and
after completion, I try to attempt to connect to the database and it
*still* says it is not accepting commands to avoid wraparound data
loss!

Where did these log files come from? You said nothing about a crash.
Perhaps you are not shutting down the standalone mode cleanly after
the vacuum?

regards, tom lane

"Leon Mergen" <leon@solatis.com> writes:

Perhaps you are not shutting down the standalone mode cleanly after
the vacuum?

Okay, that was obviously it -- I didn't realize I needed to send a
crtl+D signal to the server when in single user mode to shut it down,
and figured that any uncommitted changes would be replayed when the
database was started.

Most of them would be, but the one you actually needed here (the update
of pg_database.datfrozenxid) is treated as a non-WAL-logged action in
pre-8.2 releases :-( ... so you had to have a checkpoint to ensure it
got out to disk.

It's actually not that easy to get out of the single-user mode without
it doing a checkpoint. I suppose you must have either SIGQUIT or
SIGKILL'd it. While there's nothing we can do about SIGKILL, it strikes
me that it might be a good safety measure if single-user mode treated
SIGQUIT the same as SIGTERM, ie, non-panic shutdown. Comments anyone?

regards, tom lane

"Leon Mergen" <leon@solatis.com> writes:

On 7/8/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

It's actually not that easy to get out of the single-user mode without
it doing a checkpoint. I suppose you must have either SIGQUIT or
SIGKILL'd it. While there's nothing we can do about SIGKILL, it strikes
me that it might be a good safety measure if single-user mode treated
SIGQUIT the same as SIGTERM, ie, non-panic shutdown. Comments anyone?

What I found with SIGTERM was that it did nothing, since it was still
waiting for the (single-user) client to exit, and thus had no effect
unless I sent an end-of-input ctrl+d singal, which would have resulted
in a shutdown anyway.

We might need a bit of rejiggering around the edges of the single-user
command reading code to make this work nicely, but what I'm envisioning
is that a keyboard-generated SIGQUIT ought to result in a clean
shutdown, same as EOF does.

At least on my machine there doesn't seem to be a defined way to
generate SIGTERM from the terminal; so I can see where if someone hasn't
read the postgres man page carefully, their first instinct upon finding
that control-C doesn't get them out of single-user mode might be to type
control-\ (or whatever the local QUIT character is). It doesn't seem
like it should be quite that easy to force a panic stop.

regards, tom lane

Tom Lane wrote:

"Leon Mergen" <leon@solatis.com> writes:

On 7/8/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

It's actually not that easy to get out of the single-user mode without
it doing a checkpoint. I suppose you must have either SIGQUIT or
SIGKILL'd it. While there's nothing we can do about SIGKILL, it strikes
me that it might be a good safety measure if single-user mode treated
SIGQUIT the same as SIGTERM, ie, non-panic shutdown. Comments anyone?

What I found with SIGTERM was that it did nothing, since it was still
waiting for the (single-user) client to exit, and thus had no effect
unless I sent an end-of-input ctrl+d singal, which would have resulted
in a shutdown anyway.

We might need a bit of rejiggering around the edges of the single-user
command reading code to make this work nicely, but what I'm envisioning
is that a keyboard-generated SIGQUIT ought to result in a clean
shutdown, same as EOF does.

At least on my machine there doesn't seem to be a defined way to
generate SIGTERM from the terminal; so I can see where if someone hasn't
read the postgres man page carefully, their first instinct upon finding
that control-C doesn't get them out of single-user mode might be to type
control-\ (or whatever the local QUIT character is). It doesn't seem
like it should be quite that easy to force a panic stop.

That seems reasonable, but I know an awful lot of people that don't know
how to generate SIGQUIT at all (I only learned about it about a year
ago, I think). In fact anyone who doesn't already know that Ctrl-D
closes the session is not very likely to know about Ctrl-\. So while we
should do it anyway, IMHO there's not much gain there. I think it may
be good to add "exit" and "quit" commands to close the standalone
session.

--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
"God is real, unless declared as int"

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane wrote:

At least on my machine there doesn't seem to be a defined way to
generate SIGTERM from the terminal; so I can see where if someone hasn't
read the postgres man page carefully, their first instinct upon finding
that control-C doesn't get them out of single-user mode might be to type
control-\ (or whatever the local QUIT character is). It doesn't seem
like it should be quite that easy to force a panic stop.

That seems reasonable, but I know an awful lot of people that don't know
how to generate SIGQUIT at all (I only learned about it about a year
ago, I think). In fact anyone who doesn't already know that Ctrl-D
closes the session is not very likely to know about Ctrl-\.

Sure, but those who do know how to SIGQUIT might reach for that before
they reach for control-D. There's hardly anyone out there who could
be called an experienced user of the standalone mode, I think, and so
we shouldn't assume that users always know control-D is the way out.

regards, tom lane

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane wrote:

At least on my machine there doesn't seem to be a defined way to
generate SIGTERM from the terminal; so I can see where if someone hasn't
read the postgres man page carefully, their first instinct upon finding
that control-C doesn't get them out of single-user mode might be to type
control-\ (or whatever the local QUIT character is). It doesn't seem
like it should be quite that easy to force a panic stop.

That seems reasonable, but I know an awful lot of people that don't know
how to generate SIGQUIT at all (I only learned about it about a year
ago, I think). In fact anyone who doesn't already know that Ctrl-D
closes the session is not very likely to know about Ctrl-\.

Sure, but those who do know how to SIGQUIT might reach for that before
they reach for control-D. There's hardly anyone out there who could
be called an experienced user of the standalone mode, I think, and so
we shouldn't assume that users always know control-D is the way out.

I agree completely, but is that an argument _against_ a "quit" command?
(Maybe the argument against the quit command is feature freeze.)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane wrote:

Sure, but those who do know how to SIGQUIT might reach for that before
they reach for control-D. There's hardly anyone out there who could
be called an experienced user of the standalone mode, I think, and so
we shouldn't assume that users always know control-D is the way out.

I agree completely, but is that an argument _against_ a "quit" command?

No, it's orthogonal to whether we want a "quit" command. (My opinion is
not, because what the heck will we do with it in multiuser mode? And
there is no good way to shoehorn it into just the single-user mode, it'd
have to be a grammar entry.)

regards, tom lane

Tom Lane wrote:

No, it's orthogonal to whether we want a "quit" command. (My opinion is
not, because what the heck will we do with it in multiuser mode? And
there is no good way to shoehorn it into just the single-user mode, it'd
have to be a grammar entry.)

Hmm, I was thinking that we could just add it in its own grammar, but
then I remembered that the separate grammar is for the bootstrap mode,
not standalone :-( Sorry for the noise.

--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"Just treat us the way you want to be treated + some extra allowance
for ignorance." (Michael Brusser)