Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1B9912E0032 for ; Sun, 25 Nov 2007 18:37:42 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 49947-02 for ; Sun, 25 Nov 2007 18:37:39 -0400 (AST) Received: from linus.numail.org (mail.numail.org [216.130.152.7]) by postgresql.org (Postfix) with ESMTP id E9ADF2E26B1 for ; Sun, 25 Nov 2007 18:37:38 -0400 (AST) Received: (qmail 637 invoked by uid 0); 25 Nov 2007 22:37:36 -0000 Received: from 72.15.64.64 by linus.numail.org (envelope-from , uid 101) with qmail-scanner-1.23 (fsecure: 4.50/2111/fprot(2004-10-21)/avp(2004-10-21). Clear:RC:1(72.15.64.64):. Processed in 0.135512 secs); 25 Nov 2007 22:37:36 -0000 Received: from unknown (HELO ?24.25.1.100?) (72.15.64.64) by mail.numail.org with AES256-SHA encrypted SMTP; 25 Nov 2007 22:37:36 -0000 Message-ID: <4749F939.7090107@laotraesquina.com.ar> Date: Sun, 25 Nov 2007 17:37:45 -0500 From: Pablo Alcaraz User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: doubt with pg_dump and high concurrent used databases References: <4749A6ED.2010005@laotraesquina.com.ar> <9D744F5C-08B2-4C85-B06A-1A9FAD533AB6@myemma.com> <20568.1196019327@sss.pgh.pa.us> In-Reply-To: <20568.1196019327@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL=0.002, BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200711/296 X-Sequence-Number: 27711 Tom Lane wrote: > "Peter Childs" writes: > >> On 25/11/2007, Erik Jones wrote: >> >>>> Does the pg_dump create this kind of "consistent backups"? Or do I >>>> need to do the backups using another program? >>>> >>> Yes, that is exactly what pg_dump does. >>> >>> >> Yes so long as you are using transactions correctly. Ie doing a begin before >> each invoice and a commit afterwards if your not bothering and using auto >> commit you *may* have problems. >> > > I think you need to qualify that a bit more. What you're saying is that > if an application has consistency requirements that are momentarily > violated during multi-statement updates, and it fails to wrap such > updates into a single transaction, then pg_dump could capture one of the > intermediate states. That's true, but it's hardly pg_dump's fault. > If there were a system crash partway through such a sequence, the > consistency requirements would be violated afterwards, too. > > Agree. In my case I define "consistent database state" like the state the database has when the program that use it is stopped normally and without errors. In this "state" the program starts without troubles and "everything looks fine". I believe this behavior is because all the inserts and updates are made using transactions. Another things will be a bug, it ll be fixed and it ll not be pg_dump fault. So if pg_dump can capture a "consistent state" with all the data until the start time, without all the pending open transaction updates/inserts in the same way that I did when I stopped the program before start pg_dump, for me is usefull and enough to solve my problem. Thanks to all! Pablo