Configure the Target Database


During the full load process, AWS DMS as default does not load tables in any particular order, so it might load child table data before parent table data. As a result, foreign key constraints might be violated if they are enabled. Also, if triggers are present on the target database, they might change data loaded by AWS DMS in unexpected ways. To overcome this, we drop the constraints on the target database.

  1. Open pgAdmin V4 from within the EC2 server task bar at the bottom of the screen (look for elephant head), and click on that/open it. If it asks for a pgAdmin password just provide one like password or pgadmin. Close the warning about a new version of pgAdmin.

    \[SqlDms04a-PG\]

  2. Click on Server in the left pane then right Click and highlight Create then highlight Server and create a new database connection for the target Aurora PostgreSQL database using the following values:

    \[SqlDms04-PG\]

    Parameter Value
    General Tab -> Connection Name Target Aurora RDS (PostgreSQL)
    Connection Tab -> Host Name <TargetAuroraPostgreSQLEndpoint>
    Connection Tab -> Port 5432
    Connection Tab -> Maintenance DB AuroraDB
    Connection Tab -> Username dbmaster
    Connection Tab -> Password dbmaster123

    After you can click Save and it will connect you. \[SqlDms04-PG\]

  3. Click on the Target Aurora RDS (PostgreSQL) from the server list of PostgreSQL Connections in PGAdmin then drill into the schemas and select your schema and look at the tables like mlb_data.

    \[SqlDms05-PG\]

  4. In this step you are going to drop the foreign key constraint from the target database:

    1. Within PGAdmin, click on the Tools menu, and choose Query Tool. \[SqlDms06-PG\]
    2. Open DropConstraintsSQLServer-to-PostgreSQL.sql with a text editor on your computer.
    3. If necessary Copy the content to pgAdmin query editor and adjust for your schema name with a search and replace.
    4. Execute the script. You should see either a successful alter table/drop constraint or skipping constraint doesn’t exist both are fine.

    \[SqlDms06a-PG\] You can now proceed to the next step