Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group