Re: pgSQL deployment

On Thursday 20 May 2004 02:42 pm, LSanchez(at)ameritrade(dot)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(at)jonathangardner(dot)net