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 ACB5D9DCD5B for ; Sat, 25 Mar 2006 02:23:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 57562-09 for ; Sat, 25 Mar 2006 02:23:30 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from chilco.textdrive.com (chilco.textdrive.com [207.7.108.242]) by postgresql.org (Postfix) with ESMTP id 003DD9DCC60 for ; Sat, 25 Mar 2006 02:23:28 -0400 (AST) Received: from [192.168.1.109] (unknown [209.149.57.250]) by chilco.textdrive.com (Postfix) with ESMTP id B41EEDBD9F for ; Sat, 25 Mar 2006 06:25:38 +0000 (UTC) Mime-Version: 1.0 (Apple Message framework v746.3) In-Reply-To: <200603250019.53577.peter_e@gmx.net> References: <33C7314B-CE32-4E5A-8D73-1AA0B32EBB84@sitening.com> <200603250019.53577.peter_e@gmx.net> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <35415ACA-B193-4040-B032-7746E2DF3510@sitening.com> Content-Transfer-Encoding: 7bit From: "Thomas F. O'Connell" Subject: Re: Continuous On-line Backups Date: Sat, 25 Mar 2006 00:23:22 -0600 To: "PgSQL Admin" X-Mailer: Apple Mail (2.746.3) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.259 required=5 tests=[AWL=0.259] X-Spam-Score: 0.259 X-Spam-Level: X-Archive-Number: 200603/254 X-Sequence-Number: 21011 On Mar 24, 2006, at 5:19 PM, Peter Eisentraut wrote: > Thomas F. O'Connell wrote: >> So long as I know that nothing will access the recovery database with >> write activity, is there a way to toggle the continuity so that I >> could allow recovery to complete on a nightly basis, pg_dump the >> recovered database (a read-only action), and then resume recovering? > > Even a read-only action uses up transaction numbers and other > things, so > I'd expect that to be a problem. Good point. My chief concern with the loss of flexibility would be the greater difficulty in detecting a lost or corrupted WAL segment file if archive_command is operating across a network (or locally, with an external script responsible for moving the files across the network). With nightly pg_dumps/pg_restores, at least I know my window of a valid database is on the order of 24 hours. Simon Riggs recently recommended base backups as a weekly process with a continuous on-line backup operating in the interim. I guess the price to be paid for the higher availability is increased scrutiny of the process. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 3004 B Poston Avenue Nashville, TN 37203-1314 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax)