Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 452E12E2DB9 for ; Wed, 16 Jan 2008 13:28:48 -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 71841-02 for ; Wed, 16 Jan 2008 13:28:44 -0400 (AST) Received: from mail2.yozons.com (mail2.yozons.com [63.251.168.97]) by postgresql.org (Postfix) with ESMTP id 2869F2E0065 for ; Wed, 16 Jan 2008 13:28:43 -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 m0GHSalG024890 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Wed, 16 Jan 2008 09:28:37 -0800 Message-ID: <478E3ECA.2010806@computer.org> Date: Wed, 16 Jan 2008 09:28:42 -0800 From: David Wall Reply-To: d.wall@computer.org User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 To: Brian Modra CC: pgsql-admin@postgresql.org Subject: Re: Backup of live database References: <5a9699850801152257g6ee9513i6c4fc88a5f20a785@mail.gmail.com> <478DAC1D.3010909@commandprompt.com> <5a9699850801152310g39fa9bf3y3926892145ab3dcf@mail.gmail.com> <478DBAC3.8050802@commandprompt.com> <5a9699850801160024y7abdcd2fjdb1035c2070797eb@mail.gmail.com> In-Reply-To: <5a9699850801160024y7abdcd2fjdb1035c2070797eb@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/186 X-Sequence-Number: 27960 Brian Modra wrote: > Sorry to be hammering this point, but I want to be totally sure its > OK, rather than 5 months down the line attempt to recover, and it fails... > > Are you absolutely certain that the tar backup of the file that > changed, is OK? (And that even if that file is huge, tar has managed > to save the file as it was before it was changed - otherwise I'm > afraid that the first part of the file is saved to tar, and then the > file is modified, and the last part of the file is saved to tar from > the point it was modified - and so therefore not consistent with the > first part... And therefore the file has lost its integrity, so even a > WAL restore won't help because the base files themselves are corrupt > in the tar file? Not sure if the answers you got answered your question or not. Here's my take: 1) If the database is not running, tar works fine. 2) If the database is running, you can ONLY use tar if you also use WAL archiving since the database will not only need the tar files, which will be inconsistent, but also the WAL files (in your $PGDATA/pg_xlog) in order to recover from those inconsistencies. I find this is best if you are creating a warm standby that is keeping a backup database in sync with a primary. 3) If the database is running, use pg_dump to create a consistent backup. 4) No matter what, as previously mentioned, you should test your backup procedures to ensure you can reliably restore. Good luck, David