Re: Trigger Function and backup - Mailing list pgsql-general

On Mon, Jun 15, 2009 at 4:29 AM, Nishkarsh<nishkarsh_k@rediffmail.com> wrote:
> Hello every one,
>
> I am new to databases. I am using Postgres 8.2 (Migrating to 8.3.7 in few
> days) on windows platform.
>
> I had tried using Slony-I for replication and was not able to create a
> cluster.
>
> After struggling for some time i decide to implement a way around to take
> differential backup. As the tables getting changed were very less.
>
> Here is what i intend to do:
>
> - Write a trigger for each of the tables in concern
> - Some how write a function which can copy / execute the same query in
> another temp Db on the same physical system (I have no idea how to do that)
> - Take a backup of temp DB which will be the differential backup of DB (We
> need to clear temp db after backup)
>
> Am i going in the right direction?
> Is there any way i can implement it.
> Any help will be really of great help

Generating a full trigger based replication system on your own is
IMNSHO crazy.  Slony is the best solution to this problem (trigger
replication with postgres) that I know of, and is probably better than
any one person to come up with in a reasonable amount of time.
Probably, your best course of action if you need to get things running
right now is to give slony another go (why did you not succeed?).

Hand written trigger replication is ok if you need to copy, say, a
couple of tables or you have some other very specific requirement.  In
particular, copying an insert to a mirror database with trigger
function wrapping dblink is a snap (updates are more problematic, but
doable).  Of course, you need to figure out how to deal with schema
updates and other issues that plague replication systems such as
volatile data in cascading triggers (just to name one).  General
purpose trigger replication is a huge project...

It sounds to me that what you really want is the 'hot standby' feature
that unfortunately missed the cut for 8.4.  Hot standby is probably
the easiest way to mirror a database for purposes of read only
querying.  There are no triggers to worry about, just a few .conf
settings and some other setup to get going (more or less, it isn't
finalized yet).  So maybe, waiting for hot standby (or even, digging
up a hot standby patch and trying to apply it vs. 8.4 if your
adventurous) is the answer.

Another possibility is to look at statement level replication, like pgpool.

merlin