Re: PITR Functional Design v2 for 7.5

>Hannu Krosing
> Josh Berkus kirjutas T, 09.03.2004 kell 19:46:
> > In my personal experience, the *primary* use of PITR is recovery
from
> User
> > Error. For example, with one SQL Server 7.0 installation for a law
> firm,
> > I've made use of PITR 4 times over the last 4 years: once was
because
> and HDD
> > failed, the other three were all becuase of IT dept. staff running
> > unconstrained UPDATE queries against the back end. For recovery
with
> > minimal loss of data, there are existing solutions, such as
replication
> > servers, in addition to PITR; for recovery from User Error, only
PITR
> will
> > suffice.
>
> Actually PostgreSQL used to have very good support for this until some
> time in Postgres95 development by supporting additional temporal
> qualifiers for queries. I.e. one could ask for 'SELECT SALARY FROM
> EMPLOYEES AS IT WAS AT YESTERDAY NOON' (the syntax was not exacly this
> :)

Shame we can't run cc -as was 4 years ago.
Or even better cc -as will be in 3 weeks; that would save me loads :)
!!

> It was a very simple and logical result of PostgreSQL's MVCC storage
and
> was supported by VACUUM allowing dropping only deleted tuples older
than
> some specified time.
>
> Oracle has recently added something similar (using their WAL's) to
ver.
> 9.0 or 10.x of their DBMS exactly for recovery from user errors.
>
> The support for this was dropped from postgreSQL citing performance
> reasons at that time, but I still hope that it can restored some time.

Flashback query is a new feature in Oracle 9i. I believe it is regarded
with some horror by the DBA community...

I get your idea though, though I think it is a different thing.

PITR is about the avoidance of risk, not really about fixing any
particular classes of problem. If you have PITR you can recover from
"all" problems, frequent or not, depending upon how carefully and for
how long you protect your backups.

Reading old MVCC copies won't take that away, though is a feature that
would be useful within a particular time window. It's also hard to tell
whether VACUUM has been run, and if so on which tables, since that will
also change the answer you get from those MVCC-usage type queries.

Oracle recognise this also. Flashback hasn't replaced backup/restore.
Neither has it prevented them from enhancing log miner.

Temporal support is a different issue anyway. It is up to you to come up
with a database design that supports being able to ask that question, if
that is a business requirement.

Best regards, Simon Riggs