End-to-end data synchronization to integrate transaction processing with data analysis-AnalyticDB:Zero - ETL

Zero-ETL lets you replicate data from your online transaction processing (OLTP) databases to AnalyticDB for PostgreSQL in real time—without building or maintaining a data pipeline. It integrates transaction processing with data analysis, enabling end-to-end data synchronization and management.

Overview

Traditional extract, transform, load (ETL) pipelines introduce three common problems:

Cost: Each data source often requires a separate ETL tool, adding licensing and operational fees.
Complexity: Managing ETL jobs increases operational overhead and pulls engineering time away from application development.
Latency: Batch-based ETL runs on a schedule, so near-real-time analytics isn't feasible.

Zero-ETL addresses all three. It continuously replicates data from an OLTP source to an online analytical processing (OLAP) destination—AnalyticDB for PostgreSQL—so you can run analytics on fresh data without managing pipelines.

AnalyticDB for PostgreSQL supports zero-ETL tasks from the following sources:

PolarDB for PostgreSQL to AnalyticDB for PostgreSQL
PolarDB for MySQL to AnalyticDB for PostgreSQL
ApsaraDB RDS for PostgreSQL to AnalyticDB for PostgreSQL
ApsaraDB RDS for MySQL to AnalyticDB for PostgreSQL

Key concepts

Term	Definition
Zero-ETL task	A managed replication job between one source instance or cluster and one AnalyticDB for PostgreSQL destination instance.
Source instance/cluster	The OLTP database where data originates: ApsaraDB RDS for PostgreSQL, ApsaraDB RDS for MySQL, PolarDB for PostgreSQL, or PolarDB for MySQL.
Destination instance	The AnalyticDB for PostgreSQL instance that receives the replicated data for analytics.
DDL synchronization	Schema-level changes (CREATE, ALTER, DROP, RENAME, TRUNCATE) replicated from source to destination.
DML synchronization	Row-level changes (INSERT, UPDATE, DELETE) replicated from source to destination.

Benefits

No pipeline management: Select source and destination instances or clusters to create a real-time sync task. No data pipelines to build or maintain.
No extra charges: Zero-ETL tasks are free. You pay only for the source and destination instances.
Multi-source analytics: Aggregate data from multiple source instances or clusters into a single AnalyticDB for PostgreSQL instance for global analysis.

Limitations

For the full list of limitations by source type, see the "Usage notes" sections of the corresponding Data Transmission Service (DTS) guides:

Billing

Zero-ETL tasks have no additional charge. You are billed only for the source instance or cluster and the destination AnalyticDB for PostgreSQL instance.

Supported regions

China (Shenzhen), China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Qingdao), China (Guangzhou), China East 2 Finance, China (Chengdu), China (Hong Kong), Malaysia (Kuala Lumpur), US (Virginia), US (Silicon Valley), Germany (Frankfurt), Indonesia (Jakarta), Japan (Tokyo), and Singapore.

Prerequisites

Before you begin, make sure you have:

An AnalyticDB for PostgreSQL instance. See Create an instance.
A source instance or cluster: ApsaraDB RDS for PostgreSQL, ApsaraDB RDS for MySQL, PolarDB for PostgreSQL, or PolarDB for MySQL. See Create an ApsaraDB RDS for PostgreSQL instance, Create an ApsaraDB RDS for MySQL instance, Create a PolarDB for PostgreSQL cluster, and Create a serverless cluster.
The wal_level parameter of the source instance or cluster set to logical. See Synchronize data from an ApsaraDB RDS for PostgreSQL instance to an AnalyticDB for PostgreSQL instance.
Available storage capacity in the AnalyticDB for PostgreSQL instance that exceeds the used storage of the source instance or cluster.

Set up zero-ETL

Step 1: Create a service-linked role

Before creating a zero-ETL task, check that the service-linked role AliyunServiceRoleForADBPG exists in your account.

Log on to the Resource Access Management (RAM) console. In the left-side navigation pane, choose Identities > Roles.
Check whether AliyunServiceRoleForADBPG appears in the role list.
If the role does not exist, click Create Role in the upper-left corner.
Select Alibaba Cloud Service and enter a Principal Name.
Click OK, then complete the Security Verification step in the confirmation dialog.

For details, see Create a RAM role for a trusted Alibaba Cloud service.

Step 2: Grant RAM user permissions

To let a RAM user create and manage zero-ETL tasks, attach two sets of permissions.

Permissions to manage AnalyticDB for PostgreSQL

Attach the AliyunGPDBFullAccess policy to the RAM user. See Grant permissions to a RAM user.

Permissions to create zero-ETL tasks

Create a custom policy that grants DTS access for data synchronization. Use one of the following sample scripts based on the scope you need.

