Dear PostgreSQL folks;
New WAL archive commands, to be used in "archive_command" and
"restore_command" are released in
http://pgfoundry.org/projects/pglesslog/
You can download pglesslog 1.1 beta and use this in PostgreSQL8.3.x to
reduce the size of archive log. You don't need any modification to
PostgreSQL itself. Size reduction is done by replaceing full page
writes (needed to recovery from incomplete database writes at the crash,
which is not needed in PITR) with corresponding incremental logs.
Full page writes between pg_start_backup and pg_stop_backup are not
replaced to restore incomplete pages during the backup.
This is the first beat release for 32bit environment. General release
including 64bit environment will be done in a couple of weeks.
The following is the notes from README.lesslog file in the release file.
Please note that in the case of pgbench, archive log size will be one
seventh of the original.
** What is lesslog?
Lesslog is a set of tools to reduce the size of PostgreSQL archive log.
Lesslog consists of the following programs.
** What's new in lesslog 1.1?
Lesslog 1.1 can generate incremental log for HEAP2_CLEAN WAL records,
which are created by vacuum and prune (HOT). Because full page writes
of this WAL records occupies major part of WAL segments, replacing this
with incremental log dramatically reduces the size of WAL when archiving.
** How small archive log will be?
Pglesslog compression rate is shown in pgbench.
1. Environment
Scale factor: 100
Connection: 10
Transactions: 360,000 per connection
shared_buffers: 32MB
max_fsm_pages: 204,800
checkpoint_segments: 1,000
checkpoint_timeout: 5min
checkpoint_completion_target: 0.5
autovacuum: on
2. Size of original/compressed WAL
WAL segments :1,379
Original WAL size : 22,064MB
Compressed WAL size: 3,598MB
Size ratio: 16% of the original
** Acknowledgement
I appreciate Pavan Deolasee and Heikki Linnakangas for their stimulating
comments and advise. Without their advises, the solution would have
been much more complicated.
--
Koichi Suzuki