ApsaraDB RDS:Migrate data from an ApsaraDB MyBase for PostgreSQL instance to an ApsaraDB RDS for PostgreSQL instance

Preparations

1. Create an ApsaraDB RDS for PostgreSQL instance that meets the following requirements:
   • The major engine version and edition of the ApsaraDB RDS for PostgreSQL instance are the same as those of the ApsaraDB MyBase for PostgreSQL instance.
   • The storage capacity of the ApsaraDB RDS for PostgreSQL instance is larger than that of the ApsaraDB MyBase for PostgreSQL instance.
   • The ApsaraDB RDS for PostgreSQL instance belongs to the dedicated instance family.
   For more information, see Create an ApsaraDB RDS for PostgreSQL instance.
   Note

   • If you migrate data over an internal network, make sure that the region and virtual private cloud (VPC) of the ApsaraDB RDS for PostgreSQL instance are the same as those of the ApsaraDB MyBase for PostgreSQL instance.
   • If you migrate data over the Internet, make sure that you applied for public endpoints for the ApsaraDB RDS for PostgreSQL instance and the ApsaraDB MyBase for PostgreSQL instance. For more information, see Apply for or release a public endpoint on an ApsaraDB RDS for PostgreSQL instance and Apply for or release a public endpoint.
2. Configure IP address whitelists for the ApsaraDB RDS for PostgreSQL instance and the ApsaraDB MyBase for PostgreSQL instance to enable communication between the instances. For more information, see Configure an IP address whitelist for an ApsaraDB RDS for PostgreSQL instance and Configure an IP address whitelist.
   To view the CIDR blocks that you must add to the IP address whitelists, you can log on to the ApsaraDB RDS console or the ApsaraDB MyBase console and go to the Database Connection page of each instance.
3. Create a privileged account for the ApsaraDB MyBase for PostgreSQL instance. For more information, see Create a database account.

Usage notes

• During the cloud migration, you can read data from and write data to the ApsaraDB MyBase for PostgreSQL instance. However, we recommend that you do not perform operations such as migration, restart, or specification changes on the instance.
• The ApsaraDB MyBase for PostgreSQL instance is configured to process only read requests.
• The ApsaraDB RDS for PostgreSQL instance that uses a new general-purpose instance type cannot be used for migration over the Internet. For more information, see Primary ApsaraDB RDS for PostgreSQL instance types (x86).

Step 1: Evaluate whether data can be migrated

1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
2. In the left-side navigation pane, click Migrate to Cloud. On the page that appears, click the Migration Assessment tab.
3. In the Select Migration Source step of the configuration wizard, select Self-managed ECS-based PostgreSQL Database or ApsaraDB RDS for PostgreSQL Instance and click Next.
4. In the Configure Destination Database step of the configuration wizard, click Next.
5. In the Configure Source Database step of the configuration wizard, select all listed items and click Next. You must complete the preparations that are described in the listed items before you can start the cloud migration.
   
6. In the Initiate Migration Assessment step of the configuration wizard, configure the parameters for the ApsaraDB MyBase for PostgreSQL instance.

   Parameter	Description
   Migration Task Name	The name of the cloud migration task. The system automatically generates a name for the cloud migration task. You do not need to modify the generated name.
   Source VPC/DNS IP or Source Public/DNS IP	The endpoint of the ApsaraDB MyBase for PostgreSQL instance. For information about how to view the private and public endpoints of an instance, see View and change the private and public endpoints and ports of an instance.
   Source Port	The port number of the ApsaraDB MyBase for PostgreSQL instance. For more information, see View and change the private and public endpoints and ports of an instance.
   Username	The username of the privileged account for the ApsaraDB MyBase for PostgreSQL instance. For more information, see Create a database account.
   Password	The password of the privileged account for the ApsaraDB MyBase for PostgreSQL instance.

7. Click Create Migration Assessment Task.
   Note During the cloud migration assessment, the status of the ApsaraDB MyBase for PostgreSQL instance changes to Maintaining Instance.
   After the cloud migration assessment is complete, you can view the status of the cloud migration assessment task on the Migration Assessment tab.

   • If Successful is displayed in the Status column of the cloud migration assessment task, you can start the cloud migration. For more information, see Step 2: Start the cloud migration.
   • If Failed is displayed in the Status column of the cloud migration assessment task, you can click View Report in the Actions column to view and handle the reported errors. For more information about common errors, see Introduction to cloud migration assessment reports.

   After you handle the reported errors, you can click Re-access in the Actions column to run the cloud migration assessment task again.

   

