Supported Source and Destination Database Types
The following table describes the supported source and destination database types.
| Source Database Types | Destination Database Types |
|---|---|
| RDS PostgreSQL databases and self-managed PostgreSQL databases | RDS FOR PostgreSQL |
Note: The engine of a self-managed source database can only be PostgreSQL 12, and the engine version of the destination database must be equal to or later than that of the source database.
Supported Migration Objects and SQL Operations
Migration Objects
Objects supported for structure migration:
Schemas, tables, indexes, constraints (foreign keys, unique constraints, and exclusive constraints), views, materialized views, sequences, stored procedures, functions, rules, triggers, custom types, and domains.
Supported field types:
Number, currency, character string, binary data, date/time, Boolean, enumeration, geometry data, network address, bit string, text search, UUID, XML, JSON, composite type, and range.
Precautions
A task can be used to migrate data from only one database. To migrate multiple databases, you must create multiple DTS tasks.
Schema: DTS does not migrate system schemas such as pg_toast, pg_temp_1, pg_toast_temp_1, pg_catalog, or information_schema.
Table: DTS does not migrate temporary tables. DTS migrates the indexes and constraints of tables during full data migration and migrates triggers and rules of tables after full data migration.
Sequence: If a sequence is associated with a table to be migrated, the sequence must also be migrated to the destination database.
Name mapping rule:
If a migration task does not include incremental data migration, you can configure database, table, and column name mapping. If column name mapping is enabled, DTS does not migrate the constraints related to the columns.
If a migration task includes incremental data migration, you cannot configure database, table, or column name mapping.
You cannot configure name mapping for the tables that store data of views, stored procedures, functions, domains, and custom types. Otherwise, the views, stored procedures, and functions will become invalid in the destination database.
If a migration object contains fields of the LONGTEXT or LONGBLOB type, we recommend that you create a DTS instance with high specifications for the data migration task. Otherwise, out-of-memory (OOM) issues may occur.
SQL Operations Supported During Incremental Data Migration
| Operation Type | SQL Statements |
|---|---|
| DML | INSERT, UPDATE, and DELETE |
| DDL | CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE SEQUENCE, ALTER SEQUENCE, DROP SEQUENCE, CREATE VIEW, ALTER VIEW, DROP VIEW, CREATE INDEX, ALTER INDEX, and DROP INDEX Note: DTS does not support the following statement: CREATE TABLE Table name AS SELECT. If you rename a source table and insert data into the table during migration, DTS does not migrate the newly added data to the destination database. DTS does not migrate DDL statements starting with comments. |
Database Accounts and Permissions
The following table describes the information about database accounts and required permissions.
| Database | Required Permissions | Sample Statement for Granting Permissions |
|---|---|---|
| Source Database | 1. The USAGE permissions on schemas. 2. To perform SELECT operations on incremental data of migration objects, the SUPERUSER or REPLICATION permissions on migration objects are required. | Grant the USAGE permission on a schema to a user: GRANT USAGE ON SCHEMA schema_name TO user_name; Grant the SELECT permission on an object to a user: GRANT SELECT ON object_name TO user_name; Grant the SUPERUSER permission to a user: ALTER USER user_name WITH SUPERUSER; Grant the REPLICATION permission to a user: ALTER USER user_name WITH REPLICATION; |
| Destination Database | The permission for creating schemas. | Grant the permission for creating schemas in a database to a user: GRANT CREATE ON DATABASE database_name TO user_name; |
Preparation
Log in to the server on which your self-managed PostgreSQL database is hosted.
Set the value of the wal_level parameter in the postgresql.conf file to logical.
Add the IP address of your DTS instance to the pg_hba.conf file of the self-managed PostgreSQL database. If you specify 0.0.0.0/0 as the trusted IP address as showing in the following figure, skip this step.
If the task includes incremental data migration, you must install decoderbufs, a PostgreSQL logical decoder output plugin. It is recommended that you install a decoderbufs version later than v2.1.1.Final. An earlier version may lead to core dump errors on the PostgreSQL database. For information about how to install decoderbufs, see Official Website.
Precautions
A DTS migration/synchronization process includes the following phases: precheck, structure migration, full data migration/synchronization, and incremental data migration/synchronization. To ensure that each phase runs smoothly, read the following usage notes before you create a migration task.
Before You Start
Source Database Requirements
Triggers on partitioned tables in the source database must be enabled.
During full data migration, the standby nodes of the source database are supported. To support standby database nodes, the value of the hot_standby_feedback parameter must be set to on. In incremental data migration, the standby nodes of the source database are not supported.
The objects that the source objects depend on and are associated with must also be migrated. Otherwise, the task may fail.
To perform incremental data migration, the pg_hba.conf file of the source database must contain the following configurations:
host replication all 0.0.0.0/0 md5 and the value of wal_level must be logical.The decoderbufs plug-in must be installed on the source database before data migration.
The replica identity attribute of tables that do not have primary keys in the source database must be full.
The value of the max_replication_slots parameter in the source database configuration must be greater than the number of used replication slots.
The value of the max_wal_senders parameter in the source database configuration must be greater than or be equal to the value of the max_replication_slots parameter value.
If the toast attribute of the primary key column of the source table is main, external, or extended, the value of the replica identity attribute must be full.
The objects that the source objects depend on and are associated with must also be migrated. Otherwise, the task may fail.
Destination Database Requirements
The value of the block_size parameter in the destination database configuration must be greater than or equal to that in the source database configuration.
The value of the lc_monetary parameter in the destination database configuration must be the same as that in the source database configuration.
To perform incremental data migration, if the objects to be migrated include foreign keys, triggers, or event triggers, set the value of the session_replication_role parameter in the destination database configuration to replica. After the data migration task is complete, change the value of the parameter to origin.
The destination database cannot contain objects with the same type and of the same names as the objects to be migrated, such as schemas, tables, and sequences. Otherwise, the migration task may fail. If the name of the system databases, system schemas, and system tables are the same, the migration task is not affected.
If you migrate selected tables, you are not recommended to rename the tables during incremental data migration.
Before starting a full and incremental data migration task or an incremental data migration task, make sure that no long-running transactions are enabled in the source database. Long-running transactions in the source database may block logical replication slot creation. As a result, the task fails.
To migrate DDL operations, make sure that the destination database supports the operations performed on the source database.
During structure and full data migration
Do not change the ports of the source and destination databases, or change or delete the passwords or permissions that are used to connect to the source and destination databases. Otherwise, the task may fail.
Do not run DDL statements on the source database. Otherwise, the data in destination database may be inconsistent with the data in the source database, or the task may fail.
Do not write data to the destination database. Otherwise, the data in destination database may be inconsistent with the data in the source database.
During incremental data migration
Do not change the ports of the source and destination databases, or change or delete the passwords or permissions that are used to connect to the source and destination databases. Otherwise, the task may fail.
Do not change the primary keys or unique keys (if the tables have no primary keys) of tables in the source database. Otherwise, the incremental data migrated to the destination database may be inconsistent with the incremental data added to the source database, or the task may fail.
Do not change the replica identity attribute of tables in the source database. Otherwise, the incremental data migrated to the destination database may be inconsistent with the incremental data added to the source database, or the task may fail.
Do not write data to the destination database. Otherwise, the data in destination database may be inconsistent with the data in the source database.
During database-level migration, if you add a table without a primary key to the source database, you must set the replica identity attribute of the table to full before writing data to the table. Otherwise, the data in the destination database may be inconsistent with the data in the source database, or the task may fail.
During database-level migration, if you add a table with a primary key to the source database and the toast attribute of the primary key column is main, external, or extended, you must set the replica identity attribute of the table to full before writing data to the table. Otherwise, the data in the destination database may be inconsistent with the data in the source database, or the task may fail.
To migrate DDL operations, make sure that the destination database supports the operations performed on the source database.
Data Audit
To avoid negative data inconsistency error reports and reduce impacts on the source database and the DTS task, we recommend that you perform data comparison during off-peak hours.
If new data is written to the source database during incremental data migration, the comparison result may show data inconsistency.
Procedure
1. Purchase a DTS instance.
Go to the buy page of DTS and purchase a DTS instance. When configuring the DTS instance, set the destination database type to PostgreSQL.
2. Go to the Configure page.
After you purchase a DTS instance, go to the Configure page to configure your task.
3. Configure the source and destination databases.
Configure the information about the source and destination databases, including the database type, IP addresses, ports, database accounts, and database passwords. Then, perform a connectivity test to check whether the source and destination databases can be connected.
4. Configure objects and advanced settings.
After you configure the source and destination databases, click Test Connectivity and Proceed. Then, select the objects that you want to migrate.
Migration object configurations:
| Configuration | Description |
|---|---|
| Step | To perform only full data migration, select Struct Migration and Full Data Migration. To migrate data without service downtime, select Struct Migration, Full Data Migration, and Incremental Data Migration. Note: If Incremental Data Migration is not selected, to ensure data consistency, do not perform DML and DDL operations on the source database instance during data migration. |
| Migration Objects | You can migrate multiple types of objects from a source PostgreSQL database, including tables, views, functions, stored procedures, materialized views, rules, triggers, domains, and custom types. Select the objects that you want to migrate from the left-side multi-select box. If you mistakenly select an object that you do not want to migrate, you can also click the < icon to remove an object from the right-side box. If you select the database-level object instead of some specific objects in the source database, DTS migrates the entire database. During subsequent incremental data migration, you can add new tables to the source database, and the newly added tables will also be migrated to the destination database. However, other types of newly added objects cannot be migrated during incremental migration. |
| Configuring name mapping | DTS supports database name mapping, table name mapping, and column name mapping. To change the name of an object in the destination instance, such as the destination database name or a table or column name, locate the object and click Edit. To change object names in the destination instance in a batch, click Batch Edit in the upper right corner of the right-side box. If a migration task includes only structure migration and full data migration, you can configure column name mapping. If the task includes incremental data migration, you cannot configure column name mapping. A table name can contain a maximum of 64 characters and can contain only letters, digits, and underscores (_). Note: If the entire source database is to be migrated, we recommend that you do not configure table name mapping. |
| Filtering the data to be migrated | You can set a WHERE condition to filter data. The WHERE clause can contain single quotation marks (') and cannot contain semicolons (;) or two consecutive hyphens (--). DTS migrates only data that meets the WHERE condition to the destination database. |
| DML operations supported during incremental data migration | Configure DML operations for incremental data migration. 1. Select an object that you want to migrate. 2. Click Edit. 3. In the pop-up window, select the DML operations for incremental data migration. If you configure DML operations for both the database and table levels, the table-level settings overwrite the database-level settings. |
| DDL operations supported during incremental data migration | You can select whether to migrate incremental DDL statements during incremental data migration. Note: 1. If the task involves migrating an entire database, you must select Incremental DDL Statements. 2. In partial database migration, if Incremental DDL Statements is selected, DTS only migrates the DDL statements for objects that you want to migrate. 3. In partial database migration, if Incremental DDL Statements is not selected, DTS does not migrate DDL statements in the incremental data migration stage. How to implement incremental DDL migration: The source database captures DDL statements through event triggers and records the DDL statements in specific tables. Therefore, you must create event triggers and functions in the source database before data migration. For more information, see Prerequisites for Incremental DDL Migration for a PostgreSQL Source Database. |
| Start Task as Scheduled | You can specify the start time of the task. By default, the migration task starts immediately after you click Start Task. |
5. Precheck and start the task.
If the precheck is passed, click Start Migration to start the data migration task.