Incrementally migrate self-managed PostgreSQL (v10.0 or earlier) to ApsaraDB for PostgreSQL

Prerequisites

• The source self-managed PostgreSQL database is version 9.4.8 or later, 9.5, 9.6, or 10.0.
• The destination ApsaraDB for PostgreSQL instance has sufficient storage for the source data.
• The source PostgreSQL database port is accessible over the internet.

Usage notes

• A full data migration consumes read and write resources on both the source and destination databases, increasing their server load. This added load can degrade performance or cause service unavailability, especially if your databases have poor performance, low specifications, or are already busy (for example, with many slow SQL queries, tables without primary keys, or deadlocks in the destination database). Before you start the migration, evaluate the performance of both databases and perform the migration during off-peak hours, for example, when CPU utilization is below 30%.

• During incremental data migration, if you select a schema as the migration object and then create a new table within that schema or rebuild a table by using the RENAME command, you must execute the ALTER TABLE schema.table REPLICA IDENTITY FULL; command before writing data to the table.
  Note Replace schema and table in the command with the actual schema and table names.

• If a source table to be migrated lacks a primary key or unique constraint, duplicate data may occur in the destination database.

• DTS automatically retries failed tasks. Stop or release the task before switching applications to the destination instance to prevent source data from overwriting destination data.
• Long-running transactions in the source database during incremental migration prevent WAL records from being cleared, which can exhaust disk space.

Limitations

• A single data migration task can migrate data from only one database. If you need to migrate multiple databases, you must create a separate data migration task for each one.
• The name of a database to be migrated cannot contain hyphens (-), for example, dts-testdata.
• If a primary/secondary switchover occurs on the source database during migration, the incremental data migration task cannot resume from where it left off.
• Replication latency can cause data inconsistency between the primary and secondary nodes of the source database. Always use the primary node of the source database as the data source for migration.
  Note To avoid impacting your business, perform data migration during off-peak hours. You can also adjust the migration rate based on the read and write load on your source database. For more information, see Modify the transfer rate of full data migration.
• DTS validates data content but does not currently support the validation of metadata such as sequences. You must validate this metadata manually.
• Sequences in the destination database do not continue from the source maximum after switchover. Before switching, query each sequence's maximum value in the source and set it as the starting value in the destination. Query sequence values:

  do language plpgsql $$
  declare
    nsp name;
    rel name;
    val int8;
  begin
    for nsp,rel in select nspname,relname from pg_class t2 , pg_namespace t3 where t2.relnamespace=t3.oid and t2.relkind='S'
    loop
      execute format($_$select last_value from %I.%I$_$, nsp, rel) into val;
      raise notice '%',
      format($_$select setval('%I.%I'::regclass, %s);$_$, nsp, rel, val+1);
    end loop;
  end;
  $$;

• To ensure the migration runs smoothly, primary/secondary switchover is currently supported only for ApsaraDB RDS for PostgreSQL 11. You must also set the rds_failover_slot_mode parameter to sync. For instructions, see Logical Replication Slot Failover.
  Warning A primary/secondary switchover on a self-managed PostgreSQL database or other versions of ApsaraDB RDS for PostgreSQL will interrupt the migration.

Billing

Database account permissions

To create accounts and grant permissions:

• Self-managed PostgreSQL database: CREATE USER and GRANT.
• ApsaraDB for PostgreSQL instance: Create an account.

Data migration process

To resolve object dependencies and improve the migration success rate, DTS migrates PostgreSQL schemas and data in the order shown in the following table.

Preparations

1. Optional: Download, compile, and install the PostgreSQL source code.
   Note Skip if your database was deployed from source code.
   1. Download the source code for your database version from the PostgreSQL official website.
   2. Run the following commands to configure, compile, and install the source code.

      sudo ./configure --prefix=/usr/local/postgresql
      sudo make
      sudo make install

      Important

      • The prefix value must differ from your existing PostgreSQL installation path.
      • The OS version must be compatible with the GCC version.
      • If sudo ./configure fails, adjust based on the error. For example, for readline library not found. Use --without-readline to disable readline support., use sudo ./configure --without-readline.
      • If you use a different installation method, compile the ali_decoding plugin in a test environment with the same OS and GCC versions.
