Re: online tape backup

On 13/02/07, Michael Monnerie <michael(dot)monnerie(at)it-management(dot)at> 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.