Migrate data from Oracle to PostgreSQL
The “landscape” of the DBMS in the projects of our company until recently looked like this: Oracle was the largest part, significantly smaller were MS SQL and MySQL.
But as you know, there is nothing eternal, and recently we received a request for the applicability of Postgres in one of our projects. For the last couple of years, we have been looking very closely at this DBMS - we attended conferences, meetups, but until recently we didn’t have to try it in “combat” conditions.
Given: Oracle server (single instance) 11.2.0.3 and a set of unrelated schemes with a total volume of ~ 50GB. Necessary: transfer data, indexes, primary and reference keys from Oracle to Postgres.
An overview of the migration toolkit showed the presence of both commercial tools, such as the Enterprise DB Migration Toolkit and Oracle Golden Gate, and free software. The translation was planned one-time, therefore a mature means was required, at the same time clear and simple. In addition, of course, the question of cost was taken into account. Of the free software, the most mature for today is the project of Ora2Pg of Gilles Darold (Darold Gill), it is in many ways surpassed in functionality and commercial options. Advantages, tipped the scales in his direction:
')
The principle of operation of the command-line utility Ora2Pg is quite simple: it connects to an Oracle database, scans the schema specified in the configuration file, and unloads the schema objects as DDL instructions into sql files. The data itself can either be uploaded as INSERTs into the sql file, or inserted directly into the created Postgres DBMS tables.
In the company, we use the DevOps approach to create virtual machines, install the necessary software, configure and deploy software. Our working tool is Ansible. But in order to facilitate perception and not introduce new entities into the article, which are irrelevant to business, then we will show manual actions from the command line. For those who are interested, we post the Ansible playbook for all the steps here .
So, on the virtual machine with Centos OS 6.6, perform the following steps.
All further actions will be performed under the
Install Postgres 9.4:
Create a Postgres cluster:
Setting up access comes down to the fact that we specifically reduce Postgres connection security for ease of testing. Of course, in the production environment, we do not recommend doing so.
In the /var/lib/pgsql/9.4/data/postgresql.conf file, you must uncomment the line
Register Postgres as a service and launch it:
To install the Oracle instant client, you need to download the following packages from OTN:
Install them:
Create a folder for
Set the following environment variables (in user’s .bash_profile):
And check the performance.
If everything is ok, we will get something like this:
The last configuration step is left - setting Ora2pg. We download the latest version of Ora2Pg from the site (at the time of writing this article was version 15.2). Install the necessary packages:
Install the CPan module:
Install additional modules for Perl:
Let's unpack Ora2pg in, say, / install:
Let's collect Ora2pg:
Postgres is closest in spirit to Oracle. In both data types are well correlated, and there, and there is such a thing as a scheme. We take advantage of this and we will transfer the data "poschemno". The migration process will consist of the following steps.
All subsequent actions will be performed from the postgres user.
Create a migration project. The project consists of a set of tables / functions / views / packages folders containing sql files with the DDL of the corresponding objects, the ora2pg.conf configuration file and the launch script - export_schema.sh.
The configuration file Ora2pg is quite large, and I will focus only on those parameters that are root or required during the migration of our data. About the rest, I recommend to learn from this article .
Section describing the parameters of the connection with the Oracle database:
Section describing which scheme is unloaded:
And the indication in which scheme we load:
Specify the type of export. The
Section describing the parameters of the connection with Postgres DB:
Data type conversion section. To ensure that the
This completes the configuration steps, and we are ready to proceed with the migration. Unload the schema descriptions in the form of a set of sql files with the DDL objects:
Create a qqq database, test_owner user and issue the necessary rights.
Perform import of sql file from DDL tables:
Now everything is ready to copy the data. Run:
Despite the fact that we specify the
The last step is to create indexes and constraints.
If in the process of executing the previous commands you did not receive any errors, congratulations, the migration was successful! But, as is known from the law of Murphy: "Anything that can go wrong will go wrong".
The first pitfall has already been mentioned above: the
The next difficulty was that Postgres does not have a type similar to Oracle anydata. In this regard, we were forced, by analyzing and adjusting the logic of the application, to the detriment of flexibility, convert it into “suitable” types, for example, in
Utility Ora2Pg, despite the complexity of the settings, simple and reliable to use. It can be safely recommended for the migration of small and medium-sized databases. By the way, its author at PGConf Russia announced that it is starting the MS2Pg project. It sounds promising.
Successful migrations!
But as you know, there is nothing eternal, and recently we received a request for the applicability of Postgres in one of our projects. For the last couple of years, we have been looking very closely at this DBMS - we attended conferences, meetups, but until recently we didn’t have to try it in “combat” conditions.
So, the task
Given: Oracle server (single instance) 11.2.0.3 and a set of unrelated schemes with a total volume of ~ 50GB. Necessary: transfer data, indexes, primary and reference keys from Oracle to Postgres.
Migration tool selection
An overview of the migration toolkit showed the presence of both commercial tools, such as the Enterprise DB Migration Toolkit and Oracle Golden Gate, and free software. The translation was planned one-time, therefore a mature means was required, at the same time clear and simple. In addition, of course, the question of cost was taken into account. Of the free software, the most mature for today is the project of Ora2Pg of Gilles Darold (Darold Gill), it is in many ways surpassed in functionality and commercial options. Advantages, tipped the scales in his direction:
')
- rich functionality;
- active development of the project (15 years of development, 15 major releases).
The principle of operation of the command-line utility Ora2Pg is quite simple: it connects to an Oracle database, scans the schema specified in the configuration file, and unloads the schema objects as DDL instructions into sql files. The data itself can either be uploaded as INSERTs into the sql file, or inserted directly into the created Postgres DBMS tables.
Install and configure the environment
In the company, we use the DevOps approach to create virtual machines, install the necessary software, configure and deploy software. Our working tool is Ansible. But in order to facilitate perception and not introduce new entities into the article, which are irrelevant to business, then we will show manual actions from the command line. For those who are interested, we post the Ansible playbook for all the steps here .
So, on the virtual machine with Centos OS 6.6, perform the following steps.
- Install the Postgres repository.
- Install Postgres 9.4 server.
- Create a database and configure access.
- Install Postgres as a service and launch it.
- Install the Oracle instant client.
- Install the utility Ora2Pg.
All further actions will be performed under the
root account. Install the repository:#yum install yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm
Install Postgres 9.4:
#yum install postgresql94-server
Create a Postgres cluster:
#service postgresql-9.4 initdb
Setting up access comes down to the fact that we specifically reduce Postgres connection security for ease of testing. Of course, in the production environment, we do not recommend doing so.
In the /var/lib/pgsql/9.4/data/postgresql.conf file, you must uncomment the line
listen_addresses = '*' . In the /var/lib/pgsql/9.4/data/pg_hba.conf file for local and remote connections, you must put the trust method. The section after editing looks like this: # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all all trust Register Postgres as a service and launch it:
#chkconfig postgresql-9.4 on #service postgresql-9.4 restart To install the Oracle instant client, you need to download the following packages from OTN:
oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm Install them:
#yum install /tmp/oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm #yum install /tmp/oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm #yum install /tmp/oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm #yum install /tmp/oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm Create a folder for
tnsnames.ora : #mkdir -p /usr/lib/oracle/11.2/client64/network/admin #chmod 755 /usr/lib/oracle/11.2/client64/network/admin Set the following environment variables (in user’s .bash_profile):
export ORACLE_HOME=/usr/lib/oracle/11.2/client64 export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib export TNS_ADMIN=$ORACLE_HOME/network/admin And check the performance.
sqlplus system/<you_password_here>@host.domain.ru/SERVICE If everything is ok, we will get something like this:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> The last configuration step is left - setting Ora2pg. We download the latest version of Ora2Pg from the site (at the time of writing this article was version 15.2). Install the necessary packages:
#yum install gcc cpan postgresql94-plperl postgresql94-devel Install the CPan module:
#cpan Install additional modules for Perl:
#cpan Test::Simple DBI Time::HiRes DBD::Oracle DBD::Pg Let's unpack Ora2pg in, say, / install:
#cd /install #tar -xvf ora2pg-15.2.tar.gz Let's collect Ora2pg:
#perl Makefile.PL #make #make install Migration
Postgres is closest in spirit to Oracle. In both data types are well correlated, and there, and there is such a thing as a scheme. We take advantage of this and we will transfer the data "poschemno". The migration process will consist of the following steps.
- Creating a migration project using Ora2pg.
- Edit the ora2pg.conf configuration file.
- Unloading DDL tables, indexes, constraints from Oracle.
- Creating a database in Postgres.
- Import DDL tables, prepared in step 3.
- Copying data.
- Import DDL indexes and constraints.
All subsequent actions will be performed from the postgres user.
#su -l postgres Create a migration project. The project consists of a set of tables / functions / views / packages folders containing sql files with the DDL of the corresponding objects, the ora2pg.conf configuration file and the launch script - export_schema.sh.
$ora2pg --init_project my_project_name $cd my_project_home $vi config/ora2pg.conf Configuration
The configuration file Ora2pg is quite large, and I will focus only on those parameters that are root or required during the migration of our data. About the rest, I recommend to learn from this article .
Section describing the parameters of the connection with the Oracle database:
ORACLE_HOME /usr/lib/oracle/11.2/client64 ORACLE_DSN dbi:Oracle:host=oracle_host.domain.ru;sid=<SID> ORACLE_USER SYSTEM ORACLE_PWD MANAGER Section describing which scheme is unloaded:
EXPORT_SCHEMA 1 SCHEMA TST_OWNER And the indication in which scheme we load:
PG_SCHEMA tst_owner Specify the type of export. The
COPY parameter says that we will copy data directly from Oracle to Postgres, bypassing the text file. TYPE TABLE,COPY Section describing the parameters of the connection with Postgres DB:
PG_DSN dbi:Pg:dbname=qqq;host=localhost;port=5432 PG_USER tst_owner PG_PWD tst_onwer Data type conversion section. To ensure that the
number() type without precision is not converted to bigint , we indicate: DEFAULT_NUMERIC numeric This completes the configuration steps, and we are ready to proceed with the migration. Unload the schema descriptions in the form of a set of sql files with the DDL objects:
$./export_schema.sh Create a qqq database, test_owner user and issue the necessary rights.
$psql postgres=#create database qqq; CREATE DATABASE postgres=#create user test_owner password 'test_owner'; CREATE ROLE postgres=#grant all on database qqq to test_owner; GRANT postgres=#\q Perform import of sql file from DDL tables:
$psql -d qqq -U test_owner < schema/tables/table.sql Now everything is ready to copy the data. Run:
$ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf Despite the fact that we specify the
- parameter in the command line with the name of the file to which the upload should be saved, data is inserted directly from Oracle to Postgres. In our case, the insertion speed was about 6 thousand lines per second, but this, of course, depends on the types of copied data and the surrounding infrastructure.The last step is to create indexes and constraints.
$psql -d qqq -U test_owner < schema/tables/INDEXES_table.sql $psql -d qqq -U test_owner < schema/tables/CONSTRAINTS_table.sql If in the process of executing the previous commands you did not receive any errors, congratulations, the migration was successful! But, as is known from the law of Murphy: "Anything that can go wrong will go wrong".
Our pitfalls
The first pitfall has already been mentioned above: the
number() type is converted to bigint without specifying accuracy, but this is easily fixed with the correct configuration.The next difficulty was that Postgres does not have a type similar to Oracle anydata. In this regard, we were forced, by analyzing and adjusting the logic of the application, to the detriment of flexibility, convert it into “suitable” types, for example, in
varchar2(100) . In addition, if you have any custom types, then everything will have to be redone, since they are not translated, but this is a topic for at least a separate article.Let's sum up
Utility Ora2Pg, despite the complexity of the settings, simple and reliable to use. It can be safely recommended for the migration of small and medium-sized databases. By the way, its author at PGConf Russia announced that it is starting the MS2Pg project. It sounds promising.
Successful migrations!
Source: https://habr.com/ru/post/262605/
All Articles