Re: Incremental backups, and backup history

On 19 Jun 2003 at 11:42, Antonios Christofides wrote:

> Hi,
>
> I have two backup questions, not much related to each other; here they
> are.
>
> First: With PostgreSQL, I can't do incremental backups. pg_dump will
> dump the entire database. Thus, if I want to keep daily backups on tape,
> I'm in trouble because I'll have to do a full backup every day, which
> may need several hours and several tapes.
>
> One workaround I'm thinking is to not store BLOBs in the database, but
> store them in the filesystem and store the filenames in the database
> instead. This needs some additional work (for example, a garbage
> collector to periodically delete unreferenced files), but will move a
> large amount of space from the database into the filesystem, which is
> capable of incremental backups. Only BLOBs, that is; for some tables
> that will have several tens of millions of small rows, I can't think of
> any workaround.

A cheaper solution in terms of efforts is to add a latest update timestamp to
each blob and dump with a custom SQL.

Incremental backups would be real nice. If you have real high turnover, I would
suggest you to look asynchornous replication solution which work based on WAL
sengmenting. That way you would backup only things that are changed.

HTH

Bye
Shridhar

--
Blore's Razor: Given a choice between two theories, take the one which is
funnier.