X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4E32911F6C2F for ; Sat, 8 Apr 2006 14:10:08 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.12 X-Spam-Level: X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id W9zNJYJp-a5F for ; Sat, 8 Apr 2006 14:09:55 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id DBB5B11F6949 for ; Sat, 8 Apr 2006 14:10:04 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id p77so409352nfc for ; Sat, 08 Apr 2006 10:10:06 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=o+MuLEjXx+9labtwMba/IdBWhhWQrGPWm4TXHibmU3QMy0c29oPgEpBhM5hzj0AAm0p8/O/GX31B2bQWjwJ6PJm/tvit5JFTPZxjBcOkymNIiU4Rw23QQeMW/zqgXKQHzC+dDoMBEXWCdPMk/dLAqsr18NK+CKqNTevF48XIcMs= Received: by 10.48.246.8 with SMTP id t8mr902029nfh; Sat, 08 Apr 2006 10:10:04 -0700 (PDT) Received: by 10.49.27.16 with HTTP; Sat, 8 Apr 2006 10:10:04 -0700 (PDT) Message-ID: Date: Sat, 8 Apr 2006 22:40:04 +0530 From: "Rajesh Kumar Mallah" To: "Grega Bremec" Subject: Re: FW: Setting up of PITR system. Cc: pgsql-admin@postgresql.org In-Reply-To: <4437AF15.3020707@p0f.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <442B93DD.4020305@p0f.net> <442E1945.4030006@p0f.net> <442FDC7F.9010001@p0f.net> <4430BC59.7040006@p0f.net> <4437AF15.3020707@p0f.net> X-Archive-Number: 200604/82 X-Sequence-Number: 21251 > Rajesh Kumar Mallah wrote: > |>| Do you see any problem in the current approach ? > |>| i have seen it working fine till now. > |> > |>I do, to be honest. The WAL location counter accounts for 4294967295 > |>positions and while I'm certain that's WAY more than the average number > |>of transactions that go into a WAL, quite a number of small ones can > |>certainly happen before a WAL is rolled over, and until then, you're > |>dealing with the same log file. > |> > |>If two backups happen in that period of time for whatever reason, you'r= e > |>going to have a false positive by looking into ${WAL_ARCHIVE} and > |>searching just for the WAL name, so including the location in the searc= h > |>of a WAL fragment is certainly necessary. Infact, going purely by > |>chance, the probability of hitting the same location in two different > |>log files in two subsequent backups is much lower than hitting the same > |>WAL twice. > | > | The current wal log is not being removed from the wal archive area > | in any case. The files less than the current ones are being rm'ed. > | > | I am sorry i am not able to get your apprehension. But i shall > | surely try harder to understand your point. > > Hi Rajesh, list. > > I'm sorry I didn't get back to you earlier, I was at an IBM business > conference for a couple of days; not to say it rendered me incapable of > communicating via e-mail, but it did bring along certain social > responsibilities which caused me to both stay up and sleep late, if you > know what I mean. :) > > Let me explain the above predicament in more practical terms. > > Let us say you started a backup very soon after a WAL had been rolled > over. Current WAL at that time was called, for example, > ${PGDATA}/pg_xlog/000000010000000E0000000A. The location at that time > was 000F594A (iow, early in the WAL cycle). [disclaimer: all events in > this story are entirely fictional, any similarity to actual persons and > events is purely coincidental :) ] > > pg_start_backup() will create a WAL backup: > ~ ${PGDATA}/pg_xlog/000000010000000E0000000A.000F594A.backup > > which will be archived to ${WAL_ARCHIVE} under the same name, or > possibly given a different extension, depending on archive_method. Let > us assume for the purpose of this explanation, that archive_method > consists only of cp -i identical if one used gzip -c, for example. > > Now, this backup fails for whatever reason (rsync trouble, etc.). You > abort it and leave WAL archive as it was. You diagnose the problem that > caused the backup to fail and repeat the procedure. And since your > diagnostic skills are so good it took you almost no time to fix it, the > database engine is now at location 002D94AF in that _same_ WAL. > > Once you restart the backup script, pg_start_backup() is called and > ${PGDATA}/pg_xlog/000000010000000E0000000A.002D94AF.backup is created > and archived to ${WAL_ARCHIVE} under that same name. > > Your method of discovering logs to delete will now match _two_ "current" > log file archives instead of one, because they both come from the same > WAL, fail to actually delete the stale one (the one from position > 000F594A) and thus clutter your backup with irrelevant WAL fragments. Dear Grega, Thanks for the reply. now i have started understanding ! Is cluttering of the wal archive area in cases where that backup had to be re-started for whatever reasons is the *only* concern ? if its so , we should not be too much bothered becoz in the next successfull backup the extra clutter will get deleted. if there are other concern please lemme know. > > The second part of the second paragraph was only to expose that, > following the same logic as outlined above, if you take WAL locations as > the criterion of removing stale WAL fragments instead of WAL names, it > is far less likely to hit a false positive, because you would have to > pg_start_backup() _exactly_ 4294967296 locations after the first one. > > Of course, you want to be unambiguous in your search of the perfect WAL > archive, so you want to use _both_ WAL name and location as the criterion= . > > | the old log files without the base backup are not useful. since > | rsync is being used to optimise the copying by overwriting the > | base backup everytime, i dont thing preserving the old files > | makes sense. Had it been and non overwritng backup the files > | would have made sense. > > I see. I was assuming you used rsync to copy the database cluster > somewhere then tar it there, while it was lying still ("Fell, Destroyed" > of Fugazi comes to mind :) ). > > I will get back to you with the review of your script later. A quick > scan reveals there is not much left to be improved, though. Please do not put too much effort, as i the drives in my other server has got installed and i am adapting the script for doing remote backup ( which is a more common senerio). Thank You Regds Rajesh Kumar Mallah. >