Colorful Cables

As a longtime fan of PostgreSQL, I found Jeremy Baumgartner's Sneak Preview of Pervasive Postgres 8 ("Database Wars," April 14) very exciting. Our company still uses version 7.4, but we look forward to upgrading, especially in light of Postgres' new point-in-time recovery feature.

In one test, Baumgartner demonstrated that Postgres 8 can resume and complete a transaction--specifically, the running of a program that continuously inserts serial numbers--following a server crash. But point-in-time recovery offers even greater capability.

For example, let's say you've accidentally run that serial program one too many times, creating database updates you no longer want. With point-in-time recovery, you can restore your database using the most current backup, then apply all the transactions that took place up to a given point in time just before you ran the serial program. This will restore your database not only up to the point of your last backup, but also to the point in time just before the serial program ran by mistake. The same thing will work if you need to recover a table you inadvertently dropped, yet don't want to lose the data updates that took place after your last full backup.

There's another advantage to point-in-time recovery: Rather than having to do a full database backup all the time, you can do it once and then just back up the point-in-time transaction logs, much like a partial backup in which only the changes are replicated. This way, even if you had a total disk failure, you could restore the database using the most current full backup, then apply all the transactions in the point-in-time logs to recover your database to a point further along than the full backup. Of course, it's still a good idea to do full backups as regularly as possible to avoid the headaches and time of applying all those point-in-time transactions.