Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id CE11D9FA433 for ; Wed, 13 Dec 2006 07:22:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 49737-04 for ; Wed, 13 Dec 2006 07:22:39 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.235]) by postgresql.org (Postfix) with ESMTP id 4A7E69FA363 for ; Wed, 13 Dec 2006 07:22:43 -0400 (AST) Received: by nz-out-0506.google.com with SMTP id i11so61551nzh for ; Wed, 13 Dec 2006 03:22:42 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=lw/u+GcDnCUACLN00SWg7HMlYdTgdtt/S9yLtxNqJILbuTwLa5Mti9iGmmX1KmlYbKkhOE2HWeK2zCNt8i2I+n3OzQew92K2RLK4A6RJImSmXzn7UOY8oNsfcul3BJ4sYzah+8jkqiLa5BHs0HCcyzlZItQnH+HpCWsEg+EpEyc= Received: by 10.64.148.8 with SMTP id v8mr1287101qbd.1166008961970; Wed, 13 Dec 2006 03:22:41 -0800 (PST) Received: by 10.65.206.17 with HTTP; Wed, 13 Dec 2006 03:22:41 -0800 (PST) Message-ID: Date: Wed, 13 Dec 2006 16:22:41 +0500 From: "Shoaib Mir" To: "Eduardo J. Ortega" Subject: Re: Backup Cc: pgsql-admin@postgresql.org In-Reply-To: <200612130551.12935.ejortegau@cable.net.co> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_92041_13680251.1166008961928" References: <200612122343.54504.ejortegau@cable.net.co> <200612130551.12935.ejortegau@cable.net.co> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/181 X-Sequence-Number: 23973 ------=_Part_92041_13680251.1166008961928 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline >> In addition, the backup is performed only everytime the WAL file is filled; i need to take >>consistent backups every hour or so You have the archive_timeout ( http://www.postgresql.org/docs/8.2/static/runtime-config-wal.html) option for that in PG 8.2 to achieve similar functionality >>Well, I don't really like the fact that admin has to specify the archiving and >>restoring command; an error here (a lack of -i in cp, for example) may render >>the backup useless You dont have to take the base backup again and again, just will be needing the WAL archives to restore to a certain point, but if still you want to take the backup you can easily write a cron job and schedule it. >>If this is it, then I'll end up with an old level zero (i.e. full, base ) >>backup and A LOT of level 1 (i.e. transaction log) backup. I think it should >>be more like it's for Informix, where you ask the database to perform an >>online level 0 backup (base); after this, it stores transaction logs on disk, >>which you can archive with level 1 backup. I think that can be also possible using something similar to warm standby ( http://www.postgresql.org/docs/8.2/static/warm-standby.html) Hope this helps.... ---------------- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 12/13/06, Eduardo J. Ortega wrote: > > Hi: > > Well, I don't really like the fact that admin has to specify the archiving > and > restoring command; an error here (a lack of -i in cp, for example) may > render > the backup useless. In addition, the backup is performed only everytime > the > WAL file is filled; i need to take consistent backups every hour or so, > and I > am not sure if that time represents more or less than a WAL file ( i could > still measure that, i guess). Finally, as I understand, the WAL backup > method > works like this: > 1) Take full base FS backup > 2) get some way to copy WAL files > > If this is it, then I'll end up with an old level zero (i.e. full, base ) > backup and A LOT of level 1 (i.e. transaction log) backup. I think it > should > be more like it's for Informix, where you ask the database to perform an > online level 0 backup (base); after this, it stores transaction logs on > disk, > which you can archive with level 1 backup. Then, say everyonce in a week, > you > get another level 0 backup, and the database clears the already-archived > logs > from disk and starts all over. > > I guess this could be achieved with PG, but it requieres considerably more > steps (pg_start_backup, pg_stop_backup, manually cleaning old log files > which > could be an error point), or I am getting something wrong. Besides, why do > you need to tell the database to stop the backup? shouldn't it stop by > itself > when there's no more information to be archived? > > Perhaps if any of you has this method working, you could provide me with > your > archiving and restoring commands as well as a description of the whole > process the way you implemented it. > > Thanks, > > Eduardo. > > On Wednesday 13 December 2006 02:39, Shoaib Mir wrote: > > To me PITR looks like a very nice solution for incremental backup and > even > > they can serve as a warm standby. What exactly are the complications you > > see with WAL archiving? > > > > --------------- > > Shoaib Mir > > EnterpriseDB (www.enterprisedb.com) > > > > On 12/13/06, Eduardo J. Ortega wrote: > > > Hi there: > > > > > > Are there any nice (official or third party) backup utilities for > > > postgres? I > > > have a database which is several GB, so pg_dumping it to file and then > > > bzipping2 every hour is not really the way to go. I've read a little > > > about using WAL for incremental backup, but it sounds a little > > > complicated and *very* user-error prone. > > > > > > (Not sure if this is the right place for it, but i really think that > PG's > > > developers should consider including a viable backup utility in the > next > > > version) > > > > > > thanks. > > > > > > -- > > > Eduardo J. Ortega - Linux user #222873 > > > "No fake - I'm a big fan of konqueror, and I use it for everything." > -- > > > Linus > > > Torvalds > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > TIP 2: Don't 'kill -9' the postmaster > > -- > Eduardo J. Ortega - Linux user #222873 > "No fake - I'm a big fan of konqueror, and I use it for everything." -- > Linus > Torvalds > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ------=_Part_92041_13680251.1166008961928 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline >> In addition, the backup is performed only everytime the WAL file is filled; i need to take >>consistent backups every hour or so

