On Jun 18, 2008, at 7:07 AM, David wrote:
- Many foreign keys weren't enforced - Some fields needed special treatment (eg: should be unique, or behave like a foreign key ref, even if db schema doesn't specify it. In other cases they need to be updated during the migration). - Most auto-incrementing primary keys (and related foreign key references) needed to be updated during migration, because they are already used in the destination database for other records. - Many tables are undocumented, some fields have an unknown purpose - Some tables didn't have fields that can be used as a 'natural' key for the purpose of migration (eg: tables which only exist to link together other tables, or tables where there are duplicate records). I wrote a Python script (using SQLAlchemy and Elixir) to do the above for our databases. Are there any existing migration tools which could have helped with the above? (it would have required a *lot* of user help). Are there recommended ways of designing tables so that synchronization is easier? The main thing I've read about is ensuring that all records have a natural key of some kind, eg GUID. Also, your migration app needs to have rules for conflict resolution.
Well, it sounds like you've got a good list of what NOT to do. The first step is to make sure that you have a good database design, outside of replication considerations. Most tables should have natural unique keys; make sure you have FK's, documment things (see the COMMENT ON command), etc. If you have low data quality to start with, spreading that all over is just going to make things worse.
For the actual replication, there isn't really a multi-master solution for Postgres. Your best bet is to try and design the system so that you don't have conflicts (ie: if you have a bunch of branch offices, each one is responsible for their own data). You can then build something akin to multi-master using londiste and pgq.
-- Decibel!, aka Jim C. Nasby, Database Architect decibel@xxxxxxxxxxx Give your computer some brain candy! www.distributed.net Team #1828
<<attachment: smime.p7s>>
