AWS Database Blog

How BCM One migrated data from an unencrypted Amazon RDS for PostgreSQL database instance to a new encrypted instance using AWS DMS

This post is co-authored with Kate Fike, Software Engineer at BCM One.

BCM One is a leading global provider of NextGen Communications and Managed Services to IT leaders and channel resellers. They have multiple NextGen Communications brands, including Flowroute. Flowroute offers SIP trunking and a business messaging platform for mission-critical voice applications.

In this post, we walk through how Flowroute encrypted their existing Amazon Relational Database Service (Amazon RDS) for PostgreSQL database (DB) instances using AWS Database Migration Service (AWS DMS).

Storage encryption and migration challenges

Encrypting storage is important to prevent unauthorized access to the disk and is a recommended configuration as documented in Security in Amazon RDS. Storage is encrypted using an encryption key stored in AWS Key Management Services (AWS KMS) in order to access the disk.

To encrypt an unencrypted DB instance with minimal downtime we used Amazon RDS snapshots, AWS DMS, and Amazon RDS Proxy. The migration method described in this post builds on the solution in Encrypt an existing Amazon RDS for PostgreSQL DB instance.

The following diagram illustrates our architecture before encryption.

The following diagram illustrates our architecture after encryption.

One way to achieve the migration with near-zero downtime is to use full load and change data capture (CDC) AWS DMS tasks. However, as this is a homogeneous migration, it is simpler and faster to take a snapshot of the complete database and then replicate only the changes that occurred since taking the snapshot. This post describes how to complete a near-zero downtime migration using one CDC-only AWS DMS task instead.

Solution overview

This post provides a high-level outline of the steps and considerations involved in migrating an unencrypted RDS for PostgreSQL database to an encrypted RDS for PostgreSQL database. As of this writing, you have no direct encryption path. By using a combination of Amazon RDS utilities and AWS DMS, you can migrate the database instance to an encrypted RDS database.

The high-level steps for implementing this solution are as follows:

  1. Create an empty RDS instance, AWS DMS replication instance, AWS DMS endpoints, and CDC-only AWS DMS task.
  2. Start the CDC-only AWS DMS task.
  3. Stop the AWS DMS task.
  4. Delete the empty RDS instance.
  5. Create a snapshot of the existing, unencrypted RDS instance.
  6. Create an encrypted snapshot from the unencrypted snapshot in step 5.
  7. Restore a new encrypted RDS database instance from the encrypted snapshot in step 6.
  8. Resume the CDC-only AWS DMS task.
  9. Validate the data on the encrypted target RDS instance in a validation-only AWS DMS task.
  10. Using RDS proxy, edit target group to facilitate the switchover to the newly encrypted RDS instance. This approach allows you to manage the transition seamlessly for application’s connectivity.

The following diagram illustrates the solution and workflow.

Prerequisites

In this solution, you connect the existing, unencrypted RDS for PostgreSQL DB instance to an RDS proxy. The RDS proxy sits in front of the database and is responsible to minimize the downtime during cutover. Instead of connecting directly to a database, services connect to the proxy. For setup instructions, see Using Amazon RDS Proxy.

The RDS proxy is ideal for handling the switchover to the encrypted database as it facilitates the transition between the two legs of the connection: client to proxy, and proxy to database. When the RDS proxy’s target group is changed, it seamlessly connects to the new target group without dropping existing connections. This removes the complexity of restarting services one by one, ensuring that everything stays in sync. The time to cut over is also very quick, generally taking under a minute.

Using the RDS proxy for cutover is more straightforward than other approaches, such as using a CNAME record. The RDS proxy continues to accept connections even when the database is down or during the switchover when you’re changing the underlying target group. This is why using the RDS proxy for switchover is a common and recommended approach, as it allows for a seamless transition once the new encrypted database is available.

