Zero-ETL to synchronize data to a data warehouse - ApsaraDB RDS

Zero-ETL automatically replicates data from ApsaraDB RDS for PostgreSQL to AnalyticDB for PostgreSQL in real time—no data pipelines to build or maintain. The pipeline is free of charge.

Supported links

RDS for PostgreSQL → AnalyticDB for PostgreSQL

Supported regions

China (Beijing), China (Hangzhou), China (Shanghai), and China (Shenzhen)

Support for more regions is being rolled out. For the latest information, see the RDS console.

How it works

Traditional extract-transform-load (ETL) pipelines extract data from a transactional database, transform it, and load it into a data warehouse. Maintaining these pipelines adds operational overhead, increases costs when multiple sources need different tools, and introduces latency that makes near-real-time analysis difficult.

Zero-ETL eliminates the pipeline layer entirely. It captures changes from your online transaction processing (OLTP) source database using logical replication and streams them directly into your online analytical processing (OLAP) data warehouse, keeping both systems in sync continuously.

Key concepts

Term	Definition
Zero-ETL task	A managed data pipeline that replicates data from an RDS for PostgreSQL instance to an AnalyticDB for PostgreSQL instance.
Source	The RDS for PostgreSQL instance where transactional data originates.
Destination	The AnalyticDB for PostgreSQL instance that receives the replicated data.
Logical replication	The PostgreSQL mechanism that captures row-level changes and streams them to a subscriber. Zero-ETL uses this as its underlying transport.

Benefits

No pipeline management: Select a source and destination, and the pipeline is created and managed automatically. No ETL tooling to deploy or operate.
Zero cost: Zero-ETL data synchronization is free of charge.
Multi-source aggregation: Replicate data from multiple RDS for PostgreSQL instances into a single AnalyticDB for PostgreSQL instance to build a unified analytics view.

Limitations

Only one link is supported: RDS for PostgreSQL to AnalyticDB for PostgreSQL.
The source and destination instances must be in the same region.
The wal_level parameter on the source instance must be set to logical.

Billing

Zero-ETL data synchronization is free of charge.

Prerequisites

Before you begin, make sure you have:

An AnalyticDB for PostgreSQL instance. See Create an AnalyticDB for PostgreSQL instance
A database in the AnalyticDB for PostgreSQL instance to receive the replicated data. See Manage databases in an AnalyticDB for PostgreSQL instance
A privileged account on the RDS for PostgreSQL instance, set as the owner of the destination database. See Create an account for an ApsaraDB RDS for PostgreSQL instance
A database account on the AnalyticDB for PostgreSQL instance. The initial account or any account with SUPERUSER permissions also works. See Create a database account for an AnalyticDB for PostgreSQL instance
The wal_level parameter on the RDS for PostgreSQL instance set to logical. See Modify the parameters of an ApsaraDB RDS for PostgreSQL instance
Logical Replication Slot Failover enabled on the RDS for PostgreSQL instance (enabled by default). When enabled, the sync task continues running and logical subscriptions are not interrupted during a primary/secondary failover. See Logical Replication Slot Failover

Set up permissions

Before creating a Zero-ETL pipeline, complete two setup tasks: create a service-linked role and grant the required Resource Access Management (RAM) permissions.

Create a service-linked role

The service-linked role AliyunServiceRoleForADBPG authorizes the Zero-ETL service to access AnalyticDB for PostgreSQL on your behalf. Create it before creating any pipeline.

Go to the Roles list in the RAM console.
Check whether AliyunServiceRoleForADBPG already exists. If it does, skip to the next section.
Open the service-linked role creation page. Set Select Service to AliyunServiceRoleForADBPG.
Click Create Service Linked Role.

Return to the Roles list and confirm that the role appears.

Grant RAM user permissions

A RAM user needs two permissions to create and manage Zero-ETL pipelines.

Permission 1: AnalyticDB for PostgreSQL management

Grant the RAM user the AliyunGPDBFullAccess policy (full management permissions on AnalyticDB for PostgreSQL). See Grant permissions to a RAM user.

Permission 2: Zero-ETL pipeline management

Create a custom policy that allows the RAM user to create pipelines between RDS for PostgreSQL and AnalyticDB for PostgreSQL instances, then grant it to the RAM user. See Create custom policies.

Use one of the following sample policies depending on your access scope:

Grant permissions on all RDS for PostgreSQL and AnalyticDB for PostgreSQL instances

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

Grant permissions on specific RDS for PostgreSQL and AnalyticDB for PostgreSQL instances

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "dts:*",
            "Resource": [
                "acs:gpdb:*:*:dbcluster/gp-bp13e375cd8x2****",
                "acs:rds:*:*:dbinstance/pgm-2zeyjzi91g53****"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "dts:DescribeRegions",
                "dts:DescribeConfigRelations",
                "dts:DescribeSrcLinkConfig",
                "dts:DescribeDestLinkConfig",
                "dts:DescribeLinkConfig",
                "dts:DescribeConciseJobStatics",
                "dts:ListUserAuthorizationLogs",
                "dts:CreateUserAuthorization"
            ],
            "Resource": "acs:dts:*:*:*"
        }
    ]
}

Create a Zero-ETL task

Log on to the RDS console. In the left navigation pane, click Data Integration.
On the Zero-ETL tab, click Create Zero-ETL Task.

On the Create Zero-ETL Task page, configure the source and destination databases. Configure the source database: Configure the destination database:

Parameter	Description
Task Name	A name for the Zero-ETL task
Database Type	Select RDS for PostgreSQL
Access Method	Only Alibaba Cloud Instance is supported
Instance Region	The region where the source instance resides
Instance ID	The ID of the RDS for PostgreSQL instance
Database Name	The name of the database in the RDS for PostgreSQL instance
Database Account	The database account for the RDS for PostgreSQL instance
Database Password	The password of the database account
Encryption	The connection encryption method

Parameter	Description
Database Type	Only AnalyticDB For PostgreSQL is supported
Access Method	Only Alibaba Cloud Instance is supported
Instance Region	The region where the destination instance resides
Instance ID	The ID of the destination instance
Database Name	The database in the destination instance that receives the data
Database Account	The database account for the destination instance
Database Password	The password of the database account

Click Test Connectivity and Proceed. Configure the synchronization scope:

Parameter	Description
DDL and DML Operations to Be Synchronized	Select the DML operations (insert, update, delete) and DDL operations (create, alter, drop, rename, truncate) to synchronize. All operations are selected by default.
Source Objects and Selected Objects	Choose which database objects to replicate
Advanced Settings (Optional)	Set the retry interval when the source or destination database is unreachable, and for other connectivity issues

Select O&M-free Zero-ETL and click Next: Configure Database and Table Fields. Map source tables to the destination:

Parameter	Description
Database Name	Select the destination database
Table Name	Select the destination table
Primary Key Column	The primary key column of the selected table
Distribution Key	The distribution key column of the selected table
Type	The table type: partitioned table or replicated table
Definition Status	Changes from Undefined to Defined after you complete the configuration

Click Next: Save Task Settings and Precheck.
If the precheck passes, click Start to start the Zero-ETL task.

Verify the task

On the Zero-ETL page, click the Source tab to view information about the zero-ETL task, such as its ID/Name, Source/Destination, and Status.

If the precheck fails, resolve the reported issues—typically a missing prerequisite or incorrect parameter—and retry the precheck before clicking Start.

What's next

Monitor the running task from the Zero-ETL page in the RDS console.
To replicate additional sources into the same AnalyticDB for PostgreSQL instance, create additional Zero-ETL tasks following the same procedure.