Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 7F1789FBAA3 for ; Thu, 11 Oct 2007 13:02:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 54578-07 for ; Thu, 11 Oct 2007 13:02:42 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from outmail136047.authsmtp.com (outmail136047.authsmtp.com [62.13.136.47]) by postgresql.org (Postfix) with ESMTP id 10FBE9FBA9E for ; Thu, 11 Oct 2007 13:02:39 -0300 (ADT) Received: from outmail128183.authsmtp.com (outmail128183.authsmtp.com [62.13.128.183]) by punt3.authsmtp.com (8.13.8/8.13.8/Kp) with ESMTP id l9BG2YHJ040548; Thu, 11 Oct 2007 17:02:35 +0100 (BST) Received: from [192.168.0.3] (85-211-149-22.dyn.gotadsl.co.uk [85.211.149.22]) (authenticated bits=0) by mail.authsmtp.com (8.13.8/8.13.8/Kp) with ESMTP id l9BG2Y2G046219; Thu, 11 Oct 2007 17:02:34 +0100 (BST) Subject: Re: Consistent PITR backup From: Simon Riggs To: Thomas Karcher Cc: Tom Lane , pgsql-admin@postgresql.org In-Reply-To: <1192113425.9842.2.camel@localhost.localdomain> References: <20071011093908.GA7988@arche.mine.nu> <13019.1192112569@sss.pgh.pa.us> <1192113425.9842.2.camel@localhost.localdomain> Content-Type: text/plain Date: Thu, 11 Oct 2007 17:02:42 +0100 Message-Id: <1192118562.4233.415.camel@ebony.site> Mime-Version: 1.0 X-Mailer: Evolution 2.8.2 Content-Transfer-Encoding: 7bit X-Server-Quench: 61a86e00-7813-11dc-b86a-001185d377ca X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ NksadRtFaQRbYlpF HGQLW1NEUVp7WWF/ aA8fZQBDYEtPQQRu WVZLQE1QERpoT0AI dGkcOxsFdwFAen95 K0RiW3IVWkUrfUAo Qk5JEmUHZnphaTRM TRJQcVJJcANLfxdA PlN4UyBcLwdSbGoL NiMWHhYEdR5YLj9U WR0AK05aeWc3JHY2 ThsBAC1H X-Authentic-SMTP: 61633235383639.squirrel.dmpriest.net.uk:752/Kp X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-Virus-Status: No virus detected - but ensure you scan with your own anti-virus system! X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/95 X-Sequence-Number: 27009 On Thu, 2007-10-11 at 16:37 +0200, Thomas Karcher wrote: > Hi Tom, > > > > My idea is now: When doing a consistent PITR backup, extend the > > > documented steps between 4 (pg_stop_backup()) and 5 (wait for archived > > > WAL file) with a busy wait on pg_xlog/archive_status/*.ready files - as > > > soon as no .ready files are there anymore, the archiver has done its > > > work, and I can safely backup the archived WAL files in order to have a > > > really consistent backup. > > If the database is busy, you might never see an instant where there are > > no .ready files. Certainly it's possible that this approach will > > archive many more segments than you need. > > I see - but the risk for never seeing an instant without .ready files is > relatively slow, I hope. And compared to the risk of not having a > consistent backup, I could live with having backed up more WAL files > than I actually would have needed. There will be gaps, otherwise the archiver will never catch up over time, but its hard to say when those might be. > > > Is that right? Or is there an easier way to do it? > > I believe the intended way to do this is to look at pg_stop_backup's > > return value to determine the last segment you need to archive. > > Thank you - I saw that, but I didn't have a clue about how to determine > the WAL filename in which this segment is to be found ... Look at pg_stop_backup() in the docs. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com