is it possible to run postgres with no WAL files being produced? I am looking at a way of running a 9.2 postgres database on redhat 6 without any WAL files being produced. 

We have a end of day batch process which generates a large number of WAL files (and has filled the disk a couple of times). We also take a pg_dump backup both before the start of the end of day process and also at the end. We are happy that should there be a problem during the batch process to restore the pre pg_dump backup. 

When i set the wal_level to minimal we are still getting a large number of WAL files due to the type of database updates which occur. So is it possible to have no WAL files as all (basically a wal_level=none)?

thanks
dave
Short version: no. PostgreSQL doesn't support generating no WAL whatsoever, because changes to system catalog tables in the pg_catalog schema are always logged. There's also transaction ID logging (pg_clog), the multixact tracking data (pg_multixact), etc, in addition to the xlog, but they tend to be very small.

As Daniel says, you can run with minimal WAL generation by:


Using wal_level = minimal
Creating all tables as UNLOGGED or TEMPORARY tables


In this case, you will also want to set fsync = off, full_page_writes = off and synchronous_commit = off, since you have no requirement for durability and crash recovery.

Of course, if anything goes wrong (like power loss, unplanned restart, DB server crash, pg_ctl -m immediate stop, etc) your data will be totally unrecoverable, but that seems to be what you want.

The rate of WAL creation and rotation with this configuration will be negligible for most applications.

If you don't mind having to re-initdb after any restart (even a clean one) you could put pg_xlog on a tempfs, but I doubt it's worth the hassle when Pg will generate only a tiny bit of WAL.

You should also check and make sure you don't have WAL archiving (archive_mode = on) enabled, that wal_keep_segments isn't set. Both of those are only useful if you're doing WAL-based backup or replication. If you don't, then WAL should not accumulate, it should be rapidly recycled. Maybe your checkpoints are set to be much too infrequent, so much so that you run out of disk? Check to see if the checkpoint_segments parameter is really really high.

I think you're trying to solve the wrong problem here. The issue isn't stopping PostgreSQL from producing WAL, it's figuring out why it piled up in your batch jobs.

See also: Optimizing PostgreSQL for fast testing.