pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stef Telford <stef@Chronozon.dyndns.org>
To: Richard Huxton <dev@archonet.com>
To: pgsql-sql@postgresql.org
Subject: Re: Archival of Live database to Historical database
Date: Tue, 30 Jan 2001 12:23:01 -0500 (EST)
Message-ID: <200101301723.f0UHN1109580@oberon.hades> (raw)

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



view thread (4+ messages)  latest in thread

Message-ID: <200101301723.f0UHN1109580@oberon.hades>
Permalink:  ../200101301723.f0UHN1109580@oberon.hades/
Also on:    postgresql.org/message-id/200101301723.f0UHN1109580@oberon.hades

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: stef@Chronozon.dyndns.org, dev@archonet.com
  Subject: Re: Archival of Live database to Historical database
  In-Reply-To: <200101301723.f0UHN1109580@oberon.hades>
  References: <200101301723.f0UHN1109580@oberon.hades>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox