On 13/02/07, Michael Monnerie <michael.monnerie@xxxxxxxxxxxxxxxx> wrote:
On Samstag, 10. Februar 2007 01:51 Eduardo J. Ortega wrote: > 1) pg_start_backup > 2) tar to my tape device /data/postgresql excluding > /data/postgresql/pg_xlog 3) pg_stop_backup > 4) delete WAL files under /data/pg_trans_log/ (which is the directory > to which archive_command copies my archived WAL files) with name > "smaller" than the name of the last 000*.backup > 5) append to previously created tar on the tape the WAL > directory /data/pg_trans_log/ Dear all, I'm replying to this message because it's a good start, and I'm thinking of switching from currently pg_dump on all databases to using the concept of base+WAL backup. What we did until now was (not using WAL) - pg_dump for each DB - vacuum full analyze for each DB after backup From what I understand, if I run a vacuum, the WAL logs will be enormous, as they do not simply store the vacuum command itself, but every single operation done on the db. If that's true, running vacuum before the base backup could be better, as there are less WAL logs to store, making the backup smaller, right?
No Not really, Since the base backup is just a dump of the database files only a Vacuum Full / Cluster / Reindex will actually shrink it plane Vacuum will just mark it all for reuse. A Vacuum of any sort will bloat the WAL so doing a vacuum pri to your base backups should mean that your WAL is smaller just after backup which may have advantages. Since you really ought to be using Autovacuum I would not worry about it too much. If you really want a small base backup in which case run a Vacuum Full before you backup but this may not shrink the actuall size of backup as the WAL for the privious backup will be larger. Peter.
