Received: from helpdesk.senet.com.au (helpdesk.senet.com.au [203.34.34.120]) by hub.org (8.9.3/8.9.3) with ESMTP id BAA75350 for ; Sat, 11 Sep 1999 01:13:39 -0400 (EDT) (envelope-from wayne@helpdesk.senet.com.au) Received: (from wayne@localhost) by helpdesk.senet.com.au (8.9.1/8.9.1) id OAA15799 for pgsql-sql@postgresql.org; Sat, 11 Sep 1999 14:43:34 +0930 (CST) From: Wayne Piekarski Message-Id: <199909110513.OAA15799@helpdesk.senet.com.au> Subject: Fast Backups With pg_dump To: pgsql-sql@postgresql.org Date: Sat, 11 Sep 1999 14:43:34 +0930 (CST) X-Mailer: ELM [version 2.4ME+ PL40 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit 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