AWS Database Blog

Migrate PostgreSQL from Google Cloud Platform to Amazon RDS with minimal downtime

In this post, we walk you through a procedure to migrate the PostgreSQL database from Google Cloud Platform (GCP) Cloud SQL to Amazon Relational Database Service (Amazon RDS) for PostgreSQL.

To accomplish this task, we use AWS Database Migration Service (AWS DMS), a data migration and replication service. Using AWS DMS you can do the initial load of the tables and automatically capture all changes and apply them to the destination database. AWS DMS allows minimal downtime migrations.

In this post, we perform a sample database migration and describe step-by-step approach. The sample database data we used can be found on the following GitHub repo. We used a realistic scenario with stored procedures, sequences, and different types of the tables (small tables and tables with millions of rows). The goal is to migrate this database from GCP Cloud SQL to Amazon RDS, but the same approach can be used to migrate any on-premises PostgreSQL database. To learn more about migration best practices, read this post best practices for migrating PostgreSQL databases to Amazon RDS and Amazon Aurora.

Solution overview

The following architecture diagram describes the components involved in the migration process.

Architecture diagram

We recommend you use a site-to-site virtual private network (VPN) connection between GCP and AWS. We create an AWS DMS Replication Instance in a private subnet with a replication instance that connects to GCP Cloud SQL database over secure Secure Sockets Layer (SSL) and Transport Layer Security (TLS) connections. The traffic to Amazon RDS instance is encrypted as well. AWS DMS encrypts the data at rest on replication instance by default. We use AWS Secrets Manager to store user and password information to connect to source and target databases.

AWS DMS supports two methods of change data capture from PostgreSQL. One method of is a simple test_replica extension and the second one is pglogical extension. If pglogical extension isn’t configured, then the database user that connects to source PostgreSQL database must have SUPERUSER attribute. In GCP Cloud SQL, the user postgres doesn’t have SUPERUSER and REPLICATION attributes, which is why any attempt to use the postgres user for the change data capture fails.

The good news is that we can configure pglogical on GCP Cloud SQL database. We create a separate user with required system attributes and permissions to accomplish data replication task.

The general overview of the migration steps:

  1. Create database user for replication and configure pglogical on source GCP Cloud SQL.
  2. Import metadata from source database to Amazon RDS.
  3. Create an AWS DMS replication instance, endpoints, and migration tasks.
  4. Run migration tasks and monitor replication progress.
  5. Cut-over to Amazon RDS for PostgreSQL.

Prepare GCP Cloud SQL database for replication

First, create a user for replication connection. We call this user account replication_user. Connect to GCP Cloud SQL instance as a postgres user and execute following command:

CREATE USER replication_user WITH REPLICATION IN ROLE cloudsqlsuperuser LOGIN PASSWORD 'secretpassword';

Make sure to use a secure password.

Now we configure pglogical extension. This step requires a source database restart. Plan a short downtime in appropriate timeframe for your services.

  1. On instance page choose the EDIT to enter instance info page.
  2. Look for Flags section and expand it.
  3. Select ADD FLAG and add two flags cloudsql.logical_decoding and cloudsql.enable_pglogical. Set values for both to on. Choose the SAVE. The database instance will restart.
  4. Connect as postgres user and run the command to create pglogical extension:
CREATE EXTENSION pglogical;

For self-managed PostgreSQL databases, you can set required parameters directly in postgresql.conf file as follows:

wal_level = 'logical'
max_replication_slots = 10  # one per AWS DMS migration task required
max_wal_senders = 10        # one per AWS DMS migration task required
wal_sender_timeout = 0  # DMS replication connection can be idle for some time, to prevent diconnections disable timeout
shared_preload_libraries = 'pglogical'
track_commit_timestamp = on # needed for last/first update wins conflict resolution

Run the following query to check the installation:

SELECT * FROM pg_catalog.pg_extension where extname='pglogical';

5. Grant privileges required for initial data copy and CDC to replication_user:

grant usage on schema pglogical to replication_user;
grant select on all tables in schema pglogical to replication_user;

Grant select on all tables in each schema you plan to migrate. In this post, we migrate a schema named dms_sample:

