Well, as a starting point, I'm using the following layout:

backupdir/0startschema.sql
backupdir/5tableA.sql
backupdir/5tableB.sql
backupdir/9endschema.sql

Basically, you have to seperate out the parts of the pgdump into one
file per table. The schema gets two parts, as pgdump creates the
tables at the head, and the constraints at the end.

The interesting aspect is, that these backups are rsync-friendly.

Which means, you should be able to use rsync backuptools like
duplicity to store incremental steps.

One thing is, that you should probably generate all these files by
splitting out the output of one pg_dump run => if not, you get
potentially inconsistent data.

Andreas

* lkv <lkv at defx.org> [061207 17:39]:
> Hi guys,
> 
> I'm looking at few ideas on how to backup
> postgresql data incrementally, using some sort of
> checkpoints. The first thing that came to mind is to
> use slony log shipping to produce checkpoints
> which I can reply later.
> (Since the databases already use slony for
> replication)
> 
> The reason of this is to escape from the need to
> take full fledged dumps which can consume quite
> some space when stored. Also the archival server
> would not be much useful if the dumps are zipped,
> so the requirements are: small in size; preferably
> plain text (say  sql queries).
> 
> So my plan is the following. Restart the shipping
> every night at say 3am and transfer the shipping log
> produced for the 24h period to a server where it can be
> stored on a archival storage system. The storage system
> in question is called Venti[1]. The fileserver itself
> will compress and store the logs in some namespace
> like so /backup/db/foo/ddmmyyyyy/checkpoint.
> Every week/month a new dump is taken in order to
> make the interval between checkpoints small when
> there's a need for the logs to be replayed.
> 
> Do you see this doable?
> 
> thanks in advance,
> Lou
> 
> 
> 
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general