# Migrating from Amazon RDS for Oracle to Amazon RDS for PostgreSQL and Aurora PostgreSQL Amazon Relational Database Service (Amazon RDS) for PostgreSQL and Amazon Aurora PostgreSQL-Compatible Edition have evolved as a strong and cost-effective alternatives to Oracle without the need for a software license or a server to manage. The journey from Amazon RDS for Oracle to Amazon RDS for PostgreSQL and Aurora PostgreSQL has never been easier. This guide provides a quick overview of the process and considerations to be made when moving existing workloads to Amazon RDS for PostgreSQL or Aurora PostgreSQL and some of the tools that can assist in the process. It complements a large body of detailed online reference guidance on every aspects of a migration, and serves to provide a birds eye view of the process. This document focuses on migrating custom applications where you control the source code. If you operate a packaged vendor application on Oracle, you must determine if the vendor supports the new platform. **Topics** + [Can My Oracle Database Migrate?](chap-oracle-postgresql.can-my-db-migrate.md) + [Migration Strategies](#chap-oracle-postgresql.migration-strategies) + [The 12 Step Migration Process](#chap-oracle-postgresql.migration-process) + [Future State Architecture Design](chap-oracle-postgresql.migration-process.future-state.md) + [Database Schema Conversion](chap-oracle-postgresql.migration-process.database-schema-conversion.md) + [Application Conversion or Remediation](chap-oracle-postgresql.migration-process.application-conversion.md) + [Script/ETL/Report Conversion](chap-oracle-postgresql.migration-process.script-conversion.md) + [Integration with Third-Party Applications](chap-oracle-postgresql.migration-process.integration.md) + [Data Migration Mechanism](chap-oracle-postgresql.migration-process.data-migration.md) + [Testing and Bug Fixing](chap-oracle-postgresql.migration-process.testing.md) + [Performance Tuning](chap-oracle-postgresql.migration-process.performance-tuning.md) + [Setup, DevOps, Integration, Deployment, and Security](chap-oracle-postgresql.migration-process.deployment.md) + [Documentation and Knowledge Transfer](chap-oracle-postgresql.migration-process.knowledge-transfer.md) + [Project Management and Version Control](chap-oracle-postgresql.migration-process.project-management.md) + [Post-Production Support](chap-oracle-postgresql.migration-process.post-production.md) + [Automation](#chap-oracle-postgresql.automation) + [Platform Differences](chap-oracle-postgresql.platform-differences.md) # Can My Oracle Database Migrate? To quickly see if your workload qualifies as a migration candidate, please use the **DMA Connect Application and Database Questionnaire** to sort out migration obstacles specific to your application. Consider the following questions. The more you answer **No**, the easier the migration to Amazon RDS for PostgreSQL or Aurora PostgreSQL will be. | Application Questions | Comments | | --- | --- | | Are there Oracle dependent parts of the application that you can’t modify by yourself? | If you don’t control all of the code it can be difficult to change the underlying database. | | Is the application commercial off the shelf, and not available for PostgreSQL? | Unless the commercial off-the shelf software (COTS) application also supports PostgreSQL, it will not be able to migrate. | | Does the application use specific methods to connect to an Oracle database such as Oracle Call Interface (OCI)? | Refactoring OCI calls to ODBC is not impossible, but typically an involved process. | | Does the application use Oracle specific libraries? | It could be challenging finding PostgreSQL replacements for Oracle specific libraries. | | Database Questions | Comments | | --- | --- | | Does the database use any third party packages? | It could be challenging finding PostgreSQL replacements for Oracle specific packages. | | Does the database use any data cartridges? | It could be challenging finding PostgreSQL replacements for Oracle specific cartridges. | | Does the application use Oracle Forms or Application Express (APEX)? | Completely replacing Forms or APEX with a non-Oracle solution is substantial. | | Does the database use SQLJ or .NET stored procedures? | You can refactor external stored procedure code for use with PostgreSQL, but it adds development work. | | Does the database use Oracle Streams? | Some refactoring is required to replace Oracle Streams with a PostgreSQL-compatible solution. | | Does the database use Oracle Multi Media? | Some refactoring is required to replace Oracle Multi Media with a PostgreSQL-compatible solution. | | Does the database use Oracle Locator? | Depending on feature use, such a solution may be refactored to work with PostGIS 3.1. | | Does the database use Oracle Java Virtual Machine (JVM)? | Detaching a Java application from Oracle JVM can be involved development work. | | Does the database use Oracle Machine Learning or formerly Advanced Analytics? | The solution will have to be refactored to use similar functionality on AWS. | ## Migration Strategies The options for dealing with a legacy application have often been described as the 6 R’s. For more information, see [6 Strategies for Migrating Applications to the Cloud](https://aws.amazon.com/blogs/enterprise-strategy/6-strategies-for-migrating-applications-to-the-cloud/). + Re-host + Re-platform + Repurchase + Refactor/Re-architect + Retire + Retain This document describes the steps to migrate database instances running on Amazon RDS for Oracle to Aurora PostgreSQL or Amazon RDS for PostgreSQL. This also details out the steps to Re-platform and Refactor the application(s) running on these databases. Re-platforming and re-architecting a database application ranges from modifying the code to work with a different cloud-native database to also adopting other cloud-native operations such as serverless application architectures like Kubernetes. This document deal with the changes necessary to migrate to a new database with pointers to other available documentation. ## The 12 Step Migration Process You may have an Oracle database in Amazon RDS for both production or non-production purposes, and it may just be convenience and familiarity that steered you to Oracle even though there is a licensing cost to this choice. It is certainly easier to continue with the database you know than something new, but sometimes there are few remaining reasons do so. Everyone’s Oracle application is special, and nobody has the same setup and needs for the future. To provide a single framework for database migrations, this guide organizes the work in 12 steps. These steps cover what is in scope for most migrations. You can use these steps in sequence for multiple purposes and you shouldn’t see them as a strictly linear process. You can consider these steps as an overall arch of a migration project where individual steps and activities can be overlapped or swapped to fit specific project conditions. The following image shows the 12 steps with an approximate share of effort in a typical project. ![\[The 12 Step Migration Process\]](http://docs.aws.amazon.com/dms/latest/sbs/images/12-step-migration-process.png) Each step will be described at a high level in order to allow the reader to skip to relevant topics in the following chapters. 1. **Future State Architecture Design** The understanding of the current design and its requirements together with those of the future state are addressed here with deployment diagrams and feature or component mappings for things that will change as a result of the migration. This step defines the scope and architectural view of the migration. 1. **Database Schema Conversion** Because we are migrating a database application from Oracle to Amazon RDS or Aurora, the database schema needs to change. Subtle differences in functionality and syntax need to accommodate the new platform and comprehensive tooling exists to automate this step. In this step we include replacements for any Oracle specific database feature which works differently on PostgreSQL. 1. **Application Conversion or Remediation** The Oracle application may be implemented in any programming language like Java or C\$1, and often abstracts from the nature of the underlying database through an object relational model (ORM). But it is also common to have some reliance on the database syntax directly in the application code, and this step covers the necessary changes to the application code to work with Amazon RDS for PostgreSQL. In this step we also include operating system dependencies like direct file access which may need to change on the new platform. 1. **Script/ETL/Report Conversion** An Oracle application may move data in and out sideways in addition to the application for the purpose of reporting or data import/export. This can happen by executing a stored procedure or through external scripting and SQL\$1Loader. Such scripts and PL/SQL statements and the operational framework will need to be modified to work with PostgreSQL. 1. **Integration with Third-Party Applications** Few applications are islands and often connect to other applications and monitoring. These dependencies may be affected by the move to the PostgreSQL database platform. The monitoring of the database may need to use native AWS tools or the third-party applications use Oracle specific means of communication. These dependencies may already support PostgreSQL or suitable replacements will need to be found. 1. **Data Migration Mechanism** As we move from one database platform to another the data needs to move as well. This will happen several times through the migration, first for testing purposes and later for production cutover. If there are multiple customers of the database application they may need to be migrated at different times once the application has been migrated. 1. **Testing and Bug Fixing** Migration touches all the stored procedures and functions and may affect substantial parts of the application code. For this reason good testing is required both at the unit and system functional level. 1. **Performance Tuning** Due to database platform differences and syntax, certain constructs or combinations of data objects may perform differently on the new platform. The performance tuning part of the migration resolves any bottlenecks that might have been created. 1. **Setup, DevOps, Integration, Deployment, and Security** How the application is put together and deployed may be impacted by the migration, and many customers take the opportunity to embrace infrastructure as code for the first time in the context of a migration. In this step we also focus on the impact to application security. In this step we also address cutover planning. 1. **Documentation and Knowledge Transfer** In order to support the application going forward it may be necessary to document the changes that happened to the application and the operational environment. Maintenance of the application will have been impacted by the change of database and certain application behavior may have changed. This is especially important if the migration is done by a different team from those maintaining the application. 1. **Project Management and Version Control** A migration certainly involves people with different skills and often entirely different teams, and maybe an outside party. A successful project needs to be well planned and coordinated to execute on a predictable schedule. Version control is a crucial foundation for a migration since database code may not be managed in the same way as application code. 1. **Post-Production Support** After the application is live, the migration team may need to stay around for a while to address any emerging problems on the new platform that were not caught by testing. ## Automation This document references the freely available AWS Schema Conversion Tool (AWS SCT) for code conversion and the AWS Database Migration Service for data migration. For more information, see [Installing, verifying, and updating Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html) and [Database Migration Service](https://aws.amazon.com/dms/). # Oracle application future state architecture design When you migrate an Oracle application to use a different database like PostgreSQL you must capture the architecture of the existing application to ensure that all considerations are covered, we call that the current state architecture. The current state architecture describes the part of the application that matters to the migration from an architectural point of view. The same is true for the future state architecture which takes the new database platform into account. We don’t need to describe everything, but some things, like external dependencies, are very important, and help us determine what work to do. You may already have some favorite drawing tools for architecture diagrams such as Lucidchart, Visio or the freely available [Diagrams.net](http://diagrams.net/) which are all great choices as they supports AWS infrastructure symbols along with many others to describe the current and future environment. But the tool is less important than what is captured in the diagrams. The architecture diagrams also serve the important role of defining the context of what is inside and outside the scope of work as a team collaborates on the task. ## Current State Architecture There may be existing documentation on the database application which should be examined for currency and relevance. Let us review what is important for the migration work before we decide if more documentation is needed. A **network diagram** is useful because It typically connects servers to each other, and servers to databases. It may also show the division into multiple availability or disaster recovery zones. This is useful because it shows potential server and network dependencies that must be addressed in the new architecture. A network diagram may also highlight important security considerations like multiple networks and internet connectivity. A **component diagram** is useful if the application is comprised of multiple parts using different technologies which each may present the migration with their own challenges to address. A **class diagram** is useful if it shows a specific persistence layer or a query factory where the migration can focus while leaving the rest of the application untouched. A **data flow diagram** is useful because it directly shows parts of the value chain of information flowing inside and outside the application highlighting what additional code may needs to be changed. The following image shows a simple network diagram that can help easily communicate current architecture. ![\[A simple network diagram\]](http://docs.aws.amazon.com/dms/latest/sbs/images/oracle-postgresql-current-architecture.png) ## Future State Architecture The future state architecture envisions the application using the new database, and potentially other services in the environment. It’s a new version of the current state architecture diagrams with certain parts replaced with the new components. This document will focus mainly on replacing Amazon RDS for Oracle with Amazon RDS for PostgreSQL or Aurora PostgreSQL. ## Transition Architecture Depending on how involved your migration is, you may need a transition architecture by which we mean, infrastructure that is there only for migration purposes. Examples of transition architecture includes AWS DMS servers and other mediating or transformation platforms. Such infrastructure has to be provisioned, secured and removed after the migration to avoid additional vulnerability and cost. The following image shows a transition architecture diagram. ![\[A transition architecture diagram\]](http://docs.aws.amazon.com/dms/latest/sbs/images/oracle-postgresql-transition-architecture.png) For more information, see [AWS Well-Architected Framework](https://docs.aws.amazon.com/wellarchitected/latest/framework/welcome.html). # Oracle database schema conversion Relational databases contain a tabular structure for data using basic data types and procedural code in the form of triggers, functions and stored procedures. Oracle uses the PL/SQL dialect which is different from the PL/pgSQL dialect of PostgreSQL and while some table definitions and queries may look the same, others will require modification. Doing so manually would be a substantial task, but fortunately there are freely available AWS tools to automate this job (See Automation section in the Introduction). The AWS Schema Conversion Tool (AWS SCT) is capable of connecting to Oracle and reading all PL/SQL code directly from the source Oracle database and converting it to PostgreSQL PL/pgSQL. AWS SCT will retrieve the DDL for tables, views, triggers, stored procedures and functions from the database, parse it and generate the equivalent PostgreSQL code. Based on experience, AWS SCT fully converts 90\$1% of the database code which leaves less than 10% for the database expert to improve. ## Process At a high level, the database conversion process works like this: + Download and install AWS SCT (Linux or Windows). + Download and install Oracle database drivers (you probably have those already). + Download and install the PostgreSQL database drivers for Amazon RDS or Aurora PostgreSQL. + Run AWS SCT and create a migration assessment report. For more information, see [Creating migration assessment reports](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_AssessmentReport.html). + Run AWS SCT and automatically convert the database code. For more information, see [Converting database schemas](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Converting.html). + Fix any warnings and error in the database code conversion. AWS SCT operates with default assumptions about mappings between Oracle and PostgreSQL which may or may not be optimal for your particular application due to the data you have in the database. Certain data type mappings may need to be changed to ensure good performance. As an example, a NUMBER datatype in Oracle is an extremely versatile container which without further qualification may be too expensive for the application. In this case you would look at the type of data contained in the NUMERIC column and its requirements for precision and scale, and then determine the best match for that in PostgreSQL with the appropriate precision and scale. Once AWS SCT has automatically converted the DDL code, the developer needs to investigate any warnings and errors which need manual remediation. Warnings and Errors can happen for many reasons. AWS SCT does not have 100% coverage of all syntactical situations, and code inside the database can be corrupted or encrypted preventing AWS SCT from reading it. In these situations, the output DDL code is marked up with comments about the problem AWS SCT had with conversion, and ask the developer for help. ## Exceptions There are exceptions to the automatic code conversion by AWS SCT like SQLJ, .NET Stored Procedures, Spatial data, RDF Graphs. But in each case there are good candidate replacement features like Lambda functions, PostGIS and Neptune. ## Interactive and Batch Modes AWS SCT offers both an interactive GUI and a command line interface (CLI) which are useful in different situations. The user interface is good in a more interactive situations where the user needs to explore the schema and perhaps select only part of it for conversion. The CLI is good for automation in situations where DDL code might be coming from a different source such as reports. For more information, see [Script/ETL/Report Conversion](chap-oracle-postgresql.migration-process.script-conversion.md). ## Schema Drift If the original database schema changes during the timeframe of migration, this can be detected in AWS SCT which can compare the old and the new database schema and highlight the object that need to be updated. If an object was converted 100% or with few manual changes, that object can be converted again and remediated. For more information, see [AWS Schema Conversion Tool User Guide](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/Schema-Conversion-Tool.pdf), [Oracle Database 19c To Amazon Aurora with PostgreSQL Compatibility (12.4) Migration Playbook](https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.html), and [AWS Schema Conversion Tool CLI and Interactive Mode Reference](https://s3.amazonaws.com/publicsctdownload/AWS+SCT+CLI+Reference.pdf). # Oracle application conversion or remediation The Oracle application may be written in a variety of languages like C\$1\$1, C\$1 and Java, each with their own patterns for calling Oracle. A common case is the use of an object relational model (ORM) between the application code and the database which reduce the amount of PL/SQL that needs to be changed. Examples include Entity Framework and Hibernate which are also supported on PostgreSQL. Oracle uses the PL/SQL dialect which is different from the PL/pgSQL dialect of PostgreSQL and while some table definitions and queries may look the same, others will require modification. Doing so manually would be a substantial task, but the freely available AWS Schema Conversion Tool (AWS SCT). AWS SCT is capable of identifying and replacing embedded PL/SQL in the application code with the equivalent PostgreSQL code. For more information, see [Automation](chap-oracle-postgresql.md#chap-oracle-postgresql.automation). In addition to using AWS SCT, you must also examine the source code for possible issues like: + Specific ORM or other data access framework and versions or in use and confirm its compatibility with the target engine. + Modify database connection as appropriate for the new engine. + Modify any table/entity mapping configuration or code as appropriate for the converted schema. + Identify and refactor any vendor-specific driver functionality in use in the code. ## Process At a high level, the application conversion process works like this: 1. Perform the database conversion. This is necessary because the PL/SQL conversion needs to know the schema of the database. For more information, see [Database Schema Conversion](chap-oracle-postgresql.migration-process.database-schema-conversion.md). 1. Run AWS SCT and automatically convert the application code. For more information, see [Converting application SQL](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Converting.App.html). 1. Fix any warnings and errors in the application code conversion. ## Exceptions There are exceptions to the automated application code conversion process If the application uses the native Oracle Call Interface (OCI). In this case the developer must refactor the code to use ODBC or JDBC. # Database migration script/ETL/report conversion ETL is an acronym that stands for Extract, Transform and Load. The ETL process plays a central role in data integration strategies. ETL allows businesses to gather data from multiple sources and consolidate it into a single, centralized location. ETL also makes it possible for different types of data to work together. ELT is similar to ETL. However, the primary difference between them is that the data transformation processes occur after the Raw data from the source have been extracted and loaded into a staging area. The transformation of the data may occur in the destination database or in the middle tier or via serverless tools that might reduce the cost of the data processing. Transforming the data is a critical process that may provide significant value to the data. It’s also the stage where the data could be cleansed, standardized, deduplicated, verified, sorted, shared, and much more. The role of ELT or ETL in database migration projects is critical for any successful migration. For the remainder of this document, ETL will also refer to ELT patterns. ETL can be implemented in the database itself, in external scripts or in third-party tools such as Informatica, Talend, and so on. If the ETL is done using Oracle stored procedure, the freely available AWS Schema Conversion Tool (AWS SCT) is capable of converting the ETL code to AWS Glue. For more information, see [Automation](chap-oracle-postgresql.md#chap-oracle-postgresql.automation). ## Process for Conversion to AWS Glue If Python/Glue is a desired future state architecture for ETL code, and the ETL is implemented in the database, the conversion process works like this: 1. Perform the database conversion. This is necessary because the PL/SQL conversion needs to know the schema of the database. For more information, see [Database Schema Conversion](chap-oracle-postgresql.migration-process.database-schema-conversion.md). 1. Run AWS SCT, select the code involved in ETL and automatically convert the ETL code to AWS Glue. For more information, see [Converting ETL processes](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP-converting-aws-glue.html). 1. Fix any warnings and errors in the ETL code conversion. ## Process for Conversion of Stored Procedures If ETL or report process is implemented in the database, then the database conversion takes care of converting the code, and only the method to call the stored procedures need to change. ## Process for Conversion of Scripts, Reports, and Third-Party ETL If the ETL or Report code is available in scripts or hosted in third-party tools and those tools will be used in the future, then a custom process will have to be implemented: 1. Perform the database conversion. This is necessary because the PL/SQL conversion needs to know the schema of the database. For more information, see [Database Schema Conversion](chap-oracle-postgresql.migration-process.database-schema-conversion.md). 1. Extract PL/SQL statements from the third-party ETL or reporting tool into flat files, unless already available. 1. Write YAML configuration files for AWS SCT CLI to convert external files. 1. Run AWS SCT CLI on the external scripts using the YAML configuration files. For more information, see [AWS Schema Conversion Tool CLI and Interactive Mode Reference](https://s3.amazonaws.com/publicsctdownload/AWS+SCT+CLI+Reference.pdf). 1. Fix any warnings and errors in the ETL or report code conversion. 1. Insert the converted PL/pgSQL code back into the third-party ETL or reporting tool, unless they stay as flat files. # Oracle application migration and integration with third-party applications Few applications are islands and your Oracle application is likely to integrate with other applications that are not themselves going to be migrated. Examples include ETL, reporting, and monitoring applications for alerts and logs. For more information, see [Script/ETL/Report Conversion](chap-oracle-postgresql.migration-process.script-conversion.md). If these third-party applications connect directly to the Oracle database, they are going to be affected by the migration. If they are packaged applications, the vendor may offer support for Amazon RDS and Aurora PostgreSQL and if they are custom, you may need to modify them to work with the migrated application. There are a wealth of resources on the partner network which complement any solution from AWS. AWS native tools such as [Amazon Simple Notification Service](https://aws.amazon.com/sns/), [Amazon RDS Performance Insights](https://aws.amazon.com/rds/performance-insights/), [Amazon CloudWatch](https://aws.amazon.com/cloudwatch/), and [Amazon Relational Database Service](http://aws.amazon.com/rds) are already integrated with the Amazon RDS and Aurora PostgreSQL database platform and are recommended for a full picture of the ongoing performance. For more information, see [Engage with Amazon Web Services Partners](https://partners.amazonaws.com/). # Amazon RDS for Oracle data migration mechanism For testing purposes and for production cutover, data needs to be migrated from the old Amazon RDS for Oracle instance to the new Amazon RDS or Aurora PostgreSQL instance. Such a data migration requires knowledge of data type mapping and possibly incremental loading, depending on the size of the data and migration window. For this purpose AWS Database Migration Service (AWS DMS) can be used to connect source and target databases to replicate the contents of the data in the most optimal way. ## Process 1. Create a replication server. 1. Create source and target endpoints that have connection information about your data stores. 1. Create one or more migration tasks to migrate data between the source and target data stores. After you configured AWS DMS, you can perform the following operations: + A full data migration from Oracle to PostgreSQL. + An ongoing replication from Oracle to PostgreSQL. Depending on the type of data in the database, you may need to optimize AWS DMS for handling certain data types like LOBS which you can read more about in the product guidance. ## Reverse Migration Normally you just fall back to the old system if a migration fails during smoke testing, and in most cases you may decide to fix forward after cutover, in which case you fix any unforeseen bugs in the migrated system. But in some cases you may decide to have the option of migrating production data back from the new system to the original system after having been in production for a time. In those cases, a reverse data migration mechanism must be configured. ![\[Reverse Migration\]](http://docs.aws.amazon.com/dms/latest/sbs/images/oracle-postgresql-reverse-migration.png) For more information, see [What is Database Migration Service?](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) and [Migrating Oracle databases with near-zero downtime](https://aws.amazon.com/blogs/database/migrating-oracle-databases-with-near-zero-downtime-using-aws-dms/). # Oracle database migration testing and bug fixing Testing can be manual or automated. We recommend that you use an automated framework for testing. During migration, you will need to run the test multiple times, so having an automated testing framework helps speed up the bug fixing and optimization cycles. ## Unit Testing Unit testing at the data level after migration can range from comparing every last bit of data in source and target by comparing extracted CSV files, but more realistically, custom aggregation queries should be constructed to incorporate large amounts of the migrated data and compare the results. Unit tests validate individual units of your code, independent from any other components. Unit tests check the smallest unit of functionality and should have few reasons to fail. Database objects need to be validated after migrating the DDL of an Oracle database to PostgreSQL. Database objects includes packages, tables, views, sequences, triggers, primary and foreign keys, indexes, constraints. A typical way to perform unit testing on the converted database is to script out calls to stored procedures and functions and compare the returned data with external tools such as standard Linux/Unix tooling of diff. The application needs to be validated with new and existing test case scenarios based on documented changes on database objects such as field names, types, minimum and maximum values, length, mandatory fields, field level validations etc. ## Functional Testing Functional testing of the application is done by exercising user stories and comparing the results on the source and target system. This is typically a manual process, but third-party tools do exist to make automated regression tests of the UI (e.g. Selenium). Functional testing of the database is largely done through the application, but there may be additional direct database use cases that can only be done directly on the database such as ETL for imports and extracts. In these cases, the data can be compared automatically before and after using standard Linux/Unix tooling like diff on extracted CSV files for example. Functional testing of reports involve visual inspection to see that all fields are correctly displayed and comparison of the semantic values between the old and the new reports. ## Load Testing In order to stress the migrated system and test its performance you may perform load testing which is typically done on a system that is scaled the same as production and requires a means of simulating load on the system. It is sometimes limited to running specific well-known expensive operations rather than user traffic. ## Standard Operating Procedures Standard Operating Procedures (SOP) may be affected by a migration of database application. Database management procedures change when going to PostgreSQL and some procedures may be unnecessary when going to the highly managed Aurora PostgreSQL. In any case, all existing operational procedures need to be tested and their language updated to reflect the new environment. For more information, see [Managing Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Managing.html). ## Monitoring Monitoring of the database will be affected by the migration and some metrics may change which could affect how SLA is monitored. The way operational staff go from detecting a problem to diving into the underlying details may be affected. For more information, see [Monitor Amazon RDS for PostgreSQL and Amazon Aurora for PostgreSQL database log errors and set up notifications using Amazon CloudWatch](https://aws.amazon.com/blogs/database/monitor-amazon-rds-for-postgresql-and-amazon-aurora-for-postgresql-database-log-errors-and-set-up-notifications-using-amazon-cloudwatch/). ## Cutover Cutover procedures are the planned event where everything goes the way you want, but it still needs to be tested. ## Fallback Fallback is when you have both old and new systems in sync with the new one operating as primary and you decide to switch back to the original which is still in sync. ## Rolling back the Oracle database migration to PostgreSQL Rollback is usually the scenario when you don’t have an ongoing replication mechanism to keep old and new in sync, so in the event of a no-go decision during the cutover, you abandon the new system and go back to the original. ## Migrate Back In some rare cases, you may decide to include the option of migrating production from the new system back to the old system after having the cutover. If you include this scenario, it must be tested. For more information, see [Testing Amazon Aurora PostgreSQL by using fault injection queries](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Managing.FaultInjectionQueries.html), [Automate benchmark tests for Amazon Aurora PostgreSQL](https://aws.amazon.com/blogs/database/automate-benchmark-tests-for-amazon-aurora-postgresql/), [Validating database objects after migration](https://aws.amazon.com/blogs/database/validating-database-objects-after-migration-using-aws-sct-and-aws-dms/), and [Validate database objects after migrating from Oracle to Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/validate-database-objects-after-migrating-from-oracle-to-amazon-aurora-postgresql.html). # Oracle database migration performance tuning Any migration is likely to slightly change the performance of individual queries in the application and in stored procedures and functions. Depending on the context, those small differences may not matter in reality. But it is a good idea to deliberately compare the performance of operations that are known to be slow in the original system because any difference in performance is likely to be greater. Such testing is usually confined to specific long running ETL jobs and reports. Other performance issues may show up during functional or load testing and will be addressed as bugs. # Oracle dabatase migration to PostgreSQL setup, DevOps, integration, deployment, and security Deployment to production is the culmination of the migration activity and is a high stakes effort which requires good planning and benefits from well tested automation. With DevOps, you can create a process that helps easily deploy and update your virtual architecture in a scalable and repeatable way. This reduces the risk of human error. Furthermore, DevOps allow us to deploy much faster than humans which may be a factor in large deployments. ## Wave Planning For any application or cluster of applications there is an important question of sequencing because every cutover window, for example, a weekend can only accommodate so much work. This means that larger portfolio may need to be migrated in multiple waves, and this makes wave planning necessary. Wave planning considers that some parts of the application will move while other stay behind under different network, connectivity and security conditions. Different parts of the application may also be under different ownership, so wave planning becomes the place where all stakeholders coordinate their efforts. Wave planning is a matter of minimizing risk during the overall migration. ## Infrastructure Automation Infrastructure automation is a code layer that wraps API calls to a cloud provider with commands to provision infrastructure. Such code typically in YAML scripts are easily learned with any coding experience and are scalable and powerful. This layer will allow you to spin up one or one hundred web nodes nearly simultaneously. This layer is not designed to configure files on a server, install software on a server, or run commands on a server - That comes in the next section. [Terraform](https://www.terraform.io/) represents a cross cloud incarnation of this idea. The downside of Terraform is that its cross-cloud and open source nature makes it slower to adopt new features and provide detailed provisioning, often months after a new feature or configuration is released. [CloudFormation](https://aws.amazon.com/cloudformation/) is a native AWS language in JSON or YAML format. Use AWS CloudFormation to write infrastructure code more specifically to specific features because it doesn’t have to work with other clouds. ## Configuration Management Configuration management systems manages configuration of software and state of files on a server or group of servers. These systems however are capable of much more than that, they also allow functionality such as installing software, running local commands, starting services and more in the same scalable way. [Ansible](https://www.ansible.com/) is a lightweight and easily installed tool that is configured using YAML files. It doesn’t require a local daemon to be installed on the instances that Ansible is managing. The way that ansible does all this is through open source functions that essentially wrap cli commands that are run on remote hosts over Secure Shell Protocol (SSH). This allows for a plethora of functionality from database manipulation to package instillation through simple changes in pre-written functions at the YAML level. Beyond a large library of open source function one can easily write custom functions (in python) or simply use the cli function to run any cli command through ansible remotely and in a scalable fashion. Some environments could be prevented from using Ansible due to limited or highly restricted SSH access to resources due to security protocols and standards. [Puppet](https://puppet.com/) works in a primary and secondary system that communicates over https (443) and is configured using its own language called [puppet](https://puppet.com/docs/puppet/7/puppet_language.html). It’s often found in enterprise level deployments, configuration management platforms based on a locally deployed daemon called a node. Puppet differs from other similar platforms like Chief is its methodology in regards to how a resource acquires a desired state. Puppet takes a declarative approach, which is to say it defines the end state it requires, but makes not design on how it is achieved. Due to its fairly high level of technical investment in regards to its programming language, puppet is generally not recommended for smaller deployments as the investment. [Chef](https://www.chef.io/) has a lot in common with puppet like a similar primary and secondary model, they both communicate over https, and are configured using a programming language. Where they differ is in terms of how they handles state. Chef takes an imperative approach, which is to say you as the end user have nearly full control on how a resource acquires a desired state which it achieved through Ruby as its configuration system. This type of deployment provides more flexibility as well as being easy to adopt if you are already using Ruby. ## Code Repository A code repository offers safe storage of code and a change capture log which facilitates parallel development of a codebase by many developers simultaneously with the use of code branches, and integration with CI/CD pipelines. Other files than application source code might be stored in a Git repository such as infrastructure as code (IaC), database reports; recorded state changes or even short logs. Importantly you should never store credentials in the code repository. Credentials should be handled in other ways discussed below to avoid sensitive files being deleted or scrubbed, remnants left behind of the original state. The two most widely used code repositories are [GitHub](http://github.com/) and [GitLab](http://gitlab.com/) with similar features and functionality. ## Secrets Management A vault is for credentials. There are several options when it comes to Vault, many of which are baked into a lot of the technologies already described. Ansible has ansible vault, Jenkins has a functionality for storing and parametrize credentials which can be used at a smaller scale as a vault. However most of these baked in vaults don’t generally have the effectiveness and capabilities of a dedicated vault. [HashiCorp Vault](https://www.vaultproject.io/) is a dedicated vault that differs from a secrets manager that comes packaged with another product is its varied capabilities around secret management. HashiCorp is an industry leader in this regards with capabilities such as dynamic secrets that can be generated on the fly for database or application credentials, data encryption, leasing and renewal which always credentials to be expired and rotated as well as the ability to revoke credentials remotely. In general, if an enterprise requires a wide range of credentials stored across a range a technologies, Vault is generally a good option to start. ## Orchestration Orchestration is the glue that binds DevOps together. Many of the described technologies can be executed manually or from a scheduled script, however building on this idea of removing one of the largest points of failures such as humans from the actual deployment and management of infrastructure we can eliminate many of the pitfalls that can arise in the process of executing deployment scripts. Orchestration allows you to create a repeatable timeline, with logic gates, to deploy your infrastructure in exactly the order with the configurations chosen. This also allows deployments themselves to be tested before a change or deployment to production. Each of the configuration management platforms discussed above generally have an orchestration platform, they are generally focused on scheduling jobs within their particular vertical. For example, AWX for Ansible is mostly limited to scheduling ansible jobs. [Jenkins](https://www.jenkins.io/) is managed through a GUI running on a primary node that is deployed on a resource within the company. There is also functionality to allow secondary nodes deployed on micro services for larger scale. The process is arranged in a series of [Groovy](http://groovy-lang.org/semantics.html) files called Jenkinsfile that dictate what action will be taken during each step in the pipeline. These jobs then can be scheduled jobs that periodically run and kick any job, from a ansible jobs that runs periodically to prevent configuration drift, to reporting jobs that execute a series of database calls. Generally Jenkins can connect any modern codebase and technology together. An example pipeline you might run on Jenkins: use git to pull configuration scripts for the pipeline, then use a Terraform job from the cloned codebase to deploy a server, use Ansible to install and configure a database, then push a status file with information on the pipeline run back to git all while using Vault to manage the secrets for both access right for Jenkins and configuration of users on the database. ![\[Orchestration\]](http://docs.aws.amazon.com/dms/latest/sbs/images/oracle-postgresql-orchestration.png) # Oracle dabatase migration to PostgreSQL documentation and knowledge transfer As a result of the migration, the operation of the database and the future development of the application and database will have been affected due to infrastructure and technology changes. If the migration is done by a separate team, it is vital that these changes be documented. There may be a need for additional PostgreSQL training to operate the database and develop for PostgreSQL going forward. # Oracle dabatase migration to PostgreSQL project management and version control Experience tells us that the steps take different amounts of efforts across a typical project. When planning a migration project, tooling like AWS SCT can provide an important data point in the form of how much manual work needs to be done to fix database and application code that was not automatically converted. Using the above rules of thumb shares of the overall project, an initial plan can be created. # Oracle dabatase migration to PostgreSQL post-production support After production cutover, there are a few possibilities for what can happen. You will have either decided to fix forward as the old system is being decommissioned, or you have decided to way a certain amount of time, a bake-in time, with production on the new system, during which a decision to abandon the new system can be made. Abandoning the new system has the following flavors: + Roll back, all new data is lost. + Roll back and reapply all new transactions. + Roll back and migrate new production data back. + Maintain a live replication back to the old system until bake-in period is over. During this time, defects are tracked and triaged for possibly triggering the rollback or being fixed forward. Help desk will have been trained in the new system differences and will be able to detect if an end user inquiry just requires training or it may be a defect. Beyond acceptance migration criteria, the application may have well defined KPIs defined already which can be observed when in production on the new system and compared to historical KPIs. For more information, see [How to Migrate Your Oracle Database to PostgreSQL](https://aws.amazon.com/blogs/database/how-to-migrate-your-oracle-database-to-postgresql/). # Oracle and PostgreSQL platform differences This section discusses some of the differences between Oracle and PostgreSQL to illustrate opportunities and challenges with migrating an Oracle application. This overview is by no means an exhaustive, however these are common challenges you may encounter when administering PostgreSQL after a background with Oracle. ## Range and List Partitions Along with possible performance difference, architecturally partitions on Oracle and PostgreSQL act quite differently. On Oracle you can define a Range, for example each month or year being a partition, or a list, where every occurrence of say the letter “N” or “Y” in a char field is partitioned at the table definition level and PostgreSQL handles these operations differently. PostgreSQL operates with a “parent” table that holds no data and a “child” table that defines the partitions themselves and holds the data. The parent table is created first, the child tables is then defined with corresponding constraints to create the partition. You must supply a trigger to insert into the parent table and have the data be routed to the correct partition. For more information, see [Strategy for Migrating Partitioned Tables from Oracle to Amazon RDS for PostgreSQL and Amazon Aurora with PostgreSQL Compatibility](https://aws.amazon.com/blogs/database/strategy-for-migrating-partitioned-tables-from-oracle-to-amazon-rds-postgresql-and-amazon-aurora-postgresql/). ## Data Types Some PostgreSQL data types are much easier to work with than their corresponding Oracle types. For example, the Text type can store up to 1 GB of text and can be handled in SQL just like the char and varchar fields. They don’t require special large object functions like character large objects (CLOBs) do. However, there are some important differences to note. You can use the `Numeric` field in PostgreSQL to map any `Number` data types. But when you use it for joins (such as for a foreign key), it is less performant than using an `int` or `bigint` data type. This is a typical area where custom data type mapping should be considered. The PostgreSQL Timestamp with time zone field is slightly different from and corresponds to the Oracle Timestamp with local time zone. These small differences can cause either performance issues or subtle application bugs that require thorough testing. For more information, see [Migration tips for developers converting Oracle and SQL Server code to PostgreSQL](https://aws.amazon.com/blogs/database/code-conversion-challenges-while-migrating-from-oracle-or-microsoft-sql-server-to-postgresql/). ## Transaction Control and Exception Handling PostgreSQL Multiversion Concurrency Control (MVCC) is very different from Oracle rollback segments, even though they both provide ACID transactions. PostgreSQL creates a snapshot state taken at the start of the transaction, and essentially copies the data to a temporary page while the transaction is in flight. This can affect both the queries and the application, as well as hardware considerations. Because open transactions require temporary space to hold their snapshot state during the transaction, a workload that requires many open transactions in addition to possible deadlocks, needs to consider the location and parameters surrounding temporary table space. Unlike Oracle, PostgreSQL uses auto-commit for transactions by default. However, there are two options to support explicit transactions, which are similar to the default behavior in Oracle (non-auto-commit). You can use the `START TRANSACTION` or `BEGIN TRANSACTION` statements and then `COMMIT` or `ROLLBACK`; or you can simply set `AUTOCOMMIT` to `OFF` at the session or system level. PostgreSQL does not allow transaction control inside of PL/pgSQL like commit or roll back inside a stored procedure. The caller must perform the transaction management. If your existing PL/SQL contains explicit commit and rollback code it must be modified. When a run-time exception has occurred in a transaction, you must roll back that transaction before you can execute any new statement on the connection. Finally, exception handling in PL/pgSQL, using a `BEGIN…EXCEPTION…END` block to let your code catch any errors that occur. This block automatically creates a savepoint before the block, and rolls back to that savepoint when an exception occurs. You can then determine what logic to execute based on whether there was an error. Exception blocks are expensive however, due to the created savepoint. If you don’t need to catch an error, or if you are planning to simply raise the error back to the calling application, don’t use the exception block at all. Let the original error flow up to the application. For more information, see [Oracle Database 19c To Amazon Aurora with PostgreSQL Compatibility (12.4) Migration Playbook](https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.html).