PGConf.Russia 2023

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.

There is set of Ansible roles for any Postgres configuration: a standalone instance; a streaming replica; patroni+etcd; stolon+etcd; patroni+consul; stolon+consul. In this talk, we will discuss why using this set of Ansible roles is better than other solutions.

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.

The following topics will be covered as part of the presentation:

• Architecture of logical replication
• Publisher introduction
• Subscriber introduction
• Data syncronization introduction
• Logical decoding
• Replication slot
• output plugin