2. Compile a new ali_decoding file from the PostgreSQL source code and replace the existing file.
   1. Download the DTS-provided ali_decoding plugin.
      Note Run these commands to download and extract the plugin.

      sudo wget https://github.com/aliyun/rds_dbsync/archive/refs/heads/master.zip
      unzip master.zip

   2. Copy the ali_decoding directory to the contrib directory of the compiled PostgreSQL installation.

      sudo cp rds_dbsync-master/ali_decoding /tmp/postgresql-9.4.26/contrib/ -r

      Note Replace /tmp/postgresql-9.4.26/contrib/ with your PostgreSQL source code directory.

      total 1068
      -rw-r--r--  1 1107 1107    384 Sep 27  2016 aclocal.m4
      drwxrwxrwx  2 1107 1107   4096 Sep 27  2016 config
      -rw-r--r--  1 root root 374806 Sep  7 10:10 config.log
      -rwxr-xr-x  1 root root  39032 Sep  7 10:10 config.status
      -rwxr-xr-x  1 1107 1107 471157 Sep 27  2016 configure
      -rw-r--r--  1 1107 1107  75195 Sep 27  2016 configure.in
      drwxrwxrwx 56 1107 1107   4096 Sep  7 10:28 contrib
      -rw-r--r--  1 1107 1107   1192 Sep 27  2016 COPYRIGHT
      drwxrwxrwx  3 1107 1107   4096 Sep 27  2016 doc
      -rw-r--r--  1 root root   3638 Sep  7 10:10 GNUmakefile
      -rw-r--r--  1 1107 1107   3638 Sep 27  2016 GNUmakefile.in
      -rw-r--r--  1 1107 1107    283 Sep 27  2016 HISTORY
      -rw-r--r--  1 1107 1107  75065 Sep 27  2016 INSTALL
      -rw-r--r--  1 1107 1107   1489 Sep 27  2016 Makefile
      -rw-r--r--  1 1107 1107   1209 Sep 27  2016 README
      drwxrwxrwx 16 1107 1107   4096 Sep  7 10:10 src

   3. In the ali_decoding directory, replace the Makefile content with:

      # contrib/ali_decoding/Makefile
      MODULE_big = ali_decoding
      MODULES = ali_decoding
      OBJS    = ali_decoding.o
      DATA = ali_decoding--0.0.1.sql ali_decoding--unpackaged--0.0.1.sql
      EXTENSION = ali_decoding
      NAME = ali_decoding
      #subdir = contrib/ali_decoding
      #top_builddir = ../..
      #include $(top_builddir)/src/Makefile.global
      #include $(top_srcdir)/contrib/contrib-global.mk
      #PG_CONFIG = /usr/pgsql-9.6/bin/pg_config
      #pgsql_lib_dir := $(shell $(PG_CONFIG) --libdir)
      #PGXS := $(shell $(PG_CONFIG) --pgxs)
      #include $(PGXS)
      # For source code installation, use the following:
      ifdef USE_PGXS
      PG_CONFIG = pg_config
      PGXS := $(shell $(PG_CONFIG) --pgxs)
      include $(PGXS)
      else
      subdir = contrib/ali_decoding
      top_builddir = ../..
      include $(top_builddir)/src/Makefile.global
      include $(top_srcdir)/contrib/contrib-global.mk
      endif

   4. Optional: For PostgreSQL 9.4 (9.4.8 to 9.4.26), remove the false parameter from the set_config_option function in ali_decoding.c. The modified function:

          if (extra_float_digits < 3)
              (void) set_config_option("extra_float_digits", "3",
                      PGC_USERSET, PGC_S_SESSION,
                      GUC_ACTION_SAVE, true, 0);

   5. In the ali_decoding directory, run sudo make and sudo make install. Expected output after sudo make install:

      /usr/bin/install -c -m 755  ali_decoding.so '/usr/local/pgsql/lib/ali_decoding.so'
      /usr/bin/install -c -m 644 ./ali_decoding.control '/usr/local/pgsql/share/extension/'
      /usr/bin/install -c -m 644 ./ali_decoding--0.0.1.sql ./ali_decoding--unpackaged--0.0.1.sql  '/usr/local/pgsql/share/extension/'
      /usr/bin/install -c -m 755  ali_decoding.so '/usr/local/pgsql/lib/'

   6. Copy the generated ali_decoding files to your source PostgreSQL installation, replacing the files at these paths.
      Note Replace /usr/local/postgresql with your source PostgreSQL installation path.

      /usr/local/postgresql/lib/ali_decoding.so
      /usr/local/postgresql/share/extension/ali_decoding.control 
      /usr/local/postgresql/share/extension/ali_decoding--0.0.1.sql 
      /usr/local/postgresql/share/extension/ali_decoding--unpackaged--0.0.1.sql

3. Create the corresponding database and schema in the destination ApsaraDB for PostgreSQL instance. Schema names must match the source. Create a database and Manage schemas.

Procedure

1. Log on to the Data Transmission Service (DTS) console.

   Note

   If you are automatically redirected to the Data Management (DMS) console, you can click the icon in the lower-right corner and then click to return to the classic DTS console.

2. In the navigation pane on the left, click Data Migration.

3. At the top of the Migration Tasks page, select the region where the destination instance is located.

4. In the upper-right corner of the page, click Create Data Migration Task.

