Skip to content

Exchanging PostgreSQL for Oracle

I have a client who was building some commercial software on top of PostgreSQL. This plans to be a fairly high volume site, 1.8 million views/hour 500 hits a second. Most of the software seemed to work just fine, but they had some issues with Postgres. Specifically, the backup was failing and we couldn’t figure out why. Then, a few days ago, we saw this message:

ERROR: could not access status of transaction 1936028719
DETAIL: could not open file “/usr/local/postgres/data/pg_clog/0836”: No such file or directory

After a bit of searching, I saw two threads suggesting fixes, which ranged from deleting the offending row to recreating the entire database.

I suggested these to my client, and he thought about it for a couple of days and came up with a solution not suggested on these threads: move to Oracle. Oracle, whose licensing and pricing has been famously opaque, now has a pricing list available online, with prices for the Standard Edition One and Enterprise Edition versions of their database, as well as other software they sell. And my client decided that he could stomach paying for Oracle, given:

1. The prices aren’t too bad.
2. The amount of support and knowledgeable folks available for Oracle dwarfs the community of Postgres.
3. He just wants something to work. The value add of his company is in his service, not in the back end database (as long as it runs).

I can’t fault him for his decision. PostgreSQL is full featured, was probably responsible for Oracle becoming more transparent and reasonable in pricing, and I’ve used it in the past, but he’d had enough. It’s the same reason many folks have Macs or Windows when there is linux, which is a free tank that is “… invulnerable, and can drive across rocks and swamps at ninety miles an hour while getting a hundred miles to the gallon!”.

I’ll let you know how the migration goes.

Oracle Technology | moore |

10 thoughts on “Exchanging PostgreSQL for Oracle”

  1. Daniel Gaudreau says:

    Hi Dan,

    We are in the same process here, moving from Postgres to Oracle. I am currently looking for tools to help with the conversion work : DDL and Data itself.

    Let me know the outcome of your work.

    I will keep you posted if we find anything of value.

    Cheers,

    Daniel

  2. Damien says:

    Daniel,

    Don’t know if it’s of any use to you, but we’ve just finished a number of oracle->postgres conversions for a number of our clients (the jump from $20k to $110k for 8i->10g was a little much to stomach for a few clients, so they opted to throw more money into hardware, and go postgres..), and out of that project came a very funky little java util that takes two jdbc uri’s, and does a full transfer of the ddl and data of one db to the other (recreates tables, transfers data, recreates constraints and indexes).. Shoot me an email if you want a copy to play with.. 🙂

    And now I’m off to keep trying to find a solution to the problem Dan mentions in his post.. 😐

  3. Anand says:

    Hi All..
    I have just started working on a migration from postgres to oracle.Kindly let me know the location of proper material and few tips to begin with.I am new to this field.
    thanks

  4. moore says:

    Hi Anand,

    It really depends on the application. If it’s a java app that uses postgresql for storage, then you just want to start modifying the schema. I’d look at the oracle manual and the postgresql manual and start mapping data types. If the application use PG/SQL or some other postgres specific features, I’d start looking for Oracle replacements for those.

    Good luck.

  5. Anand says:

    thanks Moore..
    I will get back to you if I have some further queries

  6. Punita says:

    Hi ,

    we have a linux based postgrSQL database. we want to transfer the data from PG/SQL to oracle. How can we do this ?? is there any tool or script for this ??
    Since the data is huge , we cant do it by exporting to a flat file and then importing it to oracle by mapping the datatypes. Please suggest any way to do this.

    Thanks,
    Punita

  7. moore says:

    Hi Punita,

    Have you tried to export to a flat file table by table, or portion of table by portion of table? It looks like you can dump schema by schema or table by table: http://www.postgresql.org/docs/8.0/interactive/app-pgdump.html

    Also, the pg manual has some suggestions for large dbs:
    http://www.postgresql.org/docs/8.0/interactive/backup.html#BACKUP-DUMP-LARGE

    The SQL that pg_dump exports might not be Oracle compatible–you could use sed or perl to munge the text to change the SQL.

    Hope this helps.

  9. chris marx says:

    Any updates on the migration? Any new tools come out in the last year? I am not looking forward to working through the db creation scripts for 60 tables that need to get ported over to oracle-

  10. moore says:

    Chris, I have not looked at this issue for a while.

    I don’t have much more to offer you than a google search.

    Sorry.

Comments are closed.