Re: Trigger Function and backup

On Jun 16, 7:48 pm, nishkars(dot)(dot)(dot)(at)rediffmail(dot)com (Nishkarsh) wrote:
> Hi Merlin, thanks for the detailed input.
>
> As per ur suggestion i will try to implement Slony-I.
>
> I think i will need some help to do it.
>
> I am useing Postgres 8.3.7, on Windows.
>
> I was following the Slony-I example in the help for pgAdmin III. I am able
> to perform the steps from 1-7. Step 8 : create Slony-I cluster i am getting
> a msg in the interface
>
> "Slony-I creation script no available; only join possible"
>
> On doing some research i found some scripts to be copied (I was not able to
> find very clear instruction) or give slony-I path. i tried all that but was
> not able to move ahead.
>
> Can u plz guide me through &-(%-|
>
> Regards
> Nishkarsh
>
>
>
> Merlin Moncure-2 wrote:
>
> > On Mon, Jun 15, 2009 at 4:29 AM, Nishkarsh<nishkars(dot)(dot)(dot)(at)rediffmail(dot)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
>
> > --
> > Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> > To make changes to your subscription:
> >http://www.postgresql.org/mailpref/pgsql-general
>
> --
> View this message in context:http://www.nabble.com/Trigger-Function-and-backup-tp24030638p24051851...
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Hi Merlin,

you could also consider giving "rubyrep" a try.
Like Slony it is also an open source, trigger based, asynchronous
replication solution.

Focus of rubyrep is easy setup.
Tutorial, screencast and other information are available on the
project website:
http://www.rubyrep.org

Best Regards,
Arndt Lehmann

(Disclosure: I wrote rubyrep)