Experience in porting the production management system database from Oracle DBMS to PostgresPro DBMS in a manufacturing enterprise

The practice of transferring structure, logic and data from Oracle DBMS to PostgresPro DBMS. Features and main difficulties of migration. Advantages of PostgresPro in terms of porting logic.

How to transfer 10TB from Oracle to Postgres in 24 hours?

We offer to your attention our experience in data migration and the Ora2PgCopy program written in Java for high-speed data transfer from Oracle to Postgres, which is used after creating tables and transferring the program code of application systems. High data transfer speed is provided by using the Postgres command “copy”, using multithreaded Java technology for file processing, managing the nologged/logged table option, and supporting LOB and CLOB data types. According to the test results, Ora2PgCopy works noticeably faster than such analogues as: Ispirer (convertum), oracle_fdw, ora2pg, Pentaho kettle. Ora2PgCopy can function as a module as part of the LUI4ORA2PG migration automation system or independently of it. The history of the growth of the Live Universal Interface (LUI) web application development tool and the LUI4ORA2PG migration tool can be found in previous presentations at PGConf conferences: https://pgconf.ru/2019/118109 , https://pgconf.ru/201911/264095 , https://pgconf.ru/2020/262456, https://pgconf.ru/2021/288310, https://pgconf.ru/2022/316022.

How we switched Oracle for PostgreSQL for a client, before it became mainstream

The history of import substitution did not start last year after well-known events. Its launch dates back to 2014. It was from this year that state and near-state companies began to think of switching to the so called "recommended software". One of these companies approached us back in 2020 with a project to move from Oracle to PostgreSQL. This project was designed to solve the accumulated architectural problems (imperfect storage system for telemetry data, the DBMS itself worked inside a virtual machine), and optimize the use of disk space (make space in the main storage, debug saving archived data, ensure correct backup). Since the customer's system should have worked uninterrupted 24/7, it was necessary to switch from one DBMS to another "seamlessly" without downtime, with simultaneous operation of both to ensure step-by-step translation of subsystems and the ability to control the correctness of the data. And, of course, the work had to be completed as quickly as possible.

In the report we will discuss how we managed to solve this case.

Let's get acquainted with GEQO in 20 minutes

-----------------------------------------------------------QUERY PLAN--------------------------------------------------------------
Hash Join
  Hash Cond: (Subject = GEQO)
  -> Hash Join
        Hash Cond: (**Optimizer task = choose the best query execution plan**)
        -> Seq Scan on **The number of potential plans grows exponentially as the number of tables in a query increases**
        -> Hash
              -> Seq Scan on **PostgreSQL solves this problem by using the genetic optimizer (GEQO)**
  -> Hash
        -> Seq Scan on **Topics of the report:**
              Filter: (**(What is GEQO)** AND **(Pros and cons)** AND **(How it works)**)
(10 rows)