Re: xlogdump fixups and WAL log question.

On Fri, 2006-10-20 at 17:04 -0400, Theo Schlossnagle wrote:
> On Oct 20, 2006, at 4:24 PM, Simon Riggs wrote:

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

You aware you can turn archive_command off until after the load? That
way you'll have nothing to back up at all until its done. In 8.1, when
archive_command is off, CREATE TABLE AS SELECT is optimised to produce
no WAL.

Turning off WAL is a difficult topic. Without it you have no crash
recovery, which IMHO everybody says they don't care about until they
crash, then they realise. It's hard to be selective about writing WAL
for specific operations also.

However, there may be two cases not discussed before:
1. A newly created database into which a full load and/or pg_dump
restore is being run. In that case we could have a mode where we turn
off WAL completely during initdb via pg_control and then turn it back on
again permanently (i.e. a one way switch) once the server is fully
loaded. That covers the "restore database" use case.

2. Turn off WAL for one or more tablespaces, though never the main data
directory. This allows for data which is "externally recoverable" to be
isolated from things like the catalog and other more normal data. That
would be a tablespace level option that would propagate to each object.
That covers the "regular snapshot load" use case you describe.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com