On Jan 18, 2008, at 9:21 AM, gordan@xxxxxxxxxx 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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