Grant access to all instances and clusters:

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "dts:*",
            "Resource": [
                "acs:gpdb:*:*:*",
                "acs:rds:*:*:*",
                "acs:polardb:*:*:*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "dts:DescribeRegions",
                "dts:DescribeConfigRelations",
                "dts:DescribeSrcLinkConfig",
                "dts:DescribeDestLinkConfig",
                "dts:DescribeLinkConfig"
            ],
            "Resource": [
                "acs:gpdb:*:*:*"
            ]
        }
    ]
}

Grant access to specific instances and clusters:

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "dts:*",
            "Resource": [
                "acs:gpdb:*:*:dbinstanc****",
                "acs:rds:*:*:dbinstance****",
                "acs:polardb:*:*:dbclus****"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "dts:DescribeRegions",
                "dts:DescribeConfigRelations",
                "dts:DescribeSrcLinkConfig",
                "dts:DescribeDestLinkConfig",
                "dts:DescribeLinkConfig"
            ],
            "Resource": "acs:dts:*:*:*"
        }
    ]
}

Create the custom policy in the RAM console. See Create custom policies.

Step 3: Create a zero-ETL task

Log on to the AnalyticDB for PostgreSQL console.
In the upper-left corner, select the region where your instance resides.
Find the target instance and click its instance ID.
In the left-side navigation pane, choose Zero-ETL. Click Create Zero-ETL Task in the upper-left corner.

In the Configure Source and Destination Databases step, configure the source and destination parameters.

Source parameters:

Parameter	Description	Example
Task Name	Name of the zero-ETL task	Zeroetltest
Database Type	Engine of the source instance or cluster. Select RDS for PostgreSQL, RDS for MySQL, PolarDB for MySQL, or PolarDB for PostgreSQL.	RDS for PostgreSQL
Access Method	Access method for the source. Automatically set to Alibaba Cloud Instance.	Alibaba Cloud Instance
Instance Region	Region where the source instance or cluster resides	China (Shanghai)
Instance ID	ID of the source instance or cluster	pgm-2ze****
Database Name	Name of the database in the source instance or cluster	testdb
Database Account	Database account for the source	test
Database Password	Password for the database account	password****
Encryption	Connection encryption method. Select Non-encrypted or SSL-encrypted. SSL encryption uses certificates to protect data in transit.	SSL-encrypted

Destination parameters:

Parameter	Description	Example
Database Type	Automatically set to AnalyticDB for PostgreSQL	AnalyticDB for PostgreSQL
Access Method	Automatically set to Alibaba Cloud Instance	Alibaba Cloud Instance
Instance Region	Region where the AnalyticDB for PostgreSQL instance resides	China (Shanghai)
Instance ID	ID of the AnalyticDB for PostgreSQL instance	gp-2zeod****
Database Name	Name of the database in the destination instance	testdb
Database Account	Database account for the destination	test
Database Password	Password for the database account	password****

Click Test Connectivity and Proceed. In the Configure Zero-ETL step, set the following parameters.

Parameter	Description	Example
DDL and DML operations to be synchronized	DML operations (INSERT, UPDATE, DELETE) and DDL operations (CREATE, ALTER, DROP, RENAME, TRUNCATE) to replicate	DML: INSERT, UPDATE, DELETE; DDL: CREATE, ALTER, DROP, RENAME, TRUNCATE
Source objects and selected objects	Objects to include in the sync	public
Advanced settings (optional)	Retry time for failed connections and other issues between source and destination	Retry time for failed connections: 10 minutes; Retry time for other issues: 10 minutes

Click Next: Configure Database and Table Fields. Configure the table mapping parameters.

Parameter	Description	Example
Database Name	Name of the selected database	public
Table Name	Name of the selected table	table1
Primary key column	Primary key column for the table	id
Distribution key	One or more distribution key columns	id
Type	Data partition type for the table	Hash-Distributed Table
Definition status	Configuration status of the table. Changes from Undefined to Defined after you configure the table fields.	Defined

Click Next: Save Task Settings and Precheck.
If the precheck passes, the system automatically starts the zero-ETL task. Track progress in the task list.

Monitor zero-ETL tasks

After a task starts, use Cloud Monitor to track sync latency and set up alerts.

View monitoring data

Log on to the Cloud Monitor console.
In the left-side navigation pane, choose Cloud Service Monitoring > Cloud Service Monitoring > Database.
Move the pointer over the AnalyticDB for PostgreSQL card and click AnalyticDB for PostgreSQL - Zero-ETL Latency.
Click the instance ID to view monitoring details for its zero-ETL tasks.

Create an alert rule

In the left-side navigation pane, choose Alerts > Alert Rules.
Click Create Alert Rule and configure alert parameters for your zero-ETL tasks. See Create an alert rule.

Create a subscription policy

In the left-side navigation pane, choose Event Center > Event Subscription.
Click Create Subscription Policy and configure subscription parameters for your zero-ETL tasks. See Manage event subscription policies (recommended).

What's next

After data is flowing into AnalyticDB for PostgreSQL, run analytical queries directly on the synced tables. For query guidance and data type mapping between source and destination, refer to the AnalyticDB for PostgreSQL documentation.