One issue that needs to be handled with replication is the synchronization of
serial/sequence values, especially when these are used as primary/foreign
keys. Here's part of how I've seen this handled, for what it's worth to
anyone who might work on this. I'm sure there are better solutions, but its
food for thought.
There was the notion of a 'primary' db server and any number of 'secondary' db
servers. Replication would occur on the 'secondaries' by serially
hot-streaming a log of all successful state-changing queries (creates, drops,
inserts, updates, and deletes) to a replayer on each downstream 2ndary
server. The replayers would then re-execute those queries on that server.
Q: How would you keep track of where in the replay log you were if a server
went down, etc.? A: Each secondary dbserver had a table with a single record
that noted the filename and offset of the log it was currently processing.
The replayer would read the logs and update this table as it processed the
log.
Q: How were serials/sequences kept in sync? A: A special INSERT command was
created called 'SINSERT' (as in "Serial INSERT"). When the primary db server
saw this, it knew to log the query with the explicit value of the primary's
sequence/serial rather than allow the downstream secondaries to autogenerate
it.
Q: Did the databases ever get out of sync? A: Yes, occasionally. It was
not bulletproof. If things got way out of sync, a backup copy was put out on
all servers (with some data loss, which was acceptable).
Q: How did it handle transaction effects, serialization level, etc.? A: The
db had no transactions, so it didn't handle this at all. This is the hard
part of the problem in my view.
Cheers,
Ed Loehr