X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id BF162D1DB79 for ; Sun, 7 Mar 2004 19:47:24 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 05408-02 for ; Sun, 7 Mar 2004 15:47:24 -0400 (AST) Received: from obelix.cdrbill.com (h246.162.213.151.ip.alltel.net [151.213.162.246]) by svr1.postgresql.org (Postfix) with ESMTP id 197C3D1CCC5 for ; Sun, 7 Mar 2004 15:47:22 -0400 (AST) Received: from ruprecht.org (asterix [192.168.0.6]) by obelix.cdrbill.com (8.12.8/8.12.8) with ESMTP id i27JlM4R003914 for ; Sun, 7 Mar 2004 14:47:22 -0500 Date: Sun, 7 Mar 2004 14:47:17 -0500 Subject: Re: How to do fast, reliable backups? Content-Type: text/plain; charset=ISO-8859-1; format=flowed Mime-Version: 1.0 (Apple Message framework v553) From: Chris Ruprecht To: pgsql-admin@postgresql.org Content-Transfer-Encoding: quoted-printable In-Reply-To: <200403061329.17799.silvanadimartino@tin.it> Message-Id: <3D0F473F-7070-11D8-ABCF-000393447280@ruprecht.org> X-Mailer: Apple Mail (2.553) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/111 X-Sequence-Number: 12680 On Saturday, Mar 6, 2004, at 08:39 US/Eastern, Silvana Di Martino wrote: > > Making a copy of your data directory while your server is running is > definitely a bad idea. Forget it. Backing up the file system used by a=20 > RDBMS > usually requires to put the server off-line and flush all the=20 > unwritten data > to the disk. This cannot be done in a 24/7 environment. > >> What I'd like to see is a transaction aware backup which backs up=20 >> indexes >> as well as data. > Well, at work we're using a commercial database called Progress. It has=20 a very nice backup utility which creates a backup file as follows: All current database blocks are backed up as they are at the time the=20 backup starts. Each DB block has a unique identifier. If a running=20 transactions wants to write to a block which has not jet been backed=20 up, the transaction is put on hold, the block is backed up and the=20 transaction can then continue. If you restore a database which contains blocks of incomplete=20 transactions, these transactions are backed out when you start the DB=20 server (broker). The DB can also (not done by default) maintain an after image log which=20 contains all transactions since the last backup. This log needs to be=20 kept on a drive, different from the DB. In case the DB drive crashes,=20 you can recover from your last backup and then roll forward the after=20 image log. Is there any slight possibility, some mechanism can make it into=20 Progresql at some stage (maybe version 8)? Best regards, Chris -- Chris Ruprecht Network Grunt and Bit Pusher extraordina=EDre