Re: How to use a trigger to write rows to a remote server

From: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
To: "Michael Dengler" <michael(dot)dengler(at)gmail(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: How to use a trigger to write rows to a remote server
Date: 2007-07-27 18:27:00
Message-ID: b42b73150707271127q7c42d09cuafb263eefe193b84@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On 7/18/07, Michael Dengler <michael(dot)dengler(at)gmail(dot)com> wrote:
> Hmm..I was hoping to avoid personal insults....
>
> Anyway, Nuts or not...what I am attempting is to simply have row from one
> table inserted into another servers DB I don't see it as replication
> because:

I think you took Tom's comments the wrong way. He is suggesting you
are nuts to attempt trigger based data transfer to remote server when
there are two clearly better options, slony and dblink. You took as a
personal insult which was just some frank (and frankly good) advice...

Slony is in fact the _solution_ to the problem of transferring data
between servers with triggers. If your tables are well designed and
you are reasonably proficient with stored procedures, and you
requirements of transfer are very specific and not extremely time
sensitive, a poll based system over dblink is also a good solution.

> a) The destination table will have a trigger that modifies the arriving data
> to fit its table scheme.
> b) It is not critical that the data be synchronous (ie a lost row on the
> destination DB is not a big deal)
> c) I see as more of a provision of data to the destination DB NOT A
> REPLICATION OF DATA.

based on this you may want to rig dblink/poll. 3rd option is pitr
shipping to warm standby, depending on your requirements.

> Essentially the remote server just wants to know when some record arrives at
> the source server and wants to know some of the info contained in the new
> record.
>
> And yes it may be that I know little about the myriad of problems involved
> with replication...but I do know how to carry on a civil, adult
> conversation....maybe we can have a knowledge exchange.

now that's a bit dramatic :-)

merlin

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Nis Jørgensen 2007-07-27 18:28:52 Re: Slow query with backwards index scan
Previous Message Tilmann Singer 2007-07-27 17:27:03 Slow query with backwards index scan