Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 9D7D89FB490 for ; Thu, 15 Feb 2007 15:03:19 -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 06719-09 for ; Thu, 15 Feb 2007 15:03:04 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from web55411.mail.re4.yahoo.com (web55411.mail.re4.yahoo.com [206.190.58.205]) by postgresql.org (Postfix) with SMTP id 6314A9FB2D8 for ; Thu, 15 Feb 2007 15:03:05 -0400 (AST) Received: (qmail 40192 invoked by uid 60001); 15 Feb 2007 19:03:04 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=E5FhGDPCHNHI6viXO/x9vLdLuNC74uivMMe9cq0fBXaUagCVJmqrXHGS50Dg+1eEFv2GwI0VSauDZhSxhcUVRNBEZgGTBQwTuxePBdqux5oEIZsE68zgMHrTwqD2Cdn5jKuI+25j/oJVoHT5EtM2YbpjK+UVimFRbz0gJKOiVLw= ; Message-ID: <20070215190304.40190.qmail@web55411.mail.re4.yahoo.com> X-YMail-OSG: ecytu7wVM1kb7gUhxPlhHh5g2EcIfm4XOaGssK2VBpcPb60dCsS.Azwmb0kz4KMl4Zf571rNRZ2w4S6fojqYu3t.H.1_m3wjxaiq5sSsxSM01.00AlTXP5IJ1hTZ7QeLm8Rr.Gl9lrbe6A-- Received: from [200.53.62.220] by web55411.mail.re4.yahoo.com via HTTP; Thu, 15 Feb 2007 11:03:04 PST Date: Thu, 15 Feb 2007 11:03:04 -0800 (PST) From: pedro noticioso Subject: Re: WAL files backup To: pgsql-admin@postgresql.org In-Reply-To: <200702142002.24377.ejortegau@cable.net.co> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.938 tagged_above=0 required=5 tests=AWL, BAYES_20 X-Spam-Level: X-Archive-Number: 200702/227 X-Sequence-Number: 24692 for the base backup I tried with the procedure outlined in http://www.postgresql.org/docs/8.1/static/backup-online.html#BACKUP-BASE-BACKUP and using tar zcvf /usr/local/pgsql/data/ and to restore deleted WAL files at pg_xlog because they are older than the WAL files in /backup/wals dir mentioned it the archive_command and restore_command I: created a database added data to the database Added to postgresql.conf: archive_command = 'cp -i %p /backup/wals/%f wrote: > hi: > > You enable WAL archiving by setting an appropiate > archive_command in your > postgresql.conf (probably something copying the WAL > files somewhere you store > them). You MUST restart postgres after changing this > file. > > About the WAL file size: sorry, i don't know exactly > how to do that. I do know > that it requires that you recompile postgres (or > that's what i read > somewhere). There's an alternative, if you switch to > version 8.2. I haven't > actually tried it, but it appears that in 8.2, in > addition to archiving > everytime the WAL grows to 16 MB, it also archives > the WAL records every > certain amount of time that you can configure on > postgresql.conf. So you can > store WAL files, say, every 30 or 60 minutes, > regardless of how big it is. > This way maybe the DB speed won't be seriuosly > affected. > > Small question, though. How are you taking your base > backup? > > Regards, > > Eduardo. > On Wednesday 14 February 2007 18:38, pedro noticioso > wrote: > > Everyone please excuse the mistake in my previous > > question, there is a lot in my mind. > > > > We are already creating a complete backup every > day, > > and would like to have WAL files to restore up to > the > > last minute of course. > > > > Acording to > > > > > http://www.postgresql.org/docs/8.1/static/runtime-config-wal.html#GUC-ARCHI > >VE-COMMAND > > > http://www.postgresql.org/docs/8.1/static/backup-online.html#BACKUP-ARCHIVI > >NG-WAL > > > > I tried a couple of them and got an error messages > > stating that WAL archiving was not activated, so, > how > > can I activate it? thanks > > > > these docs explain a lot but are not practical > like > > the ones in howtoforge.com for example ;) > > > > In regards to my previous question, I did not > check > > what I wrote, what I meant is that the > 16*1024*1024 > > WAL file size is too big for us because we handle > just > > a few KB per day of information, so are interested > in > > a smaller WAL file size, perhaps 1024*1024 or even > > less so that we can backup the files in less media > > space, but in the postgresql@freenode.net IRC > channel > > I was told that I would make the database work > slower > > if I make a WAL file too small, what is your take > on > > the matter? recomendation? etc? > > > > thanks a lot! > > > > > > > > > > --- "Eduardo J. Ortega" > > > > wrote: > > > For what i understand, the WAL files only record > > > changes to the database, so > > > each WAL segment is absolutely different from > the > > > previous one. As a matter > > > of fact, each WAL file is the incremental backup > you > > > want to make (provided > > > you have already taken a base or level 0 > backup). > > > > > > On Monday 12 February 2007 13:42, pedro > noticioso > > > > > > wrote: > > > > hi there > > > > > > > > I lightly read this > > > > > http://www.postgresql.org/docs/8.1/static/backup-online.html > > > > > > and am interested in creating an incremental > > > > > > backup of > > > > > > > WAL files, but my database is small so each of > > > > > > this > > > > > > > WAL files must be almost identical to the > previous > > > > one. > > > > > > > > Is there a way to incrementally backup so that > it > > > > takes less space? > > > > > > > > thanks! > > > > > ___________________________________________________________________________ > > > > > >_________ Finding fabulous fares is fun. > > > > Let Yahoo! FareChase search your favorite > travel > > > > > > sites to find flight and > > > > > > > hotel bargains. > > > > > > > http://farechase.yahoo.com/promo-generic-14795097 > > > > > > > ---------------------------(end of > > > > > > broadcast)--------------------------- > > > > > > > TIP 1: if posting/reading through Usenet, > please > > > > > > send an appropriate > > > > > > > subscribe-nomail command to > > > > > > majordomo@postgresql.org so that your > > > > > > > message can get through to the mailing > list > > > > > > cleanly > > > > > > -- > > > Eduardo J. Ortega - Linux user #222873 > > > "No fake - I'm a big fan of konqueror, and I use > it > > > for everything." -- Linus > > > Torvalds > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > TIP 7: You can help support the PostgreSQL > project > > > by donating at > > > > > > > > > http://www.postgresql.org/about/donate > > > > > ___________________________________________________________________________ > >_________ Looking for earth-friendly autos? > > Browse Top Cars by "Green Rating" at Yahoo! Autos' > Green Center. > > http://autos.yahoo.com/green_center/ > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > -- > Eduardo J. Ortega - Linux user #222873 > "No fake - I'm a big fan of konqueror, and I use it > for everything." -- Linus > Torvalds > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project > by donating at > > > http://www.postgresql.org/about/donate > ____________________________________________________________________________________ Get your own web address. Have a HUGE year through Yahoo! Small Business. http://smallbusiness.yahoo.com/domains/?p=BESTDEAL