July 13, 2005

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.

Posted by moore at July 13, 2005 08:06 AM

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.



Posted by: Daniel Gaudreau at July 21, 2005 01:05 PM | Permalink


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.. :|

Posted by: Damien at July 28, 2005 05:00 PM | Permalink
© Moore Consulting, 2003-2006