Re: Archival of Live database to Historical database

From: "Stef Telford" <stef@Chronozon.dyndns.org>


> Hello everyone,
> I have hit on a limit in my knowledge and i am looking for
> some guidance. Currently I have two seperate databases, one for
> live data, the other for historical data. The only difference really
> being that the historical data has a Serial in it so that the tables
> can keep more than one 'version history'.
>
> What i would like to do, is after my insert transaction to the
> live database, i would like the information also transferred to the
> historical one. Now. I can do this via perl (and i have been doing it
> this way) and using two database handles. This is rather clumsy and
> I know there must be a 'better' or more 'elegant' solution.

Not really (AFAIK) - this crops up fairly regularly but there's no way to do
a cross-database query.

You could use rules/triggers to set a "dirty" flag for each record that
needs copying - but it sounds like you're already doing that.

If you wanted things to be more "real-time" you could look at LISTEN/NOTIFY

- Richard Huxton