24.2 Migrating from Oracle to PostgreSQL

In professional IT environments, databases such as DB2 and Oracle are very popular. Because of the broad variety of their functions and features, DB2 and Oracle have become widespread, and many companies around the globe are relying on DB2 and Oracle databases. There are several reasons why it can make sense to switch to PostgreSQL. One big advantage of PostgreSQL is that it is one of the most flexible databases available. It is a comparatively easy task to write extensions and to add new features to the core of the database. Because PostgreSQL supports a broad range of embedded languages, it is an easy task to use code that has already been written inside your database without having to make major changes to the code. Reusability of code is an important issue, and it can be a real advantage to make your code run as embedded software. PostgreSQL is a real open source database and this will help you when you need precise information about the internals of the database. Of course, it is not possible to use PostgreSQL for every application on earth, but you should keep in mind that the database can be administered easily and this will help you to reduce the total costs of ownership of your database environment.

In this section you will see how data can be migrated from Oracle to PostgreSQL. In most cases all data can be ported and you can use PostgreSQL as an alternative database platform.

PostgreSQL and Oracle are not totally different. Both databases support a major part of the ANSI SQL-92 standard, so as far as SQL is concerned, porting is a rather easy task.

In addition to porting the SQL code of your applications, it is necessary to port the functions based on embedded languages. In this case PostgreSQL is flexible because an entire set of embedded languages is enabled. PL/pgSQL is close to PL/SQL, so the majority of your PL/pgSQL code can be ported easily. If PL/pgSQL is not enough to port your functions, you can try to use PL/Perl or Pl/Tcl. There is no function that cannot be ported to PL/pgSQL, PL/Perl, or PL/Tcl.

For porting the data and the data structure stored in an Oracle database to PostgreSQL, we recommend using a Perl script. The easiest way to migrate data is to use ora2pg, which can be downloaded from http://www.samse.fr/GPL/ora2pg/. It is a Perl module that has been written to port data and data structures from Oracle to PostgreSQL. The module is constantly improved and provides an easy interface to Oracle and PostgreSQL. Perl programming is beyond the scope of this book, so we recommend that you check out the Web site and take a look at the latest version of the man pages, which are distributed along with the module.