Replication - Mailing list pgsql-hackers
From | Damond Walker |
---|---|
Subject | Replication |
Date | |
Msg-id | 000f01bf4dd7$42008160$b263a8c0@vmware98.walkers.org Whole thread Raw |
List | pgsql-hackers |
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
pgsql-hackers by date: