pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Childs <peterachilds@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: online tape backup
Date: Tue, 13 Feb 2007 09:49:02 +0000
Message-ID: <a2de01dd0702130149v4e88cbe3j48558ab8b66536ba@mail.gmail.com> (raw)
In-Reply-To: <200702131027.02556@zmi.at>
References: <200702091951.14100.ejortegau@cable.net.co>
	<200702131027.02556@zmi.at>

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



view thread (6+ messages)  latest in thread

Message-ID: <a2de01dd0702130149v4e88cbe3j48558ab8b66536ba@mail.gmail.com>
Permalink:  ../a2de01dd0702130149v4e88cbe3j48558ab8b66536ba@mail.gmail.com/
Also on:    postgresql.org/message-id/a2de01dd0702130149v4e88cbe3j48558ab8b66536ba@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: peterachilds@gmail.com
  Subject: Re: online tape backup
  In-Reply-To: <a2de01dd0702130149v4e88cbe3j48558ab8b66536ba@mail.gmail.com>
  References: <200702091951.14100.ejortegau@cable.net.co> <200702131027.02556@zmi.at> <a2de01dd0702130149v4e88cbe3j48558ab8b66536ba@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox