Re: Archival of Live database to Historical database

Richard wrote:
> > 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
> > 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.
>

After going through the mailing list archive, i can see
that yes, this is asked a lot and that no, there is no
real solution to it at present. a shame to be sure.

> 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

What i would ideally like to do, is have the live database have
a trigger setup after an insert, so that the data will also be
copied across using a function. However, if cross database
functions or triggers are not possible, then i cant do this and
will have to stick with the current scheme (two database handles).

Its not pretty, but it works. which is the main thing.

Can i ask the postgreSQL powers that be, how hard would it be to
have the ability to reference different databases on the same
machine ? I know it might make sense to have the two on seperate
machines, but that would require hostname resolution and other
silly things. All that is really needed is the ability to reference
another database on the SAME machine.

Of course, i can see this is a loaded gun. It would be very easy
to do some very nasty things and more than a few race conditions
spring to mind. Anyway, i look forward to getting screamed at for
such a silly preposterous idea ;)

regards,
Steff