Which tools and approach should be used to meet these requirements?
Use AWS DMS to perform data migration and to automatically create all schemas with Aurora PostgreSQL
Use AWS DMS to perform data migration and use the AWS Schema Conversion Tool (AWS SCT) to automatically generate the converted code
Use the AWS Schema Conversion Tool (AWS SCT) to automatically convert all types of Oracle schemas to PostgreSQL and migrate the data to Aurora
Use the dump and pg_dump utilities for both data migration and schema conversion
Explanations:
AWS DMS is primarily used for data migration, but it does not automatically create all schemas or convert complex database objects like stored procedures. It cannot handle schema conversion and complex object migrations by itself.
AWS DMS is used for data migration, while the AWS Schema Conversion Tool (AWS SCT) can help convert schema and database code. AWS SCT will assist with converting the Oracle database schema to PostgreSQL, but some manual intervention might still be required for complex objects like stored procedures. This approach helps in minimizing downtime.
While AWS SCT can convert Oracle schemas to PostgreSQL, it does not handle data migration. This option misses the key step of migrating data, which is essential for completing the migration.
The dump and pg_dump utilities are used for PostgreSQL-specific data backups and restores. These tools are not designed to handle the migration of Oracle database schema or custom code, making them unsuitable for this migration.
I organize that the answer is:
Use AWS DMS to perform data migration and use the AWS Schema Conversion Tool (AWS SCT) to automatically generate the converted code