Re: migrating from Oracle to PostgreSQL 11

On Wed, 26 Jun 2019 19:12:07 +0530
Hitesh Chadda <hkumarchadda(at)gmail(dot)com> wrote:

> Hi PostgresSQL Support,
>
> I have to migrate from Oracle 11g to PostgresSQL 11.4. Please suggest
> best solution for doing the migration.

Bit of a broad topic for an answer. Other than "carefully", there
isn't enough of a question to answer.

Issues that will affect the transfer include the quantity of data,
relative locations of the servers, complexity of the data, how much
custom code you have tied up in triggers and local functions, and
the platforms you will be running with.

For example, moving a 6PB database has downtime and data transfer
issues that you don't have with a few GB; a 7x24 database with
multiple hot servers accessed via VPN has different challenges than
a single-server on a VM on your LAN; translating a bunch of integer
SK's and some text has different requirements than something you want
to compose arrays, ranges, or JSONB objects out of; the dialaects
of PLSQL are not entirely compatible so you will have to think about
translating triggers, reports, and op's code between dialects; Oracle
only supports "global temporary tables", PG avoids them with the
added features of drop/truncate on commit...

Your best first step will be to get a basic understanding of PG. I
like the "PostgreSQL: Up and Running", I think it's up to the 3rd
edition. Grab it on Safari, fairly short read covers the topics you
need to get a running PG database.

PG is a moving target: 11 supports all sorts of nice, new things;
12 has even more of them. Pick a PG version (suggest 11, it's
stable), look at what it does, investigate the specific applications
you have on site, document them, and start porting them to PG
from smallest to largest.

If you have specific questions about the PG version, its features,
or how to translate specific Oracle-isms into PG please feel free
to ask -- that's what we're here for :-)

--
Steven Lembark 3920 10th Ave South
Workhorse Computing Birmingham, AL 35222
lembark(at)wrkhors(dot)com +1 888 359 3508