Thread: Archival of Live database to Historical database

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.
So i stumbled onto triggers and functions. All well and good.
I create the trigger to fire off the procedure after a succesful insert
into
the table (And yes i do have triggers on the 30 tables or so i use). 
The problem arises, in the procedure. I dont know the syntax
to reference another database. I assume there must be someway to 
simply say (in a function) copy the data inserted into this database as
well.
If it helps any, the tables are the same name, and all the same
fields (Apart from the SERIAL in the historical version, but since that
auto
increments i wouldnt have to worry about it)
I am interested on ideas, code and pointers as to if this is a
good idea or not. thank you.
Regards,Steff

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

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