Re: Differential backup

On Tue, 2010-04-27 at 17:28 +0200, Csaba Nagy wrote:
> Hi all,
>
> On Tue, 2010-04-27 at 11:07 -0400, Merlin Moncure wrote:
> > The block level case seems pretty much covered by the hot standby feature.
>
> One use case we would have is to dump only the changes from the last
> backup of a single table. This table takes 30% of the DB disk space, it
> is in the order of ~400GB, and it's only inserted, never updated, then
> after ~1 year the old entries are archived. There's ~10M new entries
> daily in this table. If the backup would be smart enough to only read
> the changed blocks (in this case only for newly inserted records), it
> would be a fairly big win...

The standard trick for this kind of table is having this table
partitioned by insertion date - this way you have two benefits:

1) you already know which table to backup (the latest, and maye one
before that if you just switche to new one)

2) archiving will be fast (copy full latest table away and the truncate
it) instead of slow (copy "old enough" records out, then do delete of
the same records, both ow which are quite slow, and you also need to do
vacuum after that, which is also slow on large tables)

This would actually be a good sample case for tracking "latest dml",
except that in this particular corner case you can arrange for this
yourself.

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training