5. Configure the source and destination databases.

   Category	Setting	Description
   N/A	Task Name	DTS generates a task name automatically. Specify a descriptive name for easy identification. Uniqueness is not required.
   Source Database	Instance Type	Select based on your deployment. This example uses self-managed database with a public IP address. Note Other instance types require additional preparation. Overview of preparations.
   	Instance Region	If you select self-managed database with a public IP address as the instance type, you do not need to set the Instance Region. Note If you configured a whitelist for your self-managed PostgreSQL database, click Get IP Address Segment of DTS next to the Instance Region setting to get DTS server IP addresses and add them to your whitelist.
   	Database Type	Select PostgreSQL.
   	Hostname or IP Address	Enter the self-managed PostgreSQL database endpoint. In this example, enter the public IP address.
   	Port	Enter the service port. Default: 5432.
   	Database Name	Enter the name of the database to migrate.
   	Database Account	Enter the database account. For permissions, see Database account permissions.
   	Database Password	Enter the password for the database account. Note After you enter the source database information, you can click Test Connectivity next to Database Password to verify that the information is correct. If the information is correct, the message Passed is displayed. If the message Failed is displayed, click Diagnose next to the Failed message and adjust the source database information based on the prompts.
   Destination Database	Instance Type	Select RDS Instance.
   	Instance Region	Select the region of the destination RDS instance.
   	RDS Instance ID	Select the destination RDS instance ID.
   	Database Name	Enter the destination database name. This can differ from the source database name. Note Create the database and schema in the RDS instance first. Preparations.
   	Database Account	Enter the database account. For permissions, see Database account permissions.
   	Database Password	Enter the password for the database account. Note After you enter the destination database information, you can click Test Connectivity after Database Password to verify that the entered information is correct. If the information is correct, a Passed message is displayed. If a Failed message is displayed, click Diagnose after Failed and adjust the destination database information based on the prompts.

6. After completing the configuration, click Authorize Whitelist and Go to Next Step.

   If the source or destination is an Alibaba Cloud database instance (such as ApsaraDB RDS for MySQL or ApsaraDB for MongoDB), DTS automatically adds the IP addresses of DTS servers in the corresponding region to the instance's whitelist. If the source or destination is a self-managed database on an ECS instance, DTS automatically adds the DTS server IP addresses to the security rules of the ECS instance. You must also ensure that the self-managed database does not restrict access from the ECS instance. If the database is a cluster deployed across multiple ECS instances, you must manually add the DTS server IP addresses for the region to the security rules of each ECS instance. If the source or destination is a database in a data center or another cloud, you must manually add the IP addresses of the DTS servers for the region to allow access from them. For a list of DTS server IP addresses, see IP address blocks of DTS servers.

   Warning

   Adding public IP addresses of DTS servers, whether automatically or manually, may introduce security risks. By using this product, you acknowledge and accept these potential risks. You are responsible for implementing basic security measures, such as using strong passwords, restricting open ports, using authentication for internal APIs, regularly reviewing and limiting unnecessary network segments, or connecting by using private connections such as Express Connect, VPN Gateway, or Smart Access Gateway.

7. Select the migration types and objects.

   Setting	Description
   Migration Type	For full data migration only, select both Schema Migration and Full Data Migration. For minimal downtime, select Schema Migration, Full Data Migration, and Incremental Data Migration. In this example, all three migration types are selected. Note If Incremental Data Migration is not selected, do not write to the source database during full data migration to ensure data consistency.
   Migration Object	Select objects from the Available box and click to move them to the Selected Objects box. Note You can select objects at the schema, table, or column level. Object names remain unchanged after migration by default. To rename objects, use the DTS object name mapping feature. Map tables and columns. Object name mapping may cause dependent object migration to fail.
   Object name mapping	To rename migrated objects in the destination instance, use object name mapping. Map tables and columns.
   Connection retry duration	DTS retries for 12 hours by default. You can customize this duration. If DTS reconnects within the specified time, the task resumes; otherwise, it fails. Note DTS charges for running tasks during the retry period. Customize the retry duration based on your needs, or release the DTS instance when the source and destination are released.

8. After you complete these settings, click Pre-check and Start in the lower-right corner of the page.

   Note

   • DTS performs a precheck before the task starts. The task can start only after it passes the precheck.

   • If the precheck fails, click the icon next to the failed item to view details.

     • Fix the issues based on the details, and then run the precheck again.

     • For warnings that do not require a fix, you can select Ignore or Ignore and Rerun Precheck to run the precheck again.

9. After the task passes the precheck, click Next.

10. On the Confirm Order page, select a Instance Class and select the Data Transmission Service (Pay-As-You-Go) Terms of Service checkbox.

11. Click Buy and Start. The migration task starts.

    • Full data migration

      Do not manually stop the migration task. This can cause data loss. Wait for the task to finish. The task stops automatically.

    • Incremental data migration

      The migration task does not stop automatically. You must stop it manually.

      Note

      Stop the task manually at a suitable time, such as during off-peak hours or when you are ready to switch your business to the destination instance.

      1. Wait until the task status changes to Incremental Data Migration and shows Undelayed. Then, stop writing data to the source database for a few minutes. The status of Incremental Data Migration may show a latency.

      2. Wait until the status of Incremental Data Migration changes back to Undelayed. Then, stop the migration task manually. The progress of schema migration and full data migration both show 100%. To pause the migration task, select the task and click Pause in the batch operations bar at the bottom.

12. Switch your applications to the RDS instance.

What to do next

After migration, delete the migration accounts from both the source and destination databases for security.