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 SCHEMAschema_name
TOyour_user
; GRANT CONNECT ON DATABASEdb_name
toyour_user
; GRANT CREATE ON DATABASEdb_name
TOyour_user
; GRANT CREATE ON SCHEMAschema_name
TOyour_user
; GRANT UPDATE, INSERT, SELECT, DELETE, TRUNCATE ON ALL TABLES IN SCHEMAschema_name
TOyour_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
, ordisabled
. When you set thesession_replication_role
parameter toreplica
, only triggers in thereplica
state are active. Otherwise, the triggers remain inactive.