Synchronization between Oracle and PostgreSQL Databases


Some projects and tasks require to store and update the data in two or more databases simultaneously. For this purpose, database administrators run the procedure known as database synchronization that is sequence of step to establishes data consistency. Since Oracle and PostgreSQL are popular advanced DBMS being often used together, it is required to synchronize data between these two systems.

There are multiple approaches to database synchronization. The most straightforward one is to scan Oracle and PostgreSQL tables for changes that are not reflected into another DBSM and performs necessary INSERT, UPDATE or DELETE statements to replicate those changes. Obviously, this approach is not so efficient and may take extremely long time for large databases.

Another traditional approach to database synchronization is known as Change Data Capture (CDC). The main feature of CDC method is to track/capture modified data in the source database in real-time and replicate the same to the destination database. The software working on CDC model provides the best performance because it transfers minimal volume of the data. It is especially important when synchronize between heterogeneous databases across network for the systems operating with low-latency, reliable, and scalable data.

There are two Change Data Capture techniques of synchronization between Oracle and PostgreSQL:

  • Trigger-based
  • Transaction Log-based

Trigger-based approach requires to create insert/update/delete triggers in the source database for each table being synchronized. Those triggers should capture any data modifications into special changelog table inside Oracle database. Then synchronization tool scans that changelog table and replicates all stored modifications into the target PostgreSQL database.

Transaction Log-based method of database synchronization is based on the fact that every Oracle database has transaction logs (also known as redos) to store sequence of all transactions for purpose of crash recovery. Using native database plugins those transactions can be captured to changelog table just like in the previously considered trigger-based approach. Later, the sequence of captured data modification statements is replicated into PostgreSQL database.

Although both CDC options specified above can underlaid synchronization between Oracle and PostgreSQL, those techniques are quite sophisticated for manual implementation. Fortunately, there are high quality reliable software that support both CDC approaches available on the market. One of these products is database synchronization tool Oracle-PostgreSQL Sync developed by Intelligent Converters.

Key features of the product are: 

  • All versions of Oracle and PostgreSQL running on Linux/Unix and Windows OS are supported
  • All SAAS variations of both DBMS (such as Amazon RDS, Heroku) are supported
  • Bi-directional synchronization Oracle->PostgreSQL and PostgreSQL->Oracle is available
  • Option to synchronize individual tables
  • Support for incremental trigger-based synchronization
  • Command line support allows to automated synchronization between Oracle and PostgreSQL databases
  • Synchronization settings are stored into profile

As every Intelligent Converters product, Oracle-PostgreSQL Sync is supplied with 24/7 support service and 1-year subscription for updates.