grant usage on schema dms_sample to replication_user;
grant select on all tables in schema dms_sample to replication_user;

The source tables should have primary keys or, at least unique indexes. If you can’t create primary key or unique index, then you can set REPLICA IDENTITY for these tables by altering the table. Check the alter table documentation for more details about REPLICA IDENTITY property of the table. If you set the replica identity to FULL, you’ll force PostgreSQL to log old values of all columns of the affected table into the log stream. Depending on the number of columns and Data Manipulation Language (DML) statements in the database, it can significantly affect the size of the Write-Ahead Logs (WAL) generated and trigger more frequent checkpoints. Frequent checkpoints can negatively affect database performance. In such situations, consider adjusting the max_wal_size parameter of the database instance. In general, it’s recommended to avoid using REPLICA IDENTITY FULL, if possible.

Import metadata from the source database to the destination

This part is straight forward. We’ll export all objects without data rows from GCP Cloud SQL PostgreSQL into the sql file and then run this script on destination Amazon RDS for PostgreSQL instance.

From the shell which has access to source database run the following command:

pg_dump —schema=dms_sample —schema-only —file gcppg-schema.sql -h <IP or DNS name> -d postgres

This command exports all the metadata in form of DDL commands to a file named gcppg-schema.sql.

From the shell that has access to the target Amazon RDS for PostgreSQL instance run the following command:

psql --file=gcppg-schema.sql -h database-1.c#########m.eu-central-1.rds.amazonaws.com -d postgres

Prepare AWS DMS components

AWS DMS consists of these components:

  • Replication instance – Managed Amazon Elastic Compute Cloud (Amazon EC2) instance to host AWS DMS software.
  • Endpoints – Connection descriptions for source and target databases.
  • Database migration tasks – Describes the particular data load and/or Change Data Capture (CDC) instance.

The replication instance is required to test endpoints, which are required to define the migration tasks. Because of this dependency, we’ll first create a replication instance, and then endpoints and at the end replication task.

To create the replication instance, the instance type must be properly selected. The instance type selected must be powerful enough to handle the initial data load and the volume of the changes generated on source database. For more information on how to choose an instance class, see Choosing the right AWS DMS replication instance for your migration.

On the AWS DMS Management Console, choose Replication Instances page and select Create replication instance.

Follow the instructions from Setting up replication for AWS Database Migration Service to finalize replication instance creation.

In our scenario we have several small tables and one big table dms_sample.sporting_event_ticket with more than 14 million records. To maximize the initial data load speed and speed of change data apply, we’ll configure two replication tasks.

The first replication task will do the initial load of the small tables and start the continuous change data capture for them. The second task will do initial load of the big table dms_sample.sporting_event_ticket and start the continuous CDC for this table. Because this table is large, we’ll also configure a parallel load for it. We’ll start both of these migration tasks simultaneously.

Triggers can produce unexpected results on our target database during initial load and CDC phases. In our sample database, we have after-insert and after-update triggers, which do some calculations and insert result to the third results table. AWS DMS captures changes from all tables, including this third results table, and applies these changes onto a target database. If we left our trigger in enabled state, then the trigger runs and double entries occur in the table. To avoid such logical corruption situations, we disable triggers for the AWS DMS CDC processes. To achieve this behavior, we can use session level PostgreSQL parameter session_replication_role, which can be set to replica value. The value replica for this parameter disables all triggers including internal ones. Because referential integrity in PostgreSQL is implemented by internal triggers, the referential integrity between tables is disabled.

We set the session_replication_role parameter on our target Amazon RDS for PostgreSQL instance on two levels:

  • On the instance level by modifying instance parameter group. Because this parameter is dynamic no instance restart required. Instance level setting helps during the initial data load phase. After the initial load completes and replication tasks switch to continuous change data apply mode, we’ll disable this parameter.
  • On AWS DMS CDC apply the connection level. This way we can be sure that change data apply process always has proper session environment set to eliminate unwanted results and errors.

For the instructions on how to set session_replication_role parameter on instance level, see Create Amazon RDS parameter groups. Follow these instructions to prepare Amazon RDS for PostgreSQL instance for initial data load.

