This topic describes how to migrate data from a PolarDB for PostgreSQL cluster to a self-managed Oracle database by using Data Transmission Service (DTS). This is suitable for scenarios such as data backflow tests and functional tests.
Prerequisites
- The tables to be migrated from the source PolarDB for PostgreSQL cluster must contain primary keys or UNIQUE NOT NULL indexes.
- The version number of the self-managed Oracle database is 9i, 10g, 11g, 12c, 18c, or 19c.
- The schemas of objects such as tables are created in the self-managed Oracle database.
- The storage space of the self-managed Oracle database is larger than the total size of the data in the PolarDB for PostgreSQL cluster.
Limits
- In this scenario, DTS supports only full data migration and incremental data migration. DTS does not support schema migration.
- During full data migration, DTS uses read and write resources of the source and destination databases. This may increase the loads of the database servers. Before you migrate data, evaluate the impact of data migration on the performance of the source and destination databases. We recommend that you migrate data during off-peak hours.
- If the self-managed Oracle database is deployed in a Real Application Cluster (RAC)
architecture and is connected to DTS over an Alibaba Cloud virtual private cloud (VPC),
you must connect the Single Client Access Name (SCAN) IP address of the Oracle RAC
and the virtual IP address (VIP) of each node to the VPC and configure routes. The
settings ensure that your DTS task can run as expected. For more information, see
Configure a route between DTS and Express Connect, VPN Gateway, or Smart Access Gateway.
Important When you configure the source Oracle database in the DTS console, you can specify the SCAN IP address of the Oracle RAC as the database endpoint or IP address.
- A data migration task can migrate data from only a single database. To migrate data from multiple databases, you must create a data migration task for each database.
- If you select a schema as the object to be migrated and create a table in the schema
or execute the RENAME statement to rename a table in the schema, you must execute
the
ALTER TABLE schema.table REPLICA IDENTITY FULL;
statement before you write data to the table.Note Replace theschema
andtable
variables in the preceding statement with your schema name and table name. - To ensure that the latency of data migration is accurate, DTS adds a heartbeat table
named
dts_postgres_heartbeat
to the source database. The following figure shows the schema of the heartbeat table. - If the source database has long-running transactions and the task contains incremental data migration, the WAL logs that are generated before the long-running transactions are submitted may not be cleared and therefore pile up, resulting in insufficient storage space in the source database.
Billing
Migration type | Task configuration fee | Internet traffic fee |
---|---|---|
Full data migration | Free of charge. | Charged only when data is migrated from Alibaba Cloud over the Internet. For more information, see Billing overview. |
Incremental data migration | Charged. For more information, see Billing overview. |
SQL operations that can be synchronized during incremental data migration
INSERT, UPDATE, and DELETE
Permissions required for database accounts
Database | Required permission |
---|---|
PolarDB PostgreSQL | Permissions of a privileged account |
Self-managed Oracle database | Permissions of the schema owner |