An IAM role is required to access the secret for use with the created proxy in AWS Secrets Manager for each user (the PostgreSQL login role) that needs to connect to the database. The secret ARN is added to the proxy’s authentication configuration. Prior to encrypting our storage, our database user credentials were stored in Parameter Store, a capability of AWS Systems Manager, or other third-party secret managers. We used this as an opportunity to standardize our database user credential storage so that all credentials are stored in Secrets Manager.

It is recommended for applications connecting through the proxy to use a login role (user) that is different from the administrative user, that follows the least privilege as best practice.

For a full list of the prerequisites, see Using a PostgreSQL database as an AWS DMS source.

Migration steps

Complete the following steps to migrate your database:

  1. On the existing, unencrypted database, create a dms login role:
CREATE ROLE dms WITH LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT rds_replication, rds_superuser TO dms;
Enter password for new role: ”password”
Enter it again: ”password”
  1. Create an empty RDS DB instance.
  2. For DB instance identifier, enter a name for your DB instance. The empty instance must have the same instance identifier as the new, encrypted instance. In this example, the instance identifier is example-target.
  1. For Initial database name, enter a name for your empty instance. The empty instance must have the same database name you want to migrate from the existing cluster. In this example, we migrate a database named postgres, which is the default database created for a new PostgreSQL instance.
  1. On the empty RDS instance, create a dms login role. Use the same password that was used for the dms login role on the unencrypted database.
  2. Configure the unencrypted database’s RDS parameter group for logical replication. For instructions, see Working with AWS-managed PostgreSQL databases as a DMS source.
  3. Create an AWS DMS replication instance. For instructions, see Creating a replication instance.
  4. Create an AWS DMS endpoint for the unencrypted database. This is the source endpoint. For instructions, see Creating source and target endpoints.
  5. Create an AWS DMS endpoint for the empty database. This is the target endpoint.
  6. Test both the source endpoint and target endpoint’s ability to connect to the AWS DMS replication instance. The test should be successful and the endpoint should show as active on the AWS DMS console.
  7. Create a CDC-only AWS DMS task. Use the default task configuration settings.

  1. Start the AWS DMS task.
  1. Verify replication is successful. This can be done by inserting, updating, or deleting a row.
kfike@ubuntu:~$ psql -h example-source -d postgres -U postgres
flowroute=> INSERT INTO country (country) VALUES ('Suriname');
INSERT 0 1
postgres=> SELECT * FROM country ORDER BY country_id DESC LIMIT 1;
country_id | country  |        last_update
------------+----------+----------------------------
111 | Suriname | 2023-12-12 03:30:20.747883
(1 row)
\q
kfike@ubuntu:~$ psql -h example-target -d postgres -U postgres
postgres=> SELECT * FROM country ORDER BY country_id DESC LIMIT 1;
country_id | country  |        last_update

------------+----------+----------------------------
111  | Suriname | 2023-12-12 03:30:20.747883
(1 row)
  1. Stop the AWS DMS task.

Even though the task is stopped, it will continue to queue CDC replication events.

To demonstrate how the AWS DMS task is queuing CDC events, a row is inserted before the snapshot was created:

kfike@ubuntu:~$ psql -h example-source -d postgres -U postgres
flowroute=> INSERT INTO country (country) VALUES ('Bahamas');
INSERT 0 1
postgres=> SELECT * FROM country ORDER BY country_id DESC LIMIT 1;
country_id | country  |        last_update
------------+----------+----------------------------
112  | Bahamas  | 2023-12-12 03:40:49.293128
(1 row)
\q
  1. Delete the empty database. It’s critical that the AWS DMS task stops before the empty database is deleted.
  2. Create a snapshot of the unencrypted RDS instance. Another row is inserted after the snapshot is taken.