You have the archive_timeout ( http://www.postgresql.org/docs/8.2/static/runtime-config-wal.html) option for that in PG 8.2 to achieve similar functionality

>>Well, I don't really like the fact that admin has to specify the archiving and
>>restoring command; an error here (a lack of -i in cp, for example) may render
>>the backup useless

You dont have to take the base backup again and again, just will be needing the WAL archives to restore to a certain point, but if still you want to take the backup you can easily write a cron job and schedule it.

>>If this is it, then I'll end up with an old level zero (i.e. full, base )
>>backup and A LOT of level 1 (i.e. transaction log) backup. I think it should
>>be more like it's for Informix, where you ask the database to perform an
>>online level 0 backup (base); after this, it stores transaction logs on disk,
>>which you can archive with level 1 backup.

I think that can be also possible using something similar to warm standby ( http://www.postgresql.org/docs/8.2/static/warm-standby.html)

Hope this helps....

----------------
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)


On 12/13/06, Eduardo J. Ortega <ejortegau@cable.net.co> wrote:
Hi:

Well, I don't really like the fact that admin has to specify the archiving and
restoring command; an error here (a lack of -i in cp, for example) may render
the backup useless. In addition, the backup is performed only everytime the
WAL file is filled; i need to take consistent backups every hour or so, and I
am not sure if that time represents more or less than a WAL file ( i could
still measure that, i guess). Finally, as I understand, the WAL backup method
works like this:
1) Take full base FS backup
2) get some way to copy WAL files

If this is it, then I'll end up with an old level zero (i.e. full, base )
backup and A LOT of level 1 (i.e. transaction log) backup. I think it should
be more like it's for Informix, where you ask the database to perform an
online level 0 backup (base); after this, it stores transaction logs on disk,
which you can archive with level 1 backup. Then, say everyonce in a week, you
get another level 0 backup, and the database clears the already-archived logs
from disk and starts all over.

I guess this could be achieved with PG, but it requieres considerably more
steps (pg_start_backup, pg_stop_backup, manually cleaning old log files which
could be an error point), or I am getting something wrong. Besides, why do
you need to tell the database to stop the backup? shouldn't it stop by itself
when there's no more information to be archived?

Perhaps if any of you has this method working, you could provide me with your
archiving and restoring commands as well as a description of the whole
process the way you implemented it.

Thanks,

Eduardo.

On Wednesday 13 December 2006 02:39, Shoaib Mir wrote:
> To me PITR looks like a very nice solution for incremental backup and even
> they can serve as a warm standby. What exactly are the complications you
> see with WAL archiving?
>
> ---------------
> Shoaib Mir
> EnterpriseDB ( www.enterprisedb.com)
>
> On 12/13/06, Eduardo J. Ortega <ejortegau@cable.net.co> wrote:
> > Hi there:
> >
> > Are there any nice (official or third party) backup utilities for
> > postgres? I
> > have a database which is several GB, so pg_dumping it to file and then
> > bzipping2 every hour is not really the way to go. I've read a little
> > about using WAL for incremental backup, but it sounds a little
> > complicated and *very* user-error prone.
> >
> > (Not sure if this is the right place for it, but i really think that PG's
> > developers should consider including a viable backup utility in the next
> > version)
> >
> > thanks.
> >
> > --
> > Eduardo J. Ortega - Linux user #222873
> > "No fake - I'm a big fan of konqueror, and I use it for everything." --
> > Linus
> > Torvalds
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster

--
Eduardo J. Ortega - Linux user #222873
"No fake - I'm a big fan of konqueror, and I use it for everything." -- Linus
Torvalds

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

------=_Part_92041_13680251.1166008961928--