AWS dms documentation change
Summary
Updated PostgreSQL version compatibility table to include 17.x requirements
Security assessment
Version requirement update without security context
Diff
diff --git a/dms/latest/userguide/CHAP_Source.PostgreSQL.md b/dms/latest/userguide/CHAP_Source.PostgreSQL.md index b503b5f7e..0bb428083 100644 --- a//dms/latest/userguide/CHAP_Source.PostgreSQL.md +++ b//dms/latest/userguide/CHAP_Source.PostgreSQL.md @@ -5 +5 @@ -Working with self-managed PostgreSQL databasesWorking with AWS-managed PostgreSQL databasesUsing logical replication to enable CDC during migrationUsing native CDC start points to set up a CDC loadPostgreSQL to PostgreSQL migrationsMigrating from BabelfishRemoving artifacts from a PostgreSQL sourceAdvanced configuration settings for PostgreSQL as a sourceUsing the MapBooleanAsBoolean PostgreSQL endpoint settingEndpoint and ECA settingsLimitations on PostgreSQL as a sourceSource data types for PostgreSQL +Working with self-managed PostgreSQL databasesWorking with AWS-managed PostgreSQL databasesUsing logical replication to enable CDC during migrationUsing native CDC start points to set up a CDC loadPostgreSQL to PostgreSQL migrationsMigrating from BabelfishRemoving artifacts from a PostgreSQL sourceAdvanced configuration settings for PostgreSQL as a sourceRead replica as a source for PostgreSQLUsing the MapBooleanAsBoolean PostgreSQL endpoint settingEndpoint and ECA settingsLimitations on PostgreSQL as a sourceSource data types for PostgreSQL @@ -38,0 +39 @@ PostgreSQL source version | AWS DMS version to use +17.x | Use AWS DMS version 3.6.1 and higher. @@ -82,0 +84,2 @@ Before you create a task for change data capture (a CDC-only or full-load and CD + * Read replica as a source for PostgreSQL + @@ -176,2 +178,0 @@ AWS DMS supports CDC on Amazon RDS PostgreSQL databases when the DB instance is -You cannot use RDS PostgreSQL read replicas or Aurora PostgreSQL readers for CDC (ongoing replication). - @@ -188 +189 @@ RDS for PostgreSQL with PostgreSQL 10.21 compatibility (or higher) | Yes | Yes -If you use an account other than the master user account, make sure to create these specific objects from the master account for the account that you use. For more information, see Migrating an Amazon RDS for PostgreSQL database without using the master user account. +If you use an account other than the master user account, make sure to create several objects from the master account for the account that you use. For more information, see Migrating an Amazon RDS for PostgreSQL database without using the master user account. @@ -202,0 +204,76 @@ When setting `wal_sender_timeout` to a non-zero value, a DMS task with CDC requi +###### To use PostgreSQL MultiAZ DB Cluster Read Replica for CDC (ongoing replication) + + 1. Set the `rds.logical_replication` and `sync_replication_slots` parameters in your DB CLUSTER parameter group to 1. This static parameters require a reboot of the DB instance to take effect. + + 2. Run the following command to create the `awsdms_ddl_audit` table on Writer and to replcae the `objects_schema` with the name of the schema to use: + + CREATE TABLE objects_schema.awsdms_ddl_audit + ( + c_key bigserial primary key, + c_time timestamp, -- Informational + c_user varchar(64), -- Informational: current_user + c_txn varchar(16), -- Informational: current transaction + c_tag varchar(24), -- Either 'CREATE TABLE' or 'ALTER TABLE' or 'DROP TABLE' + c_oid integer, -- For future use - TG_OBJECTID + c_name varchar(64), -- For future use - TG_OBJECTNAME + c_schema varchar(64), -- For future use - TG_SCHEMANAME. For now - holds current_schema + c_ddlqry text -- The DDL query associated with the current DDL event + ); + + 3. Run the following command to create the `awsdms_intercept_ddl` function and to replace the `objects_schema` with the name of the schema to use: + + CREATE OR REPLACE FUNCTION objects_schema.awsdms_intercept_ddl() + RETURNS event_trigger + LANGUAGE plpgsql + SECURITY DEFINER + AS $$ + declare _qry text; + BEGIN + if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE' or tg_tag = 'CREATE TABLE AS') then + SELECT current_query() into _qry; + insert into objects_schema.awsdms_ddl_audit + values + ( + default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry + ); + delete from objects_schema.awsdms_ddl_audit; + end if; + END; + $$; + + 4. Run the following command to create the `awsdms_intercept_ddl` event trigger: + + CREATE EVENT TRIGGER awsdms_intercept_ddl ON ddl_command_end EXECUTE PROCEDURE objects_schema.awsdms_intercept_ddl(); + +Ensure that all the users and roles that access these events have the necessary DDL permissions. For example: + + grant all on public.awsdms_ddl_audit to public; + grant all on public.awsdms_ddl_audit_c_key_seq to public; + + 5. Create replication slot on Writer: + + SELECT * FROM pg_create_logical_replication_slot('dms_read_replica_slot', 'test_decoding', false, true); + + 6. Ensure the replication slot is available on Reader: + + select * from pg_catalog.pg_replication_slots where slot_name = 'dms_read_replica_slot'; + + slot_name |plugin |slot_type|datoid|database|temporary|active|active_pid|xmin|catalog_xmin|restart_lsn|confirmed_flush_lsn|wal_status|safe_wal_size|two_phase|inactive_since |conflicting|invalidation_reason|failover|synced| + ---------------------+-------------+---------+------+--------+---------+------+----------+----+------------+-----------+-------------------+----------+-------------+---------+-----------------------------+-----------+-------------------+--------+------+ + dms_read_replica_slot|test_decoding|logical | 5|postgres|false |false | | |3559 |0/180011B8 |0/180011F0 |reserved | |true |2025-02-10 15:45:04.083 +0100|false | |false |false | + + + + 7. Create DMS source endpoint for Read Replica and set logical replication slot name via the Extra Connection Attribute: + + slotName=dms_read_replica_slot; + + 8. Create and start the CDC/FL+CDC task. + +###### Note + +For CDC/FL+CDC migrations DMS considers task start time as a CDC start position. All older LSNs from replication slot are ignored. + + + + @@ -664 +741,22 @@ You can add additional configuration settings when migrating data from a Postgre -## Using the MapBooleanAsBoolean PostgreSQL endpoint setting +## Read replica as a source for PostgreSQL + +Use PostgreSQL read replicas as CDC sources in AWS DMS to reduce primary database load. This feature is available from PostgreSQL 16.x and requires AWS DMS version 3.6.1 or later. Using read replicas for CDC processing reduces the operational impact on your primary database. + +###### Note + +Amazon RDS PostgreSQL version 16.x has limitations for read replica logical replication in the Three-AZ (TAZ) configurations. For full read replica logical replication support in TAZ deployments, you must use PostgreSQL version 17.x or later. + +### Prerequisites + +Before using a read replica as a CDC source for AWS DMS, you must enable logical replication on both the primary Database instance and its read replica to create logical decoding on a read replica. Perform the following actions: + + * Enable logical replication on both, your primary database instance and its read replica along with any other required database parameters. For more information, see [Working with AWS-managed PostgreSQL databases as a DMS source](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.RDSPostgreSQL). + + * For CDC-only tasks, create a replication slot on the primary (writer) instance. For more information, see [Using native CDC start points to set up a CDC load of a PostgreSQL source](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.v10). This action is necessary as read replicas do not support the creation of replication slot. + + + + +After completing the prerequisites, you can set up your AWS DMS source endpoint with replication `SlotName` of the read replica source in the endpoint settings and configure your AWS DMS task using native CDC start points. For more information see, [Endpoint settings and Extra Connection Attributes (ECAs) when using PostgreSQL as a DMS source](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.ConnectionAttrib) and [Using native CDC start points to set up a CDC load of a PostgreSQL source](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.v10). + +## Using the `MapBooleanAsBoolean` PostgreSQL endpoint setting @@ -684 +781,0 @@ Attribute name | Description -`ConnectionTimeout` | Use this extra connection attribute (ECA) to set the endpoint connection timeout for the PostgreSQL instance, in seconds. The default value is 10 seconds. ECA Example: `ConnectionTimeout=30`. @@ -744 +841 @@ For example, the following change isn't captured. - * AWS DMS does not support change processing of TRUNCATE operations for PostgreSQL 10 or below, and if the DMS replication instance version is not greater than 3.5. + * AWS DMS does not support change processing of TRUNCATE operations. @@ -805,0 +903,2 @@ On Aurora PostgreSQL versions 13 and later, you can tune the `logical_decoding_w + * For full load and CDC tasks using a read replica as the source, AWS DMS cannot create replication slots on read replicas. +