- Release Notes and Announcements
- Product Introduction
- Purchase Guide
- Getting Started
- Access Management
- Preparations
- Data Migration
- Databases Supported by Data Migration
- Cross-Account TencentDB Instance Migration
- Migrating to MySQL
- Migrating to MariaDB
- Migrating to TDSQL-C for MySQL
- Migrating to TDSQL MySQL
- Migrating to PostgreSQL
- Migrating to MongoDB
- Migrating to SQL Server
- Migrating to TencentDB for Redis
- Offline Migration of MySQL Data
- Advanced Migration Operations
- Task Management
- Data Sync
- Databases Supported by Data Sync
- Cross-Account TencentDB Instance Sync
- Synchronize to PostgreSQL
- Sync into Tencent Cloud
- Sync from Tencent Cloud
- Advanced Sync Operations
- Task Management
- Task Status Description
- Viewing Task
- Starting Task
- Retrying Task (MySQL)
- Verifying Task
- Stopping Task
- Pausing Task
- Resuming Task
- Resetting Task
- Deleting Task
- Changing Configuration
- Transition from Pay-as-You-Go to Monthly Subscription
- Terminating/Returning Instance
- Renewing Task
- Deisolating Task
- Deleting Instance
- Viewing Log
- Deleting Task
- Data Subscription (Kafka Edition)
- Databases Supported by Data Subscription
- Creating Data Subscription Task
- Data Consumption
- Creating Consumer Group
- Managing Consumer Group
- Consuming MySQL Data
- Consuming MongoDB Data
- Consuming TDSQL for PostgreSQL Data
- Consuming Subscribed Data with Kafka Client (Protobuf)
- Consuming Subscribed Data with Kafka Client (Avro)
- Consuming Subscribed Data with Kafka Client (JSON)
- Consuming Subscribed Data with Flink Client (Avro)
- Consuming Data with Flink
- Advanced Subscription Operations
- Task Management
- Data Migration (OldDTS)
- Data Subscription (OldDTS)
- Fix for Verification Failure
- Check Item Overview
- Check Items of MySQL/MariaDB/Percona/TDSQL-C for MySQL/TDSQL for MySQL
- MongoDB Check Items
- PostgreSQL Check Items
- TDSQL for PostgreSQL
- Redis/Tendis
- SQL Server
- Database Connection Check
- Source Database Existence Check
- Target Database Existence Check
- Peripheral Check
- Migration Network Check
- Version Check
- Source Instance Permission Check
- Account Conflict Check
- Partial Database Parameter Check
- Source Instance Parameter Check
- Source Instance Type (Master or Replica)
- Parameter Configuration Conflict Check
- Target Database Content Conflict Check
- Target Database Space Check
- Target Database Load Check
- Local Disk Space Check
- Binlog Parameter Check
- Oplog Check
- Foreign Key Dependency Check
- View Check
- Advanced Object Check
- Incremental Migration Precondition Check
- Extension/Plugin Compatibility Check
- Source Database Balancer Check
- Source Database Node Role Check
- ShardKey Check
- Warning Item Check
- DLL Ring Sync Check for Single Database/Table Object
- DLL Sync Conflict Check for Single Database/Table Object in the Same Target
- Level-2 Subpartitioned Table Check
- Primary Key Check
- DDL Check for Tables to Be Migrated
- System Database Conflict Check
- Table Structure Check for Source and Target Instances
- InnoDB Table Check
- Migration Object Dependency Check
- Constraint Check
- Cutover Description
- Monitoring and Alarms
- Ops Management
- DTS-DBbridge
- Best Practices
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- (NewDTS) Data Migration APIs
- DescribeMigrationJobs
- DescribeMigrationDetail
- CreateMigrationService
- ModifyMigrationJob
- CreateMigrateCheckJob
- DescribeMigrationCheckJob
- SkipCheckItem
- StartMigrateJob
- StopMigrateJob
- ResumeMigrateJob
- RecoverMigrateJob
- ModifyMigrateName
- ModifyMigrateJobSpec
- IsolateMigrateJob
- DestroyMigrateJob
- DescribeMigrateDBInstances
- CompleteMigrateJob
- PauseMigrateJob
- ContinueMigrateJob
- Data Sync APIs
- ModifySyncJobConfig
- CreateModifyCheckSyncJob
- DescribeModifyCheckSyncJobResult
- StartModifySyncJob
- DescribeSyncJobs
- CreateSyncJob
- ConfigureSyncJob
- CreateCheckSyncJob
- SkipSyncCheckItem
- DescribeCheckSyncJobResult
- StartSyncJob
- StopSyncJob
- ResumeSyncJob
- ResizeSyncJob
- RecoverSyncJob
- IsolateSyncJob
- DestroySyncJob
- PauseSyncJob
- ContinueSyncJob
- Data Consistency Check APIs
- Data Types
- Error Codes
- DTS API 2018-03-30
- SDK Documentation
- FAQs
- Error Handling (NewDTS)
- Error Handling (OldDTS)
- Service Agreement
- Release Notes and Announcements
- Product Introduction
- Purchase Guide
- Getting Started
- Access Management
- Preparations
- Data Migration
- Databases Supported by Data Migration
- Cross-Account TencentDB Instance Migration
- Migrating to MySQL
- Migrating to MariaDB
- Migrating to TDSQL-C for MySQL
- Migrating to TDSQL MySQL
- Migrating to PostgreSQL
- Migrating to MongoDB
- Migrating to SQL Server
- Migrating to TencentDB for Redis
- Offline Migration of MySQL Data
- Advanced Migration Operations
- Task Management
- Data Sync
- Databases Supported by Data Sync
- Cross-Account TencentDB Instance Sync
- Synchronize to PostgreSQL
- Sync into Tencent Cloud
- Sync from Tencent Cloud
- Advanced Sync Operations
- Task Management
- Task Status Description
- Viewing Task
- Starting Task
- Retrying Task (MySQL)
- Verifying Task
- Stopping Task
- Pausing Task
- Resuming Task
- Resetting Task
- Deleting Task
- Changing Configuration
- Transition from Pay-as-You-Go to Monthly Subscription
- Terminating/Returning Instance
- Renewing Task
- Deisolating Task
- Deleting Instance
- Viewing Log
- Deleting Task
- Data Subscription (Kafka Edition)
- Databases Supported by Data Subscription
- Creating Data Subscription Task
- Data Consumption
- Creating Consumer Group
- Managing Consumer Group
- Consuming MySQL Data
- Consuming MongoDB Data
- Consuming TDSQL for PostgreSQL Data
- Consuming Subscribed Data with Kafka Client (Protobuf)
- Consuming Subscribed Data with Kafka Client (Avro)
- Consuming Subscribed Data with Kafka Client (JSON)
- Consuming Subscribed Data with Flink Client (Avro)
- Consuming Data with Flink
- Advanced Subscription Operations
- Task Management
- Data Migration (OldDTS)
- Data Subscription (OldDTS)
- Fix for Verification Failure
- Check Item Overview
- Check Items of MySQL/MariaDB/Percona/TDSQL-C for MySQL/TDSQL for MySQL
- MongoDB Check Items
- PostgreSQL Check Items
- TDSQL for PostgreSQL
- Redis/Tendis
- SQL Server
- Database Connection Check
- Source Database Existence Check
- Target Database Existence Check
- Peripheral Check
- Migration Network Check
- Version Check
- Source Instance Permission Check
- Account Conflict Check
- Partial Database Parameter Check
- Source Instance Parameter Check
- Source Instance Type (Master or Replica)
- Parameter Configuration Conflict Check
- Target Database Content Conflict Check
- Target Database Space Check
- Target Database Load Check
- Local Disk Space Check
- Binlog Parameter Check
- Oplog Check
- Foreign Key Dependency Check
- View Check
- Advanced Object Check
- Incremental Migration Precondition Check
- Extension/Plugin Compatibility Check
- Source Database Balancer Check
- Source Database Node Role Check
- ShardKey Check
- Warning Item Check
- DLL Ring Sync Check for Single Database/Table Object
- DLL Sync Conflict Check for Single Database/Table Object in the Same Target
- Level-2 Subpartitioned Table Check
- Primary Key Check
- DDL Check for Tables to Be Migrated
- System Database Conflict Check
- Table Structure Check for Source and Target Instances
- InnoDB Table Check
- Migration Object Dependency Check
- Constraint Check
- Cutover Description
- Monitoring and Alarms
- Ops Management
- DTS-DBbridge
- Best Practices
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- (NewDTS) Data Migration APIs
- DescribeMigrationJobs
- DescribeMigrationDetail
- CreateMigrationService
- ModifyMigrationJob
- CreateMigrateCheckJob
- DescribeMigrationCheckJob
- SkipCheckItem
- StartMigrateJob
- StopMigrateJob
- ResumeMigrateJob
- RecoverMigrateJob
- ModifyMigrateName
- ModifyMigrateJobSpec
- IsolateMigrateJob
- DestroyMigrateJob
- DescribeMigrateDBInstances
- CompleteMigrateJob
- PauseMigrateJob
- ContinueMigrateJob
- Data Sync APIs
- ModifySyncJobConfig
- CreateModifyCheckSyncJob
- DescribeModifyCheckSyncJobResult
- StartModifySyncJob
- DescribeSyncJobs
- CreateSyncJob
- ConfigureSyncJob
- CreateCheckSyncJob
- SkipSyncCheckItem
- DescribeCheckSyncJobResult
- StartSyncJob
- StopSyncJob
- ResumeSyncJob
- ResizeSyncJob
- RecoverSyncJob
- IsolateSyncJob
- DestroySyncJob
- PauseSyncJob
- ContinueSyncJob
- Data Consistency Check APIs
- Data Types
- Error Codes
- DTS API 2018-03-30
- SDK Documentation
- FAQs
- Error Handling (NewDTS)
- Error Handling (OldDTS)
- Service Agreement
Migration from PostgreSQL to TDSQL-C for PostgreSQL
Last updated: 2023-12-25 14:23:19
This document describes how to use the data migration feature of DTS to migrate data from PostgreSQL to TDSQL-C for PostgreSQL.
The steps of data migration from TDSQL-C for PostgreSQL to TDSQL-C for PostgreSQL are basically the same as those in this scenario.
Notes
When DTS performs full data migration, it will occupy some resources in the source instance resources, which may increase the load of the source instance and the database pressure. If your database has low configurations, we recommend that you migrate the data during off-peak hours.
When you migrate an instance over the public network, make sure that the source database service is accessible over the public network and keep the public network connection stable. If the network fluctuates or fails, migration will fail, and you need to restart the migration task.
If the source PostgreSQL database is not TencentDB for PostgreSQL, it must have the replication permission; otherwise, the precheck will fail.
During migration, the migration rate can be affected by factors such as the read performance of the source, the network bandwidth between the source and the target instances, and the specification of the target instance. Migration concurrency is determined by number of CPU cores of target instance, for instance, if the target instance has 2 cores, concurrency will be 2.
Prerequisites
You have created a TDSQL-C for PostgreSQL instance as instructed in Creating Cluster.
The source and target databases must meet the requirements for the migration feature and version as instructed in Databases Supported by Data Migration.
You have completed all the preparations as instructed in Overview.
Permissions required for the source database:
If the source PostgreSQL database is not TencentDB for PostgreSQL, it must have the replication permission.
If the source database is TencentDB for PostgreSQL, the source database account must be the initial user selected when the TencentDB instance is created.
If some tables or objects have no permissions, you can use a high-privileged user to run the following sample statements to grant them permissions:
grant select on table `table name` to `username`;grant select on SEQUENCE `sequence name` to `username`;grant connect on database `database name` to `username`;grant select on large object `large object name` oid to `username`;GRANT USAGE ON SCHEMA `schema name` to `username`;
The target database account must be the initial user selected when the TencentDB instance is created.
If the target database instance contains the database to be migrated and the database owner is not the current migration user, run the following statement to grant the database permissions to the migration user:
alter database `database name` owner to `migration user`;
If the migration user (the account executing the migration task) is not a user with the
pg_tencentdb_superuser role, the system will prompt "Failed to verify the target instance permissions and unable to get the schema list" during verification. In this case, run the following statement to grant the initial user's permissions to the migration user:grant pg_tencentdb_superuser to migration user;
Application restrictions
Correlated data objects must be migrated together; otherwise, migration will fail. Common correlations include table referenced by views, view referenced by views, view/table referenced by procedures/functions/triggers, and tables correlated through primary/foreign keys.
To ensure the migration efficiency, the data of CVM-based self-built instances cannot be migrated across regions over the private network. If you need to migrate data across regions, you can do so over the public network.
To migrate the entire instance, there cannot be users and roles in the target database with the same name as those in the source database.
If you select Full + Incremental migration, tables in the source database must have a primary key; otherwise, data inconsistency will occur in the source and target database. We recommend you select Full migration for tables without the primary key.
Operation restrictions
Do not modify or delete user information (including username, password, and permissions) in the source and target databases and port numbers during migration.
Do not perform DDL or large object operations during structural, full, and incremental migration; otherwise, the migrated data will be inconsistent.
If you only perform full data migration, only data before the migration start time will be migrated. If you write new data into the source instance during migration, there will be data inconsistency between the source and target databases. In scenarios with data writes, to ensure the data consistency in real time, we recommend you select full + incremental data migration.
Environment requirements
Notes
The system will automatically check the following environment requirements before starting a migration task and report an error if a requirement is not met. If you can identify the failed check item, fix it as instructed in Check Item Overview; otherwise, wait for the system verification to complete and fix the problem according to the error message.
Type | Environment requirements |
Requirements for the source database | The source and target databases can be connected. The server where the source database resides must have enough outbound bandwidth; otherwise, the migration speed will be affected. Requirements for instance parameters: The wal_level parameter in the source database must be set to logical during incremental migration.The value of max_replication_slots in the source database must be greater than the number of databases to be migrated during incremental migration.The value of max_wal_senders in the source database must be greater than the number of databases to be migrated during incremental migration. |
Requirements for the target database | In full migration, the target database version must be greater than or equal to the source database version; in incremental migration, migration between versions later than 10.x is supported. The available size of the target database space must be at least 1.2 times that of the instances to be migrated in the source database. Incremental data migration will execute UPDATE and DELETE operations, causing some tables in the database to generate data fragments. Therefore, after migration is completed, the size of the tables in the target database may be larger than that in the source database. This is mainly because that the autovacuum trigger conditions of the source and target databases are different.The target database cannot have migration objects such as usernames and tables with the same name as those in the source database. The value of max_worker_processes of the target database during incremental migration must be greater than that of max_logical_replication_workers. |
Directions
The steps of data migration from PostgreSQL to TDSQL-C for PostgreSQL are basically the same as those described in Migration from PostgreSQL to TencentDB for PostgreSQL.
Yes
No
Was this page helpful?