BladePipe 1.7.0: Stronger alerts, Broader DB support, Faster KingbaseES scanning.
跳到主要内容

PolarDB for MySQL to AnalyticDb for Pg

BladePipe supports data replication from PolarDB for MySQL to AnalyticDb for Pg. View supported migration, sync, verification, and connector capabilities.

Target DataSource：
AnalyticDb for Pg

Connection

Basic Functions

FunctionDescription
Schema Migration

If the target schema does not exist, BladePipe will automatically generate and execute CREATE statements based on the source metadata and the mapping rule.

Full Data Migration

Migrate data by sequentially scanning data in tables and writing it in batches to the target database.

Incremental Data Sync

Sync of common DML like INSERT, UPDATE, DELETE is supported.
UPDATE and DELETE for tables without primary keys are not synced by default (manual selection required).

Data Verification and Correction

Verify all existing data. Optionally, you can correct the inconsistent data based on verification results. Scheduled DataTasks are supported.
For more information, see Create Verification and Correction DataJob.

Subscription Modification

Add, delete, or modify the subscribed tables with support for historical data migration. For more information, see Modify Subscription.

Position Resetting

Reset positions by file position or timestamp. Allow re-consumption of incremental data logs in a past period or since a specific Binlog file and position.

Table Name Mapping

Support the mapping rules, namely, keeping the name the same as that in Source, converting the text to lowercase, converting the text to uppercase, truncating the name by "_digit" suffix.

DDL Synchronization
  • ALTER TABLE ADD COLUMN, DROP COLUMN, MODIFY COLUMN, CHANGE COLUMN
  • ADD INDEX
  • RENAME TABLE
Metadata Retrieval

Retrieve the target metadata with filtering conditions or target primary keys set from the source table.

Advanced Functions

FunctionDescription
Handling of Zero Value for Time

Allow setting zero value for time to different data types to prevent errors when writing to the Target.

Scheduled Full Data Migration

For more information, see Create Scheduled Full Data DataJob.

Custom Code

For more information, see Custom Code Processing, Debug Custom Code and Logging in Custom Code.

Data Filtering Conditions

Support data filtering using WHERE conditions, with SQL-92 as the SQL language. For more information, see Data Filtering.

Setting Target Primary Key

Change the primary key to another field to facilitate data aggregation and other operations.

Limits

LimitDescription
Primary Key Conflict Handling

PostgreSQL <= 9.4 or Greenplum <= 6 does not support conflict skipping or replacement. Performance may be affected when there are a large number of primary key conflicts.

Character Set

Support utf8, utf8mb4, latin1. Other encodings have not been tested.

FAQ

Source

Prerequisites

PrerequisiteDescription
Permissions for Account

A privileged account or a normal account with read and write permissions of PolarDbMySQL.

Enable Binlog

In the PolarDbMySQL instance, click Details > Configuration and Management > Parameter Configuration > Set the value of loose_polar_log_bin to true.

Character Set

Support utf8, utf8mb4, latin1. Other encodings have not been tested.

Parameters

ParameterDescription
parseBinlogParallel

Number of threads for parallel parsing of Binlog in Incremental DataJobs.

parseBinlogBufferSize

Size of the circular buffer for parsing Binlog in Incremental DataJobs.

maxTransactionSize

Maximum number of data rows per transaction. If exceeded, the transaction will be split and flushed in parts.

limitThroughputMb

Limit the throughput of incremental Binlogs.

extraDDL

Support synchronization of additional DDL, including PT, GHOST, ALI_DMS, and PT_GHOST.

needJsonEscape

Escape special characters in JSON to be written to the target database.

Tips: To modify the general parameters, see General Parameters and Functions.

Target

Prerequisites

PrerequisiteDescription
Permissions for Account

Required permissions include SELECT, INSERT, DELETE, UPDATE, and common DDL permissions.
For Aliyun AnalyticDB for PostgreSQL, initial accounts or those with SELECT, INSERT, DELETE, UPDATE, and common DDL permissions are needed.

Port Preparation

Allow the migration and sync node (sidecar) to connect to PostgreSQL / Greenplum / AnalyticDB for PostgreSQL / PolarDB for PostgreSQL standard interactive interface (e.g., 5432).

Parameters

ParameterDescription
keyConflictStrategy

Strategy for handling primary key conflicts during write in Incremental DataTask:

  • IGNORE: Ignore conflicts (default)
  • REPLACE: Replace conflicts (optional)

dstWholeReplace

Convert INSERT and UPDATE operations into full row replacement in the Target.

enableTimeZoneProcess

Enable time zone conversion to datetime fields.

timezone

Target time zone, e.g., +08:00, Asia/Shanghai, America/New_York.

defaultZeroDate

Default value for replacing '0000-00-00 00:00:00' / '0000-00-00' values. Optional values include:

  • null (empty)
  • Time (14:23:33)
  • Date (1970-01-01)
  • DateTime (1970-01-01 00:00:00),
  • TimeZone Time (14:23:33+08:00 or 1970-01-01 00:00:00+08:00)
caseSensitive

Case sensitivity strategy for SQL statements, including:

  • UpperCase: Convert to uppercase
  • LowerCase: Convert to lowercase
  • Sensitive: Add qualifiers
  • NoSpecified: No conversion/No qualifiers

writeStrategy

Strategy of writing data to the Target, including:

  • ROW (single row)
  • MULTI_SQL (multiple statements)
  • BATCH (write data in batches, default option)
  • COPY (PostgreSQL COPY command)
defaultGisSRID

Set the SRID for GIS data types.

Tips: To modify the general parameters, see General Parameters and Functions.