Using a PostgreSQL database as an Amazon DMS source

On-premises databases

Databases on an Amazon EC2 instance

Databases on an Amazon RDS DB instance

Databases on an DB instance based on Amazon Aurora PostgreSQL-Compatible Edition

Databases on an DB instance based on Amazon Aurora PostgreSQL-Compatible Serverless Edition

Create a PostgreSQL user with appropriate permissions to provide Amazon DMS access to your PostgreSQL source database.

Create a PostgreSQL source endpoint that conforms with your chosen PostgreSQL database configuration.

Create a task or set of tasks to migrate your tables.

To create a full-load-only task, no further endpoint configuration is needed.

Before you create a task for change data capture (a CDC-only or full-load and CDC task), see Enabling CDC using a self-managed PostgreSQL database as a Amazon DMS source or Enabling CDC with an Amazon-managed PostgreSQL DB instance with Amazon DMS.

Make sure that you use a PostgreSQL database that is version 9.4.x or higher.

For full-load plus CDC tasks or CDC-only tasks, grant superuser permissions for the user account specified for the PostgreSQL source database. The user account needs superuser permissions to access replication-specific functions in the source. For full-load only tasks, the user account needs SELECT permissions on tables to migrate them.

Add the IP address of the Amazon DMS replication server to the pg_hba.conf configuration file and enable replication and socket connections. An example follows.

            # Replication Instance
            host all all 12.3.4.56/00 md5
            # Allow replication connections from localhost, by a user with the
            # replication privilege.
            host replication dms 12.3.4.56/00 md5
            

PostgreSQL's pg_hba.conf configuration file controls client authentication. (HBA stands for host-based authentication.) The file is traditionally stored in the database cluster's data directory.

If you're configuring a database as a source for logical replication using Amazon DMS see Enabling CDC using a self-managed PostgreSQL database as a Amazon DMS source

Set wal_level = logical.

Set max_replication_slots to a value greater than 1.

Set the max_replication_slots value according to the number of tasks that you want to run. For example, to run five tasks you set a minimum of five slots. Slots open automatically as soon as a task starts and remain open even when the task is no longer running. Make sure to manually delete open slots. Note that DMS automatically drops replication slots when the task is deleted, if DMS created the slot.

Set max_wal_senders to a value greater than 1.

The max_wal_senders parameter sets the number of concurrent tasks that can run.

The wal_sender_timeout parameter ends replication connections that are inactive longer than the specified number of milliseconds. The default for an on-premises PostgreSQL database is 60000 milliseconds (60 seconds). Setting the value to 0 (zero) disables the timeout mechanism, and is a valid setting for DMS.

When setting wal_sender_timeout to a non-zero value, a DMS task with CDC requires a minimum of 10000 milliseconds (10 seconds), and fails if the value is less than 10000. Keep the value less than 5 minutes to avoid causing a delay during a Multi-AZ failover of a DMS replication instance.

We recommend that you use an Amazon user account with the minimum required permissions for the PostgreSQL DB instance as the user account for the PostgreSQL source endpoint for Amazon DMS. Using the master account is not recommended. The account must have the rds_superuser role and the rds_replication role. The rds_replication role grants permissions to manage logical slots and to stream data using logical slots.

Make sure to create several objects from the master user account for the account that you use. For information about creating these, see Migrating an Amazon RDS for PostgreSQL database without using the master user account.

If your source database is in a virtual private cloud (VPC), choose the VPC security group that provides access to the DB instance where the database resides. This is needed for the DMS replication instance to connect successfully to the source DB instance. When the database and DMS replication instance are in same VPC, add the appropriate security group to its own inbound rules.

For information about enabling logical replication for CDC on self-managed PostgreSQL source databases, see Enabling CDC using a self-managed PostgreSQL database as a Amazon DMS source

For information about enabling logical replication for CDC on Amazon-managed PostgreSQL source databases, see Enabling CDC with an Amazon-managed PostgreSQL DB instance with Amazon DMS.

You have a homogeneous migration, where you are migrating from a source PostgreSQL database to a target PostgreSQL database.

You are migrating an entire database.

The native tools allow you to migrate your data with minimal downtime.

In some cases, the PostgreSQL NUMERIC(p,s) data type doesn't specify any precision and scale. For DMS versions 3.4.2 and earlier, DMS uses a precision of 28 and a scale of 6 by default, NUMERIC(28,6). For example, the value 0.611111104488373 from the source is converted to 0.611111 on the PostgreSQL target.

A table with an ARRAY data type must have a primary key. A table with an ARRAY data type missing a primary key gets suspended during full load.

POINT

LINESTRING

POLYGON

MULTIPOINT

MULTILINESTRING

MULTIPOLYGON

GEOMETRYCOLLECTION

You can add values to the extra connection attribute to capture DDL events and to specify the schema in which the operational DDL database artifacts are created. For more information, see Endpoint settings and Extra Connection Attributes (ECAs) when using PostgreSQL as a DMS source.

You can override connection string parameters. Choose this option to do either of the following:

By using the table-level parameter REPLICA IDENTITY in PostgreSQL versions 9.4 and higher, you can control information written to write-ahead logs (WALs). In particular, it does so for WALs that identify rows that are updated or deleted. REPLICA IDENTITY FULL records the old values of all columns in the row. Use REPLICA IDENTITY FULL carefully for each table as FULL generates an extra number of WALs that might not be necessary. For more information, see ALTER TABLE-REPLICA IDENTITY

Amazon DMS doesn't work with Amazon RDS for PostgreSQL 10.4 or Amazon Aurora PostgreSQL 10.4 either as source or target.

A captured table must have a primary key. If a table doesn't have a primary key, Amazon DMS ignores DELETE and UPDATE record operations for that table. As a workaround, see Enabling change data capture (CDC) using logical replication.

Note: We don't recommend migrating without a Primary Key/Unique Index, otherwise additional limitations apply such as "NO" Batch apply capability, Full LOB capability, Data Validation and inability to replicate to Redshift target efficiently.

Amazon DMS ignores an attempt to update a primary key segment. In these cases, the target identifies the update as one that didn't update any rows. However, because the results of updating a primary key in PostgreSQL are unpredictable, no records are written to the exceptions table.

Amazon DMS doesn't support the Start Process Changes from Timestamp run option.

Amazon DMS doesn't replicate changes that result from partition or subpartition operations (ADD, DROP, or TRUNCATE).

Replication of multiple tables with the same name where each name has a different case (for example, table1, TABLE1, and Table1) can cause unpredictable behavior. Because of this issue, Amazon DMS doesn't support this type of replication.

In most cases, Amazon DMS supports change processing of CREATE, ALTER, and DROP DDL statements for tables. Amazon DMS doesn't support this change processing if the tables are held in an inner function or procedure body block or in other nested constructs.

For example, the following change isn't captured.

CREATE OR REPLACE FUNCTION attu.create_distributors1() RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
create table attu.distributors1(did serial PRIMARY KEY,name
varchar(40) NOT NULL);
END;
$$;

Currently, boolean data types in a PostgreSQL source are migrated to a SQL Server target as bit data type with inconsistent values. As a workaround, pre-create the table with a VARCHAR(1) data type for the column (or have Amazon DMS create the table). Then have downstream processing treat an "F" as False and a "T" as True.

Amazon DMS doesn't support change processing of TRUNCATE operations.

The OID LOB data type isn't migrated to the target.

Amazon DMS supports the PostGIS data type for only homogeneous migrations.

If your source is a PostgreSQL database that is on-premises or on an Amazon EC2 instance, ensure that the test_decoding output plugin is installed on your source endpoint. You can find this plugin in the PostgreSQL contrib package. For more information about the test-decoding plugin, see the PostgreSQL documentation.

