migrating oracle to postgresql

the issue you are running into has nothing to do with either oracle nor postgresql.


What are you installing, you need to make sure your DBI, DBD, build includes a perl module for postresql.

not sure which perl version are you using? what is the system on which it is running, linux, solaris version?


perl -MCPAN -e 'install DBI"

which modules do you have install and which module are you missing?

you may have to make sure that all your dependencies are satisfied for DBI, DBD, Oracle, postgresql.......

do you have all the related devel packages against whom the module you are building can be linked?

The solution is provided by arnold.

perl -MCPAN -e 'install DBI"

Select allOpen in new window

Aside: Migrating from Oracle to PostgreSQL is non-trivial.

Migrating from Oracle to MariaDB running in Oracle compatibility mode is even fairly complex.

Oracle != PostgreSQL

A great number of major differences, so getting DBI installed (along with DBD::Oracle + DBD::Pg) is only the first step in a very long process.

You'll save yourself a massive amount of time waiting till MariaDB-10.5 GAs, then use MariaDB Oracle mode for a project like this.

As others have stated:  The prerequisites are in the docs.  Looks like you will also need an Oracle Client:
http://ora2pg.darold.net/documentation.html

To connect to a database and proceed to his migration you need the DBI Perl module > 1.614. To migrate an Oracle database you need the DBD::Oracle Perl modules to be installed. To migrate a MySQL database you need the DBD::MySQL Perl modules. These modules are used to connect to the database but they are not mandatory if you want to migrate DDL input files.

To install DBD::Oracle and have it working you need to have the Oracle client libraries installed and the ORACLE_HOME environment variable must be defined.

What exactly are you migrating?  If it is just data, you may not even need ora2pg.  We rolled our own to use CSV straight from sqlplus piped into psql.

It will require some downtime to cut over when everything is ready to go.

am geting

postre@postre-VirtualBox:~/Downloads/DBD-Oracle-1.80$ perl -MCPAN -e "install DBI"

CPAN.pm requires configuration, but most of it can be done automatically.
If you answer 'no' below, you will enter an interactive dialog for each
configuration option instead.

Would you like to configure as much as possible automatically? [yes] yes

 <install_help>

Warning: You do not have write permission for Perl library directories.

To install modules, you need to configure a local Perl library directory or
escalate your privileges.  CPAN can help you by bootstrapping the local::lib
module or by configuring itself to use 'sudo' (if available).  You may also
resolve this problem manually if you need to customize your setup.

What approach do you want?  (Choose 'local::lib', 'sudo' or 'manual')
 [local::lib]
what must i chose

Select allOpen in new window

i what to migrate a schema to postgresql

>>Warning: You do not have write permission for Perl library directories.

Most OS type installs need to be performed as root not the postgres user.


>>i what to migrate a schema to postgresql

The term "schema" doesn't tell us anything.  A schema can have stored procedures and functions, domain indexes like Text, User-defined types, and the list goes on.

Specifically what in the schema are you looking to migrate? Before you say "everything", you need to know what objects are in it.  Porting PL/SQL (code) to different databases isn't simple.

Simple data and indexes, fairly easy.

ok its done whats next i did sudo perl -MCPAN -e "install DBI"

Installing /usr/local/man/man3/DBI::Const::GetInfo::ODBC.3pm
Installing /usr/local/man/man3/DBD::File::HowTo.3pm
Installing /usr/local/man/man3/DBI::Gofer::Transport::Base.3pm
Installing /usr/local/man/man3/DBI::Gofer::Transport::stream.3pm
Installing /usr/local/man/man3/DBI::ProfileData.3pm
Installing /usr/local/man/man3/DBD::Gofer::Policy::pedantic.3pm
Installing /usr/local/man/man3/DBD::Gofer::Transport::null.3pm
Installing /usr/local/man/man3/DBD::Gofer::Transport::pipeone.3pm
Installing /usr/local/man/man3/DBI.3pm
Installing /usr/local/man/man3/DBI::ProfileDumper::Apache.3pm
Installing /usr/local/man/man3/DBI::Gofer::Response.3pm
Installing /usr/local/man/man3/DBI::Gofer::Serializer::Base.3pm
Installing /usr/local/man/man3/DBI::Const::GetInfo::ANSI.3pm
Installing /usr/local/man/man3/DBD::File::Roadmap.3pm
Installing /usr/local/man/man3/DBD::File::Developers.3pm
Installing /usr/local/man/man3/DBD::Proxy.3pm
Installing /usr/local/man/man3/DBI::DBD::Metadata.3pm
Installing /usr/local/man/man3/DBI::DBD.3pm
Installing /usr/local/man/man3/DBD::Gofer::Policy::classic.3pm
Installing /usr/local/man/man3/DBI::Gofer::Transport::pipeone.3pm
Installing /usr/local/man/man3/DBD::DBM.3pm
Installing /usr/local/man/man3/Bundle::DBI.3pm
Installing /usr/local/man/man3/DBI::ProfileSubs.3pm
Installing /usr/local/man/man3/DBI::Gofer::Request.3pm
Installing /usr/local/man/man3/DBI::W32ODBC.3pm
Installing /usr/local/man/man3/DBD::Gofer::Policy::Base.3pm
Installing /usr/local/man/man3/DBD::Gofer::Transport::corostream.3pm
Installing /usr/local/man/man3/DBD::Gofer.3pm
Installing /usr/local/man/man3/DBI::DBD::SqlEngine.3pm
Installing /usr/local/bin/dbiproxy
Installing /usr/local/bin/dbiprof
Installing /usr/local/bin/dbilogstrip
Appending installation info to /usr/local/lib/x86_64-linux-gnu/perl/5.26.1/perllocal.pod
  TIMB/DBI-1.643.tar.gz
  /usr/bin/make install  -- OK

At a minimum, install the other two things mentioned in the documentation.  I know you are trying the Oracle client from your other question but the docs also mention the DBD module.

As for what's next after those, I would defer to the documentation on installing and using it.

Kin short you need to build the Oracle module for Perl and the PostgreSQL related module that you will be using
$oracle_connection
$postgresql_connectiom

Potentially you are pulling data from one, and inserting them into the other.

As others noted, scripting the data e traction/population is not a simple process.

Might be useful to go through all required steps here.

1) You must get your Oracle + PostgreSQL client tools working.

In other words, you must be able to make connections to both your Oracle + PostgreSQL instances.

These instance might be on the same machine as your client or on different machines.

You must be able to connect to both databases as your first step.

2) Then install DBI.

3) Then install DBD::Oracle + DBD::Pg.

4) Test both DBD installs (#3) using simple code just to connect to each database instance, then list all the databases by name... as one simple example.

5) Then start through what arnold mentioned, the long grueling task of SELECTing Oracle data, then making all the transforms necessary, then INSERTing transformed data into your PostgreSQL instance.