Обсуждение: Postgres 8.3 archive_command

Поиск
Список
Период
Сортировка

Postgres 8.3 archive_command

От
Rudolf van der Leeden
Дата:
Hi,

I just installed v8.3beta3 and discovered that the way WAL archiving  
is handled has changed.
From postgresql.conf:    #archive_mode = off           # allows archiving to be done   
(change requires restart)    #archive_command = ''      # command to use to archive a logfile  
segment

In v8.2.4 we are currently switching archiving on/off on the fly by  
just inserting an archiving command.
Now we have got a separate option for handling the on/off state.  
That's ok.
But the note "change requires restart" is a serious change over v8.2.

Background:
We are using the archive_command for PITR backup every night. This  
can easily be done while the server is running.
After the migration to v8.3 we'd have to    (1) stop the server, switch on archiving, start the server,    (2) run PITR
backup,and    (3) stop the server, switch off archiving, start the server.
 

Questions:  - Is the requirement  "change of archive_mode requires restart"   
just temporary or is it going to stay in the final release?    If the server restart is planned for final release (for
whatever 
 
reason) then my next question is:
  - The workaround is a script that simply does a NOOP if archiving  
is effectively disabled while archive_mode=on all the time.    How can one switch off the archiving log messages?

Thanks and best regards,
Rudolf VanderLeeden
IT Consultant
Logicunited GmbH
Germany
vanderleeden@logicunited.com




Re: Postgres 8.3 archive_command

От
Simon Riggs
Дата:
On Wed, 2007-11-21 at 14:45 +0100, Rudolf van der Leeden wrote:

> In v8.2.4 we are currently switching archiving on/off on the fly by  
> just inserting an archiving command.
> Now we have got a separate option for handling the on/off state.  
> That's ok.
> But the note "change requires restart" is a serious change over v8.2.

Thanks for the feedback.

There was a window of data loss caused by the capability to change the
archive_command on and off while running a large COPY, CTAS, CLUSTER or
CREATE INDEX, which we had to avoid.

The new way of doing this changes that so you must have archive_mode set
on always, but you can still change archive_command on the fly.

>    - The workaround is a script that simply does a NOOP if archiving  
> is effectively disabled while archive_mode=on all the time.
>      How can one switch off the archiving log messages?

You can't.

I think if you choose to set archive command to something that doesn't
actually archive the file, thats up to you. The server log shows that
Postgres server did as you asked it to do. That helps to avoid
complaints like "How come Postgres didn't tell me when it wasn't
archiving".

Perhaps we should move the successful archived message to DEBUG1 now,
except for the first message after the archiver starts or when the
archive_command changes, plus one message every 255 segments? That would
reduce the log volume in the normal case without endangering our ability
to see what is happening.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com



Re: Postgres 8.3 archive_command

От
"Kevin Grittner"
Дата:
>>> On Wed, Nov 21, 2007 at  8:49 AM, in message
<1195656591.4246.42.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
wrote:
> Perhaps we should move the successful archived message to DEBUG1 now,
> except for the first message after the archiver starts or when the
> archive_command changes, plus one message every 255 segments? That would
> reduce the log volume in the normal case without endangering our ability
> to see what is happening.
I would prefer to always log the archives; it has proven useful in
investigating what's going on when our backup stream "stalls".  How
much other stuff would I be getting if I had to turn on DEBUG1 to
see this?  Would it be overkill to add a GUC to configure the log
level of archiving?
For less active databases, where we rarely get more than one
segment per hour, it would be kinda odd to see an archive logged
once every couple weeks.
-Kevin




Re: Postgres 8.3 archive_command

От
Tom Lane
Дата:
Simon Riggs <simon@2ndquadrant.com> writes:
> Perhaps we should move the successful archived message to DEBUG1 now,
> except for the first message after the archiver starts or when the
> archive_command changes, plus one message every 255 segments? That would
> reduce the log volume in the normal case without endangering our ability
> to see what is happening.

That would confuse people terribly, and it *would* endanger our ability
to see what was happening, 254 times out of 255.
        regards, tom lane


Re: Postgres 8.3 archive_command

От
Simon Riggs
Дата:
On Wed, 2007-11-21 at 11:27 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Perhaps we should move the successful archived message to DEBUG1 now,
> > except for the first message after the archiver starts or when the
> > archive_command changes, plus one message every 255 segments? That would
> > reduce the log volume in the normal case without endangering our ability
> > to see what is happening.
> 
> That would confuse people terribly, and it *would* endanger our ability
> to see what was happening, 254 times out of 255.

That's my feeling too, just wanted to check it still made sense for
y'all.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com



Re: Postgres 8.3 archive_command

От
Tom Lane
Дата:
Simon Riggs <simon@2ndquadrant.com> writes:
> On Wed, 2007-11-21 at 11:27 -0500, Tom Lane wrote:
>> That would confuse people terribly, and it *would* endanger our ability
>> to see what was happening, 254 times out of 255.

> That's my feeling too, just wanted to check it still made sense for
> y'all.

Just to clarify: I don't object to lowering "successfully archived"
messages to DEBUG1, if the field consensus is that it's too chatty.
What I didn't like was the idea of logging some events but not other
identical events.
        regards, tom lane


Re: Postgres 8.3 archive_command

От
"Kevin Grittner"
Дата:
>>> On Wed, Nov 21, 2007 at 10:57 AM, in message <12172.1195664252@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
>> On Wed, 2007-11-21 at 11:27 -0500, Tom Lane wrote:
>>> That would confuse people terribly, and it *would* endanger our ability
>>> to see what was happening, 254 times out of 255.
>
>> That's my feeling too, just wanted to check it still made sense for
>> y'all.
>
> Just to clarify: I don't object to lowering "successfully archived"
> messages to DEBUG1, if the field consensus is that it's too chatty.
> What I didn't like was the idea of logging some events but not other
> identical events.
Agreed on the intermittent logging.  I don't feel it's too chatty,
but on the other hand, I could always change the logging level on
the fly if I was investigating a problem, so it wouldn't be much of
an inconvenience to switch it if it bugs others.
In poking around the logs just now, I noticed one message I'd like
to squelch.  Run against Milwaukee County's recent log files:
grep -c 'PRIMARY KEY will create implicit index'
I get this for the last ten full days:
/var/pgsql/data/cc/pg_log/postgresql-2007-11-11_000000.log:210
/var/pgsql/data/cc/pg_log/postgresql-2007-11-12_000000.log:14138
/var/pgsql/data/cc/pg_log/postgresql-2007-11-13_000000.log:13250
/var/pgsql/data/cc/pg_log/postgresql-2007-11-14_000000.log:14912
/var/pgsql/data/cc/pg_log/postgresql-2007-11-15_000000.log:11635
/var/pgsql/data/cc/pg_log/postgresql-2007-11-16_000000.log:10774
/var/pgsql/data/cc/pg_log/postgresql-2007-11-17_000000.log:183
/var/pgsql/data/cc/pg_log/postgresql-2007-11-18_000000.log:120
/var/pgsql/data/cc/pg_log/postgresql-2007-11-19_000000.log:12667
/var/pgsql/data/cc/pg_log/postgresql-2007-11-20_000000.log:13992
It's actually pretty hard to find the archive logging amidst all
that.  The log is next to useless without a grep -v to filter
them out.
If we're going to pursue the idea further, I guess I should
spawn a new thread, but would people consider moving *that* one
to DEBUG1?  There's an idea I can get behind!
-Kevin



Re: Postgres 8.3 archive_command

От
"Zeugswetter Andreas ADI SD"
Дата:
> Perhaps we should move the successful archived message to DEBUG1 now,
> except for the first message after the archiver starts or when the
> archive_command changes, plus one message every 255 segments?
> That would reduce the log volume in the normal case without
endangering
> our ability to see what is happening.

Wouldn't it be more useful to increase the WAL segment size on such
installations
that switch WAL files so frequently that it is a problem for the log ?

This currently needs a recompile. I wondered for some time now whether
16 Mb isn't
too low for current hw. Maybe it is time for making WAL segment size
changeable
in the conf with a clean shutdown.

Andreas


Re: Postgres 8.3 archive_command

От
Tom Lane
Дата:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote: 
>> Just to clarify: I don't object to lowering "successfully archived"
>> messages to DEBUG1, if the field consensus is that it's too chatty.
>> What I didn't like was the idea of logging some events but not other
>> identical events.
> Agreed on the intermittent logging.  I don't feel it's too chatty,
> but on the other hand, I could always change the logging level on
> the fly if I was investigating a problem, so it wouldn't be much of
> an inconvenience to switch it if it bugs others.

Also, you can always tweak your archive_command script to do some
logging of its own, so it's always possible to make the thing more
noisy if you need to.  Less noisy, though, is hard without changing
the server code.

> In poking around the logs just now, I noticed one message I'd like
> to squelch.  Run against Milwaukee County's recent log files:
> grep -c 'PRIMARY KEY will create implicit index'

Set log_min_messages higher than NOTICE.  Given the current usage of
NOTICE --- basically they're *all* newbie-annoyance items ---
I kinda wonder why WARNING isn't the default setting anyway.
        regards, tom lane


Re: Postgres 8.3 archive_command

От
Simon Riggs
Дата:
On Wed, 2007-11-21 at 18:16 +0100, Zeugswetter Andreas ADI SD wrote:
> > Perhaps we should move the successful archived message to DEBUG1 now,
> > except for the first message after the archiver starts or when the
> > archive_command changes, plus one message every 255 segments? 
> > That would reduce the log volume in the normal case without
> endangering 
> > our ability to see what is happening.
> 
> Wouldn't it be more useful to increase the WAL segment size on such
> installations
> that switch WAL files so frequently that it is a problem for the log ?
> 
> This currently needs a recompile. I wondered for some time now whether
> 16 Mb isn't
> too low for current hw. Maybe it is time for making WAL segment size
> changeable 
> in the conf with a clean shutdown.

I think its too late in the release cycle to fully consider all the
implications of that. 16MB is hardcoded in lots of places. The
performance advantages of that have been mostly removed in 8.3, you
should note.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com



Re: Postgres 8.3 archive_command

От
Tom Lane
Дата:
Simon Riggs <simon@2ndquadrant.com> writes:
> On Wed, 2007-11-21 at 18:16 +0100, Zeugswetter Andreas ADI SD wrote:
>> Maybe it is time for making WAL segment size
>> changeable in the conf with a clean shutdown.

> I think its too late in the release cycle to fully consider all the
> implications of that. 16MB is hardcoded in lots of places.

Starting with the WAL file names.  You couldn't even find the last
checkpoint record if this were changed between shutdown and restart;
you'd very likely go looking under the wrong filename, and even if
you hit on an existent filename you'd be looking at the wrong offset
within it.

I don't think that should even be a TODO item --- it seems far more
likely to provide a foot-gun than useful capability.

Whether 16MB is still a reasonable default segment size is worth
questioning, though I don't think that increasing it is an open-and-shut
proposition.  Larger segments mean more overhead in configurations that
force frequent segment switches, for instance.
        regards, tom lane


Re: Postgres 8.3 archive_command

От
Simon Riggs
Дата:
On Wed, 2007-11-21 at 12:24 -0500, Tom Lane wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Just to clarify: I don't object to lowering "successfully archived"
> >> messages to DEBUG1, if the field consensus is that it's too chatty.
> >> What I didn't like was the idea of logging some events but not other
> >> identical events.
>
> > Agreed on the intermittent logging.  I don't feel it's too chatty,
> > but on the other hand, I could always change the logging level on
> > the fly if I was investigating a problem, so it wouldn't be much of
> > an inconvenience to switch it if it bugs others.
>
> Also, you can always tweak your archive_command script to do some
> logging of its own, so it's always possible to make the thing more
> noisy if you need to.  Less noisy, though, is hard without changing
> the server code.

OK, both of those thoughts are good, so happy now with DEBUG1 for
success messages: "archived transaction log file..."

Enclosed patch to set archived message to DEBUG1.

I've got a few other comments for PITR docs, so I'll mention this also
in forthcoming patch.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

Вложения

Re: Postgres 8.3 archive_command

От
"Kevin Grittner"
Дата:
>>> On Wed, Nov 21, 2007 at 11:24 AM, in message <12760.1195665880@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> In poking around the logs just now, I noticed one message I'd like
>> to squelch.  Run against Milwaukee County's recent log files:
>
>> grep -c 'PRIMARY KEY will create implicit index'
>
> Set log_min_messages higher than NOTICE.  Given the current usage of
> NOTICE --- basically they're *all* newbie-annoyance items ---
Done statewide.  Thanks for the suggestion.
> I kinda wonder why WARNING isn't the default setting anyway.
Based on what you said above, I wonder, too.  Maybe that's something
to consider changing?
-Kevin




Re: Postgres 8.3 archive_command

