PostgreSQL is an open-source relational database management system (RDBMS), which offers an extended range of advanced features compared to Oracle’s DB.
Some of these features include:
- NoSQL functionality - NoSQL is great at handling large quantities of structured, semi-structured, and unstructured data.
- XML support - Native support for the Extensible Markup Language (XML) reduces development requirements when storing XML data.
If your business is interested in these advanced features, then keep reading to find out how you can convert your existing Oracle DB to PostgreSQL in the Azure Cloud.
A pre-migration assessment
Before you can migrate, you will need to perform a comprehensive evaluation of your existing database. This initial assessment is essential, as it can help you determine whether your database is ready in its current state, or whether it requires further development.
At this stage, you will need to undergo the following steps:
Firstly, you’ll need to identify existing data sources using an asset discovery tool. These tools can also determine which features you are currently using with your Oracle DB and whether they comply with PostgreSQL’s codebase.
On the Azure Cloud, Microsoft’s ‘Migration Assessment and Planning Toolkit’ (MAP tool) can generate a checklist for things like virtual machine environment compatibility and storage requirements, allowing you to identify and plan around potential compatibility and storage issues.
One of the most significant benefits of migrating your database to the cloud is converting from Infrastructure-as-a-Service (IaaS) to Platform-as-a-Service (PaaS). The Azure platform offers on-demand scalability and server management that you won’t find using an on-premise database solution.
For this next step, you will need to analyze your existing database and its underlying structure.
On a functional level, Oracle DB and PostgreSQL use a similar codebase. PL/pgSQL is used by PostgreSQL, with MySQL using PL/SQL. There are some differences that you should be aware of when planning a conversion:
- Data type names will often need to be translated. Oracle DB commonly uses the ‘varchar2’ string value; a non-standard SQL implementation that isn’t present in the PostgreSQL codebase.
- PostgreSQL also uses ‘schemas’, instead of MySQL’s ‘packages’ to organize functions into groups. Existing MySQL packages will need to be converted into schemas before they can be used with PostgreSQL.
Both incompatibilities can be automatically resolved using the SQL Server Migration Assistant (SSMA). SSMA can automate the migration of Oracle DB objects and datasets to PostgreSQL, along with the automatic conversion of packages into schemas. After this process is complete, it will then compare and review the new schema to the old database, to identify any discrepancies.
Initiating the migration to Azure
After the planning phase is complete, you can use SSMA to trigger actual migration to the Azure platform:
- The ‘Publish Schema’ option lets you easily publish your converted schema to an Azure SQL Database instance. This Azure PostgreSQL database can be run in an offline mode, allowing you to determine whether any further changes to the schema are needed before going live.
- After verifying that the schema is compliant with the PL/pgSQL codebase, you can then use the ‘Data Migration’ function to migrate your MySQL data to your Azure PostgreSQL cloud database.
- Once this process is complete, you can then initiate ‘Data Synchronization’. This process will require both databases to be running for some time, to ensure that all changes to the source are being replicated to the cloud during development.
Post-migration checks and optimization
After a successful migration, you will need to monitor your new cloud database to ensure that everything is functioning correctly.
- At this stage, you can start redirecting your applications to the new cloud database. This is called Application Remediation. You will need to reconfigure your applications, and check they are functioning correctly.
- Within the Azure Portal, you can also perform Performance Validation and further Optimization. With the SQL implementation being different in PostgreSQL, you may require more or less computing power than you expected. This last activity can also help you reconcile data inaccuracies that may arise after the migration is complete.
Oracle DB to PostgreSQL with Trianz
Trianz is a leading IT consultancy firm with decades of industry experience. As a fully accredited Managed Services Provider for Microsoft Azure, we can help you comprehensively plan and begin your migration to the cloud.
Considering moving from Oracle DB to PostgreSQL? Get support from the expert team at Trianz by using the form below.