[SOLVED] Issue while migrating data into specific existing PostgreSQL schema using AWS DMS from oracle

Issue while migrating data into specific existing PostgreSQL schema using AWS DMS from oracle

I am trying to migrate oracle data into PostgreSQL database(dev1)/schema (schema_53) using AWS DMS service. Target schema is already created with all objects so I only want to transfer data from oracle to PostgreSQL. However, when I am running migration task I can see a new schema created which is having same name as of source oracle schema , instead of populating data in existing postgres schema.

I have created endpoint by providing URL , port, username, password and DB name of PostgreSQL but I don't see any provision of providing schema name. It seems I am missing some concepts of postgresql.

Can someone please guide me on is it possible migrate data into existing specific schema of PostgreSQL using AWS DMS, as I don't see any option while creating endpoint.

I have run below queries on postgresql DB:

>  ALTER SCHEMA schema_53 OWNER TO dev_53;
>  GRANT CONNECT ON DATABASE dev1 to dev_53;
>  GRANT CREATE ON DATABASE dev1 TO dev_53;
>  GRANT CREATE ON SCHEMA schema_53 TO dev_53;
>  GRANT UPDATE, INSERT, SELECT, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA schema_53 TO dev_53;

      ----------------

Solution

There is an option to set TargetSchema in the task setting. You may try setting TargetSchema as the existing schema you are planning to use. Below is the snapshot of Task setting.

"TargetMetadata": {
        "ParallelApplyBufferSize": 0,
        "ParallelApplyQueuesPerThread": 0,
        "ParallelApplyThreads": 0,
        "TargetSchema": <Your Schema>,
        "InlineLobMaxSize": 0,
        "ParallelLoadQueuesPerThread": 0,
        "SupportLobs": true,
        "LobChunkSize": 0,
        "TaskRecoveryTableEnabled": false,
        "ParallelLoadThreads": 0,
        "LobMaxSize": 15,
        "BatchApplyEnabled": false,
        "FullLobMode": false,
        "LimitedSizeLobMode": true,
        "LoadMaxFileSize": 0,
        "ParallelLoadBufferSize": 0
    }

You may find additional details here. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.TargetMetadata.html