portint database from oracle to postgres

[ghjk]

I want to port oracle database to postgres. How can I do that? Is there any tool for that or do I need to convert query by query?

[legend97]

You have a couple of options.

If you port straight to Postgres, then you need a tool that can create the tables, indexes, constraints and move the data from Oracle to Postgres. There are tools out there such as EnterpriseDB's migration tool in Postgres Plus Standard Server. It will migrate all the table definitions, indexes, constraints and data for you.

Postgres Plus Standard Server migration toolkit documentation:

http://www.enterprisedb.com/docs/en/8.4/mtkguide/Postgres_Plus_Standard_Server_Migration_Toolkit_Guide-06.htm#P75_8073

If you have stored procedures in Oracle that you want to migrate, then you might want to check out Postgres Plus Advanced Server since it has Oracle compatibility and can migrate those procedures, packages, functions, tables, indexes, etc for you. Plus, you get the added benefit of the parser understanding the Oracle extensions to the SQL language, such as functions like DECODE, NVL2, etc so you won't have to rewrite your SQL statements.

Information about the Oracle Compatibility :

http://www.enterprisedb.com/products/postgres_plus_as/overview.do

[ghjk]

Thank you legend97. I'll try. But I want to use postgres 8. If I use your option is it totally different with version 8?

[legend97]

Do you mean PostgreSQL 8 or any 8.X version?

EnterpriseDB's products are based on Postgres 8.X. For example, Postgres Plus Standard Server core is the same as PostgreSQL 8.4. The Standard Server bundle includes other open source projects that most people end up using, i.e. replication, connectors, pgAdmin, etc.

So, if you were to move from PostgreSQL to Postgres Plus Standard Server, the move is simply to use pg_dump and then restore....the same steps if you were moving from one version of PostgreSQL to another.