After AWS DMS replication instance is running, proceed to endpoints creation. We need two endpoints: one for source the GCP Cloud SQL instance and another for the destination Amazon RDS for PostgreSQL instance.

Source and target endpoints

To create secure endpoint connections, we need following:

  • AWS Secrets Manager stores the username and password of source and target endpoints as secrets. For source GCP Cloud SQL instance, we’ll use the previously created replication_user account. For the Amazon RDS instance, we’ll use the instance master account (in our case this is the postgres user).
  • AWS Identity and Access Management (AWS IAM) role provides AWS DMS access to the AWS Secrets Manager secrets.
  • Intermediate and root certificates of the GCP Cloud SQL and Amazon RDS.

To create the AWS Secrets Manager secret for Amazon RDS for PostgreSQL database instance, follow the instructions on Create a database secret. You can use these same instructions to create the secret for GCP Cloud SQL instance; however, for Secret Type choose Credentials for other database and for Database choose PostgreSQL. Enter the IP or DNS name of the GCP Cloud SQL instance and the name of the database to connect to the PostgreSQL instance. If you have more than one database in your GCP Cloud SQL PostgreSQL instance that you want to migrate, then create the AWS Secrets Manager secret, source endpoint, and migration task for each of these databases.

In our case, we created AWS Secrets Manager secrets: gcppg for GCP Cloud SQL PostgreSQL database and rdspg for Amazon RDS for PostgreSQL instance.

Now, we need an IAM role to allow AWS DMS to access our secrets. From the IAM Console, proceed to the Policies view and create the following policy. Replace the ARNs with the ARNs of the AWS Secrets Manager secrets created previously, as shown in the following code:

{
"Version": "2012-10-17",
"Statement": [
    {
      "Effect": "Allow",
      "Action": "secretsmanager:GetSecretValue",
      "Resource": [
                   "arn:aws:secretsmanager:eu-central-1:45########16:secret:gcppg-wVjLuz",
                   "arn:aws:secretsmanager:eu-central-1:45########16:secret:rdspg-t7rY9j"
                   ]
    }
  ]
}

Switch to the Roles view and create a role with DMS as an AWS trusted entity and previously created IAM policy. Make sure to have the DMS Regional Service Principal as trusted entity in form dms.<region name>.amazonaws.com. For the EU Central Region, for example, it will be dms.eu-central-1.amazonaws.com.

The last prerequisite is intermediate and root certificates in PEM (Privacy-Enhanced Mail) format. To download the certificate bundles for Amazon RDS, follow to the Certificate bundles for AWS Regions page and download certificate bundle for your region.

To download the certificate bundle from GCP Cloud SQL, do the following:

  1. Go to your database instance page
  2. From the left side menu choose Connections.
  3. Select the DOWNLOAD CERTIFICATES button to download the certificates bundle.

Upload these certificate bundles to the AWS DMS:

  1. Go to the AWS DMS Management Console.
  2. From the left side menu choose Certificates.
  3. Select the Import certificate button and Choose file to upload the certificate.

Now we can create AWS DMS secure endpoints.

Lets start from target endpoint:

  1. Go to AWS DMS Management Console.
  2. From the right side menu choose Endpoints.
  3. Select the Create endpoint button.
  4. Choose Target endpoint option.
  5. Check Select RDS DB instance checkbox.
  6. Choose the target Amazon RDS for PostgreSQL instance from the drop-down list.
  7. Enter the name for this endpoint into Endpoint identifier text box.
  8. Select AWS Secrets Manager as the Access to endpoint database.
  9. Enter the ARN of the AWS Secrets Manager secret created for Amazon RDS for PostgreSQL instance.
  10. Enter the ARN of the IAM role created previously to allow AWS DMS access the secret.
  11. For the Secure Socket Layer (SSL) mode select verfy-ca.
  12. Choose the CA certificate uploaded for Amazon RDS.
  13. Enter the database name to connect.
  14. In the Endpoint settings section, select the Add new setting and select AfterConnectScript from the list of the available parameters.
  15. Enter SET session_replication_role=replica into the Value field. This way, we instruct the AWS DMS replication task to set session_replication_role parameter to replica for change data apply connections. It disables triggers and referential integrity for the CDC phase.

