Tuesday, August 30, 2011

Postgresql WAL archiving / pg_archivecleanup

  • The thing with WAL archiving is as follows:

The postgresql WAL is always being generated but depending on our preferences - wal level - (archive, hot_standby, minimal )the quantity of logs that are kept at any particular moment varies; the minimal level is self explanatory: just a small number of logs are kept and those that are considered "old" (that is, that are behind a checkpoint or save) are recycled or renumbered to a higher value and then re-utilized. TODO:The archive level keeps up to max_wal_segments and I'm not sure how hot standby works in this particular aspect.

Though, there are some caveats and some precautions not to let the pg_xlog directory to grow indefinitely (i.e. using the pg_archivecleanup function). There's this interesting setting "archive_timeout" that will force the generation of a WAL segments in a constant interval regardless of whether the log is ready or not (if it's reached the 16mb requirement), nevertheless WAL files are always the same size (16mb), no matter what, so setting this parameter up with a low value will result in the bloating of your WAL storage much more rapidly.


pg_archivecleanup usage:

To configure a standby server to use pg_archivecleanup, put this into its recovery.conf configuration file:
archive_cleanup_command = 'pg_archivecleanup archivelocation %r'


One thing to have in mind is what happens when the pg_xlog directory runs out of space, should the situation arise,postgres will do a PANIC shutdown.


1 comment:

  1. SQIAR (http://www.sqiar.com/solutions/technology/tableau) is a leading global consultancy which provides innovative business intelligence services to small and medium size (SMEs) businesses. Our agile approach provides organizations with breakthrough insights and powerful data visualizations to rapidly analyse multiple aspects of their business in perspectives that matter most.