Replication

    I've been thinking of taking a shot at implementing replication between
distributed servers.  What I'm looking for at this point are gaping holes in
my reasoning/plans/etc.  I had planned on doing this in Python but if that
proves to be troublesome (from a performance standpoint) then I will code
the thing in C.  Though to be honest I'm not expecting that much trouble...
   What I'm *not* talking about here is running distributed queries
   Here's the replication system in a nutshell.
   1) Using a configuration file format yet to be determined, define values
which denote: 1) the databases to replicate, 2) the tables to be replicated
[possibly, primary key information as well], and 3) the servers to be
included in the 'conversation.'  This configuration file be a database on a
PostgreSQL server or a standard text file.  Other information in this
   2) Programatically add a column to represent the last time a row was
updated.  This column will be named pgr_time or some other long column name
which won't be easily confused with user columns.  If I read the
documentation correctly all dates and times are stored as Zulu (GT) time
correct?  This should take care of time zones and the like.  Further
direction (or a 'hey dummy, read this page') would be helpful here.
   3) Programatically create plpgsql triggers to update the PGR_TIME field
on all inserts, updates.  These triggers will call a plpgsql function to
update the field.  The naming scheme will be
<table_name>_replicate_update_function and
<table_name>_replicate_update_trigger.
   4) This program will be a stand-alone application which can be fired off
via cron or whatever scheduling application is used at the local site.
   5) If you run the applet from the command line a GUI will pop up
allowing you to change replication settings or whatever.  Running the applet
with any commandline will start the replication process.  Further
commandline processing can take place to only replicate with one server or
show the status of the last or current replication attempts.
   Replication, using this scheme anyway, is a simple matter of comparing a
few dates: 1) Give me a list of all rows which changed from the last
replication time.  Using these rows, build a list of rows on the local
server and start comparing dates stored in the remote/local PGR_TIME field.
Depending on how the rows compare to each other, update the local or remote
tables.
   Some kind of strategy will have to be developed to determine the depth
of the replication in the row itself.  Do I do full row replication or do I
do fine grained (column level) replication?  I'd prefer column level but
I'll probably start at row level with a "most current date/time stamp wins"
strategy as it's the easiest to get going.
   I can see some serious performance problems as tables get huge (we're
talking a ton of network chit-chat here).  How much is acceptable?
   I have a problem with #2 and #3 from the standpoint that I don't like
tools "automagically" carressing my data if I can help it.  That's when the
testing comes into play.  On the flip side, a feature will have to be put in
place to clean up a replica (remove all replication functions/fields/etc)
from a database.
   I havn't talked about security yet either...  Do I write code to handle
the system catalogs as well?  Or do I just stick to pushing data around?
   Any "you're insane, why don't you just run home to momma" comments?


               Damond