Amazon DMS doesn't support change processing to set and unset column default values (using the ALTER COLUMN SET DEFAULT clause on ALTER TABLE statements).

Amazon DMS doesn't support change processing to set column nullability (using the ALTER COLUMN [SET|DROP] NOT NULL clause on ALTER TABLE statements).

When logical replication is enabled, the maximum number of changes kept in memory per transaction is 4 MB. After that, changes are spilled to disk. As a result ReplicationSlotDiskUsage increases, and restart_lsn doesn’t advance until the transaction is completed or stopped and the rollback finishes. Because it is a long transaction, it can take a long time to rollback. So, avoid long running transactions or many sub-transactions when logical replication is enabled. Instead, break the transaction into several smaller transactions.

On Aurora PostgreSQL versions 13 and later, you can tune the logical_decoding_work_mem parameter to control when DMS spills change data to disk. For more information, see Spill files in Aurora PostgreSQL.

A table with an ARRAY data type must have a primary key. A table with an ARRAY data type missing a primary key gets suspended during full load.

Amazon DMS doesn't support replication of partitioned tables. When a partitioned table is detected, the following occurs:

To replicate partitioned tables from a PostgreSQL source to a PostgreSQL target, first manually create the parent and child tables on the target. Then define a separate task to replicate to those tables. In such a case, set the task configuration to Truncate before loading.

The PostgreSQL NUMERIC data type isn't fixed in size. When transferring data that is a NUMERIC data type but without precision and scale, DMS uses NUMERIC(28,6) (a precision of 28 and scale of 6) by default. As an example, the value 0.611111104488373 from the source is converted to 0.611111 on the PostgreSQL target.

Amazon DMS supports Aurora PostgreSQL Serverless V1 as a source for full load tasks only. Amazon DMS supports Aurora PostgreSQL Serverless V2 as a source for full load, full load and CDC, and CDC-only tasks.

Amazon DMS doesn't support replication of a table with a unique index created with a coalesce function.

When using LOB mode, both the source table and the corresponding target table must have an identical Primary Key. If one of the tables does not have a Primary Key, the result of DELETE and UPDATE record operations will be unpredictable.

When using the Parallel Load feature, table segmentation according to partitions or sub-partitions isn't supported. For more information about Parallel Load, see Using parallel load for selected tables, views, and collections

Amazon DMS doesn't support Deferred Constraints.

Amazon DMS version 3.4.7 supports PostgreSQL 14.x as a source with these limitations:

Amazon DMS doesn't support CDC for Amazon RDS Proxy for PostgreSQL as a source.

When using source filters that don't contain a Primary Key column, DELETE operations won't be captured.

If your source database is also a target for another third–party replication system, DDL changes might not migrate during CDC. Because that situation can prevent the awsdms_intercept_ddl event trigger from firing. To work around the situation, modify that trigger on your source database as follows:

alter event trigger awsdms_intercept_ddl enable always;

Amazon DMS doesn't support CDC for Amazon RDS Multi-AZ database cluster for PostgreSQL as a source, since RDS for PostgreSQL Multi-AZ database clusters don't support logical replication.

BLOB – Set Limit LOB size to the Maximum LOB size (KB) value at task creation.

CLOB – Replication handles each character as a UTF8 character. Therefore, find the length of the longest character text in the column, shown here as max_num_chars_text. Use this length to specify the value for Limit LOB size to. If the data includes 4-byte characters, multiply by 2 to specify the Limit LOB size to value, which is in bytes. In this case, Limit LOB size to is equal to max_num_chars_text multiplied by 2.

NCLOB – Replication handles each character as a double-byte character. Therefore, find the length of the longest character text in the column (max_num_chars_text) and multiply by 2. You do this to specify the value for Limit LOB size to. In this case, Limit LOB size to is equal to max_num_chars_text multiplied by 2. If the data includes 4-byte characters, multiply by 2 again. In this case, Limit LOB size to is equal to max_num_chars_text multiplied by 4.