Imagine a scenario where your organization relies on Oracle Database, amassing vast amounts of data over the years—tens of terabytes, to be precise. As you contemplate a transition to Postgres Pro, the migration process can quickly become a daunting challenge:
- The data transfer can be painfully slow.
- The source system must remain operational, even as data continues to change.
- There exists a genuine risk of data loss or corruption, stemming from transmission errors, type mismatches, and other unforeseen issues.
In response to these challenges, we developed ProGate, a comprehensive toolkit designed to simplify the migration process for database administrators (DBAs) and expedite the transition to Postgres Pro.
What’s ProGate?
ProGate serves as an all-encompassing solution for migrating data into Postgres Pro. It comprises a suite of specialized tools that address every phase of the migration journey:
- ProCopy — facilitates high-speed initial data loading.
- ProSync — ensures continuous change synchronization (CDC — Change Data Capture).
- ProCheck — conducts post-migration data quality and integrity assessments.
Let’s delve into each component.
Step 1. Initial data load with ProCopy
ProCopy is a console utility engineered for rapid data transfer into Postgres Pro without halting the source system. This capability minimizes downtime and reduces the impact on ongoing applications. Recent synthetic tests have demonstrated impressive speeds of 200–500 MB/sec for migrations from Oracle to Postgres Pro, translating to approximately 41 TB/day. For PostgreSQL to Postgres Pro migrations, we achieved around 1 GB/sec.
How does it work
Crafted in Go, ProCopy is lightweight and highly concurrent, adept at handling databases of any size. Its underlying architecture includes:
- A pool of parallel read/write processes.
- An internal data bus for swift inter-process communication.
- Robust error handling mechanisms with retries for problematic records.
ProCopy use case
Consider a scenario where you have a CUSTOMERS table in Oracle containing billions of rows. With ProCopy, you can:
- Execute the migration in parallel, adjusting the number of reader/writer processes and batch sizes.
- Omit unnecessary columns.
- Rename columns or modify their data types.
- Transform
NULLvalues into default values on the fly.
Highlights
- Blazing fast data transfer rates.
- Flexible configuration options (YAML/JSON).
- Ability to resume from the last checkpoint.
- Support for complex data types (LOB, XML, JSON, etc.).
Step 2. Change synchronization with ProSync (CDC)
ProSync maintains synchronization between Oracle and Postgres Pro by continuously capturing changes (CDC) and applying them to the target database, enabling migration with minimal downtime.
How does it work
Oracle logs every data change—whether inserts, updates, or deletes—in redo logs. ProSync monitors these logs in real time, identifies each change, and promptly applies it to Postgres Pro. Consequently, your Postgres Pro instance remains current with Oracle, reflecting every committed transaction.
Real-world example
For a bank, even an hour of downtime is unacceptable. With ProSync, they can gradually migrate data and applications while the existing Oracle system remains operational. Customers continue to interact with Oracle, and every transaction is instantly mirrored to Postgres Pro. Once synchronization is achieved, the switchover takes mere minutes.
Highlights
- Seamless real-time data synchronization.
- Minimal downtime during migration.
Step 3. Data quality check with ProCheck
ProCheck ensures that the migration process is executed flawlessly, confirming that the data in Postgres Pro mirrors that in Oracle.
How does it work
This tool compares tables, rows, and columns across both databases, identifying discrepancies, conversion errors, and missing data, and generates a comprehensive report.
ProGate use case
After migrating a financial system, ensuring that every balance matches to the last cent is crucial. ProCheck meticulously verifies each row to confirm that no data has been lost or altered. ProGate is particularly well-suited for:
- Extensive databases (terabytes and beyond).
- Hot migrations with minimal downtime.
- Strict data consistency requirements.
Known Limitations
While ProGate alleviates numerous challenges, it’s essential to consider the following:
- Schema changes during migration may necessitate manual intervention.
- Custom data types might require specific mapping.
- Tables lacking primary keys—ProSync operates most effectively with unique identifiers.
What’s next
The public release of ProGate is slated for this fall. Our roadmap includes:
- A graphical user interface, backend, and API.
- Support for additional sources/targets (MS SQL Server, MySQL, Shardman).
In essence, ProGate promises a swift, secure, and seamless transition from Oracle to Postgres Pro—eliminating prolonged downtimes, data discrepancies, and unexpected complications. Further technical insights will be shared following the public launch.
