Re: Backup

>> 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(at)cable(dot)net(dot)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(at)cable(dot)net(dot)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
>