pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedArchival of Live database to Historical database
4+ messages / 3 participants
[nested] [flat]
* Archival of Live database to Historical database
@ 2001-01-29 20:00 Stef Telford <stef@Chronozon.dyndns.org>
0 siblings, 1 reply; 4+ messages in thread
From: Stef Telford @ 2001-01-29 20:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Archival of Live database to Historical database
@ 2001-01-30 09:09 Richard Huxton <dev@archonet.com>
parent: Stef Telford <stef@Chronozon.dyndns.org>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2001-01-30 09:09 UTC (permalink / raw)
To: Stef Telford <stef@Chronozon.dyndns.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Archival of Live database to Historical database
@ 2001-01-30 17:23 Stef Telford <stef@Chronozon.dyndns.org>
0 siblings, 0 replies; 4+ messages in thread
From: Stef Telford @ 2001-01-30 17:23 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Archival of Live database to Historical database
@ 2001-01-31 21:25 miss <miss@flex.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: miss @ 2001-01-31 21:25 UTC (permalink / raw)
To: pgsql-sql
Hello all
I have been watching with some interest the "problem" of not being able
to work with more than one database. My muse is aimed at creating an
incremental backup for a very low usage database - say several hundred
transactions per day
Back in my days of DOS about v3 I used to use the >> pipe which appends
to an existing file.
If the pgsql command 'COPY TO' was extended or a similar command created
to allow it, transactions could be written to a delimited text file/s
held on a dedicated drive.
For my requirements, such a facility, coupled with regular backups,
would give me all the security I require. It would also provide a
partial solution for Stef Telford. Such a solution would require
regular maintenance, however, the period depending on the rate of growth
of the files and the frequency of backups. Definitely for the smaller
system.
My knowledge of computer programming is very limited, so that I cannot
take any such idea any further.
regards
Max Wood
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-01-31 21:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-29 20:00 Archival of Live database to Historical database Stef Telford <stef@Chronozon.dyndns.org>
2001-01-30 09:09 ` Richard Huxton <dev@archonet.com>
2001-01-30 17:23 Re: Archival of Live database to Historical database Stef Telford <stef@Chronozon.dyndns.org>
2001-01-31 21:25 Archival of Live database to Historical database miss <miss@flex.com.au>
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