AnalyticDB for PostgreSQL provides multiple data migration and synchronization solutions to meet different business requirements. This allows you to migrate and synchronize data across various kinds of databases.

Migrate data to AnalyticDB for PostgreSQL

Migration type References Description
Write data Use INSERT ON CONFLICT to overwrite data The INSERT ON CONFLICT statement allows you to update an existing row that contains a primary key when you execute the INSERT statement to insert a new row that contains the same primary key.

Use COPY ON CONFLICT to overwrite data When you import data by executing a COPY statement, the data that you want to import may conflict with table constraints, and the COPY statement may fail with an error message. To solve this issue, AnalyticDB for PostgreSQL provides the COPY ON CONFLICT statement.
Use the Client SDK AnalyticDB for PostgreSQL provides the Client SDK that allows you to develop new or access existing data write programs. A custom data write program developed by using the Client SDK simplifies the data write process based on its internal mechanisms such as parallel processing and delivers a performance that is multiple times better than COPY and INSERT statements. You do not need to worry about connection pool or cache issues.AnalyticDB for PostgreSQL
Migrate table data Use Data Integration to migrate and batch synchronize data Data Integration is a secure, cost-effective, stable, efficient, and elastic data synchronization platform provided by Alibaba Cloud. It is one of the core features of DataWorks. It supports data migration and synchronization across heterogeneous systems in diverse network environments. For more information, see Supported data sources, readers, and writers.
Use a Realtime Compute for Apache Flink cluster to write data to an AnalyticDB for PostgreSQL instance Alibaba Cloud Realtime Compute for Apache Flink provides an end-to-end platform to process big data in real time based on Apache Flink. You can use Realtime Compute for Apache Flink to write data to AnalyticDB for PostgreSQL instances.
Use the \COPY command to import or export on-premises data You can use the \copy command to import data from local text files in your computer to an AnalyticDB for PostgreSQL instance.
Migrate data by using an OSS external table AnalyticDB for PostgreSQL allows you to use the Object Storage Service (OSS) external table (gpossext) feature to import data from OSS to AnalyticDB for PostgreSQL.
Use an external table to read and write HDFS data You can migrate data from a Hadoop cluster to an AnalyticDB for PostgreSQL instance by using the gphdfs protocol.
Migrate warehouse data Migrate data from a self-managed Greenplum cluster to an AnalyticDB for PostgreSQL instance AnalyticDB for PostgreSQL V6.0 is built based on Greenplum 6.0 and optimized by Alibaba Cloud. AnalyticDB for PostgreSQL V6.0 supports vector computing and transaction processing when multiple coordinator nodes are supported. It uses the same API operations as Greenplum Community Edition. Both applications and data can be migrated from Greenplum to AnalyticDB for PostgreSQL. AnalyticDB for PostgreSQL allows you to smoothly migrate your applications and select from a variety of data migration solutions to migrate data.
Migrate data from a Teradata database to an AnalyticDB for PostgreSQL instance AnalyticDB for PostgreSQL is compatible with Teradata syntax, which allows you to migrate data from a Teradata database to an AnalyticDB for PostgreSQL instance.
Migrate data from an Amazon Redshift instance to an AnalyticDB for PostgreSQL instance You can migrate data from an Amazon Redshift instance to an AnalyticDB for PostgreSQL instance.
Migrate data from an Oracle database to an AnalyticDB for PostgreSQL instance AnalyticDB for PostgreSQL is compatible with Oracle syntax, which allows you to migrate data from an Oracle database to an AnalyticDB for PostgreSQL instance.
Migrate data from a self-managed Oracle database to an AnalyticDB for PostgreSQL instance You can use Data Transmission Service (DTS) to migrate data from a self-managed Oracle database to an AnalyticDB for PostgreSQL instance.

Migrate data from AnalyticDB for PostgreSQL

References Description
Use \COPY to export data to your computer You can use the \copy command to export data from an AnalyticDB for PostgreSQL instance to your computer.
Use OSS external tables to export data to OSS AnalyticDB for PostgreSQL allows you to export data to OSS buckets in parallel by using the OSS external table (gpossext) feature. AnalyticDB for PostgreSQL also supports GZIP compression for OSS external tables to reduce file size and storage costs.

Synchronize data from cloud databases

References Description
Use DTS to synchronize data from an ApsaraDB RDS for MySQL instance to an AnalyticDB for PostgreSQL instance You can use DTS to synchronize data from an ApsaraDB RDS for MySQL instance to an AnalyticDB for PostgreSQL instance.AnalyticDB for PostgreSQL
Synchronize data from an ApsaraDB RDS for SQL Server instance to an AnalyticDB for PostgreSQL instance You can use DTS to synchronize data from an ApsaraDB RDS for SQL Server instance to an AnalyticDB for PostgreSQL instance.AnalyticDB for PostgreSQL
Synchronize data from an ApsaraDB RDS for PostgreSQL instance to an AnalyticDB for PostgreSQL instance You can use DTS to synchronize data from an ApsaraDB RDS for PostgreSQL instance to an AnalyticDB for PostgreSQL instance.AnalyticDB for PostgreSQL
Synchronize data from a PolarDB for MySQL cluster to an AnalyticDB for PostgreSQL instance You can use DTS to synchronize data from a PolarDB for MySQL cluster to an AnalyticDB for PostgreSQL instance.AnalyticDB for PostgreSQL

Synchronize data from self-managed databases

References Description
Use DTS to synchronize data from an ECS-based user-created MySQL database to an AnalyticDB for PostgreSQL instance You can use DTS to synchronize data from an ECS-based self-managed MySQL database to an AnalyticDB for PostgreSQL instance.AnalyticDB for PostgreSQL
Use DTS to synchronize data from a user-created MySQL database connected by a leased line, VPN Gateway, or Smart Access Gateway to an AnalyticDB for PostgreSQL instance You can use DTS to synchronize data from a self-managed MySQL database connected by a leased line, VPN gateway, or smart access gateway to an AnalyticDB for PostgreSQL instance.AnalyticDB for PostgreSQL
Synchronize data from a self-managed SQL Server database hosted on ECS to an AnalyticDB for PostgreSQL instance You can use DTS to synchronize data from a self-managed SQL Server database to an AnalyticDB for PostgreSQL instance.AnalyticDB for PostgreSQL
Synchronize data from a self-managed PostgreSQL database to an AnalyticDB for PostgreSQL instance You can use DTS to synchronize data from a self-managed PostgreSQL database to an AnalyticDB for PostgreSQL instance.AnalyticDB for PostgreSQL
Use rds_dbsync to migrate or synchronize data from a MySQL database to an AnalyticDB for PostgreSQL database rds_dbsync is an open source tool used to migrate or synchronize data. You can use the mysql2pgsql feature of this tool to migrate data from a MySQL database to an AnalyticDB for PostgreSQL database, Greenplum Database, PostgreSQL database, or PPAS database without having to store data. mysql2pgsql connects to both the source MySQL database and the destination database, retrieves the data that you want to export from the source MySQL database, and then uses a COPY statement to import the data to the destination database.
Use rds_dbsync to migrate or synchronize data from a PostgreSQL database to an AnalyticDB for PostgreSQL database You can use the pgsql2pgsql feature of rds_dbsync to migrate tables across AnalyticDB for PostgreSQL, Greenplum Database, PostgreSQL, and PPAS.AnalyticDB for PostgreSQL