Just a slick little tool for anyone who needs to export an Oracle database to PostgreSQL: Ora2Pg.
Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects to your Oracle database, scans it automatically, and extracts its structure or data. It then generates SQL scripts that you can load into your PostgreSQL database.
Ora2Pg consist of two parts: a Perl script (ora2pg) and a Perl module (Ora2Pg.pm). The only thing you have to modify is the configuration file, ora2pg.conf. You'l need to set the DSN to the Oracle database and optionally the name of a schema.
By default Ora2Pg exports to a file that you can load into PostgreSQL with the psql client, but you can also import directly into a PostgreSQL database by setting its DSN into the configuration file. With all configuration options of ora2pg.conf you have full control of what should be exported and how.
- Export full database schema (tables, views, sequences, indexes), with unique, primary, foreign key and check constraints.
- Export grants/privileges for users and groups.
- Export a table selection (by specifying the table names).
- Export Oracle schema to a PostgreSQL 7.3+ schema.
- Export predefined functions, triggers, procedures, packages and package bodies.
- Export range and list Oracle partition.
- Export full data following a WHERE clause.
- Export Oracle views as PG tables.
- Provides basic help for converting PLSQL code to PLPGSQL (may still need some tweaking).
- Works on any platform.
For more information on porting Oracle PL/SQL code to PostgreSQL PL/PGSQL, look at Converting from other Databases to PostgreSQL, in the Oracle section.
All in all, a darn handy little tool.