- 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 TencentDB for PostgreSQL
Last updated: 2023-04-03 16:54:25
This document describes how to use the data migration feature of DTS to migrate data from PostgreSQL to TencentDB for PostgreSQL.
The steps of data migration from TDSQL-C for PostgreSQL to TencentDB 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, 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.
When you configure the migration task, the source database address must be an IP and port that can directly connect to the PostgreSQL instance instead of IPs and ports of proxies such as Pgpool.
Prerequisites
You have created a TencentDB for PostgreSQL instance as instructed in Creating TencentDB for PostgreSQL Instance.
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
1. (Optional) When PostgreSQL 9.4, 9.5, and 9.6 are used as the source database for "full + incremental migration", you can install the tencent_decoding extension as instructed below. For other scenarios, skip this step.
1.1 Download extension based on architecture of server where source database resides.
Only "x86_64" and "aarch64" system architectures are supported.
The version of the extension version need to be the same as that of PostgreSQL.
Requirements for the Glibc version: x86_64 system should be v2.17 - 323 or later; aarch64 system should be v2.17 - 260 or later.
View the Glibc version on Linux:
RHEL/CentOS: rpm -q glibc
View the Glibc version on other operation systems (Debian/Ubuntu/SUSE):
ldd --version | grep -i libc
1.2 Place the downloaded tencent_decoding.so file in the lib folder of the Postgres process directory without restarting the instance.
2. Log in to the DTS console, select Data Migration on the left sidebar, and click Create Migration Task to enter the Create Migration Task page.
3. On the Create Migration Task page, select the types, regions, and specifications of the source and target instances and click Buy Now.
Configuration Item | Description |
Source Instance Type | Select the source database type, which cannot be changed after purchase. In this scenario, select PostgreSQL. |
Source Instance Region | Select the source database region. If the source database is a self-built one, select a region nearest to it. |
Target Instance Type | Select the target database type, which cannot be changed after purchase. In this scenario, select PostgreSQL. |
Target Instance Region | Select the target database region. |
Specification | Select the specification of the migration link based on your business conditions. For the performance and billing details of different specifications, see Billing Overview. |
4. On the Set source and target databases page, configure the task, source database, and target database settings. After the source and target databases pass the connectivity test, click Create.
Notes
If the connectivity test fails, troubleshoot as prompted or as instructed in Database Connection Check and try again.
Category | Configuration Item | Description |
Task Configuration | Task Name | Set a task name that is easy to identify. |
| Running Mode | Immediate execution: The task will be started immediately after the task verification is passed. Scheduled execution: You need to configure a task execution time and the task will be started automatically then. |
| Tag | Tags are used to manage resources by category in different dimensions. If the existing tags do not meet your requirements, go to the console to create more. |
Source Database Settings | Source Database Type | The source database type selected during purchase, which cannot be changed. |
| Region | The source database region selected during purchase, which cannot be changed. |
| Access Type | Select a type based on your scenario. In this scenario, select Database. For the preparations for different access types, see Overview. 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. Public Network: The source database can be accessed through a public IP. Self-Build on CVM: The source database is deployed in a CVM instance. Direct Connect: The source database can be interconnected with VPCs through Direct Connect. VPN Access: The source database can be interconnected with VPCs through VPN Connections. Database: The source database is a TencentDB instance. CCN: The source database can be interconnected with VPCs through CCN. |
| Cross-/Intra-Account | Intra-account: The source and target database instances belong to the same Tencent Cloud root account. Cross-account: The source and target database instances belong to different Tencent Cloud root accounts. The following uses migration under the same account as an example. |
| Database Instance | Select the instance ID of source PostgreSQL database. |
| Account | Account of the source PostgreSQL database, which must have the required permissions. |
| Password | Password of the source PostgreSQL database account. |
Target Database Settings | Target Database Type | The target database type selected during purchase, which cannot be changed. |
| Region | The target database region selected during purchase, which cannot be changed. |
| Access Type | Select a type based on your scenario. In this scenario, select Database. |
| Database Instance | Select the instance ID of the target database. |
| Account | Account of the target database, which must have the required permissions. |
| Password | Password of the target database. |
5. On the Set migration options and select migration objects page, configure the migration type and objects and click Save.
Configuration Item | Description |
Migration Type | Select an option based on your scenario. Structural migration: Structured data such as databases and tables in the database will be migrated. Full migration: The entire database will be migrated. The migrated data will only be existing content of the source database when the task is initiated but not include the incremental data written to the source database after the task is initiated. Full + Incremental migration: The migrated data will include the existing content of the source database when the task is initiated as well as the incremental data written to the source database after the task is initiated. If there are data writes to the source database during migration, and you want to smoothly migrate the data in a non-stop manner, select this option. |
Migration Object | Entire instance: Migrate the entire database instance, including the metadata definitions of roles and users but excluding system databases such as system objects in PostgreSQL. Specified objects: Migrate specified objects. |
Specify object | Select the objects to be migrated in Source Database Object and move them to the Selected Object box. |
6. On the task verification page, verify the task. After the verification is passed, click Start Task.
If the verification fails, fix the problem as instructed in Check Item Overview and initiate the verification again.
Failed: It indicates that a check item fails and the task is blocked. You need to fix the problem and run the verification task again.
Alarm: It indicates that a check item doesn't completely meet the requirements, and the task can be continued, but the business will be affected. You need to assess whether to ignore the alarm or fix the problem and continue the task based on the alarm message.
7. Return to the data migration task list, and you can see that the task has entered the Preparing status. After 1–2 minutes, the data migration task will be started.
Select Structural migration or Full migration: Once completed, the task will be stopped automatically.
Select Full + Incremental migration: After full migration is completed, the migration task will automatically enter the incremental data sync stage, which will not stop automatically. You need to click Complete to manually stop the incremental data sync. Then, the task will enter the Completed status. At this point, do not make any changes to the source and target databases. The backend will automatically align some objects with the source.
Manually complete incremental data sync and business switchover at appropriate time.
Check whether the migration task is in the incremental sync stage without any lag. If so, stop writing data to the source database for a few minutes.
Manually complete incremental sync when the data gap between the target and the source databases is 0 MB and the time lag between them is 0 seconds.
8. (Optional) If you want to view, delete, or perform other operations on a task, click the task and select the target operation in the Operation column. For more information, see Viewing Task.
9. After the migration task status becomes Task successful, you can formally cut over the business. For more information, see Cutover Description.
Yes
No
Was this page helpful?