4

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

  • 2
    There is no way for doing that. WAL stands for Write Ahead Log, and this is the mechanism how the data and their changes are persisted - the DB cannot live without it. Read postgresql.org/docs/current/static/wal-intro.html for more details. You will have to add more disk space and/or rethink the batch job to produce fewer changes. – dezso Jun 3 '15 at 12:43
  • 6
    Consider using unlogged tables. From CREATE TABLE doc: Data written to unlogged tables is not written to the write-ahead log. But you still need to transfer final results to logged tables for durable storage, or to out-of-postgres durable storage if WAL is insufferable. – Daniel Vérité Jun 3 '15 at 12:48
  • @dezso, i was hoping that as i can live with not being able to recover from a crash (as i would restore the backup) there would be a way of not producing them. Thanks. – davegreen100 Jun 3 '15 at 12:52
6

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.

  • 1
    What you really want seems to be a non-durable database, by the sounds. It'd maybe be possible to make PostgreSQL create catalog tables as unlogged with heavy patching to the code, but I don't think it'd be possible to totally get rid of the xlog. You might want to look at DBMSs that are designed more for this use case, like HSQLDB or H2. – Craig Ringer Jun 3 '15 at 13:31
  • 3
    If you can't get your dev team to change CREATE TABLE to CREATE UNLOGGED TABLE then you have a company management problem, not a technical problem. That's absurd, and you're not going to find a technical fix for that problem. – Craig Ringer Jun 3 '15 at 13:48
  • 1
    you could say that, i couldn't possibly comment. – davegreen100 Jun 3 '15 at 13:49
  • 2
    Right. Well, if you stop WAL archiving then your running-out-of-space problem will go away anyway as WAL will just get immediately recycled. Note that UNLOGGED tables aren't logged to WAL (kind of the point) and therefore cannot be replicated over WAL shipping or streaming replication. – Craig Ringer Jun 3 '15 at 14:09
  • 2
    @davegreen100 Sounds about right. Check your checkpoint settings while you're there; if they're at the defaults they're certainly too low (and you probably get warnings about it in the logs), but also make sure there's enough disk space for checkpoint_segments * 16MB WAL segs if it's high. Oh, and have a look at stackoverflow.com/q/9407442/398670 if you want to go the non-durable route. – Craig Ringer Jun 3 '15 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.