Efficiently migrate from Oracle to Postgres with our guide. Achieve successful Postgres and database migration by leveraging DevOps and GitHub.
Many organizations are migrating from Oracle to powerful and cost-effective open source PostgreSQL. With its Oracle compatibility features, EDB Postgres Advanced Server (EPAS) makes this transition smooth. However, large-scale migrations still demand careful planning and the right tools.
In this blog post, we’ll outline a comprehensive migration strategy that leverages the power of DevOps practices with GitHub at its core, along with specialized EDB tools to streamline the process.
Initial Schema Conversion for Postgres Migration
The following workflow relies on EDB’s Migration Toolkit and Migration Portal:
- Assessment and identification: The Migration Portal or Migration Toolkit analyzes your Oracle schema, highlighting potential incompatibilities and the need for manual adjustments.
- Schema breakdown: Break your large schema into manageable SQL scripts (Tables.sql, Packages.sql, Procedures.sql, etc.) for focused conversion.
- Version control setup: Start a GitHub repository and store the initial Oracle schema in the “master” branch for reference.
- Branch creation for conversion: Create separate branches from the “master” to work on the conversion scripts generated by the Migration Portal. This modularizes your changes.
Schema Management during Database Migration
GitHub becomes your central hub for schema management as your database migration progresses.
- Branching for isolation: Continue creating branches for specific schema modifications. This keeps your main development environment stable while testing and refining changes.
- Collaboration and tracking: GitHub’s version control ensures seamless collaboration and a transparent history of all schema adjustments.
Automated Issue Detection for Oracle to Postgres Migration
Let’s introduce automation for error detection and quality control:
- GitHub Actions: Set up GitHub Actions to trigger automated checks whenever code is pushed to the repository.
- SPL Check: Integrate the EPAS “SPL Check extension” into a GitHub Action to analyze SQL and PL/SQL code for Oracle compatibility issues.
Compatibility Checkpoints While Migrating Databases
To provide additional checkpoints during database migration, let’s leverage the power of GitHub releases:
- Milestone releases: Create releases within your GitHub repository as significant portions of the schema are converted.
- Compatibility reassessment: Upload each release to the EDB Migration Portal. It will re-analyze the converted schema and provide a compatibility score against EPAS. This helps track progress and address issues.
Data Validation for Oracle to Postgres Migration
Extend your GitHub Actions to ensure your converted procedures and packages function as expected:
- Containerized testing: Actions can spin up Docker containers with EPAS and sample data. These containers become isolated testing environments to validate database functionality.
Performance Tuning for Database Migration
Don’t overlook performance! To focus on efficiency:
- CICD integration: The EPAS “DBMS_PROFILER” tool can be incorporated into your continuous integration and deployment (CICD) pipeline to gather performance data and pinpoint bottlenecks in the migrated database.
Adapting the framework using ora2pg and plpgsql_check
These principles and workflow can be adapted to your Oracle to Postgres migration using the ora2pg tool. Here are key steps:
- Initial conversion with ora2pg: Use ora2pg to perform the initial schema and data conversion from Oracle to Postgres.
- Compatibility checks with plpgsql_check: Install the “plpgsql_check” Postgres extension. This extension provides functions and analysis tools to validate the compatibility and correctness of your PL/pgSQL code. Integrate it into your GitHub Actions alongside ora2pg output for automated checks.
- GitHub for management and automation: The core benefits of using GitHub for version control, branching, and GitHub Actions for automation remain applicable with ora2pg.
Postgres Migration: Production Rollout and Switchover
After successful schema conversion, performance optimization, and thorough testing, it’s time for production rollout:
- Production environment setup: Establish a production-ready Postgres environment adhering to best practices for architecture, backup/recovery strategies, and observability/monitoring tools.
- Change Data Capture (CDC): Implement a CDC solution (such as the EDB Replication Server) to synchronize data from Oracle to your new Postgres database continuously. This minimizes downtime during the cutover.
- Application switchover: Once data migration is complete, switch your application to point to the Postgres database.
- Reverse replication: Set up replication from Postgres back to Oracle. This acts as a safeguard in case unexpected issues arise after the switchover.
- Monitoring and decommissioning: Closely monitor the new Postgres environment for a few weeks. If all goes smoothly, you can decommission the legacy Oracle database.
Note: This migration framework focuses on the database aspect. Developers and DBAs should integrate application conversion, functional testing, and QA processes in parallel, ideally before the performance benchmark phase.
A Successful Path Oracle to Postgres Migration
This comprehensive database migration strategy, built upon DevOps principles and powerful tools, offers a reliable, efficient path for Oracle to Postgres migration. You'll streamline migration and ensure a successful transition by emphasizing automation, collaboration, regular compatibility checks, an iterative refinement process, and a careful production rollout plan.
Businesses may outgrow the capabilities of their Oracle database and require a more extensible, feature-rich solution that supports a greater range of data types or languages. Some might also leave to avoid vendor lock-in or expensive licensing costs.
It depends on what a user or business expects from their database and the costs or complexity they are willing to bear as they scale to meet future demands. While there are many reasons to remain with Oracle, businesses might switch to PostgreSQL if they want better control and agility over their database.
PostgreSQL also has a growing ecosystem of solution providers that offer a range of enterprise-grade services, such as fully managed Postgres, cloud migrations, and 24/7 support, all of which makes PostgreSQL a viable alternative to Oracle.
PostgreSQL has zero licensing fees due to its open source licensing, which allows individuals and enterprises to access, modify, and distribute the code freely. PostgreSQL also has numerous other advantages over Oracle, namely:
- Extensibility support
PostgreSQL is catalog-driven like most relational databases, but unlike its counterparts, a wider range of information is stored within PostgreSQL catalogs. Users can easily access and modify information within PostgreSQL catalogs, making it more extensible than other databases. - Higher concurrency
MVCC (Multi-Version Concurrency Control) is a concurrency control system in PostgreSQL that allows for multiple concurrent read and write transactions while maintaining transaction isolation to ensure data isn’t rewritten and remains consistent for all users. - Feature-rich flexibility
PostgreSQL comes with a broader range of features, such as catalog-driven operations, concurrency control, layered transactions, and dynamic loading. It’s also ACID compliant, which ensures high data integrity and fault tolerance.
Both databases are equal in terms of transactions-per-second. Both can handle large data volumes and concurrent users. Both are also equal in terms of scalability. However, businesses will encounter licensing and cost complications with Oracle – a hurdle they won’t have with PostgreSQL due to its open source and extensible nature.
PostgreSQL supports ANSI SQL standard SQL syntax and data types. Oracle does not support these and includes some non-ANSI SQL syntaxes. Unsupported objects should be identified and converted manually with Postgres-supported syntax or feature workarounds.
Some best practices before migrating from Oracle into PostgreSQL:
- Get organizational buy-in
Ensure the entire business understands and agrees to migrate to PostgreSQL. Establish migration goals, timelines, and contingencies. - Determine what needs to be moved
Compile an exhaustive list of data or applications that need moving, eliminate old data the business no longer needs, and identify compatibility issues that might occur. - Test and test again
Perform stability and performance tests on data migrated to PostgreSQL to identify potential issues before proper migration. - Establish new security and observability measures
Migrating to PostgreSQL allows businesses to leverage their security and monitoring capabilities to minimize data security and outage risks.
There are many differences between databases, namely data types, syntax, and procedural language, plus potential compatibility issues for migrated applications.
It’s easier to convert schemas in Oracle to PostgreSQL with the right tools. Check this list of free online conversion tools. While these are freely available, they can be challenging to use without the right expertise. If you’re converting an extensive database filled with thousands of query lines, it’s advisable to use an enterprise-grade solution instead.
You’ll need the right solution and tools to run PostgreSQL in Oracle-compatible mode. EDB Postgres Advanced Server (EPAS) is the only Oracle-compatible solution that allows users to run Postgres in Oracle compatibility mode.
EDB created EDB Postgres Advanced Server (EPAS), an enhanced version of PostgreSQL that includes implementations of many Oracle-specific data types, object types, keywords, procedural language constructs, and other features. We continually evaluate migration challenges and introduce new compatibility features in each major version of EPAS. The solution is designed to mimic Oracle code, drivers, and queries while supporting Oracle features or workloads. These features could reduce code and application rewrites by up to 80%. EPAS also has robust application security to minimize migration risks and delays.
Our fully managed, multi-cloud Postgres solution allows you to focus on building and scaling your business while we undertake the burden of database deployment, administration, and operational tasks. We also ensure high availability and backups of your mission-critical data and provide 24/7 professional assistance.
We’ll first gain visibility over your database estate and map applications to related data schema for assessment. We’ll then define which databases are part of the scope, define ROI that meets the migration strategy, and determine a prioritized migration backlog. For full insight into our iterative approach, click here.
Our professional services team will conduct a free assessment of your schema and data to ensure your database qualifies for a migration to PostgreSQL.