Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 221A7476087 for ; Tue, 8 Oct 2002 08:17:02 -0400 (EDT) Received: from mailhub5.isdnet.net (mailhub5.isdnet.net [195.154.209.25]) by postgresql.org (Postfix) with ESMTP id 58550476083 for ; Tue, 8 Oct 2002 08:17:00 -0400 (EDT) Received: from obelix (paris.seafrance.fr [195.154.84.54] (may be forged)) by mailhub5.isdnet.net (8.9.3/8.9.3) with SMTP id OAA98451 for ; Tue, 8 Oct 2002 14:17:02 +0200 (CEST) Message-Id: Date: Tue, 08 Oct 2002 14:17:47 +0200 From: "Erwan DUROSELLE" To: , Cc: Subject: Re: Hot Backup Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/441 X-Sequence-Number: 31439 MvO> http://www.postgresql.org/idocs/index.php?wal.html=20 - The URL you refer to is the ch11 I was refering to. It seems that this ch= apter is not as easily understandable as it should... It says that with WAL, "pg is able to garantee consistency in the case of a= crash". OK, but I think is about /consistency/. For what I understand, it just says that in the case of a core dump of a se= rver process (improbable) or a power cut (probable) or an unwanted kill -9 = (may happen), Pg will not have any corrupted table or index.=20 Cool, but not enough. As Timur pointed out, I was refering to a disk crash or total loss of a ser= ver. In this case, you loose up to 1 day of data. MvO> I've never lost any data with postgres, even if it's crashed, even wit= hout MvO> WAL. Your're a lucky guy, but Pg may not be the weakest part of your information= system. In my short DBA life ( 3 years, Oracle & MS SQL ), I have already seen twic= e the case when a WHOLE raid5 array was broken and all the data lost. One time, it was the controller which wrote ch... on the disks, the other t= ime, a power failure/peak/else crashed all of the 8 disks of the array. Both times, the incremental backup method reduced the data loss to almost n= othing. > There is a need in "incremental" backup, which backs up only those > transactions which has been fulfilled after last "full dump" or last > "incremental dump". These backups should be done quite painlessly - > just copy some part of WAL, and should be small enough (compared to > full dump), so they can be done each hour or even more frequently.. >=20 > I hope sometime PostgreSQL will support that. :-) So do I. I think this would be on top of my "missing features" list. As someone said, Replication may be a way to reduce the risks. E.D. ---------------------------------------------------------------------------= ---- Erwan DUROSELLE // SEAFRANCE DSI=20 Responsable Bases de Donn=E9es // Databases Manager Tel: +33 (0)1 55 31 59 70 // Fax: +33 (0)1 55 31 85 28 email: eduroselle@seafrance.fr ---------------------------------------------------------------------------= ---- >>> "Timur V. Irmatov" 08/10/2002 13:00 >>> Martijn! Tuesday, October 08, 2002, 3:45:13 PM, you wrote: >> - No, PostgreSQL does NOT provide a way to restore a database up to the >> last commited transaction, with a reapply of the WAL, as Oracle or SQL >> Server ( and others, I guess) do. That would be a VERY good feature. See >> Administrator's guide ch11 MvO> Umm, I thought the whole point of WAL was that if the database crashed= , the MvO> WAL would provide the info to replay to the last committed transaction. MvO> ... because we know that in the event of a crash we will be able to re= cover MvO> the database using the log: ... MvO> These docs seem to corrobrate this. >> So, with Pg, if you backup your db every night with pg_dump, and your >> server crashes during the day, you will loose up to one day of work. MvO> I've never lost any data with postgres, even if it's crashed, even wit= hout MvO> WAL. Suppose you made your nightly backup, and then after a day of work the building where your server is located disappears in flames.. That's it - you lost one day of work (of course, if your dumps where stored outside that building otherwise you lost everything).. There is a need in "incremental" backup, which backs up only those transactions which has been fulfilled after last "full dump" or last "incremental dump". These backups should be done quite painlessly - just copy some part of WAL, and should be small enough (compared to full dump), so they can be done each hour or even more frequently.. I hope sometime PostgreSQL will support that. :-) Sincerely Yours, Timur mailto:itvthor@sdf.lonestar.org=20 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org