Thread: pgSQL deployment
We're currently in the process of evaluating pgSQL to migrate from Oracle, which is our enterprise rdbms. Would you guys have any white paper or any documents that lists how big companies handled their deployment, e.g. (1) is it advisable to give training classes to application development team, if yes, how could we get these services and who should we contact?; (2) do you have a "cheat sheet" of all of the essential things needed to quickly port from Oracle to pgSQL?; (3) how would the information be disseminated to all of the other parties involved (i.e., QA, networking, operations)?; (4) what are the best practices for setting up the dev, qa test, and production environments; etc. I believe that small companies don't need to have such a grand plan to do the migration because there's normally not a lot of red tape to go through. I know this, coming from a start-up internet company before, where we setup everything ourselves and quickly rollout things to production. But that's not the case for me now. Maybe pgSQL commercial vendors would do this for a corporation for a fee but if you know of anything that's available that's posted for free, that'll be great! I went through the techdocs site and didn't find anything. Thanks so much! - Lily Anne
LSanchez@ameritrade.com wrote: > We're currently in the process of evaluating pgSQL to migrate from > Oracle, which is our enterprise rdbms. Good choice ;-) > (1) is it advisable to give training classes to application > development team, if yes, how could we get these services and who > should we contact?; I just dealt with this myself a week ago. I gave 2 days of training for the development team, QA, DBAs, and Unix sysadmin -- covering (rough outline): Obtaining source and binary distributions Installation File topology Where to find help Available GUIs Database creation Schema Data types Indexes Constraints Primary keys Foreign key references Inheritance Bulk import/export Views Functions User Defined Aggregates Table Functions Triggers Domains Rules Casts Operators Configuration Security Maintenance Upgrades Tuning We set up a bunch of old PCs that no one else wanted with Fedora Core 2 (very nice, BTW) so everyone could play along. Overall it went quite well, and our Oracle developers and DBAs were suitably impressed with Postgres by the time it was done. I don't know specifically who you should call, but here's a list of consulting companies from Techdocs: http://techdocs.postgresql.org/companies.php > (2) do you have a "cheat sheet" of all of the essential things needed > to quickly port from Oracle to pgSQL?; Nothing comprehensive, because every application will be a bit different. But here's a start: http://www.postgresql.org/docs/7.4/static/plpgsql-porting.html http://www.samse.fr/GPL/ora2pg/ora2pg.html#description http://sdm.openacs.org/doc/openacs/html/oracle-to-pg-porting.html http://gborg.postgresql.org/project/orapgsqlviews/projdisplay.php > (3) how would the information be disseminated to all of the other > parties involved (i.e., QA, networking, operations)?; See above -- I think training them at the same time is a good start. > (4) what are the best practices for setting up the dev, qa test, and > production environments; etc. I don't think Postgres is any different in this regard -- was there something specific you are worried about being different? HTH, Joe
Lily Anne, Did you contact any of the companies whose names I sent you? How did they respond? > Would you guys have any white paper or any documents that lists how big > companies handled their deployment, e.g. Nope. Would be nice to have, though. > (1) is it advisable to give > training classes to application development team, Yes. This is advisable for *any* technology migration of any kind. >if yes, how could we > get these services and who should we contact?; Contact the companies I mentioned. If they can't help you, e-mail me directly and I'll hook you up with a PostgreSQL contributor who does training. (2) do you have a "cheat > sheet" of all of the essential things needed to quickly port from Oracle > to pgSQL?; Nope. Another thing that would be terrific to have. Partly, this depends on what version of Oracle you're using and what Oracle features, middleware, and development style you have. For example: 1) What version of Oracle are you using? 2) What is your middleware platform? Interface code? 3) Are your database calls confined to well-defined libraries/functions or are they intermixed with other code? 4) How complex is the SQL you use? Could you give examples of a few "complex" queries from your application? 5) Are you using any of Oracle's "enterprise" features, such as table partitioning, clustering, query result caching, or replication? 6) Can your developers estimate how much non-standard-SQL they use, containing Oracle-specific syntax? Examples include "+" for outer joins and "IS CONNECTED BY". 7) How large is your database, in records in the main tables, and in GB? > (3) how would the information be disseminated to all of the > other parties involved (i.e., QA, networking, operations)?; (4) what are > the best practices for setting up the dev, qa test, and production > environments; etc. These things are no different regardless of what you're migrating to. Any systems analyst consultant should be able to tackle these issues. > Maybe pgSQL commercial vendors would do this for a corporation for a fee > but if you know of anything that's available that's posted for free, > that'll be great! Joe sent you some links. I don't think you'll get anything any more in=depth for free, not for any of the databases you're evaluating. If IBM or Microsoft are giving you migration plans for "free", it's becuase they plan to charge you $150,000 for licenses later on. -- -Josh Berkus Aglio Database Solutions San Francisco
On Thursday 20 May 2004 02:42 pm, LSanchez@ameritrade.com wrote: > We're currently in the process of evaluating pgSQL to migrate from > Oracle, which is our enterprise rdbms. > > Would you guys have any white paper or any documents that lists how big > companies handled their deployment, e.g. (1) is it advisable to give > training classes to application development team, if yes, how could we > get these services and who should we contact?; (2) do you have a "cheat > sheet" of all of the essential things needed to quickly port from Oracle > to pgSQL?; (3) how would the information be disseminated to all of the > other parties involved (i.e., QA, networking, operations)?; (4) what are > the best practices for setting up the dev, qa test, and production > environments; etc. > I think that answering your questions is where the money is in the database industry. There aren't any whitepapers because every company will be different. If you found whitepapers at Oracle, they are specific to the case at hand. Here are some guidelines I've picked up going from Sybase to Oracle, MySQL to PostgreSQL, and MySQL to Oracle. (I haven't had the chance yet to do Oracle to PostgreSQL.) Allow yourself plenty of time. Make the testing period longer than you think you need. Don't cut time out of testing if the project starts getting behind. Bring in some experts or appoint a few to become the experts. These guys will have to know every detail about PostgreSQL, and preferrably, Oracle as well. Don't fire your Oracle experts until Oracle is completely turned off. These experts will be the resource your engineers go to to get answers. They'll also lead classes and training sessions and code reviews. Oracle is not PostgreSQL. PostgreSQL is not Oracle. Investigate how the database schema should be designed to take advantage of PostgreSQL's features and avoid its weaknesses. You'll be pleasantly surprised as things that are difficult in Oracle are actually quite easy in PostgreSQL. You need real PostgreSQL experts to make sure the schema is good. Don't pull the plug on the Oracle database until the PostgreSQL database has been doing primetime for several weeks. You may want to set up some kind of replication system to keep the Oracle database up-to-date so that switching back will be easy. What I've seen is these projects go deep into development, then experience significant testing, and then hit primetime. Some bugs won't show up until primetime. Be ready to pull the plug and go back to testing or even development. Modularity of your database interface is a boon. Consider modularizing the database interface to all of your codebase if you haven't done so already. The perfect time is while your PostgreSQL experts are learning the system and while they are writing the new schema. You want something where the applications are all database agnostic. This will make flipping between the two databases that much easier. This modularity will also prepare you for PostgreSQL upgrades that may be significant. One thing I strongly suggest avoiding is the temptation to do new development or incorporate new technologies along with the migration. What always happens is you end up with a mess with fingers pointing everywhere. Do one thing at a time. Pace yourself. Leave plenty of time for testing. -- Jonathan Gardner jgardner@jonathangardner.net