AnalyticDB for PostgreSQL (previously known as HybridDB for PostgreSQL) is a fast, easy-to-use, and cost-effective warehousing service that can process petabytes of data. This topic describes how to synchronize data from a PolarDB for MySQL cluster to an AnalyticDB for PostgreSQL instance by using Data Transmission Service (DTS). The data synchronization feature is applicable to scenarios such as ad-hoc query and analysis, extract, transform, and load (ETL) operations, and data visualization.
Prerequisites
- The binary logging feature is enabled for the PolarDB for MySQL cluster. For more information, see Enable binary logging.
- The tables to be synchronized from the PolarDB for MySQL cluster contain primary keys.
- An instance is created. For more information, see Create an instance.
Precautions
- DTS uses read and write resources of the source and destination databases during initial full data synchronization. This may increase the load of the database server. If the database performance is unfavorable, the specification is low, or the data volume is large, database services may become unavailable. For example, DTS occupies a large amount of read and write resources in the following cases: a large number of slow SQL queries are performed on the source database, the tables have no primary keys, or a deadlock occurs in the destination database. Before you synchronize data, evaluate the impact of data synchronization on the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. For example, you can synchronize data when the CPU utilization of the source and destination databases is less than 30%.
- During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination instance. After initial full data synchronization is complete, the tablespace of the destination instance is larger than that of the source cluster.
Limits
- You can select only tables as the objects to be synchronized.
- You cannot synchronize the following types of data: BIT, VARBIT, GEOMETRY, ARRAY, UUID, TSQUERY, TSVECTOR, and TXID_SNAPSHOT.
- Prefix indexes cannot be synchronized. If the source database contains prefix indexes, data may fail to be synchronized.
- We recommend that you do not use gh-ost or pt-online-schema-change to perform data definition language (DDL) operations on objects during data synchronization. Otherwise, data synchronization may fail.
SQL operations that can be synchronized
- Data manipulation language (DML) operations: INSERT, UPDATE, and DELETE
- DDL operations: ADD COLUMN and RENAME COLUMN
Note The CREATE TABLE operation is not supported. To synchronize data from a new table, you must add the table to the selected objects. For more information, see Add an object to a data synchronization task.
Supported synchronization topologies
- One-way one-to-one synchronization
- One-way one-to-many synchronization
- One-way many-to-one synchronization
Term mappings
PolarDB for MySQL | |
---|---|
Database | Schema |
Table | Table |