Replication Using Triggers

From: gordan(at)bobich(dot)net
To: pgsql-general(at)postgresql(dot)org
Subject: Replication Using Triggers
Date: 2008-01-18 15:21:17
Message-ID: alpine.LRH.1.00.0801181309560.19551@skynet.shatteredsilicon.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Is there any reason why PostgreSQL replication solutions are all add-on
3rd party ones? Is there any reason why replication couldn't be
implemented using triggers and a handful of stored procedures? This is
what I have in mind:

Have a plperl function that creates connections to all servers in the
cluster (replication partners), and issues the supplied write query to
them, possibly with a tag of some sort to indicated it is a replicated
query (to prevent circular replication).

Have a post execute trigger that calls the above replication function if
the query was issued directly (as opposed to replicated), and passes it
the query it just executed if it was successful.

If the replication failed on any node, the whole thing gets rolled back.

This would effectively give star topology synchronous replication with
very little effort, and no need for any external code. Am I missing
something obvious that would prevent this from working? It would give
replication capabilities better than MySQL's (which can only handle
ring based multi-master replication) for the sake of about 100 lines of
code. None of the required functionality required is new to PostgreSQL,
either.

Is there an existing implementation of this? Perhaps a perl program that
creates the required triggers and stored procedures from looking at a
schema?

Thanks.

Gordan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-01-18 15:31:14 Re: Forgot to dump old data before re-installing machine
Previous Message Dave Page 2008-01-18 14:16:45 Re: Forgot to dump old data before re-installing machine