On Mon, Dec 14, 2015 at 03:52:41PM +0000, Gene Poole wrote:
> I'm looking for some real world documentation on the use of ora2pg to move from Oracle DB 11gR2 64-bit to PostgreSQL
(i.e.What's the best pgsql version to use; Is it possible to use a exported copy of the Oracle DB to create a pgsql
imageof it; These kinds of questions).
>
The best version of Postgres to use is the latest stable version, if
you can. The second-best version of Postgres to use is the one you
already have in production :) If you're just doing development, it
might be worth working against 9.5. beta instead of 9.4.x, depending
on your deployment plans.
I have used ora2pg, but last time was a couple years ago so my
experience is stale. It did a pretty good job for me. You might find
that there are some things you end up wanting to modify. What I found
worked best for me was to pull the database into a schema to "stage"
with and then select from that. (When migrating, I often find it
better to look hard at the database schema at the same time. Data
types in Oracle and in Postgres are not perfect matches.)
Note that even if you end up modifying the underlying "real" schema,
you need not necessarily modify your application at the same time.
Postgres has updatable views and so on, so from the point of view of
the application, you can leave the database unchanged. I like to use
this feature, in fact, to do A/B testing on new versions of the
application: the old one gets app_schema_a and the new one
app_schema_b, so you can roll over gradually and change your schema
without a lot of outage hassle and so on.
> Does PGSQL function the same as Oracle when using Foreign Keys and Lookup Tables.
Generally, yes. There are some differences. There is an old page at
https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion that
outlines some of them. I don't think the grammar differences have
changed too much. The BLOB discussion is all obsolete.
> This is a POC to see if we can cut costs.
I'm sure you can! There are also some firms that can help with
migration if you like.
Best regards,
A
--
Andrew Sullivan
ajs@crankycanuck.ca
