Does anyone know of or can direct me to some documentation that shows how to migrate from Oracle DB to a PostgreSQL DB?

I can't give you specifics about how to do these things -- I'm not that familiar with Oracle -- but, as a general guide, this is what you will need to accomplish.

You need a data base schema. That's a text file that contains the specifications for the data base, every table, index, view and any other information that describes the data base's tables in detail. The schema should be in a text file, say dbname.sql where dbname is the name of the data base. If at all possible try to generate the schema in ANSI/ISO standard form; you'll most likely have better luck with that format.

You need (preferably) individual text files containing all the content of every table (one file for each table) named tabname.txt or tabname.unl. These should be in delimited form; the preferred delimiter is a vertical bar (|) or a tab (vertical bars don't appear in any western language which makes them good column delimiters). If possible, these files should have any leading or trailing blanks stripped out of strings (you don't want to load a bunch of space characters in your new data base -- takes up disk space). You should take a look at the data and make sure that there aren't any back slants in it; windows weenies seem to think that a slash and a back slant are the same thing: they're not (replace those with a slash or a space) they can play havoc sometimes and it's better to be safe than sorry. Also, if you use vertical bars as delimiters, replace any tab characters with space characters; those, too, can play havoc when you least expect them to.

Oracle's export utility most likely can't be used because it exports a binary image that can only be read by Oracle's import utility (and you won't be using that with PostgreSQL, eh). Check the documentation for your version of Oracle to see if export can be used. If I had to speculate, I'd look for a utility than can "print" the schema and "print" that to a file; same for table content.

Depending upon how big the Oracle data base is, and how complex the schema is, this could be a royal pain in the hiney -- expect to do a lot of editing to "correct" grammar and syntax. Spend some time trying to find a way to export the schema and table content in text format; there are a lot of Oracle experts out there in the world and one or two may be able to point you in the right direction.

Hope this helps some.

I'll give it a try as soon as I can gather all of the information you listed.

TIA,
Gene

You might also check out ora2pg, and pgadmin2 tools, which contain things to do exactly what you want. Some useful links are here:

http://archives.postgresql.org/pgsql...3/msg00613.php
http://wiki.kandalaya.org/cgi-bin/tw...leToPostgreSQL

And tronayne is right...this is going to be a huge pain. From my past experience, I'd suggest getting a complete database layout from the Oracle database, showing each table, and each field in the table. That'll tell you what field type and size you need to make. Also, export the Oracle database info to a dump file, before you begin ANYTHING. The dump can be used to rebuild your Postgres database to match Oracle, and rebuild Oracle, if things go very bad.

If you've got no DBA experience at all, and this is an important database, I'd suggest paying a consultant to do it. They could also give you a database health check, and maybe help you avoid some problems. Plus, if the database gets hosed, you'll have someone else to blame....