Choose the Create endpoint button

After the successful endpoint creation, choose it from the list and select Test connection from the Actions drop down menu. The test must be successful to use this endpoint with replication task.

Follow the same steps to create source endpoint for the GCP Cloud SQL PostgreSQL; however, select the Source endpoint option.

After we have successfully tested secure source and target endpoints and replication instance, we can proceed to database migration tasks creation step.

Database migration tasks

We’ll create two migration tasks. The first copies and replicates the changes for all tables except the biggest table (dms_sample.sporting_event_ticket). The second migration task copies and replicates the dms_sample.sporting_event_ticket table in parallel.

To create first migration task, follow these steps:

  1. On the AWS DMS Console, select Database migration tasks from the right side menu.
  2. Select the Create task button to open task creation view.
  3. Enter the name for task into the Task identifier text box.
  4. From the Replication instance drop down list, select the replication instance.
  5. From the Source database endpoint drop down list, select the source endpoint created for GCP Cloud SQL PostgreSQL instance.
  6. From the Target database endpoint drop down list, select the destination endpoint created for Amazon RDS for PostgreSQL instance.
  7. From the Migration type drop down list, select Migrate existing data and replicate ongoing changes.
  8. Select the Create recovery table on target DB checkbox, which instructs the AWS DMS to create the awsdms_txn_state table on the target database. This table contains the checkpoint information and can be used to troubleshoot CDC issues.
  9. For the Target table preparation mode, select Do nothing (because we already pre‑created our schema).
  10. Select Enable validation checkbox if you want AWS DMS to compare all the data from source and target tables after initial data load. Only tables with primary keys or unique indexes will be compared.
  11. Select the Enable CloudWatch logs checkbox, to export AWS DMS logs into Amazon CloudWatch logs.
  12. In the Advanced task settings section, you can set some parameter to affect initial data load performance, for example, the number of tables to load in parallel and commit rate during the full load is 10000 by default. We’ll set commit rate to 30000.
  13. In the Table mappings section, select the Add new selection rule button.
  14. In the Schema drop down list, select Enter a schema. In the Source name text box field, enter the name of the schema you want to include. (dms_sample in our case, which is case sensitive). For Table name leave the value % for all tables. For Action, select Include. With this rule we include all tables in schema dms_sample.
  15. Select the Add new selection rule button. In the Table name text box, enter the name of the table you want to exclude (sporting_event_ticket in our case, which is case sensitive). For Action, select Exclude.
  16. For Start migration task, select Manually later. After creating two migration tasks, we start them in parallel.
  17. Select the Create task button.

To load the non-partitioned dms_sample.sporting_event_ticket table in parallel, we split it into several logical partitions. We have the primary key column on this table. The primary key column name is id, and we use this column to divide the table into equal partitions.

We divide this table into logical partitions. Each partition must be large enough to reduce overhead of multiple parallel selects and small enough to leverage index scans. In our case this optimal number will be 7. To implement it, we divide max id by 7, then create seven ranges.

We assume an even distribution of id column values. Otherwise, we may use percentile_cont() function to accomplish an even distribution.

select max(id)/7 from dms_sample.sporting_event_ticket;

The result is 21125353. The first range is be between id 1 and 21125353. The Second range starts at 21125353 and ends with 21125353+21125353=42250706, and so on. As the result, we have these ranges:

[1,         21125353]
[21125353,  42250706]
[42250706,  63376059]
[63376059,  84501412]
[84501412,  105626765]
[105626765, 126752118]
[126752118, 147877471]

With AWS DMS migration task, we can define rules in JSON format. For parallel loading, we’ll use parallel-load rule property with ranges type. The logical partitions are defined as range boundaries, which accept only the upper bound from each range. The JSON representation of the rule will be:

        {
            "rule-type": "table-settings",
            "rule-id": "2",
            "rule-name": "2",
            "object-locator": {
                "schema-name": "dms_sample",
                "table-name": "sporting_event_ticket"
            },
            "parallel-load": {
                "type": "ranges",
                "columns": [
                    "id"
                ],
                "boundaries": [
                    ["21125353"],
                    ["42250706"],
                    ["63376059"],
                    ["84501412"],
                    ["105626765"],
                    ["126752118"],
                    ["147877471"]
                ]
            }
        }

Create a new database migration task with the same settings used for the first task, with the exception of the Table mappings section.

Select the Add new selection rule button. In the Schema drop down list, select Enter a schema. in the Source name text box field, enter the name of the schema you want to include (dms_sample, in our case). In the Table name text box, enter the name of the table you want to include (sporting_event_ticket in our case). For Action, select Include.

Now select the JSON editor button. The rule editor text box will open, and our inclusion rule is displayed. Add the JSON rule for parallel loading into the JSON rules array:

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "087764588",
      "rule-name": "087764588",
      "object-locator": {
        "schema-name": "dms_sample",
        "table-name": "sporting_event_ticket"
      },
      "rule-action": "include",
      "filters": []
    },
    {
            "rule-type": "table-settings",
            "rule-id": "2",
            "rule-name": "2",
            "object-locator": {
                "schema-name": "dms_sample",
                "table-name": "sporting_event_ticket"
            },
            "parallel-load": {
                "type": "ranges",
                "columns": [
                    "id"
                ],
                "boundaries": [
                    ["21125353"],
                    ["42250706"],
                    ["63376059"],
                    ["84501412"],
                    ["105626765"],
                    ["126752118"],
                    ["147877471"]
                ]
            }
   }
  ]
}

Set Start migration task to Manually later and select Create task button.

Run migration tasks and monitoring

After all these preparations, we have two database migration tasks.

To start the tasks, select the task from the migration tasks list and select Restart/Resume from the Actions drop down menu.

Start both replication tasks in parallel.

AWS DMS starts the migration tasks. First, the initial data load process is initiated. After the initial load completes, the data validation process compares the data row-by-row between the source and destination databases. Then, the change data capture process applies the changes to the migrated tables, which was executed during data load phase, and the migration tasks switch to ongoing replication mode.

You can track the progress of the migration on the Table statistics tab of the particular database migration task. The CloudWatch metrics tab gives you information about different stages of the migration task lifecycle, which includes:

  • Full load – Throughput metrics in kilobytes and rows.
  • CDC – Change data capture throughput and latency.
  • Validation – Metrics related to data validation phase for example, number of rows validated or failed, query latencies.
  • Impact on replication instance – How much memory was used by this particular migration task.

For more information, about the AWS DMS monitoring and metrics see the post Debugging Your AWS DMS Migrations: What to Do When Things Go Wrong.

Cut-over to Amazon RDS for PostgreSQL

AWS DMS migration tasks synchronize data between GCP Cloud SQL and Amazon RDS database instances. Now when data is in sync, the application cut-over can be planned. Check the Amazon CloudWatch CDC metrics for the replication latency values, and execute DMS data validation tasks, if required.

Before cutting over, pay attention to the following steps:

  • Set the parameter session_replication_role back to its default value origin. You can do it after initial load phase completed.
  • Vacuum and analyze the tables on the target Amazon RDS for PostgreSQL instance.
  • Consider updating sequences to their current values from the source database.
  • Make sure that any database parameters tuned on GCP Cloud SQL instance are set on the Amazon RDS for PostgreSQL as well. Adjust them accordingly.

Clean up

To clean up the resources used in this post:

  • Delete GCP Cloud SQL instance.
  • Delete Amazon RDS for PostgreSQL instance.
  • Delete AWS DMS replication instance, migration tasks, and endpoints.
  • Delete database secrets and associated IAM role.

Conclusion

In this post, we showed you a procedure to accomplish a database migration from Google Cloud Platform or other platforms or on-premises data centers into the Amazon RDS. AWS Database Migration Service provides full data load, data validation, and continuous change data capture possibilities. Together with its broad range of configuration and monitoring capabilities, it’s a powerful asset for data migration projects of any scale.

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

About the author

Aychin Gasimov is a Senior Partner Solutions Architect at AWS. He works with our customers and partners to provide guidance and technical assistance on various database migration and modernization projects.