ApsaraDB for SelectDB:Migrate data from a PostgreSQL data source

Compare migration solutions

Choose a migration solution

• DTS: Best for production workloads that require real-time synchronization, DDL propagation, and end-to-end data verification. DTS handles the full migration lifecycle with minimal manual setup.

• DataWorks: Suitable when your team already uses DataWorks for data integration workflows and needs batch synchronization with scheduling support.

• Flink CDC: Suitable for teams with existing Flink infrastructure who need fine-grained control over the synchronization pipeline, including customizing parallelism and table filtering.

• Catalog: A lightweight option for one-time historical data loads. Use a JDBC catalog to query PostgreSQL data through federated queries, then copy it into SelectDB with INSERT INTO SELECT.

Prerequisites

Before you begin, make sure that:

• The PostgreSQL source and the SelectDB instance can communicate over the network.

  • If they are in the same virtual private cloud (VPC), no additional network configuration is required.

  • If they are in different VPCs, configure cross-VPC connectivity. For details, see What do I do if a connection fails to be established between my SelectDB instance and a data source?

• The IP address of the PostgreSQL source is added to the IP address whitelist of the SelectDB instance. For details, see Configure an IP address whitelist.

• If the PostgreSQL source supports an IP address whitelist, the CIDR block of the SelectDB instance is added to it.

  • To find the VPC IP address of the SelectDB instance: go to the SelectDB console, open the Instance Details page, and check the Network Information section. For details, see How do I view the IP addresses in the VPC to which my ApsaraDB SelectDB instance belongs?

  • To find the public IP address of the SelectDB instance: run the ping command against the public endpoint.

Use DTS to migrate data

DTS supports historical data migration, incremental data synchronization, schema migration, DDL operation synchronization, and data verification.

Refer to the following DTS guides based on your PostgreSQL source type:

Data synchronization:

• Synchronize data from a PolarDB for PostgreSQL cluster to an ApsaraDB for SelectDB instance

• Synchronize data from an ApsaraDB RDS for PostgreSQL instance to an ApsaraDB for SelectDB instance

Data migration:

• Migrate data from a PolarDB for PostgreSQL cluster to an ApsaraDB for SelectDB instance

• Migrate data from an ApsaraDB RDS for PostgreSQL instance to an ApsaraDB for SelectDB instance

Use DataWorks to migrate data

Use Flink CDC to migrate data

Flink CDC supports historical data migration, incremental data synchronization, schema migration, and DDL operation synchronization.

<FLINK_HOME>/bin/flink run \
    -Dexecution.checkpointing.interval=10s \
    -Dparallelism.default=1 \
    -c org.apache.doris.flink.tools.cdc.CdcTools \
    lib/flink-doris-connector-1.16-1.5.2.jar \
    postgres-sync-database \
    --database db1 \
    --postgres-conf hostname=127.0.0.1 \
    --postgres-conf port=5432 \
    --postgres-conf username=postgres \
    --postgres-conf password="123456" \
    --postgres-conf database-name=postgres \
    --postgres-conf schema-name=public \
    --postgres-conf slot.name=test \
    --postgres-conf decoding.plugin.name=pgoutput \
    --including-tables "tbl1|test.*" \
    --sink-conf fenodes=selectdb-cn-****.selectdbfe.rds.aliyuncs.com:8080 \
    --sink-conf username=admin \
    --sink-conf password=****

Use a catalog to migrate data

The catalog approach uses a JDBC catalog to query PostgreSQL data through federated queries, then copies it into SelectDB with INSERT INTO SELECT. This approach is suited for one-time historical data loads.

Do not use Data Management (DMS) to connect to the SelectDB instance for this procedure — the SWITCH statement becomes invalid in DMS sessions. Use a MySQL client instead.

1. Connect to the SelectDB instance. For details, see Connect to an instance.

2. Create a JDBC catalog for the PostgreSQL source.

   Parameters:

   Parameter	Required	Description	Example
   user	Yes	The account used to connect to the PostgreSQL source.	root
   password	Yes	The password for the account.	123456
   jdbc_url	Yes	The JDBC URL for the PostgreSQL source.	jdbc:postgresql://127.0.0.1:5432/demo
   driver_url	Yes	The filename of the JDBC driver JAR.	postgresql-42.5.1.jar
   driver_class	Yes	The JDBC driver class name.	org.postgresql.Driver
   lower_case_table_names	No	Whether to sync database and table names in lowercase. Default: false.	false
   only_specified_database	No	Whether to migrate only specific databases. Default: false.	false
   include_database_list	No	Comma-separated list of databases to migrate. Takes effect only when only_specified_database=true. Database names are case-sensitive. Default: "".	db1,db2
   exclude_database_list	No	Comma-separated list of databases to exclude. Takes effect only when only_specified_database=true. Database names are case-sensitive. Default: "".	tmp_db

   CREATE CATALOG jdbc_postgresql PROPERTIES (
       "type"="jdbc",
       "user"="root",
       "password"="123456",
       "jdbc_url" = "jdbc:postgresql://127.0.0.1:5432/demo",
       "driver_url" = "postgresql-42.5.1.jar",
       "driver_class" = "org.postgresql.Driver",
       "checksum" = "20c8228267b6c9ce620fddb39467d3eb"
   );

   For the complete catalog configuration reference, see JDBC data source.

3. Create a table in SelectDB and copy data from the PostgreSQL source using INSERT INTO SELECT.

   -- Create the target table
   CREATE TABLE selectdb_table ...
   
   -- Migrate data from the PostgreSQL source
   INSERT INTO selectdb_table SELECT * FROM jdbc_postgresql.pg_database.pg_table;

   For details on the INSERT INTO syntax, see Use INSERT INTO to import data.