Journal "Software Engineering"
a journal on theoretical and applied science and technology
ISSN 2220-3397

Issue N1 2022 year

DOI: 10.17587/prin.13.32-40
On the Issue of Migrating Databases from Oracle to PostgreSQL
E. A. Kurako, keaipu@yandex.ru, V. L. Orlov, ovl@ipu.ru, V. A. Trapeznikov Institute of Control Sciences of Russian Academy of Sciences, Moscow, 117997, Russian Federation
Corresponding author: Kurako Evgeny A., Researcher, V. A. Trapeznikov Institute of Control Sciences of Russian Academy of Sciences, Moscow, 117997, Russian Federation, E-mail: keaipu@yandex.ru
Received on October 19, 2021
Accepted on November 18, 2021

The methods of data transfer from Oracle to PostgreSQL environment are considered, taking into account the step-by-step organization of actions and deter-mining the sequence of migration. The main directions for the transformation of the structure of databases, data formats and executed objects are highlighted. New methods and programs for converting the structure and formats in automatic mode are proposed. The methods of transformation of stored procedures and triggers written in procedural languages, which are PL/SQL and PL/PGSQL, are defined. For cases of possible ambiguous translation, programs have been developed that form hints that facilitate manual operation. The task of managing transactions inside stored functions in PostgreSQL is difficult. If such transactions are possible for Oracle, then in the PostgreSQL environment, it is necessary to organize them only in external code. They are usually published on computers that work directly with the database server. In this case, the division of the original function into several functions is used, each of which is responsible for its own transaction block. Each such function is called from external code. The issues of changing software on nodes directly connected (interfaced) with the database server and the issues of minimizing stops during migration are considered, which makes it possible to carry out migration for geographically distributed and round-the-clock functioning objects. Examples of changes in the speed of execution of requests before and after migration for various types of systems are given.

Keywords: database, DBMS, Oracle, PostgreSQL, migration, structure, data, function, stored procedures, PL/SQL, PL/pgSQL
pp. 32–40
For citation:
Kurako E. A., Orlov V. L. On the Issue of Migrating Databases from Oracle to PostgreSQL, Programmnaya Ingeneria,2022, vol. 13, no. 1, pp. 32—40.