From d.wall@computer.org Thu Jan 10 19:27:42 2008 Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 659AE2E331E for ; Thu, 10 Jan 2008 15:27:42 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 75940-08 for ; Thu, 10 Jan 2008 15:27:23 -0400 (AST) Received: from mail2.yozons.com (mail2.yozons.com [63.251.168.97]) by postgresql.org (Postfix) with ESMTP id BD7422E31D1 for ; Thu, 10 Jan 2008 15:27:36 -0400 (AST) Received: from [192.168.1.2] (pool-71-164-18-27.sttlwa.fios.verizon.net [71.164.18.27]) (authenticated bits=0) by mail2.yozons.com (8.12.10/8.12.10) with ESMTP id m0AJRZlG022578 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT) for ; Thu, 10 Jan 2008 11:27:35 -0800 Message-ID: <478671A9.9070404@computer.org> Date: Thu, 10 Jan 2008 11:27:37 -0800 From: David Wall Reply-To: d.wall@computer.org User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: PITR warm-standby with 8.2 setup questions Content-Type: multipart/alternative; boundary="------------070805000300020302020903" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/111 X-Sequence-Number: 27885 This is a multi-part message in MIME format. --------------070805000300020302020903 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit I'm trying to get WAL file copying working across two systems. It seems pretty straightforward to handle this in the "archive_command" of the primary, in which I am able to copy the files easily to a staging area for the backup system. On the backup system, I have the recovery.conf pointing to my script, but I have a few questions on how that program should behave, and whether I'd be much wiser to just use pg_standby instead of my own script. 1) I do not want to give the backup PG a file unless it's a "complete" file. I don't want to give it one that is currently in the middle of being transferred from the primary to the backup system (or a backup copy that failed in the middle). Most of my files are 16777216 bytes, so I initially checked that size. But I found that there are files with a name like '0000000100000002000000DC.00000020.backup' that is only 272 bytes. Will that file also need to be given to PG recovery, and if so, how are most determining if a small file is complete or just a partial from the copy? 2) On the recovery side, the %p and %f values are giving me %p of 'pg_xlog/RECOVERYXLOG' and %f of '0000000100000003000000B1'. Can I confirm that this means my recover program should find a file named '0000000100000003000000B1' in my backup system's WAL archive and copy it to the file named 'pg_xlog/RECOVERYXLOG', or should I put it into 'pg_xlog/0000000100000003000000B1'? 3) On the backup PG, is it correct that I have to remove backup_label, postmaster.pid and pg_xlog if they are part of the or TAR backup from the primary's PGDATA area? Thanks, David --------------070805000300020302020903 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
I'm trying to get WAL file copying working across two systems.  It seems pretty straightforward to handle this in the "archive_command" of the primary, in which I am able to copy the files easily to a staging area for the backup system.

On the backup system, I have the recovery.conf pointing to my script, but I have a few questions on how that program should behave, and whether I'd be much wiser to just use pg_standby instead of my own script.

1) I do not want to give the backup PG a file unless it's a "complete" file.  I don't want to give it one that is currently in the middle of being transferred from the primary to the backup system (or a backup copy that failed in the middle).  Most of my files are 16777216 bytes, so I initially checked that size.  But I found that there are files with a name like '0000000100000002000000DC.00000020.backup' that is only 272 bytes.  Will that file also need to be given to PG recovery, and if so, how are most determining if a small file is complete or just a partial from the copy?

2) On the recovery side, the %p and %f values are giving me %p of 'pg_xlog/RECOVERYXLOG' and %f of '0000000100000003000000B1'.  Can I confirm that this means my recover program should find a file named '0000000100000003000000B1' in my backup system's WAL archive and copy it to the file named 'pg_xlog/RECOVERYXLOG', or should I put it into 'pg_xlog/0000000100000003000000B1'?

3) On the backup PG, is it correct that I have to remove backup_label, postmaster.pid and pg_xlog if they are part of the or TAR backup from the primary's PGDATA area?

Thanks,
David

--------------070805000300020302020903-- From kevin@kevinkempterllc.com Thu Jan 10 23:09:06 2008 Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 9DCF22E0185 for ; Thu, 10 Jan 2008 19:09:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 40176-10 for ; Thu, 10 Jan 2008 19:09:03 -0400 (AST) Received: from outbound-mail-111.bluehost.com (outbound-mail-111.bluehost.com [69.89.18.7]) by postgresql.org (Postfix) with SMTP id 4B9E42E019E for ; Thu, 10 Jan 2008 19:09:02 -0400 (AST) Received: (qmail 30314 invoked by uid 0); 10 Jan 2008 23:09:00 -0000 Received: from unknown (HELO box54.bluehost.com) (69.89.20.54) by xmail.bluehost.com with SMTP; 10 Jan 2008 23:09:00 -0000 Received: from ip65-46-48-50.z48-46-65.customer.algx.net ([65.46.48.50] helo=[10.20.31.15]) by box54.bluehost.com with esmtp (Exim 4.68) (envelope-from ) id 1JD6WA-0004gv-9N for pgsql-admin@postgresql.org; Thu, 10 Jan 2008 16:08:58 -0700 From: Kevin Kempter To: pgsql-admin@postgresql.org Subject: Re: PITR warm-standby with 8.2 setup questions Date: Thu, 10 Jan 2008 16:08:48 -0700 User-Agent: KMail/1.9.6 (enterprise 0.20071123.740460) References: <478671A9.9070404@computer.org> In-Reply-To: <478671A9.9070404@computer.org> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200801101608.48180.kevin@kevinkempterllc.com> X-Identified-User: {840:box54.bluehost.com:kevinkem:kevinkempterllc.com} {sentby:bopbeforesmtp 65.46.48.50 authed with kevinkempterllc.com} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/113 X-Sequence-Number: 27887 On Thursday 10 January 2008 12:27:37 David Wall wrote: > I'm trying to get WAL file copying working across two systems. It seems > pretty straightforward to handle this in the "archive_command" of the > primary, in which I am able to copy the files easily to a staging area > for the backup system. > > On the backup system, I have the recovery.conf pointing to my script, > but I have a few questions on how that program should behave, and > whether I'd be much wiser to just use pg_standby instead of my own script. If you're on 8.2 I'd use pg_sdtandby just because it's "actively" managed- although I've written my own as well. It's pretty straight forward > > 1) I do not want to give the backup PG a file unless it's a "complete" > file. I don't want to give it one that is currently in the middle of > being transferred from the primary to the backup system (or a backup > copy that failed in the middle). Most of my files are 16777216 bytes, > so I initially checked that size. But I found that there are files with > a name like '0000000100000002000000DC.00000020.backup' that is only 272 > bytes. Will that file also need to be given to PG recovery, and if so, > how are most determining if a small file is complete or just a partial > from the copy? all the WAL segments in the target WAL archive dir from your 'master' are complete. They don't get archived until the file is complete. The .backup file is from a pg_start_backup() which tells postgres where a file system backup was done > > 2) On the recovery side, the %p and %f values are giving me %p of > 'pg_xlog/RECOVERYXLOG' and %f of '0000000100000003000000B1'. Can I > confirm that this means my recover program should find a file named > '0000000100000003000000B1' in my backup system's WAL archive and copy it > to the file named 'pg_xlog/RECOVERYXLOG', or should I put it into > 'pg_xlog/0000000100000003000000B1'? Generally you want to push the files into a "source" WAL dir and specify this source path in the recovery.conf file - don't copy the files into pg_xlog or pg_xlog/RECOVERYLOG yourself, the engine will do it as needed > > 3) On the backup PG, is it correct that I have to remove backup_label, > postmaster.pid and pg_xlog if they are part of the or TAR backup from > the primary's PGDATA area? AFAIK you only need to remove the pid file and p_xlog/* > > Thanks, > David