SEO

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



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

>>> 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




---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

>>> 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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster