Given four instances of posgres, each with a database, each instance receiving new data, and desiring a data “merge” a la BDR or similar multiple database solutions, my team has been discussing the pros and cons of generating unique keys in each table.
1. create a unique “database” id for each database, add a column to all tables for this id value and combine that with a serial id
2. use guid from pg functions
3. create id ranges for each database, e.g. db1 gets 1 to 1M, db2 gets 1M+1 to 2M, and so on
All get us to the finish line, but each has its drawbacks.
Advice, cautionary tales, suggestions and such will be warmly received.
- Mark