Close Menu

    Best practices to handle AWS DMS tasks during PostgreSQL upgrades

    AWS Database Migration Service provides a managed solution for migrating and replicating databases to Amazon Web Services (AWS) while providing data security and data integrity. AWS DMS accommodates both homogeneous migrations, where source and target databases use the same engine and heterogeneous migrations between different database environments.

    AWS DMS facilitates data migration from PostgreSQL databases to any supported target and also allows migration from any supported source to a PostgreSQL database, offering a robust pathway for businesses to transition their data infrastructure to the cloud.

    Solution overview

    Open-source PostgreSQL occasionally releases new minor and major versions that include fixes for frequently encountered bugs, security issues, and data corruption problems. Generally, Amazon RDS aims to support new engine versions within five months of their availability. You must upgrade your PostgreSQL instances when a particular version is no longer supported or you might decide to upgrade your PostgreSQL database to solve a problem and introduce new improvements or to maintain compliance and protect your data.

    When you decide to upgrade your PostgreSQL database which is configured as source or target for an ongoing AWS DMS task, it’s important to factor this into your upgrade planning.

    In this post, we discuss the best practices to handle the AWS DMS tasks during PostgreSQL upgrades to minor or major versions.

    Prerequisites

    To test the solution in this post, you need the following resources:

    Understanding version upgrades in PostgreSQL

    Before diving into how PostgreSQL upgrades affect AWS DMS tasks, let’s establish a clear understanding of major and minor version upgrades in PostgreSQL.

    Minor versions patch security vulnerabilities, fix bugs, and generally don’t add new functionality. Minor releases do not change the internal storage format and are always compatible with earlier and later minor releases of the same major version number. For example, version 14.10 is compatible with version 14.9 and version 14.16.

    For major releases of PostgreSQL, the internal format of system tables, data files, and the internal data storage format might also change. RDS for PostgreSQL uses the native pg_upgrade utility to upgrade the instance to a new major version. For more information on upgrades, see Upgrading the PostgreSQL DB engine for Amazon RDS.

    Both minor and major releases or version upgrades involves downtime and should be done within a preferred maintenance window. We recommend that you plan a scheduled maintenance window for this upgrade activity, preferably when your database is being queried the least.

    AWS DMS interaction with PostgreSQL

    Let’s assume that you’re using AWS DMS to migrate the data from a PostgreSQL source to a PostgreSQL target.

    During a full load, AWS DMS connects to the source PostgreSQL database and runs a select * on the tables that are defined in the table mappings to unload the data. Data fetched from the source is written to a CSV file in a replication instance for PostgreSQL targets. For PostgreSQL targets, AWS DMS uses the COPY command to load the data from the CSV file into the target PostgreSQL tables.

    To capture ongoing changes during migration, AWS DMS creates a logical replication slot on your source PostgreSQL database. A slot represents a stream of changes that can be replayed to a client in the order they were made on the source PostgreSQL database. DMS uses either test_decoding or pglogical plugins for logical decoding of the changes from the replication slot. If the pglogical plugin is available on a source PostgreSQL database, DMS creates a replication slot using pglogical, otherwise a test_decoding plugin is used. The changes read from the source are passed on to the sorter component on the replication instance. The sorter component sorts transactions in commit order, and then apply those changes to target database, either sequentially or in batch mode based on your DMS task configuration.

    Replication slots perform a crucial role in the full load plus CDC and CDC-only tasks. It is responsible for retaining the necessary write ahead log (WAL) files on the source PostgreSQL database. If the replication slot is dropped on the source database, DMS won’t be able to process the ongoing changes from the source database.

    How PostgreSQL upgrades affects AWS DMS tasks

    In the following sections, we discuss how to handle your DMS tasks during a minor or major version upgrade of your source or target PostgreSQL database.

    When the source PostgreSQL database is upgraded

    Full load-only DMS tasks are designed for one-time data migrations. These tasks can be safely restarted following either minor or major version upgrades of the source PostgreSQL database.

    Full load plus CDC and CDC-only DMS tasks replicate the ongoing changes continuously to your target database. Follow the best practices from the following sections to handle your full load plus CDC and CDC-only DMS tasks during PostgreSQL upgrades.

    Minor release or version upgrade

    Stop your ongoing AWS DMS replication tasks before the minor version upgrade. After the minor version upgrade is complete, you can resume your DMS task.

    Major version upgrade

    At the time of writing, DMS supports PostgreSQL version 9.4 and higher (for versions 9.x), 10.x, 11.x, 12.x, 13.x 14.x, 15.x, and 16.x. When performing major version upgrades, make sure that your replication instance supports the new PostgreSQL version.

    To proceed with the major version upgrade using pg_upgrade, the replication slots on the source PostgreSQL database need to be dropped. Failure to remove these slots can impact the upgrade process. If you attempt an upgrade without dropping the replication slots, the upgrade will fail with messages in the pg_upgrade_precheck.log indicating that the instance could not be upgraded as it was blocked by one or more logical replication slots. However, dropping the replication slots will invalidate your AWS DMS tasks and you won’t be able to resume your ongoing replication tasks.

    To address this problem and manage the ongoing replication tasks during major version upgrades use the following steps:

    1. Stop all application connections to your PostgreSQL database. Monitor active connections using:
    select * from pg_stat_activity where datname = 'database_name';

    If needed, terminate remaining connections with:

    select pg_terminate_backend(pid) from pg_stat_activity where datname = 'database_name'and pid <> pg_backend_pid();
    1. Monitor the AWS DMS task metrics to verify that both CDCLatencySource and CDCLatencyTarget are near zero. This confirms that the DMS tasks are replicating changes without delay. You can also use awsdms_txn_state in the target to get the task status (it can be enabled with the task setting TaskRecoveryTableEnabled = True). The following image shows the cloudwatch metrics of CDCLatencySource and CDCLatencyTarget.

    1. When latency is close to zero, stop all active ongoing replication DMS tasks.
    1. Remove the existing replication slots from the source PostgreSQL database.
      postgres=> select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size