Step 2: Start the cloud migration

1. On the Migration to Cloud tab, click Create Cloud Migration Task.
   
2. In the dialog box that appears, select the cloud migration assessment task whose state indicates a success as described in Step 1: Evaluate whether data can be migrated from the Associated Assessment Task drop-down list.
   

   Note After you select a cloud migration assessment task from the Associated Assessment Task drop-down list, the system automatically obtains the values of the Migration Source Type, Source IP/DNS, Source Port, and Username parameters. You do not need to manually configure these parameters.
3. Click Initiate Migration to Cloud. The system automatically starts the cloud migration task.
   Important During the cloud migration, the status of the ApsaraDB MyBase for PostgreSQL instance changes to Migrating Data In. You can read data from and write data to the ApsaraDB MyBase for PostgreSQL instance. However, we recommend that you do not perform operations such as migration, restart, or specification changes on the instance.
4. Switch the workloads.
   1. Click the link in the Cloud Migration Phase column to view the progress of the cloud migration task.
   2. When the cloud migration task enters the Incremental Data Synchronization phase, click Switchover in the Actions column of the cloud migration task to switch the workloads of the ApsaraDB MyBase for PostgreSQL instance to the ApsaraDB RDS for PostgreSQL instance.
   3. In the Switchover dialog box, configure the ApsaraDB MyBase for PostgreSQL instance to process only read requests. You can also stop connected applications from writing data to the ApsaraDB MyBase for PostgreSQL instance.
      Note You can perform the following operations to configure the ApsaraDB MyBase for PostgreSQL instance to process only read requests:

      Connect to a database on the ApsaraDB MyBase for PostgreSQL instance and execute the following statement to check whether a superuser exists. For more information, see Use DMS to log on to an ApsaraDB MyBase for PostgreSQL database.

      SELECT CASE WHEN(count(rolname)<= 3) THEN 'superuser check ok' ELSE 'exists superuser created by user' END AS result
        FROM pg_roles
       where rolsuper= 't'
         and rolname in ('aurora', 'replicator')
          or rolname like 'pg%'
       GROUP BY rolname
       limit 1;

      • If a superuser exists, contact Alibaba Cloud technical support.
      • If no superusers exist, perform the following steps:
        1. Log on to the ApsaraDB MyBase console.
        2. On the Parameters page of the ApsaraDB MyBase for PostgreSQL instance, change the value of the rds_force_trans_ro_non_sup parameter to on.
        3. Connect to the database on the ApsaraDB MyBase for PostgreSQL instance and execute the following statement to terminate all existing sessions:

           SELECT pg_terminate_backend(pid) FROM pg_stat_activity
           WHERE usename not in ('replicator', 'monitor', 'pgsql', 'aurora') AND pid != pg_backend_pid();

   4. Select all check boxes and click Switch Now. Then, wait until the cloud migration is complete.

Step 4: Connect your application to the ApsaraDB RDS for PostgreSQL instance

Method 1: Change the endpoint (recommended)

View and change the endpoint of the ApsaraDB MyBase for PostgreSQL instance. Then, replace the endpoint of the ApsaraDB RDS for PostgreSQL instance with the original endpoint of the ApsaraDB MyBase for PostgreSQL instance. For more information, see View and change the private and public endpoints and ports of an instance and View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.

For example, the endpoint of your ApsaraDB MyBase for PostgreSQL instance is pgm-aaa.pg.rds.aliyuncs.com and the endpoint of your ApsaraDB RDS for PostgreSQL instance is pgm-bbb.pg.rds.aliyuncs.com. You can change the endpoint of your ApsaraDB MyBase for PostgreSQL instance to pgm-ccc.pg.rds.aliyuncs.com and then change the endpoint of your ApsaraDB RDS for PostgreSQL instance to pgm-aaa.pg.rds.aliyuncs.com.

Method 2: Change the endpoint that is configured in your application

Obtain the endpoint of the ApsaraDB RDS for PostgreSQL instance and use the endpoint to replace the database endpoint that is configured in your application. For more information, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance