Re: Oracle to postgres migration

On Mon, Apr 8, 2019 at 8:04 AM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:

> On Mon, Apr 8, 2019 at 1:49 PM Daulat Ram <Daulat(dot)Ram(at)exponential(dot)com>
> wrote:
> >
> > Please confirm ! Can we migrate Oracle 12c database (12.1.0.1.0) running
> on Solaris to PostgreSQL 11.2 on Linux (Ubuntu). Also, please suggest the
> tools and pre-requisites.
> A database migration is likely feasible, but might require quite a lot
> of work depending on what features you're using, and the amount of PL
> code. Also, obviously migrating the database is only a part of the
> overall migration process, as you'll also need to take care of the
> application(s), the backup/restore, monitoring and all other tools you
> need.
>
> Concerning the database migration, the best tool is probably Gilles
> Darold's ora2pg. The tool also provides a migration cost assessment
> report, to evaluate the difficulty of the database migration. More
> information on http://ora2pg.darold.net/
>
>
>
The last big Oracle to PG migration that I did was several years ago. We
stood up the PostgreSQL instance(s) and then used SymmetricDS to
synchronize the Oracle and PG databases. After tuning and testing the
postgresql side, we cut over the applications live - with minimal downtime
- by releasing the updated application code and configuration. If we
needed to fail back, it was also pretty easy to undo the release and
configuration changes.

Another approach you can play with is to leverage Foreign Data Wrappers.
In that scenario, you can run queries on your Oracle database from within
PostgreSQL. You can use those queries to copy data directly into new
tables without any interim files, or as a hybrid transition while you get
the new database set up.

At the time I was working on that migration, we had too many
data-edge-cases for ora2pg to be very useful. It has come a long ways
since then. I'm not sure it can do a live cutover, so you may need to plan
a bit of downtime if you have a lot of data to move into the new database.

Note that you will also almost certainly want to use a connection pooler
like PGBouncer and/or PGPool II (or both at the same time), so be sure to
include that in your plans from the beginning.

That said, none of this is on topic for the performance mailing list.
Please try to direct your questions to the right group next time.