On Oct 20, 2006, at 4:24 PM, Simon Riggs wrote:

On Fri, 2006-10-20 at 13:18 -0400, Theo Schlossnagle wrote:
Not sure who cares, so xzilla indicated I should drop a note here.  I
just made the xlogdump stuff work for 8.1 (trivial) and fixed a few
other small issues that caused it to not work right both generally
and in our environment.


Diogo Biazus was working on that; I care also.

Cool.  Patch is short.

We're using it to track down what's causing some wal log ruckus.
We're generating about a quarter terabyte of WAL logs a day (on bad
days) which is posing some PITR backup pains.  That amount isn't a
severe challenge to backup, but our previous install was on Oracle
and it generated substantially less archive redo logs (10-20 gigs per

As Tom says, definitely because of full_page_writes=on

Can I turn that off in 8.1?

Is it possible to create tables in fashion that will not write info
to the WAL log -- knowingly and intentionally making them
unrecoverable?  This is very desirable for us.  We snapshot tables
from a production environment.  If the database goes down and we
recover, the old snapshots are out of date anyway and serve no useful
purpose.  The periodic snapshot procedure would re-snap them in short
order anyway.  I'd like to do:

INSERT INTO TABLE tblfoo_snap1 AS SELECT * from <table on remote
database> NO LOGGING;

(NO LOGGING being the only part we're currently missing) Is something
like this possible?

Do you want this because of:
1) performance?

performance in that a substantial portion of my time is spent writing to pg_xlog

2) to reduce the WAL volume of PITR backups?

Yes.  Main concern.

e.g. archive_command = 'pg_WAL_filter -f | ... '
e.g. archive_command = 'pg_WAL_filter -x 35456 | ... '

There are some other ideas for generally reducing WAL volume also.

I'd like to see them not written to the xlogs at all (if possible). Seems rather unnecessary unless I'm missing something.

// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?


Reply via email to