{"id":17188,"date":"2022-05-19T09:32:15","date_gmt":"2022-05-19T07:32:15","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/helvetia-used-aws-sct-dms-to-migrate-to-aws-rds-for-postgresql\/"},"modified":"2022-06-22T15:09:26","modified_gmt":"2022-06-22T13:09:26","slug":"helvetia-used-aws-sct-dms-to-migrate-to-aws-rds-for-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/helvetia-used-aws-sct-dms-to-migrate-to-aws-rds-for-postgresql\/","title":{"rendered":"Helvetia used AWS SCT & DMS to migrate to AWS RDS for PostgreSQL"},"content":{"rendered":"

One of our long term-time customers, Helvetia<\/a>, successfully migrated on-prem Oracle databases to AWS<\/a>, not only because of the licenses, but more importantly: to deploy faster, innovate faster, and use the state-of-the-art open source database system.<\/b>
\n\"\"<\/a>
\n\"\"<\/a>
\nWhen you plan such a project, you need to know which tools you want to use and what the target architecture shall look like. There are several options to choose from but finally Helvetia decided to use the AWS native services AWS DMS<\/a> and AWS RDS<\/a> for PostgreSQL.<\/p>\n

<\/p>\n

AWS DMS<\/a> gives you the option to initially populate the target instance from the source, and right afterwards logically replicates ongoing changes from the source to the target. However, before you can do that, you need the schema to be ready in the target. To prepare this, there is AWS SCT<\/a>. This is not an AWS service, but a free tool you can use to convert a schema from a database system to another. If you want to go from Oracle to PostgreSQL, this tool also performs an automatic conversion from Oracle’s PL\/SQL<\/a> to PostgreSQL’s PL\/pgSQL<\/a>. Although this tool does a great job, you have to be very careful with the result, and invest a good amount of time in testing. Autonomous transactions<\/a>, for example, do not exist in PostgreSQL and the AWS schema conversion utility implements a workaround using database links<\/a>. This can be fine if you rarely use it (because it needs to establish a new connection) but if you rely heavily on this feature, you’d better re-implement in a way that is native to PostgreSQL.<\/p>\n

Another area you need to pay attention to are the data types. PostgreSQL comes with many of them<\/a>. A NUMBER<\/a> in Oracle can mean many things in PostgreSQL. It could be an integer or a numeric in PostgreSQL. Depending on what you go for, this comes with space and performance impacts in PostgreSQL. PostgreSQL comes with a boolean<\/a> data type. In Oracle, this is usually implemented as a character or a numeric value. Do you want to keep it that way or do you want to convert to a boolean? Converting means that you also need to adjust the business logic in the database.<\/p>\n

Another issue that took quite some to solve was this<\/a>. The very simplified test case attached to the initial Email showed massive performance drops in PostgreSQL compared to Oracle. The reason is that Oracle’s PL\/SQL is a compiled language and PostgreSQL’s PL\/pgSQL is interpreted. If you have a case that more or less matches what is described in the thread linked above, you need to re-write this. The same applies when you have commits or rollbacks in PL\/SQL functions<\/a>. PostgreSQL does not allow you to commit or rollback in a function. You need to use procedures<\/a> for that.<\/p>\n

These are just a few hints of what might come along the way when migrating to AWS RDS for PostgreSQL. Once you have solved all this, the migration can be really smooth and will most probably be a success. Here are some posts that describe how to set this up using an Oracle sample schema as the source:<\/p>\n