AnalyticDB for PostgreSQL supports multiple methods for moving data in and out of your instance and keeping it in sync with upstream databases. This page helps you find the right method based on your source system, data volume, and latency requirements.
Choose a method
Select a method based on three factors: where your data is coming from, how much data you need to move, and how often.
| Factor | Options |
|---|---|
| Source type | Files, relational databases, data warehouses, Hadoop clusters, cloud databases |
| Latency requirement | One-time migration — batch methods; Ongoing sync — Data Transmission Service (DTS) |
Recommended for most use cases: Use OSS external tables (the gpossext feature) for large-scale bulk loads — all compute nodes read from OSS in parallel, making it highly efficient for bulk loads. For ongoing sync from cloud databases such as ApsaraDB RDS for MySQL, use DTS.
If you want to query external data without loading it: OSS external tables and Hadoop external tables (gphdfs) support federated analytics — you run queries against data that stays in OSS or HDFS without physically moving it into AnalyticDB for PostgreSQL.
Load data into AnalyticDB for PostgreSQL
Write data
| Method | When to use | Serverless |
|---|---|---|
| INSERT ON CONFLICT | Upsert rows by primary key — when an inserted row conflicts with an existing primary key, the existing row is updated instead of causing an error. | No |
| COPY ON CONFLICT | Bulk-load files when constraint conflicts are expected — extends the standard COPY statement to handle conflicts without aborting the entire import. | No |
| Client SDK | Build custom write pipelines that need high throughput — the SDK handles parallel processing internally, delivering significantly higher write performance than COPY or INSERT, with no connection pool or cache management required. | Yes |
Import table data
| Method | When to use | Serverless |
|---|---|---|
| OSS external table (gpossext) | Import large datasets from Object Storage Service (OSS) — all compute nodes read from OSS in parallel, making this a high-speed option for bulk loads. | Yes |
| Data Integration | Batch-sync data from heterogeneous sources across different network environments — a core feature of DataWorks with a wide range of source connectors. See supported data source types and plug-ins. | Yes |
\copy command | Import text files directly from your local machine — suitable for smaller datasets where a single client connection is sufficient. | Yes |
| Hadoop external table (gphdfs) | Access or migrate data from a Hadoop cluster using the gphdfs protocol — supports federated analytics without physically moving data. | Yes |
Migrate warehouse data
AnalyticDB for PostgreSQL V6.0 is built on Greenplum 6.0 and uses the same API as Greenplum Community Edition, which simplifies migration from compatible systems.
| Source | Method | Notes | Serverless |
|---|---|---|---|
| Self-managed Greenplum | External tables or application re-pointing | V6.0 supports vector computing and transaction processing with multiple coordinator nodes. Both applications and data can be migrated. | No (external tables supported) |
| Teradata | SQL migration | Compatible with Teradata syntax. | No (external tables supported) |
| Amazon Redshift | Export then import | Unload data from Redshift and import into AnalyticDB for PostgreSQL. | No (external tables supported) |
| Oracle (application) | SQL migration | Compatible with Oracle syntax. | No (external tables supported) |
| Oracle (via DTS) | Data Transmission Service (DTS) | Use DTS for automated, managed migration. | Yes |
Export data from AnalyticDB for PostgreSQL
| Method | When to use | Serverless |
|---|---|---|
| OSS external table (gpossext) | Export large datasets to OSS in parallel — supports GZIP compression to reduce file size and storage costs. | Yes |
\copy command | Export query results to a local file on your machine. | Yes |
Sync data from other databases
From cloud databases
All methods in this section use DTS to synchronize data from cloud databases to AnalyticDB for PostgreSQL.
| Source | Guide | Serverless |
|---|---|---|
| ApsaraDB RDS for MySQL | Sync from ApsaraDB RDS for MySQL | Yes |
| ApsaraDB RDS for SQL Server | Sync from ApsaraDB RDS for SQL Server | Yes |
| ApsaraDB RDS for PostgreSQL | Sync from ApsaraDB RDS for PostgreSQL | Yes |
| PolarDB for MySQL | Sync from PolarDB for MySQL | Yes |
From self-managed databases
DTS supports real-time incremental sync for most self-managed sources. For open-source migration without DTS, use rds_dbsync.
| Source | Guide | Tool | Serverless |
|---|---|---|---|
| MySQL on Elastic Compute Service (ECS) | Sync from ECS-based MySQL | DTS | Yes |
| MySQL via Express Connect, VPN Gateway, or Smart Access Gateway | Sync from MySQL over private network | DTS | Yes |
| SQL Server (self-managed) | Sync from self-managed SQL Server | DTS | Yes |
| SQL Server on ECS | Sync from SQL Server on ECS | DTS | Yes |
| PostgreSQL (self-managed) | Sync from self-managed PostgreSQL | DTS | Yes |
| Oracle (self-managed) | Sync from self-managed Oracle | DTS | Yes |
| Db2 for LUW | Sync from Db2 for LUW | DTS | Yes |
| MySQL (open source) | Migrate MySQL data with rds_dbsync — the mysql2pgsql feature connects to both databases, reads from the source, and streams data into the destination using COPY, without intermediate storage. | rds_dbsync | No |
| PostgreSQL (open source) | Migrate PostgreSQL data with rds_dbsync — the pgsql2pgsql feature migrates tables across AnalyticDB for PostgreSQL, Greenplum Database, and PostgreSQL. | rds_dbsync | No |