Re: Hot Backup

MvO> http://www.postgresql.org/idocs/index.php?wal.html
- The URL you refer to is the ch11 I was refering to. It seems that this chapter 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 server process (improbable) or a power cut (probable) or an unwanted kill -9 (may happen), Pg will not have any corrupted table or index.

Cool, but not enough.

As Timur pointed out, I was refering to a disk crash or total loss of a server.
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 without
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 twice 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 time, 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 nothing.

> 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. :-)

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
Responsable Bases de Données // Databases Manager
Tel: +33 (0)1 55 31 59 70 // Fax: +33 (0)1 55 31 85 28
email: eduroselle(at)seafrance(dot)fr
-------------------------------------------------------------------------------

>>> "Timur V. Irmatov" <itvthor(at)sdf(dot)lonestar(dot)org> 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 recover
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 without
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(at)sdf(dot)lonestar(dot)org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org