Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 2BFCD2E38E9 for ; Wed, 16 Jan 2008 11:19:19 -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 11914-01 for ; Wed, 16 Jan 2008 11:19:15 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 782E72E38E6 for ; Wed, 16 Jan 2008 11:19:15 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id m0GFJCti023636; Wed, 16 Jan 2008 10:19:13 -0500 (EST) To: Steve Holdoway cc: pgsql-admin@postgresql.org Subject: Re: Backup of live database In-reply-to: <20080116233030.c1adbcb1.steve.holdoway@firetrust.com> References: <5a9699850801152257g6ee9513i6c4fc88a5f20a785@mail.gmail.com> <478DAC1D.3010909@commandprompt.com> <5a9699850801152310g39fa9bf3y3926892145ab3dcf@mail.gmail.com> <478DBAC3.8050802@commandprompt.com> <5a9699850801160024y7abdcd2fjdb1035c2070797eb@mail.gmail.com> <20080116233030.c1adbcb1.steve.holdoway@firetrust.com> Comments: In-reply-to Steve Holdoway message dated "Wed, 16 Jan 2008 23:30:30 +1300" Date: Wed, 16 Jan 2008 10:19:12 -0500 Message-ID: <23635.1200496752@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/183 X-Sequence-Number: 27957 Steve Holdoway writes: > You can be absolutely certain that the tar backup of a file that's changed is a complete waste of time. Because it changed while you were copying it. That is, no doubt, the reasoning that prompted the gnu tar people to make it do what it does, but it has zero to do with reality for Postgres' usage in PITR base backups. What we care about is consistency on the page level: as long as each page of the backed-up file correctly represents *some* state of that page while the backup was in progress, everything is okay, because replay of the WAL log will correct any pages that are out-of-date, missing, or shouldn't be there at all. And Postgres always writes whole pages. So as long as write() and read() are atomic --- which is the case on all Unixen I know of --- everything works. (Thinks for a bit...) Actually I guess there's one extra assumption in there, which is that tar must issue its reads in multiples of our page size. But that doesn't seem like much of a stretch. regards, tom lane