Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id D47259FB231 for ; Tue, 13 Feb 2007 05:49:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 07365-04 for ; Tue, 13 Feb 2007 05:49:01 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id 48E9C9FB21B for ; Tue, 13 Feb 2007 05:49:04 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1526016ugf for ; Tue, 13 Feb 2007 01:49:02 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=rW0HHqoErdrfnZeo4I6LmZGM8Wy75Kl2zHOSCNbaS0OUvpVDSjZWWr4DZnkyL5RpvHUVreUtC3L45XOS6pPnv1/WqfvVA8FqTt8KIxbJR/laxtDhtkenhpnlnFQMcHVPg2D7tQVc6LuDvJv83DKYJ9k4sPhUCV45lHNT/e2OQ1U= Received: by 10.78.150.7 with SMTP id x7mr30702hud.1171360142396; Tue, 13 Feb 2007 01:49:02 -0800 (PST) Received: by 10.78.195.10 with HTTP; Tue, 13 Feb 2007 01:49:02 -0800 (PST) Message-ID: Date: Tue, 13 Feb 2007 09:49:02 +0000 From: "Peter Childs" Subject: Re: online tape backup Cc: pgsql-admin@postgresql.org In-Reply-To: <200702131027.02556@zmi.at> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <200702091951.14100.ejortegau@cable.net.co> <200702131027.02556@zmi.at> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/174 X-Sequence-Number: 24639 On 13/02/07, Michael Monnerie 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.