Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B460364FCD5 for ; Wed, 27 Aug 2008 10:33:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 62927-09 for ; Wed, 27 Aug 2008 10:32:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mu-out-0910.google.com (mu-out-0910.google.com [209.85.134.188]) by postgresql.org (Postfix) with ESMTP id 2D0BE64FCBA for ; Wed, 27 Aug 2008 10:32:58 -0300 (ADT) Received: by mu-out-0910.google.com with SMTP id i2so2170569mue.3 for ; Wed, 27 Aug 2008 06:32:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=cCDgg9yB6fALA8RWLU+gQGJhXAadAUAW6MdNaHk0XSY=; b=dzyHUSWSxPMHgsQrZ1FxiI3XxA2eASE3vfwNxD5SPSpquJoDKSXKbyB89rFm8tDcV8 pHrX0R4DFGwx/dKXFhzG/Ctih9pGBOiyfPf8B3QI6rrZma4zHpzIHtBLdmMXeWdlJAge 7Vq7Ul78xcJ1yLyM79bAZFVGl6TZOdmTjZe0g= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=wE5HElWdOJM5b+Kk5dVPbeJyunK1pwqBafILC2SgTFAfj7NR2Y4yQjkaiI5ukCP8A1 80QtjOkUJBTL/yHBjNOpjFZCSyNqYj3fwpCwq5TNOAPSz6lzKW8QWEHvfca/FnKmW5n4 21imbMcOdwCdcCKGWTvRWAKuUM6oBPx4yVRmQ= Received: by 10.103.40.5 with SMTP id s5mr4641018muj.133.1219843976877; Wed, 27 Aug 2008 06:32:56 -0700 (PDT) Received: by 10.103.12.18 with HTTP; Wed, 27 Aug 2008 06:32:56 -0700 (PDT) Message-ID: Date: Wed, 27 Aug 2008 09:32:56 -0400 From: "Merlin Moncure" To: "Michael Nolan" Subject: Re: [GENERAL] PITR - base backup question Cc: "Richard Broersma" , "pgsql-general@postgresql.org" , pgsql-admin@postgresql.org In-Reply-To: <4abad0eb0808270618w785f34f1pcb2c8d9b062f2d42@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <396486430808261553k6687500q19f4117b6c71ae3f@mail.gmail.com> <396486430808261804l93855f1gf86d92541a80141@mail.gmail.com> <4abad0eb0808270618w785f34f1pcb2c8d9b062f2d42@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/194 X-Sequence-Number: 30039 On Wed, Aug 27, 2008 at 9:18 AM, Michael Nolan wrote: > I have what I have sometimes called a 'tepid spare' backup. Once a week I > copy the physical files over to another system (actually to two of them) and > every few hours I make sure the archived WAL log files are in sync (using > rsync.) I have a couple of comments...see below: > 3. Shut down the Postgresql server running on the backup server, if any > pg_ctl stop > (Use 'ps ax' to make sure the server is stopped.) probably pg_ctl -m fast stop or -m immediate...since we are overwriting it. > 5. Delete the entire contents of the /disk1/postgres/data directory tree. > MAKE ABSOLUTELY SURE YOU ARE IN THE /disk1/postgres/data directory! > > cd /disk1/postgres/data > pwd > rm -rf * why not just rm -rf /disk1/postgres/data? > 6. Restore the tar file for the low-level backup from the live server > > tar xvf /usr/local/pgsql/tardir/pg_part1.tar > > (This restore may take 2-3 hours) > > 7. Remove the PostgreSQL log file and the WAL files that were restored > from the tar archive I prefer to exclude WAL files from the original tar, and recreate the folders here (being careful to chown them to postgres account). Every little bit helps. > 12. Go to the directory where the WAL files have been archived on > the server and remove all files older than the file matching the > last .backup file. The fastest way to do this is as follows: > > cd /usr/local/pgsql/archivedir > ls -1 > files This is a nice touch. With a little bash-fu you could do a find | xargs rm and list/kill the files in one pass. In the standby setups I've done I usually script the whole process, a prep on the main and a startup on the standby. merlin