Enjoy seamless Oracle migration to an open source platform and compatibility with EDB Postgres Advanced Server
One of the most significant advantages of EnterpriseDB’s (EDB) version of PostgreSQL is its Oracle compatibility and being able to use existing queries and applications with our team of experts.
Download the specific installer or RPM from the EDB site as Oracle compatibility is limited to EDB Postgres Advanced Server (EPAS). A trial version available for 60 days gives full feature access.
Below, we show you how to:
- Set up Oracle compatibility with the installer
- Check an existing cluster for Oracle compatibility
Setting Oracle Compatibility While Installing
Initialize the cluster after installing EDB Postgres Advanced Server. Edit the edb-as-11.sysconfig file to set Oracle compatibility options:
If it isn’t there, add the hash in the line “--no-redwood-compat” to ensure the EPAS instance is Oracle-compatible.
Note: If you have an installed server, use the initialization cluster option (initdb) with “--no-redwood-compat" to create a non-Oracle-compatible server. By default, the instance created with initdb from EPAS is Oracle-compatible.
Checking an Existing Cluster for Oracle Compatibility
Use this query at the SQL prompt to verify if the current installation is Oracle-compatible:
edb=# show db_dialect;
db_dialect
------------
Redwood
If the result is “Redwood,” then it’s Oracle-compatible. If not, the query will error out:
ERROR: unrecognized configuration parameter "db_dialect"
SQL state: 42704
You can also run these queries to determine Oracle-compatibility:
select * from dual;
or
select sysdate;
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 will 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 more comprehensive 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 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 has 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.