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 A73FB11F6959 for ; Thu, 30 Mar 2006 00:14:50 -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 13277-03 for ; Thu, 30 Mar 2006 00:14:46 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id B516D11F695F for ; Thu, 30 Mar 2006 00:14:45 -0400 (AST) Received: by nproxy.gmail.com with SMTP id l24so241758nfc for ; Wed, 29 Mar 2006 20:14:44 -0800 (PST) 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=pp/WFQ402qfGAD77dZonWc4CMhGQASGKoK9VlNw5U7gKBon4xv3joryTQbGwHfBIFo4moYpg17jOAkzyRD7wRanMVa6aRGqRHzX89v3BtlM3M9/INXzASkZtdQ6FPqO665ErXuK/uYvMDGKyrCAq3EKWSznCCE4fmC9+uDsqThM= Received: by 10.48.226.11 with SMTP id y11mr729194nfg; Wed, 29 Mar 2006 20:14:44 -0800 (PST) Received: by 10.49.26.20 with HTTP; Wed, 29 Mar 2006 20:14:44 -0800 (PST) Message-ID: Date: Thu, 30 Mar 2006 09:44:44 +0530 From: "Rajesh Kumar Mallah" To: "Thomas F. O'Connell" Subject: Re: Remote On-line Backup Cc: "PgSQL Admin" In-Reply-To: <89CD4C44-D35B-4D01-9728-D4CCDEC122EE@sitening.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <89CD4C44-D35B-4D01-9728-D4CCDEC122EE@sitening.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.292 required=5 tests=[AWL=-0.040, RCVD_IN_BL_SPAMCOP_NET=1.332] X-Spam-Score: 1.292 X-Spam-Level: * X-Archive-Number: 200603/359 X-Sequence-Number: 21116 On 3/29/06, Thomas F. O'Connell wrote: > I just want to make sure that I've got a good enough understanding of > the built-in on-line backup facility to be able to minimize data loss > and unavailability of the database during a remote recovery from on- > line backup. > > Here are the steps I'm proposing: > > 1. Set up archive_command in postgresql.conf on oldhost to archive to > remote repository on newhost. > 2. Perform base backup on oldhost. (I'll probably just use rsync to > backup directly to newhost.) > 3. On newhost, remove postmaster.pid from $PGDATA, disable > archive_command in postgresql.conf, and create clean pg_xlog tree. > 4. Stop the postmaster on oldhost. > 5. If the WAL file referenced by the backup file in my archive > directory on newhost is not archived when the postmaster is stopped, > copy it from oldhost to pg_xlog on newhost. even if it(STOP WAL) was archived in the new machine , you should also copy the last partially filled WAL log from pg_xlog that was created just after the file refrenced in backup file was archived. I think the overall process is fine , you may consider executing a test run skipping 4 Regds Rajesh Kumar Mallah. > 6. Create recovery.conf on newhost. > 7. Start the postmaster on newhost. > 8. Rejoice when recovery.done appears. > > The part I most want to make sure I understand well enough is step 5, > which I'm understanding to be a modification of steps 2 and 6 from > section 23.3.3 in the docs. As I understand it, there's a pretty good > possibility that the WAL file referenced by stop_backup() will not be > archived by the time I stop the postmaster on oldhost. In which case, > I should be in good shape to recover if I have a base backup, the > archived WAL files up to that final file referenced by stop_backup(), > and the partial segment file referenced by stop_backup(), which > should be the only unarchived WAL segment file and just needs to > exist in pg_xlog on newhost for things to run smoothly. > > Does this seem right? Or will I rather want to copy all the contents > of pg_xlog from oldhost as they represent current (as of stopping the > postmaster) unarchived WAL activity? > > -- > 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) > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >