Using a PostgreSQL database as a target for homogeneous data migrations in Amazon DMS - Amazon Database Migration Service
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Using a PostgreSQL database as a target for homogeneous data migrations in Amazon DMS

You can use a PostgreSQL database as a migration target for homogeneous data migrations in Amazon DMS.

Amazon DMS requires certain permissions to migrate data into your target Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL database. Use the following script to create a database user with the required permissions in your PostgreSQL target database.

CREATE USER your_user WITH LOGIN PASSWORD 'your_password'; GRANT USAGE ON SCHEMA schema_name TO your_user; GRANT CONNECT ON DATABASE db_name to your_user; GRANT CREATE ON DATABASE db_name TO your_user; GRANT CREATE ON SCHEMA schema_name TO your_user; GRANT UPDATE, INSERT, SELECT, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA schema_name TO your_user;

In the preceding example, replace each user input placeholder with your own information.

To turn on logical replication for your RDS for PostgreSQL target, set the rds.logical_replication parameter in your DB parameter group to 1. This static parameter requires a reboot of the DB instance or DB cluster to take effect. Some parameters are static, and you can only set them at server start. Amazon DMS ignores changes to their entries in the DB parameter group until you restart the server.

PostgreSQL uses triggers to implement foreign key constraints. During the full load phase, Amazon DMS loads each table one at a time. We recommend that you turn off foreign key constraints on your target database during the full load. To do so, use one of the following methods.

  • Temporarily turn off all triggers for your instance, and finish the full load.

  • Change the value of the session_replication_role parameter in PostgreSQL.

    At any given time, a trigger can be in one of the following states: origin, replica, always, or disabled. When you set the session_replication_role parameter to replica, only triggers in the replica state are active. Otherwise, the triggers remain inactive.