-----------------+-------------+-----------+--------+----------+-----------+--------+------------+------+-------------+-------------+-------------------+------------+---------------
bb6jw1f3enambi4z_00014405_e3972613_00e2_4960_ae4c_fe267b1cfcde | test_decoding | logical | 14405 | postgres | f | f | | | 898 | 0/5936F798 | 0/5F1A3440 | reserved |

(1 row)
postgres=> SELECT pg_drop_replication_slot('bb6jw1f3enambi4z_00014405_e3972613_00e2_4960_ae4c_fe267b1cfcde'); 
 pg_drop_replication_slot 
--------------------------
 
(1 row)
    1. Verify the removal of the replication slots.
      postgres=> select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+-------------+-------------+-------------------+------------+---------------
(0 rows)
    1. Complete the in-place upgrade on the PostgreSQL database.
    1. Confirm successful completion of the upgrade process. Perform database level validation checks to confirm that the database is working as expected after the upgrade. Before starting your application, follow either step 8 or step 9 to handle your DMS tasks.
    1. Create a new CDC-only task. Under task settings, select Disable custom CDC start mode for CDC start mode for source transactions. Define the other task settings and table mappings similar to your old task.

    After the task has been created, start the CDC-only task. It will create a new replication slot on your source PostgreSQL database and start migrating the changes from the time when the replication slot was created.

    1. Alternatively, you can manually create the replication slot on your source PostgreSQL database, using the DMS CDC-only task starting from the specified log sequence number (lsn). Create the replication slot in the source and note down the confirmed_flush_lsn.

    confirmed_flush_lsn represents the last LSN that the logical slot consumer has confirmed receiving data to PostgreSQL engine. Data corresponding to the transactions committed before this LSN isn’t available anymore.

    a. Modify the source endpoint settings by adding SlotName with the desired slot that was created in the source PostgreSQL database.

    b. Modify the task settings. Select Enable custom CDC start mode, then select Specify a log sequence number, and enter the LSN from confirmed_flush_lsn

    1. Start your DMS task and verify that the changes are being migrated to the target database without issues.
    1. Start your applications and monitor your DMS CDC replication.

    When the target PostgreSQL database is upgraded

    AWS DMS CDC isn’t affected by a minor version upgrade of the target PostgreSQL database. Before the upgrade of a PostgreSQL database that’s configured as a target for DMS, stop your DMS task and resume it after the minor version upgrade is successful.

    When you’re performing a major version upgrade on the PostgreSQL database that’s configured as a target for DMS:

    • Confirm that your current replication instance engine version supports the new PostgreSQL version.
    • If the new engine version is supported by your current replication instance version, you can stop the AWS DMS task, complete the major version upgrade, then resume your DMS task.
    • If the new engine version isn’t supported by your current replication instance version, you need to stop your DMS task and complete the major version upgrade on the target PostgreSQL database. You also need to upgrade your replication instance to a version that supports the current version of your target PostgreSQL database. After both the target and source databases have been updated to compatible major versions, you can resume your DMS tasks.

    Clean up

    To revert the changes and avoid any ongoing charges, delete the resources you created for this post:

    1. Delete the RDS for PostgreSQL instances and EC2 instance that are no longer required and were created for testing this solution.
    2. Delete the AWS DMS tasks that were created for testing this solution.
    3. Delete the AWS DMS source and target endpoints
    4. Delete the AWS DMS replication instance.

    Summary

    In this post, we discussed how to handle your AWS DMS tasks when upgrading PostgreSQL databases that are configured as a source or target for DMS.

    Try the solution, and share your feedback and questions in the comments.

    About the Authors

    Veeramani A is a Cloud Database Engineer at Amazon Web Services, serving as a Subject Matter Expert in AWS Database Migration Service and Amazon RDS for PostgreSQL. With more than 15 years of diverse database technology experience, he provides strategic guidance and technical expertise to customers navigating their database migration journey to the AWS Cloud.

    Manoj Ponnurangam works as a Cloud Database Engineer in Amazon Web Services. He’s a Subject Matter Expert for Amazon RDS for Oracle, Amazon RDS for PostgreSQL and AWS DMS. Manoj has 15 years of experience working with relational databases. He works with our customers to provide guidance and technical assistance on various database and migration projects.

    Source: Read More

    Related Posts

    Leave A Reply

    For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.