Re: WAL and backup recovery

Dario Brignardello <dbrignar(at)sinectis(dot)com> writes:
> checkpoint_segments = 1
> checkpoint_timeout = 30

Reducing checkpoint_segments and checkpoint_timeout is actually
seriously counterproductive, if your problem is amount of disk space
chewed up by WAL logs during a long transaction (such as bulk load of
a big table). The WAL log cannot be truncated until the xact commits,
so the checkpoints that happen meanwhile just cause log bloat. Lots
of it, because each checkpoint causes fresh copying of modified pages
into the WAL log.

Try increasing those numbers, not decreasing them. Maybe 10/600 or so?

Also, if you are short on disk space for WAL, increasing wal_files isn't
such a great idea either.

regards, tom lane