От
Greg Smith
Дата:
On Wed, 21 Nov 2007, Tom Lane wrote:

> Whether 16MB is still a reasonable default segment size is worth
> questioning, though I don't think that increasing it is an open-and-shut
> proposition.

I don't think it's a reasonable change to make right now.  I think 16MB is 
already too big for some people, reasonable for most, and only too small 
for a tiny portion of users that have fairly powerful systems. 
Incorporating what you said, there are two downsides to a bigger segment 
that immediately come to mind:

-More garbage in unfilled segments means higher archiving overhead in some 
configurations.  There are already people annoyed enough about this 
problem to be coding around it (I'm thinking of Kevin Grittner's 
pg_clearxlogtail).

-The way new WAL segments get created can block clients while they wait 
for that write.  As the segments gets larger this problem becomes worse, 
and there are many situations where reducing worse-case latency is far 
more important than throughput.

There's already been talk of improving the latter by having a background 
process create the segments, but that doesn't make the problem go away 
altogether; it just makes it less likely to happen.  I'd at least like to 
see that change and an official log tail cleaning mechanism both available 
before considering a change to the default WAL size.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD


Re: Postgres 8.3 archive_command

От
"Zeugswetter Andreas ADI SD"
Дата:
> > > Perhaps we should move the successful archived message to DEBUG1
now,
> > > except for the first message after the archiver starts or when the
> > > archive_command changes, plus one message every 255 segments?
> > > That would reduce the log volume in the normal case without
endangering
> > > our ability to see what is happening.
> >
> > Wouldn't it be more useful to increase the WAL segment size on such
> > installations
> > that switch WAL files so frequently that it is a problem for the log
?
> >
> > This currently needs a recompile. I wondered for some time now
whether
> > 16 Mb isn't
> > too low for current hw. Maybe it is time for making WAL segment size
> > changeable
> > in the conf with a clean shutdown.
>
> I think its too late in the release cycle to fully consider all the
> implications of that. 16MB is hardcoded in lots of places. The
> performance advantages of that have been mostly removed in 8.3, you
> should note.

Oh sorry, this was definitely not meant for 8.3. And here I didn't mean
the
performance of the db issue, but an issue for archiving the WAL files.
I think most archiving systems are not too happy with extremely frequent
backup calls. Also the overall handling of too many WAL files is imho
not handy.

Andreas



Re: Postgres 8.3 archive_command

От
"Zeugswetter Andreas ADI SD"
Дата:
> I don't think that should even be a TODO item --- it seems far more
> likely to provide a foot-gun than useful capability.

On further reflection I think that initdb time is probably sufficient.
Do you think that would be a reasonable TODO ?

> Whether 16MB is still a reasonable default segment size is worth
> questioning, though I don't think that increasing it is an
open-and-shut
> proposition.  Larger segments mean more overhead in configurations
that
> force frequent segment switches, for instance.

Yes, imho there is no one size fits all (if there were, it would
probably bebetween 32 and 64 Mb).
But there are installations where even 16Mb is too much e.g. for an
embedded device.

Andreas


Re: Postgres 8.3 archive_command

От
Simon Riggs
Дата:
On Thu, 2007-11-22 at 10:34 +0100, Zeugswetter Andreas ADI SD wrote:
> > I don't think that should even be a TODO item --- it seems far more
> > likely to provide a foot-gun than useful capability.
> 
> On further reflection I think that initdb time is probably sufficient.
> Do you think that would be a reasonable TODO ?

I think you'd have to explain why this was needed. It was useful for
performance once, but not anymore.

There are many possible errors there, so not a road I would go down
without good reason. Extra configuration is just one more thing to go
wrong and one more thing for people to misconfigure.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com



Re: Postgres 8.3 archive_command

От
Tom Lane
Дата:
Simon Riggs <simon@2ndquadrant.com> writes:
> On Thu, 2007-11-22 at 10:34 +0100, Zeugswetter Andreas ADI SD wrote:
>> On further reflection I think that initdb time is probably sufficient.
>> Do you think that would be a reasonable TODO ?

> I think you'd have to explain why this was needed. It was useful for
> performance once, but not anymore.

Yeah, some demonstration that choosing another size is actually useful
would be a good idea before we go to the trouble of making it runtime
configurable.  It's already build-time configurable, and I'm not
convinced that that's not sufficient.
        regards, tom lane