Re: Replication Using Triggers

On Jan 18, 2008, at 9:21 AM, gordan(at)bobich(dot)net wrote:

> Hi,
>
> Is there any reason why PostgreSQL replication solutions are all
> add-on 3rd party ones?

Because no one solution would be appropriate for everyone. The core
team and contributors feel that their time is better spent on the
database itself rather than developing and maintaining multiple
different replication solutions and dealing with the support
thereof. What has been done is to add some extra hooks in 8.3 for
replication triggers that can help to specialize when/if a given
trigger fires.

> Is there any reason why replication couldn't be implemented using
> triggers and a handful of stored procedures?

That's usually how it's done. Well, plus some external user-land
application libraries.

> 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.

That sounds pretty brittle. Do you really want all progress in your
databases to stop if there is a network issue to a single server?

> 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?

What you've described here would be pretty simple to implement.
However, I think you've greatly underestimated the performance issues
involved. If you need to push data to multiple databases before each
transaction commits I think you'll find that pretty slow. That's why
most of the available third party solutions are asynchronous. The
biggest options are out there are Slony and Londiste (both master-
slave, asynchronous) and Bucardo (asynchronous, but supports both
master-master and master-slave) which, as you would have it, is
written in Perl.

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com