kfike@ubuntu:~$ psql -h example-source -d postgres -U postgres
flowroute=> INSERT INTO country (country) VALUES ('Moldova');
INSERT 0 1
postgres=> SELECT * FROM country ORDER BY country_id DESC LIMIT 1;
country_id | country  |        last_update
------------+----------+----------------------------
113  | Moldova  | 2023-12-12 03:45:06.449679
(1 row)
\q
  1. Create an encrypted snapshot from the unencrypted snapshot. For more information, see Encrypt an existing Amazon RDS for PostgreSQL DB instance.

The key you use to encrypt the snapshot is also the key that encrypts the new database. One option is to use a KMS customer managed key (CMK). In this example, we use an AWS-managed KMS key: aws/rds. For more information about CMKs and AWS-managed keys, see AWS KMS Keys.

  1. Create an encrypted RDS instance from the encrypted snapshot.
  1. For DB instance identifier, enter a name for your instance. Again, it’s critical that the real database has the same RDS instance ID as the empty database. In this example, it was example-target.
  1. For AWS KMS key, choose your AWS-managed key.
  1. Verify that the encrypted database is available by connecting to it:
kfike@ubuntu:~$ psql -h example-target -d postgres -U postgres
Password for user postgres:
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1), server 12.14)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.
postgres=>
  1. Resume the AWS DMS task.

Don’t leave the AWS DMS task in an error state. It will exhaust the source’s CPU.

  1. Create a validation-only AWS DMS task.
  2. Verify that the encrypted database is in sync with the unencrypted database.

You can also query the encrypted database to check if the Moldova row in the country table replicated over:

  1. kfike@ubuntu:~$ psql -h example-target -d postgres -U postgres
postgres=> SELECT * FROM country ORDER BY country_id DESC LIMIT 1;
country_id | country  |        last_update
------------+----------+----------------------------
113  | Moldova  | 2023-12-12 03:45:06.449679
(1 row)
\q

    Complete switchover over using RDS proxy to the encrypted database.

The following screenshot shows the proxy configuration for connecting to the unencrypted database.

Make sure that the proxy’s AWS Identity and Access Management (IAM) role has permission to get the encrypted database’s primary user credentials and use the KMS key to decrypt the secret. Without these permissions, the target group will be unavailable.

{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Effect": "Allow",
			"Action": "kms:Decrypt",
			"Resource": "arn:aws::key/d1674ca0-653f-4785-99f2-fcfc19837b9e"
		},
		{
			"Effect": "Allow",
			"Action": "secretsmanager:GetSecretValue",
			"Resource": "arn:aws:secretsmanager::secret:rds!db-f80f9ab5-1682-449d-bed3-f268678cc80c-BVviV7"
		}
	]
}
  1. Use Amazon RDS console to update the proxy authentication to include the encrypted database’s primary user secret.

When using the AWS Command Line Interface (AWS CLI) modify-db-proxy command, the command doesn’t append secrets to the existing list; it replaces the proxy’s authentication list with only what’s listed in the modify-db-proxy command’s --auth option. If all secrets are not explicitly included every time the command is run, the command can eliminate existing secrets.

  1. Edit the default target group to point to the encrypted database and save the changes.

The proxy authentication will fail if the same login role name is used for unencrypted and encrypted databases, but each login role has different passwords. For example, let’s say the unencrypted database’s primary user is the postgres role and the password is 1234. The encrypted database’s primary user is also the postgres role, but the password is 5678. If you toggle the proxy connection from the source to the target, it will attempt to connect using the postgres role and the password 1234, which will produce an authentication error. There is a caching problem. To work around this, delete the unencrypted database’s primary user credentials after the encrypted database’s primary user credentials are added to the proxy’s authentication.

  1. Once the migration has been successfully tested and completed, clean up DMS resources: terminate the replication instance and delete the target groups.

Primary keys and unique constraints

In the previous example, the Bahamas row in the country table was inserted between the time the AWS DMS task was stopped and when the snapshot was taken. Therefore, the Bahamas row was in both the CDC event queue and on the snapshot. When the AWS DMS task was resumed, a duplicate insert of the Bahamas row was attempted. The following diagram illustrates this scenario.

