At work we have an old Oracle 7.1.5 that has been in use for what feels like forever. Once the new frontend user interface is completed the old Oracle system will be going away, to be replaced by PostgreSQL. The new frontend is a webapp that talks to both the current Oracle system and the new PostgreSQL system. One of the challenges in this process is exporting all of the Oracle data and importing it into PostgreSQL. After a bit of research I wrote a cli PHP script that gets the data from Oracle and formats so that PostgreSQL will happy with it. The resulting import file is about 115 meg. The export script isn’t perfect, the plSQL that Oracle uses will probably have to be ported by hand into the syntax the PostgreSQL supports. Assuming the data is ok, the script takes care of creating all the tables, table data, indexes and constraints (at least some of them).
The really funny (ok, not really, but kind of) is how long it takes to export data from Oracle using this method. Before I write out the numbers it is important to know that the Oracle database is running on an old version of VMS, which supports TCP/IP via a 3rd party piece of software. It has always been a given that TCP/IP traffic to that system just isn’t very fast. On top of that the interface only supports 10Mbit. With that it mind, here are the numbers.
Export table definitions, table data, index definitions and (some) constraints using my home built PHP script from Oracle 7 on VMS: 3 hours 12 minutes 45 seconds.
Import table definitions, table data, create indexes and add (some) contraints into PostgreSQL on FreeBSD: 8 minutes 34 seconds.
Export the imported database from PostgreSQL using pg_dump: 2 minutes 35 seconds.
The PHP export script is run on a third host, so all the above is done over our network. Most of the time spent on the PostgreSQL import is on creating the indexes, somewhere around 4 minutes. I don’t go through this whole process a lot since it takes so long, but when I do I always get a kick out of big the time difference is.