# Step-by-step PostgreSQL database to Amazon RDS migration walkthrough


In the following sections, you can find step-by-step instructions for migrating your PostgreSQL database to Amazon RDS for PostgreSQL using homogeneous data migrations in Amazon DMS.

**Topics**
+ [

# Step 1: Create Amazon Resources
](dm-postgresql-step-1.md)
+ [

# Step 2: Configure Your Source Database
](dm-postgresql-step-2.md)
+ [

# Step 3: Create Your Target Amazon RDS for PostgreSQL Database
](dm-postgresql-step-3.md)
+ [

# Step 4: Store Database Credentials in Amazon Secrets Manager
](dm-postgresql-step-4.md)
+ [

# Step 5: Create an Instance Profile
](dm-postgresql-step-5.md)
+ [

# Step 6: Configure Data Providers
](dm-postgresql-step-6.md)
+ [

# Step 7: Create a Migration Project
](dm-postgresql-step-7.md)
+ [

# Step 8: Configure a Data Migration
](dm-postgresql-step-8.md)
+ [

# Step 9: Running and Monitoring a Data Migration
](dm-postgresql-step-9.md)

# Step 1: Create Amazon Resources


In this step, you create and configure the required Amazon resources for homogeneous data migrations in Amazon DMS.

**Topics**
+ [

## Creating a VPC
](#dm-postgresql-step-1-vpc)
+ [

## Creating an IAM policy
](#dm-postgresql-step-1-iam-policy)
+ [

## Creating an IAM role
](#dm-postgresql-step-1-iam-role)

## Creating a VPC


In this section, you create a virtual private cloud (VPC). This VPC is based on the Amazon Virtual Private Cloud (Amazon VPC) service and contains your Amazon resources. Make sure that you create this VPC in one of the Amazon Regions that support homogeneous data migrations in Amazon DMS. For more information, see the [list of supported Regions](https://docs.amazonaws.cn/dms/latest/userguide/data-migrations.html#data-migrations-supported-regions).

To migrate your on-premises source database, make sure that you configure a private network to connect to your target database. For more information, see the [Using an on-premises source data provider](https://docs.amazonaws.cn/dms/latest/userguide/dm-network.html#dm-network-on-premises).

 **To create a VPC for homogeneous data migrations** 

1. Sign in to the Amazon Web Services Management Console and open the Amazon VPC console at [https://console.aws.amazon.com/vpc/](https://console.amazonaws.cn/vpc/).

1. Choose your Amazon Region.

1. Choose **Create VPC**.

1. On the **Create VPC** page, enter the following settings:
   +  **Resources to create** — **VPC and more** 
   +  **Name tag auto-generation** — Choose **Auto-generate** and enter a globally unique name. For example, enter `dm-vpc`.
   +  **IPv4 CIDR block** — `10.0.1.0/24` 
   +  **NAT gateways** — **In 1 AZ** 
   +  **VPC endpoints** — **None** 

1. Keep the rest of the settings as they are, and choose **Create VPC**.

Use this VPC when you create your target Amazon RDS database in [Step 3](dm-postgresql-step-3.md) and your subnet group in [Step 5](dm-postgresql-step-5.md).

## Creating an IAM policy


In this section, you create an Amazon Identity and Access Management (IAM) policy that Amazon DMS requires to run homogeneous data migrations.

 **To create an IAM policy for homogeneous data migrations** 

1. Sign in to the Amazon Web Services Management Console and open the IAM console at [https://console.aws.amazon.com/iam/](https://console.amazonaws.cn/iam/).

1. In the navigation pane, choose **Policies**.

1. Choose **Create policy**.

1. On the **Create policy** page, choose the **JSON** tab.

1. Paste the following JSON into the editor.

```
{
    "Version": "2012-10-17",		 	 	 
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "ec2:DescribeRouteTables",
                "ec2:DescribeSecurityGroups",
                "ec2:DescribeVpcPeeringConnections",
                "ec2:DescribeVpcs",
                "ec2:DescribePrefixLists",
                "logs:DescribeLogGroups"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "servicequotas:GetServiceQuota"
            ],
            "Resource": "arn:aws:servicequotas:*:*:vpc/L-0EA8095F"
        },
        {
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogGroup",
                "logs:DescribeLogStreams"
            ],
            "Resource": "arn:aws:logs:*:*:log-group:dms-data-migration-*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogStream",
                "logs:PutLogEvents"
            ],
            "Resource": "arn:aws:logs:*:*:log-group:dms-data-migration-*:log-stream:dms-data-migration-*"
        },
        {
            "Effect": "Allow",
            "Action": "cloudwatch:PutMetricData",
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "ec2:CreateRoute",
                "ec2:DeleteRoute"
            ],
            "Resource": "arn:aws:ec2:*:*:route-table/*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "ec2:CreateTags"
            ],
            "Resource": [
                "arn:aws:ec2:*:*:security-group/*",
                "arn:aws:ec2:*:*:security-group-rule/*",
                "arn:aws:ec2:*:*:route-table/*",
                "arn:aws:ec2:*:*:vpc-peering-connection/*",
                "arn:aws:ec2:*:*:vpc/*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "ec2:AuthorizeSecurityGroupEgress",
                "ec2:AuthorizeSecurityGroupIngress"
            ],
            "Resource": "arn:aws:ec2:*:*:security-group-rule/*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "ec2:AuthorizeSecurityGroupEgress",
                "ec2:AuthorizeSecurityGroupIngress",
                "ec2:RevokeSecurityGroupEgress",
                "ec2:RevokeSecurityGroupIngress"
            ],
            "Resource": "arn:aws:ec2:*:*:security-group/*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "ec2:AcceptVpcPeeringConnection",
                "ec2:ModifyVpcPeeringConnectionOptions"
            ],
            "Resource": "arn:aws:ec2:*:*:vpc-peering-connection/*"
        },
        {
            "Effect": "Allow",
            "Action": "ec2:AcceptVpcPeeringConnection",
            "Resource": "arn:aws:ec2:*:*:vpc/*"
        }
    ]
}
```

1. Choose **Next** The **Review, and create** page opens.

1. For **Name**, enter `HomogeneousDataMigrationsPolicy`, and choose **Create policy**.

Use this IAM policy when you create the IAM role.

## Creating an IAM role


In this section, you create an IAM role for homogeneous data migrations. Amazon DMS uses this IAM role to access database credentials stored in Amazon Secrets Manager, store log files in Amazon CloudWatch, and interact with Amazon EC2.

 **To create an IAM role that provides access to Amazon Secrets Manager ** 

1. Sign in to the Amazon Web Services Management Console and open the IAM console at [https://console.aws.amazon.com/iam/](https://console.amazonaws.cn/iam/).

1. In the navigation pane, choose **Roles**.

1. Choose **Create role**.

1. On the **Select trusted entity** page, choose ** Amazon service**. For **Use case**, Choose **DMS**.

1. Choose **Next**. The **Add permissions** page opens.

1. Choose **HomogeneousDataMigrationsPolicy** that you created before. Also, choose **SecretsManagerReadWrite**.

1. Choose **Next**. The **Name, review, and create** page opens.

1. For **Role name**, enter `HomogeneousDataMigrationsRole` and choose **Create role**.

1. On the **Roles** page, enter `HomogeneousDataMigrationsRole` for **Role name**. Choose **HomogeneousDataMigrationsRole**.

1. Choose the **Trust relationships** tab and choose **Edit trust policy**.

1. On the **Edit trust policy** page, paste the following JSON into the editor, replacing the existing text.

```
{
    "Version": "2012-10-17",		 	 	 
    "Statement": [
        {
            "Sid": "",
            "Effect": "Allow",
            "Principal": {
                "Service": [
                    "dms-data-migrations.amazonaws.com",
                    "dms.your_region.amazonaws.com"
                ]
            },
            "Action": "sts:AssumeRole"
        }
    ]
}
```

\$1 Replace *your\$1region* with the name of your Region, such as `us-east-1`. . Choose **Update policy**.

Use this IAM role when you create your instance profile in [Step 5](dm-postgresql-step-5.md) and your migration project in [Step 7](dm-postgresql-step-7.md).

# Step 2: Configure Your Source Database


In this step, you create a new database user on your source PostgreSQL database and configure the data replication.

Use the following script to create a database user with the required permissions in your PostgreSQL source database.

```
CREATE USER your_user WITH LOGIN PASSWORD 'your_password';
ALTER USER your_user WITH SUPERUSER;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO your_user;
```

In the preceding example, replace *your\$1user* with the name of your user. Next, replace *your\$1password* with a secure password. Finally, replace *schema\$1name* with the name of your database schema. Run the `GRANT` query for each schema that you migrate to Amazon.

To replicate ongoing changes in your source database after the data migration, configure the logical replication. To turn on logical replication, set the following parameters and values in the `postgresql.conf` configuration file.
+ Set `wal_level` to `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 migration starts and remain open even when the migration is no longer running. Make sure to manually delete open slots.
+ 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 is 60000 milliseconds (60 seconds). Setting the value to 0 (zero) disables the timeout mechanism.

After you edit the `postgresql.conf` configuration file, restart your PostgreSQL database server to apply new values of static parameters.

# Step 3: Create Your Target Amazon RDS for PostgreSQL Database


In this step, you create a new Amazon RDS for PostgreSQL database to use as a migration target. Also, you configure a new database user on your target Amazon RDS for PostgreSQL database.

If you already created the target database, skip this step and proceed with the configuration of your database user.

 **To create an Amazon RDS for PostgreSQL database for homogeneous data migrations** 

1. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.amazonaws.cn/rds/).

1. Choose your Amazon Region.

1. Choose **Create database**.

1. For **Engine type**, choose **PostgreSQL**.

1. For **Templates**, choose **Free tier**.

1. For **DB instance identifier**, enter a unique name for your PostgreSQL database.

1. For **Master password** and **Confirm master password**, enter a secure password that includes at least 8 printable characters.

1. For **Virtual private cloud (VPC)** under **Connectivity**, choose `dm-vpc`. You created this VPC in [Step 1](dm-postgresql-step-1.md).

1. For **Public access**, choose **Yes**.

1. Keep the rest of the settings as they are, and then choose **Create database**.

After you create your Amazon RDS for PostgreSQL database, configure a new database user. Then, store the credentials of this user in Amazon Secrets Manager.

You can use the following code example to create a database user with the required permissions.

```
CREATE USER your_user WITH LOGIN PASSWORD 'your_password';
GRANT USAGE ON SCHEMA schema_name TO your_user;
GRANT CONNECT ON DATABASE db_name to your_user;
GRANT CREATE ON DATABASE db_name TO your_user;
GRANT CREATE ON SCHEMA schema_name TO your_user;
GRANT UPDATE, INSERT, SELECT, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA schema_name TO your_user;
```

In the preceding example, replace *your\$1user* with the name of your user. Next, replace *your\$1password* with a secure password. Finally, replace *db\$1name* and *schema\$1name* with your values.

To turn on logical replication for your RDS for PostgreSQL target, set the `rds.logical_replication` parameter in your DB parameter group to 1. This static parameter requires a reboot of the DB instance or DB cluster to take effect. Some parameters are static, and you can only set them at server start. Amazon DMS ignores changes to their entries in the DB parameter group until you restart the server.

# Step 4: Store Database Credentials in Amazon Secrets Manager


To connect to your source and target databases in an Amazon DMS migration project, store your database credentials in Amazon Secrets Manager. Make sure that you replicate these secrets to your Amazon Region.

 **To store your source database credentials in Amazon Secrets Manager ** 

1. Sign in to the Amazon Web Services Management Console and open the Amazon Secrets Manager console at [https://console.aws.amazon.com/secretsmanager/](https://console.amazonaws.cn/secretsmanager/).

1. Choose your Amazon Region.

1. Choose **Store a new secret**. The **Choose secret type** page opens.

1. For **Secret type**, choose **Credentials for other database**.

1. For **User name** and **Password**, enter the credentials of the database user that you created for your source database in [Step 2](dm-postgresql-step-2.md).

1. For **Database**, choose **PostgreSQL**.

1. For **Server address**, **Database name**, and **Port**, enter your PostgreSQL database connection information.

1. Choose **Next**. The **Configure secret** page opens.

1. For **Secret name**, enter `dm-postgresql-source`.

1. Choose **Next**. The **Configure rotation** page opens.

1. Choose **Next**. The **Review** page opens.

1. Choose **Store**.

 **To store your target database credentials in Amazon Secrets Manager ** 

1. Sign in to the Amazon Web Services Management Console and open the Amazon Secrets Manager console at [https://console.aws.amazon.com/secretsmanager/](https://console.amazonaws.cn/secretsmanager/).

1. Choose your Amazon Region.

1. Choose **Store a new secret**. The **Choose secret type** page opens.

1. For **Secret type**, choose **Credentials for Amazon RDS database**.

1. For **User name** and **Password**, enter the credentials of the database user that you created for your target database in [Step 3](dm-postgresql-step-3.md).

1. For **Database**, choose your Amazon RDS for PostgreSQL DB instance.

1. Choose **Next**. The **Configure secret** page opens.

1. For **Secret name**, enter `dm-postgresql-target`.

1. Choose **Next**. The **Configure rotation** page opens.

1. Choose **Next**. The **Review** page opens.

1. Choose **Store**.

Use these secrets when you create your migration project in [Step 7](dm-postgresql-step-7.md).

# Step 5: Create an Instance Profile


Before you create an instance profile, configure a subnet group for your instance profile.

 **To create a subnet group** 

1. Sign in to the Amazon Web Services Management Console and open the Amazon DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.amazonaws.cn/dms/v2/).

1. Choose your Amazon Region.

1. In the navigation pane, choose **Subnet groups**, and then choose **Create subnet group**.

1. For **Name**, enter `DataMigrationSubnetGroup`.

1. For **Description**, enter `A group of private subnets`.

1. For **VPC**, choose `dm-vpc`. You created this VPC in [Step 1](dm-postgresql-step-1.md).

1. For **Add subnets**, choose two private subnet IDs.

1. Choose **Create subnet group**.

Before you create your migration project, you set up an instance profile. An instance profile specifies network and security settings for your migration project.

 **To create an instance profile** 

1. Sign in to the Amazon Web Services Management Console and open the Amazon DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.amazonaws.cn/dms/v2/).

1. Choose your Amazon Region.

1. In the navigation pane, choose **Instance profiles**, and then choose **Create instance profile**.

1. For **Name**, enter a unique name for your instance profile. For example, enter `dm-instance-profile`.

1. For **Virtual private cloud (VPC)**, choose `dm-vpc`. You created this VPC in [Step 1](dm-postgresql-step-1.md).

1. For **Subnet group**, choose the `DataMigrationSubnetGroup` subnet group that you created before.

1. Choose **Create instance profile**.

Use this instance profile when you create your migration project in [Step 7](dm-postgresql-step-7.md).

# Step 6: Configure Data Providers


In this step, you create data providers that describe your source and target databases. A data provider stores a data store type and the location information about your database. Data providers don’t include database credentials. You store database credentials in Amazon Secrets Manager. Make sure that you include data providers and database secrets in your migration project.

You can create only one data provider for a single database. If you try to create a second data provider for the same database, Amazon DMS displays an error message. However, you can use one data provider in multiple migration projects.

 **To create a data provider for your source database** 

1. Sign in to the Amazon Web Services Management Console and open the Amazon DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.amazonaws.cn/dms/v2/).

1. Choose your Amazon Region.

1. In the navigation pane, choose **Data providers**, and then choose **Create data provider**.

1. For **Configuration**, choose **Enter manually**.

1. For **Name**, enter a unique name for your source data provider. For example, enter `dm-postgresql-source-provider`.

1. For **Engine type**, choose **PostgreSQL**.

1. For **Server name**, enter the Domain Name Service (DNS) name or IP address of your database server.

1. For **Port**, enter the port used to connect to your database server.

1. For **Database name**, enter the name of your source database.

1. For **Secure Socket Layer (SSL) mode**, choose **none**. Optionally, choose the type of your SSL enforcement, and provide the certificate information.

1. Choose **Create data provider**.

 **To create a data provider for your Amazon RDS for PostgreSQL database** 

1. Sign in to the Amazon Web Services Management Console and open the Amazon DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.amazonaws.cn/dms/v2/).

1. Choose your Amazon Region.

1. In the navigation pane, choose **Data providers**, and then choose **Create data provider**.

1. For **Configuration**, choose **RDS database instance**.

1. For **Database from RDS**, choose the Amazon RDS for PostgreSQL database that you created in [Step 3](dm-postgresql-step-3.md).

1. For **Name**, enter a unique name for your target data provider. For example, enter `dm-postgresql-target-provider`.

1. Choose **Create data provider**.

Use these data providers when you create your migration project in [Step 7](dm-postgresql-step-7.md).

# Step 7: Create a Migration Project


Now you can create a migration project. A migration project describes your instance profile, source and target data providers, and secrets from Amazon Secrets Manager.

To create a migration project

1. Sign in to the Amazon Web Services Management Console and open the Amazon DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.amazonaws.cn/dms/v2/).

1. Choose your Amazon Region.

1. Choose **Migration projects**, and then choose **Create migration project**.

1. For **Name**, enter a unique name for your migration project. For example, enter `dm-project`.

1. For **Instance profile**, choose `dm-instance-profile`. You created this instance profile in [Step 5](dm-postgresql-step-5.md).

1. For **Source**, choose **Browse**, and then choose `dm-postgresql-source-provider`. You created this data provider in [Step 6](dm-postgresql-step-6.md).

1. For **Secret ID**, choose `dm-postgresql-source`. You created this secret in [Step 4](dm-postgresql-step-4.md).

1. For **IAM role**, choose `HomogeneousDataMigrationsRole`. You created this role in [Step 1](dm-postgresql-step-1.md).

1. For **Target**, choose **Browse**, and then choose `dm-postgresql-target-provider`. You created this data provider in [Step 6](dm-postgresql-step-6.md).

1. For **Secret ID**, choose `dm-postgresql-target`. You created this secret in [Step 4](dm-postgresql-step-4.md).

1. For **IAM role**, choose `HomogeneousDataMigrationsRole`. You created this role in [Step 1](dm-postgresql-step-1.md).

1. Choose **Create migration project**.

Use this migration project to migrate your source PostgreSQL database to your Amazon RDS for PostgreSQL database.

# Step 8: Configure a Data Migration


After you create the migration project with two PostgreSQL data providers, you can use this project for homogeneous data migrations.

 **To create a data migration** 

1. Sign in to the Amazon Web Services Management Console and open the Amazon DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.amazonaws.cn/dms/v2/).

1. Choose your Amazon Region.

1. Choose **Migration projects**. The **Migration projects** page opens.

1. Choose `dm-project`, and then choose **Data migrations**.

1. Choose **Create data migration**.

1. For **Name**, enter a unique name for your data migration. For example, enter `postgresql-replication`.

1. For **Replication type**, choose **Full load and change data capture (CDC)** to migrate your existing source data and replicate ongoing changes. For this replication type, Amazon DMS deletes all data, tables, and other database objects on your target database. Make sure you create a backup of your target database before you start your data migration.

1. Select the check box for **Turn on CloudWatch logs** to store data migration logs in Amazon CloudWatch.

1. For **IAM service role**, choose the IAM role that you created in [Step 1](dm-postgresql-step-1.md).

1. For **Stop mode**, choose **Don’t stop CDC**.

1. Choose **Create data migration**.

 Amazon DMS creates your data migration and sets its status to **Ready**. To migrate your data, you must start the data migration manually. For more information, see [Step 9](dm-postgresql-step-9.md).

# Step 9: Running and Monitoring a Data Migration


After you create a data migration, you can run it and monitor its status.

 **To start a data migration** 

1. Sign in to the Amazon Web Services Management Console and open the Amazon DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.amazonaws.cn/dms/v2/).

1. Choose **Migration projects**. The **Migration projects** page opens.

1. Choose the migration project that you created in [Step 7](dm-postgresql-step-7.md).

1. On the **Data migrations** tab, choose the data migration that you created in [Step 7](dm-postgresql-step-7.md).

1. For **Actions**, choose **Start**.

The first launch of a homogeneous data migration requires some setup. Amazon DMS creates a serverless environment for your data migration. This process takes up to 15 minutes.

 **To monitor a data migration** 

1. Sign in to the Amazon Web Services Management Console and open the Amazon DMS console at [https://console.aws.amazon.com/dms/v2/](https://console.amazonaws.cn/dms/v2/).

1. Choose **Migration projects**. The **Migration projects** page opens.

1. Choose the migration project that you created in [Step 7](dm-postgresql-step-7.md).

1. On the **Data migrations** tab, see the **Status** column for your data migration. For more information about values in this column, see [Statuses of homogeneous data migrations](https://docs.amazonaws.cn/dms/latest/userguide/dm-migrating-data-statuses.html).

1. For a running data migration, the **Migration progress** column displays the percentage of migrated data.

1. Choose your data migration. On the **Details** tab, you can see the progress of your homogeneous data migration.

After Amazon DMS completes the full load process, your data migration starts the replication of ongoing changes.