Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C57FD2E006F for ; Mon, 26 Nov 2007 06:41:30 -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 29386-06 for ; Mon, 26 Nov 2007 06:41:26 -0400 (AST) Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id 915232E0050 for ; Mon, 26 Nov 2007 06:41:26 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id j3so911187ugf for ; Mon, 26 Nov 2007 02:41:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; bh=SUe3GDxZkqGTP/p74nLL53WDo2AmNcqf5bNdDAQxqZY=; b=p8BYhN+A52god3NzlqILe1+92Z+Oad2IVpClBubNw1hPkb+sLqg9dm655Jgpl3VjMiMCTcKx5fGnp+CHNKjTtSKS9j4sa7i57q2oOiWIJELSgpyZgAeapDCETArnE2YCldOAS5YYgJjYRpiN4inzewtUp6FmjX4x+v4HjfUTQys= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; b=x2H2OVxgjWTlnYOK5NBiYssyLGviO+/iERqHgrpCqxOU7Fzy+CVlKd83WnZEcpH9KfKjwx5hICCfXxwDupxnehjxK021GuVs0DTMiEnIqZaEMizYmxQnRB6bscugExEChJ7Tke7EgfIG6Kx5a3u5nrwRnV72Vqpj1Q+MzBIbukI= Received: by 10.78.170.6 with SMTP id s6mr2584780hue.1196073684833; Mon, 26 Nov 2007 02:41:24 -0800 (PST) Received: by 10.78.146.15 with HTTP; Mon, 26 Nov 2007 02:41:24 -0800 (PST) Message-ID: Date: Mon, 26 Nov 2007 10:41:24 +0000 From: "Peter Childs" Subject: Re: doubt with pg_dump and high concurrent used databases Cc: pgsql-performance@postgresql.org In-Reply-To: <4749F939.7090107@laotraesquina.com.ar> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6529_6251101.1196073684823" References: <4749A6ED.2010005@laotraesquina.com.ar> <9D744F5C-08B2-4C85-B06A-1A9FAD533AB6@myemma.com> <20568.1196019327@sss.pgh.pa.us> <4749F939.7090107@laotraesquina.com.ar> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/298 X-Sequence-Number: 27713 ------=_Part_6529_6251101.1196073684823 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 25/11/2007, Pablo Alcaraz wrote: > > 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 > > Given your long description over what you though was "constant" I thought it important that the answer yes but was given rather than just a plain yes. I've met quite a few apps that create inconstant databases when the database its self is actually consistent. Peter ------=_Part_6529_6251101.1196073684823 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 25/11/2007, Pablo Alcaraz <pabloa@laotraesquina.com.ar> wrote:
Tom Lane wrote:
> "Peter Childs" <peterachilds@gmail.com> writes:
>
>> On 25/11/2007, Erik Jones <erik@myemma.com > 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


Given your long description over what you though was "constant" I thought it important that the answer yes but was given rather than just a plain yes. I've met quite a few apps that create inconstant databases when  the database its self is actually consistent.

Peter
------=_Part_6529_6251101.1196073684823--