Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C92F02E0113 for ; Sun, 25 Nov 2007 15:15:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 14648-05 for ; Sun, 25 Nov 2007 15:15:17 -0400 (AST) Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id 116B72E011B for ; Sun, 25 Nov 2007 15:15:18 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id j3so796277ugf for ; Sun, 25 Nov 2007 11:15:18 -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=QMRslcyCbc5g3PlG3avGhnWaQTqSHYV9h+S/DmPG50w=; b=PTfU/6zpar3/lQtn1JMfAbULIUjHECsb4gvaJvCeBXUpPREDOuJ2zYWm2uLmcqX1BYPubhaH+XW1J+39HCETNVX9TzPbVBrNTmmgpQjl9zeoUx4P7B5E3W00aWzSunSnXjDAfgeeQmHRcQAIq98tySterHrZpaf26J2GM9JVkD8= 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=YoX4AVD4UKRRDZyYG75/ng/fjyX5Dck5iOVt52KkZPVlP/VTGTllmWwHSuuA/nP6ZRrH9qp22Tmz09pXqJFo4kP4G57Nm7I3XU3+JSCPf0hucJWxhzkA5CX8Lo0VuN+0uqYTeA4S0ld4tSiarh8jQwHdXk8q5OO1EajwP1Yq6UA= Received: by 10.78.137.7 with SMTP id k7mr1930083hud.1196018117654; Sun, 25 Nov 2007 11:15:17 -0800 (PST) Received: by 10.78.146.15 with HTTP; Sun, 25 Nov 2007 11:15:17 -0800 (PST) Message-ID: Date: Sun, 25 Nov 2007 19:15:17 +0000 From: "Peter Childs" Subject: Re: doubt with pg_dump and high concurrent used databases Cc: pgsql-performance@postgresql.org In-Reply-To: <9D744F5C-08B2-4C85-B06A-1A9FAD533AB6@myemma.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4952_31294044.1196018117646" References: <4749A6ED.2010005@laotraesquina.com.ar> <9D744F5C-08B2-4C85-B06A-1A9FAD533AB6@myemma.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/294 X-Sequence-Number: 27709 ------=_Part_4952_31294044.1196018117646 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 25/11/2007, Erik Jones wrote: > > On Nov 25, 2007, at 10:46 AM, Pablo Alcaraz wrote: > > > Hi all, > > > > I read that pg_dump can run while the database is being used and makes > > "consistent backups". > > > > I have a huge and *heavy* selected, inserted and updated database. > > Currently I have a cron task that disconnect the database users, > > make a > > backup using pg_dump and put the database online again. The problem > > is, > > now there are too much information and everyday the database store > > more > > and more data, the backup process needs more and more time to run > > and I > > am thinking about to do the backup using a process that let me to > > do it > > with the minimal interruptions for the users. > > > > I do not need a last second backup. I could the a backup with "almost > > all" the data but I need the information on it to be coherent. For > > example, if the backup store information about an invoice it *must* to > > store both header and items invoice information. I could live if the > > backup does not store some invoices information when is ran, because > > they ll be backuped the next time the backup process run. But I can > > not > > store only a part of the invoices. That is I call a coherent backup. > > > > The best for me is that the cron tab does a concurrent backup with all > > the information until the time it starts to run while the clients are > > using the database. Example: if the cron launch the backup process at > > 12:30 AM, the backup moust be builded with all the information *until* > > 12:30AM. So if I need to restore it I get a database coherent with the > > same information like it was at 12:30AM. it does not matter if the > > process needs 4 hours to run. > > > > 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. pg_dump will show a constant state at the time when the backup was started. If your database was not "consistent" at that time you may have issues, But it will be constant from a database point of view ie foreign keys, primary keys, check constraints, triggers etc. It all depends what you mean by consistent. Peter. ------=_Part_4952_31294044.1196018117646 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 25/11/2007, Erik Jones <erik@myemma.com> wrote:
On Nov 25, 2007, at 10:46 AM, Pablo Alcaraz wrote:

> Hi all,
>
> I read that pg_dump can run while the database is being used and makes
> "consistent backups".
>
> I have a huge and *heavy* selected, inserted and updated database.
> Currently I have a cron task that disconnect the database users,
> make a
> backup using pg_dump and put the database online again. The problem
> is,
> now there are too much information and everyday the database store
> more
> and more data, the backup process needs more and more time to run
> and I
> am thinking about to do the backup using a process that let me to
> do it
> with the minimal interruptions for the users.
>
> I do not need a last second backup. I could the a backup with "almost
> all" the data but I need the information on it to be coherent. For
> example, if the backup store information about an invoice it *must* to
> store both header and items invoice information. I could live if the
> backup does not store some invoices information when is ran, because
> they ll be backuped the next time the backup process run. But I can
> not
> store only a part of the invoices. That is I call a coherent backup.
>
> The best for me is that the cron tab does a concurrent backup with all
> the information until the time it starts to run while the clients are
> using the database. Example: if the cron launch the backup process at
> 12:30 AM, the backup moust be builded with all the information *until*
> 12:30AM. So if I need to restore it I get a database coherent with the
> same information like it was at 12:30AM. it does not matter if the
> process needs 4 hours to run.
>
> 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. pg_dump will show a constant state at the time when the backup was started. If your database was not "consistent"  at that time you may have issues, But it will be constant  from a  database point of view ie foreign keys, primary keys, check constraints, triggers etc.

It all depends what you mean by consistent.

Peter.
------=_Part_4952_31294044.1196018117646--