However, due to the primary key constraint on country.country_id, the second attempt to insert the Bahamas row was rejected. This is reflected in the table created on the target by AWS DMS named awsdms_apply_exceptions:

kfike@ubuntu:~$ psql -h example-target -d postgres -U postgres -x
postgres=> SELECT * FROM awsdms_apply_exceptions;
-[ RECORD 1]--------------------------------------------------------------
TASK_NAME   | example-cdc-only
TABLE_OWNER | public
TABLE_NAME  | country
ERROR_TIME  | 2023-12-14 22:54:14.873954
STATEMENT   | INSERT INTO "public"."country" ( "country_id","country","last_update" )  VALUES (112,'Bahamas','2023-12-12 03:40:49.293128')
ERROR       | RetCode: SQL_ERROR  SqlState: 23505 NativeError: 1 Message: ERROR: duplicate key value violates unique constraint "country_pkey";+
| Error while executing the query

If a table lacks a primary key or unique constraint, then duplicate rows can be applied to the target table. The table in the following example doesn’t have a primary key or unique constraint. Rows were inserted at various timepoints:

kfike@ubuntu:~$ psql -h example-source -d postgres -U postgres
postgres=> INSERT INTO no_key_tbl (descr) VALUES ('Verify replication to the dummy');
INSERT 0 1
postgres=> INSERT INTO no_key_tbl (descr) VALUES ('After stopping the AWS DMS task');
INSERT 0 1
postgres=> INSERT INTO no_key_tbl (descr) VALUES ('After taking the snapshot');
INSERT 0 1
postgres=> INSERT INTO no_key_tbl (descr) VALUES ('After resuming the AWS DMS task');
INSERT 0 1

On the target, there is now a duplicate row: After stopping the AWS DMS task, which should have been rejected:

kfike@ubuntu:~$ psql -h example-target -d postgres -U postgres
postgres=> SELECT * FROM no_key_tbl;
descr
-----------------------------
Verify replication to the dummy
After stopping the DMS task
After stopping the DMS task
After taking the snapshot
After resuming the DMS task
(4 rows)

Conclusion

In this post, we demonstrated how Flowroute encrypted their existing RDS for PostgreSQL DB instances using AWS DMS. This was critical to prevent unauthorized access to the disk. The migration method was novel because it was done with zero downtime using a CDC-only AWS DMS task.

Migrations are highly dependent on the source database’s workload. The migration method described in this post may not be suitable for every workload. This migration method used one CDC-only AWS DMS task, whereas larger workloads may necessitate multiple CDC-only AWS DMS tasks.

If you have any questions or feedback, leave them in the comments section.

About the Authors

Kate Fike is a software engineer at BCM One based in New York City. In their role, they architect, create, and maintain telecommunications software systems to consistently deliver highly reliable and secure services for BCM One customers.

DB-3627-rob-headshotRobert Daly is a Senior Database Specialist Solutions Architect at AWS based in New York City, focusing on Amazon RDS, Amazon Aurora, and AWS DMS. He has helped multiple enterprise customers move their databases to AWS, providing assistance on performance and best practices.

Kawshik Sarkar is a Senior Solutions Architect at AWS, focusing on Small Medium Business Customers. Kawshik help customers design and build cloud-centered solutions on AWS and provides strategic guidelines to customers on how to drive business growth on the AWS Cloud.

Jose A. Ramirez is a Database Specialist Solutions Architect at AWS. He has over 30 years of experience in the technology industry as a DBA and Systems Administrator. Jose now focuses on architecting solutions for customers looking to modernize their applications and databases in AWS.

Corey Hollins is a Database Specialist at AWS. He came to AWS as a Veteran who served in the USCG. Corey has spent the last four years working with customers to align technical resource and programs to provide focused enablement as it relates to ongoing business drivers.