Обсуждение: Fast Backups With pg_dump

Hi,

Currently, I am running the pg_dump program every hour to produce backups
of the database, and then storing them in case something fails or I need
recover something.

However, the backups are quite large, and the CPU gets really thrashed a
lot doing the COPY as it has to make lots of function calls (as mentioned
in a previous email of mine to hackers) and I wanted to avoid this if
possible.

What would be really neat is if you could do something like, give me a
dump of all rows changed since the last backup, sortof like a diff file,
so I make a full backup at the start of the day, and then just store
deltas for the rest of the day. I know that postgres can keep track of
this kind of stuff because the MVCC allows it to make a consistent
snapshot of the dbms even while updates are occuring, so is this kind of
idea possible (or does it already exist?)

This would mean that the disks and CPU don't have to spend all their time
backing up data which never changes and just record down the little
differences. Then you could go backups like once every minute or
something, and even synchronise them against another database if you
wanted to? I would like to hear what others have to say about this.

thanks,
Wayne

------------------------------------------------------------------------------
Wayne Piekarski                               Tel:     (08) 8221 5221
Research & Development Manager                Fax:     (08) 8221 5220
SE Network Access Pty Ltd                     Mob:     0407 395 889
222 Grote Street                              Email:   wayne@senet.com.au
Adelaide SA 5000                              WWW:     http://www.senet.com.au

Wayne Piekarski wrote:
> 
> Currently, I am running the pg_dump program every hour to produce backups
> of the database, and then storing them in case something fails or I need
> recover something.
> 
> However, the backups are quite large, and the CPU gets really thrashed a
> lot doing the COPY as it has to make lots of function calls (as mentioned
> in a previous email of mine to hackers) and I wanted to avoid this if
> possible.
> 
> What would be really neat is if you could do something like, give me a
> dump of all rows changed since the last backup, sortof like a diff file,
> so I make a full backup at the start of the day, and then just store
> deltas for the rest of the day. I know that postgres can keep track of
> this kind of stuff because the MVCC allows it to make a consistent
> snapshot of the dbms even while updates are occuring, so is this kind of
> idea possible (or does it already exist?)

I implement transaction logging for 6.6 - you will be able
to do incremental backup by copying log file.

Vadim