Re: [GENERAL] combining db's- importing primary keys
Chris wrote: Bob Gobeille wrote: On Jul 29, 2009, at 8:26 AM, nha wrote: Hello, Le 29/07/09 15:02, Jasen Betts a écrit : On 2009-07-27, Bob Gobeille wrote: I would like to combine multiple databases (same schema) into one master db. Does anyone know how I can reconcile all my primary and foreign keys to maintain referential integrity. prefix them all with something that's unique to their source database. easy to say, hard to do. A process (and solution) may be different whether: -the master db will replace all other databases (ie. data manipulation would directly perform on data managed by master db); -or the master db will reflect a combined view of data hosted by all other databases (ie. data manipulation would still perform on distributed databases and master db would play a role of federated view); -or both (data manipulation have to be manageable at the both side: on master db and on distributed db). In my immediate case, I'm simply combining 7 global databases into a single master. Eventually, I would like to allow our (fossology.org) users to subscribe to data in other databases. That is, allow users to do periodic one-way batch updates from other servers. Have you thought about schemas? You can set up different users with different search paths so all the data's in one spot, and getting to another schema is easy enough. No changes necessary to the data itself, just the calling code. Blah, ignore - I didn't read your first email properly :P -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] combining db's- importing primary keys
Bob Gobeille wrote: On Jul 29, 2009, at 8:26 AM, nha wrote: Hello, Le 29/07/09 15:02, Jasen Betts a écrit : On 2009-07-27, Bob Gobeille wrote: I would like to combine multiple databases (same schema) into one master db. Does anyone know how I can reconcile all my primary and foreign keys to maintain referential integrity. prefix them all with something that's unique to their source database. easy to say, hard to do. A process (and solution) may be different whether: -the master db will replace all other databases (ie. data manipulation would directly perform on data managed by master db); -or the master db will reflect a combined view of data hosted by all other databases (ie. data manipulation would still perform on distributed databases and master db would play a role of federated view); -or both (data manipulation have to be manageable at the both side: on master db and on distributed db). In my immediate case, I'm simply combining 7 global databases into a single master. Eventually, I would like to allow our (fossology.org) users to subscribe to data in other databases. That is, allow users to do periodic one-way batch updates from other servers. Have you thought about schemas? You can set up different users with different search paths so all the data's in one spot, and getting to another schema is easy enough. No changes necessary to the data itself, just the calling code. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] combining db's- importing primary keys
Hello, Le 29/07/09 16:44, Bob Gobeille a écrit : > > On Jul 29, 2009, at 8:26 AM, nha wrote: > >> Hello, >> >> Le 29/07/09 15:02, Jasen Betts a écrit : >>> On 2009-07-27, Bob Gobeille wrote: I would like to combine multiple databases (same schema) into one master db. Does anyone know how I can reconcile all my primary and foreign keys to maintain referential integrity. >>> >>> prefix them all with something that's unique to their source database. >>> >>> easy to say, hard to do. >>> >> >> A process (and solution) may be different whether: [...cases...] > > In my immediate case, I'm simply combining 7 global databases into a > single master. > > Eventually, I would like to allow our (fossology.org) users to subscribe > to data in other databases. That is, allow > users to do periodic one-way batch updates from other servers. > > Bob In such a purpose (where asynchronous updates seem reasonable), prefixing (as suggested by Jasen Bett) or adding server-based column to current keys (as you mention thereafter) look like handy solutions. Good plan. Regards. -- nha / Lyon / France. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] combining db's- importing primary keys
On Jul 29, 2009, at 8:26 AM, nha wrote: Hello, Le 29/07/09 15:02, Jasen Betts a écrit : On 2009-07-27, Bob Gobeille wrote: I would like to combine multiple databases (same schema) into one master db. Does anyone know how I can reconcile all my primary and foreign keys to maintain referential integrity. prefix them all with something that's unique to their source database. easy to say, hard to do. A process (and solution) may be different whether: -the master db will replace all other databases (ie. data manipulation would directly perform on data managed by master db); -or the master db will reflect a combined view of data hosted by all other databases (ie. data manipulation would still perform on distributed databases and master db would play a role of federated view); -or both (data manipulation have to be manageable at the both side: on master db and on distributed db). In my immediate case, I'm simply combining 7 global databases into a single master. Eventually, I would like to allow our (fossology.org) users to subscribe to data in other databases. That is, allow users to do periodic one-way batch updates from other servers. Bob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] combining db's- importing primary keys
Hello, Le 29/07/09 15:02, Jasen Betts a écrit : > On 2009-07-27, Bob Gobeille wrote: >> I would like to combine multiple databases (same schema) into one >> master db. Does anyone know how I can reconcile all my primary and >> foreign keys to maintain referential integrity. > > prefix them all with something that's unique to their source database. > > easy to say, hard to do. > A process (and solution) may be different whether: -the master db will replace all other databases (ie. data manipulation would directly perform on data managed by master db); -or the master db will reflect a combined view of data hosted by all other databases (ie. data manipulation would still perform on distributed databases and master db would play a role of federated view); -or both (data manipulation have to be manageable at the both side: on master db and on distributed db). Regards. -- nha / Lyon / France. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] combining db's- importing primary keys
On Jul 29, 2009, at 7:02 AM, Jasen Betts wrote: On 2009-07-27, Bob Gobeille wrote: I would like to combine multiple databases (same schema) into one master db. Does anyone know how I can reconcile all my primary and foreign keys to maintain referential integrity. prefix them all with something that's unique to their source database. easy to say, hard to do. I can't believe this didn't occur to me. All my primary keys are bigint's, but I could add a second field, a unique server number or name, and change my primary (and foreign) keys to the two column combination. This would make it easy for new db's but I need to give some thought to see if there will be any hiccups migrating all the db's. Many thanks Jasen. Bob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] combining db's- importing primary keys
On 2009-07-27, Bob Gobeille wrote: > I would like to combine multiple databases (same schema) into one > master db. Does anyone know how I can reconcile all my primary and > foreign keys to maintain referential integrity. prefix them all with something that's unique to their source database. easy to say, hard to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
