Re: [FOSSology] Combining databases from several servers
On Jul 27, 2009, at 11:40 AM, thomas.j.mur...@kodak.com wrote: I believe your 3rd suggestion is what I'm leaning toward --- I have asked each product team to do a pg_dump to archive the database would be nice to then 'import/combine' them into a central database Hi Tom, I was hoping to find a postgresql migration utility that allows me to do the import/combine. So far, no luck. The only suggestion that has come up is to change our primary/foreign keys to prefix a server ID. There are different ways we could implement this, but code/ schema changes are needed. If you haven't added any licenses or agents, and you only want license data (not data from pkgmetagetta or specagent), and you throw away all the job queue history. I think the only keys you need to update are for folders, pfile, upload, uploadtree, and users. Some possibilities: Is this something you (or any programmer) are interested in doing yourself? You could hire HP Services, or a fossology developer, or anyone to make the changes. You could punt and simply reanalyze the code on your master server. You could manually tweek the primary keys (need to set the top 3 bits to make unique keys for your 7 servers). Then a normal import might work. You could write a program to rewrite the primary keys. You could wait and we can try and get this into v 1.3 You do realize that in addition to the db, you have to copy over your repository? That's a simple rsync but maybe a lot of data. Any other ideas? Bob___ fossology mailing list fossology@fossology.org http://fossology.org/mailman/listinfo/fossology
Re: [FOSSology] Combining databases from several servers
Bob, Thanks for the suggestions I've tried the pg_restore to import to a 'empty' fossology database and it appears to work (data only and no repository file info only the analysis results) but it won't allow me to add another set of database data (most likely due to the primary key conflicts) I may try again to get the product teams to submit their source for centralized scanning/analysis but they were reluctant to ship their code (various reasons of security and control...). So I think a future feature to aggregate analysis data from several databases would be great. I'll consider the short term suggestions and maybe we can develop a solution thanks tom Bob Gobeille bob.gobei...@hp.com 07/29/2009 11:13 AM To thomas.j.mur...@kodak.com cc fossology@fossology.org fossology@fossology.org Subject Re: [FOSSology] Combining databases from several servers On Jul 27, 2009, at 11:40 AM, thomas.j.mur...@kodak.com wrote: I believe your 3rd suggestion is what I'm leaning toward --- I have asked each product team to do a pg_dump to archive the database would be nice to then 'import/combine' them into a central database Hi Tom, I was hoping to find a postgresql migration utility that allows me to do the import/combine. So far, no luck. The only suggestion that has come up is to change our primary/foreign keys to prefix a server ID. There are different ways we could implement this, but code/schema changes are needed. If you haven't added any licenses or agents, and you only want license data (not data from pkgmetagetta or specagent), and you throw away all the job queue history. I think the only keys you need to update are for folders, pfile, upload, uploadtree, and users. Some possibilities: Is this something you (or any programmer) are interested in doing yourself? You could hire HP Services, or a fossology developer, or anyone to make the changes. You could punt and simply reanalyze the code on your master server. You could manually tweek the primary keys (need to set the top 3 bits to make unique keys for your 7 servers). Then a normal import might work. You could write a program to rewrite the primary keys. You could wait and we can try and get this into v 1.3 You do realize that in addition to the db, you have to copy over your repository? That's a simple rsync but maybe a lot of data. Any other ideas? Bob ___ fossology mailing list fossology@fossology.org http://fossology.org/mailman/listinfo/fossology
[FOSSology] Combining databases from several servers
Hello all, Our company is attempting to scan/analyze all of our product software. Many locations throughout the world, some product teams are reluctant to use our central Fossology server so they have setup their own, So we have approximately 7 separate servers collecting OSS info. I'd like to aggregate all the various server data into one database so that it can be viewed via the Fossology UI (or other means).. any ideas on how to do this? Thanks, Tom Murray Senior Systems Engineer Eastman Kodak Company 2400 Mt. Read Blvd Rochester, NY 14650-3020 thomas.j.mur...@kodak.com ___ fossology mailing list fossology@fossology.org http://fossology.org/mailman/listinfo/fossology
Re: [FOSSology] Combining databases from several servers
On Jul 27, 2009, at 9:31 AM, thomas.j.mur...@kodak.com wrote: Hello all, Our company is attempting to scan/analyze all of our product software. Many locations throughout the world, some product teams are reluctant to use our central Fossology server so they have setup their own, So we have approximately 7 separate servers collecting OSS info. I'd like to aggregate all the various server data into one database so that it can be viewed via the Fossology UI (or other means).. any ideas on how to do this? Hello Thomas, There are three things to keep track of: 1) UI (this step is unnecessary if you do 3. below) the UI can handle a split repository, but not a split database. You could change each UI plugin to query the multiple databases and aggregate the results. 2) Split repository If each repository could be nfs mounted to each other, then Hosts.conf could specify them all. If any file in the DB can't be found in host/ repository 1, then it would look in host/repository 2, ... 3) Database (this is unnecessary if you do 1. above) Each site could share the same (replicated, or synched) database. The problem is how to combine the multiple databases without breaking referential integrity. I've asked the postgresql list for some help with this because it seems crazy that this hasn't already been addressed. Bob___ fossology mailing list fossology@fossology.org http://fossology.org/mailman/listinfo/fossology
Re: [FOSSology] Combining databases from several servers
Bob, Thanks for the suggestions I believe your 3rd suggestion is what I'm leaning toward --- I have asked each product team to do a pg_dump to archive the database would be nice to then 'import/combine' them into a central database Thanks, Tom Murray Senior Systems Engineer Eastman Kodak Company Bob Gobeille bob.gobei...@hp.com 07/27/2009 12:44 PM To thomas.j.mur...@kodak.com thomas.j.mur...@kodak.com cc fossology@fossology.org fossology@fossology.org Subject Re: [FOSSology] Combining databases from several servers On Jul 27, 2009, at 9:31 AM, thomas.j.mur...@kodak.com wrote: Hello all, Our company is attempting to scan/analyze all of our product software. Many locations throughout the world, some product teams are reluctant to use our central Fossology server so they have setup their own, So we have approximately 7 separate servers collecting OSS info. I'd like to aggregate all the various server data into one database so that it can be viewed via the Fossology UI (or other means).. any ideas on how to do this? Hello Thomas, There are three things to keep track of: 1) UI (this step is unnecessary if you do 3. below) the UI can handle a split repository, but not a split database. You could change each UI plugin to query the multiple databases and aggregate the results. 2) Split repository If each repository could be nfs mounted to each other, then Hosts.conf could specify them all. If any file in the DB can't be found in host/repository 1, then it would look in host/repository 2, ... 3) Database (this is unnecessary if you do 1. above) Each site could share the same (replicated, or synched) database. The problem is how to combine the multiple databases without breaking referential integrity. I've asked the postgresql list for some help with this because it seems crazy that this hasn't already been addressed. Bob ___ fossology mailing list fossology@fossology.org http://fossology.org/mailman/listinfo/fossology
