AWS Database Migration Service
User Guide (Version API Version 2016-01-01)

Using a PostgreSQL Database as a Target for AWS Database Migration Service

You can migrate data to PostgreSQL databases using AWS DMS, either from another PostgreSQL database or from one of the other supported databases. PostgreSQL versions 9.4 and later (for 9.x), 10.x, and 11.x as a target are supported for these types of databases:

  • On-premises databases

  • Databases on an EC2 instance

  • Databases on an Amazon RDS DB instance

  • Databases on an Amazon Aurora DB instance with PostgreSQL compatibility


PostgreSQL versions 11.x are supported as a target only in AWS DMS versions 3.3.0 and later. You can use PostgreSQL version 9.4 and later (for versions 9.x) and 10.x as a target in any DMS version.

AWS DMS takes a table-by-table approach when migrating data from source to target in the Full Load phase. Table order during the full load phase cannot be guaranteed. Tables are out of sync during the full load phase and while cached transactions for individual tables are being applied. As a result, active referential integrity constraints can result in task failure during the full load phase.

In PostgreSQL, foreign keys (referential integrity constraints) are implemented using triggers. During the full load phase, AWS DMS loads each table one at a time. We strongly recommend that you disable foreign key constraints during a full load, using one of the following methods:

  • Temporarily disable all triggers from the instance, and finish the full load.

  • Use 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 the session_replication_role parameter is set to replica, only triggers in the replica state are active, and they are fired when they are called. Otherwise, the triggers remain inactive.

PostgreSQL has a failsafe mechanism to prevent a table from being truncated, even when session_replication_role is set. You can use this as an alternative to disabling triggers, to help the full load run to completion. To do this, set the target table preparation mode to DO_NOTHING. Otherwise, DROP and TRUNCATE operations fail when there are foreign key constraints.

In Amazon RDS, you can control set this parameter using a parameter group. For a PostgreSQL instance running on Amazon EC2, you can set the parameter directly.

For additional details on working with a PostgreSQL database as a target for AWS DMS, see the following sections:

Limitations on Using PostgreSQL as a Target for AWS Database Migration Service

The following limitations apply when using a PostgreSQL database as a target for AWS DMS:

  • The JSON data type is converted to the Native CLOB data type.

  • In an Oracle to PostgreSQL migration, if a column in Oracle contains a NULL character (hex value U+0000), AWS DMS converts the NULL character to a space (hex value U+0020). This is due to a PostgreSQL limitation.

Security Requirements When Using a PostgreSQL Database as a Target for AWS Database Migration Service

For security purposes, the user account used for the data migration must be a registered user in any PostgreSQL database that you use as a target.

Extra Connection Attributes When Using PostgreSQL as a Target for AWS DMS

You can use extra connection attributes to configure your PostgreSQL target. You specify these settings when you create the target endpoint. If you have multiple connection attribute settings, separate them from each other by semicolons with no additional white space.

The following table shows the extra connection attributes you can use to configure PostgreSQL as a target for AWS DMS.

Name Description


Specifies the maximum size (in KB) of any .csv file used to transfer data to PostgreSQL.

Default value: 32,768 KB (32 MB)

Valid values: 1–1048576

Example: maxFileSize=512


Sets the client statement timeout for the PostgreSQL instance, in seconds. The default value is 60 seconds.

Example: executeTimeout=100

afterConnectScript=SET session_replication_role='replica'

Add this attribute to have AWS DMS bypass all foreign keys and user triggers. This action greatly reduces the time it takes to bulk load data when using full load mode.


For an Amazon Aurora PostgreSQL target, this setting of afterConnectScript has effect only in change data capture (CDC) mode.

Example: afterConnectScript=SET session_replication_role='replica'

Target Data Types for PostgreSQL

The PostgreSQL database endpoint for AWS DMS supports most PostgreSQL database data types. The following table shows the PostgreSQL database target data types that are supported when using AWS DMS and the default mapping from AWS DMS data types. Unsupported data types are listed following the table.

For additional information about AWS DMS data types, see Data Types for AWS Database Migration Service.

AWS DMS Data Type

PostgreSQL Data Type










If the scale is from 0 through 6, then use TIMESTAMP.

If the scale is from 7 through 9, then use VARCHAR (37).
















If the length is from 1 through 21,845, then use VARCHAR (length in bytes).

If the length is 21,846 through 2,147,483,647, then use VARCHAR (65535).










If the length is from 1 through 21,845, then use VARCHAR (length in bytes).

If the length is 21,846 through 2,147,483,647, then use VARCHAR (65535).








When replicating from a PostgreSQL source, AWS DMS creates the target table with the same data types for all columns, apart from columns with user-defined data types. In such cases, the data type is created as "character varying" in the target.