tencent cloud

Feedback

Migration from PostgreSQL to TencentDB for PostgreSQL

Last updated: 2021-12-23 16:10:45

    This document describes how to use the data migration feature of DTS to migrate data from PostgreSQL to TencentDB for PostgreSQL.

    Notes

    • During full data migration, DTS consumes certain source database resources, which may increase the load and pressure of the source database. If your database configuration is low, we recommend you migrate the data during off-peak hours.
    • When migrating an instance on 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, in which case you need to initiate the migration task again.
    • If the source PostgreSQL database is not TencentDB for PostgreSQL, it must have the replication permission; otherwise, the precheck will fail.

    Prerequisites

    • You have created a 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 preparations.
    • The source database must have the following permissions:
      • 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 user 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 user must be the initial user selected when the TencentDB instance is created.
      • If the target database 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 target migration account is not a user with the pg_tencentdb_superuser role, the system will prompt "Failed to verify the target database 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 reference by views, view reference by views, view/table reference by stored procedures/functions/triggers, and tables correlated through primary/foreign keys.
    • To ensure migration efficiency, cross-region migration of CVM-based self-built instances is not supported. If you need cross-region migration, use the public network access type.
    • If the entire instance is migrated, there cannot be users and roles in the target database with the same name as those in the source database.

    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 database 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

    Note:

    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 Requirements for Check Items; otherwise, wait for the system verification to complete and fix the problem according to the error message.

    TypeEnvironment Requirement
    Requirements for 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 the database 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 above v10.x is supported.
  • The available size of the target database space must be at least 1.2 times that of the databases/tables 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 the `autovcauum` 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. 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.
    2. On the Create Migration Task page, select the region of the target database and click Free Trial. Currently, the DTS data migration feature is free of charge.
      Note:

      Select the region with caution as it cannot be changed once the migration task is created.

    3. 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.
      Note:

      If the connectivity test fails, troubleshoot and fix the problem as prompted and as instructed in Troubleshooting Guide and try again.

      Setting TypeConfiguration ItemDescription
      Task Configuration Task Name Set a meaningful name for easy task identification.
      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 TypeSelect your source database type. In this document, select **PostgreSQL**.
      Access TypeSelect a type based on your scenario. In this document, select **Database**.
      To ensure migration efficiency, cross-region migration of CVM-based self-built instances is not supported. If you need cross-region migration, use the public network access type.
      • 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 Connection.
      • Database: the source database is a TencentDB database.
      • CCN: the source database can be interconnected with VPCs through CCN.
      For the preparations for different access types, see Overview.
      RegionSelect the region of the source PostgreSQL database.
      Database InstanceSelect the instance ID of the target database.
      AccountAccount of the source PostgreSQL database, which must have the required permissions.
      PasswordPassword of the source PostgreSQL database.
      Target Database Settings Target Database TypeSelect **PostgreSQL**.
      Access TypeSelect a type based on your scenario. In this document, select **Database**.
      RegionSelect the same region as in the source database settings.
      Database InstanceSelect the instance ID of the target database.
      Account.Account of the target database, which must have the required permissions.
      PasswordPassword of the target database.
    4. On the Set migration options and select migration objects page, configure the migration type and objects and click Save.
      Configuration ItemDescription
      Migration Type Select a type 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.
      • Full + incremental migration: the entire database and subsequent incremental data will be migrated. If there are data writes 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.
    5. On the task verification page, verify the task. After the verification is passed, click Start Task.
      If the verification failed, fix the problem as instructed in Fix for Verification Failure and initiate the verification task again.
      • Failed: it indicates that a check item failed 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.
    6. 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.
        • Select an appropriate time to manually complete the incremental data sync and perform business switch.
        • You can see that the migration task is in the incremental sync stage, and there is no latency. Stop writing the source database for several minutes.
        • When the source-target database data gap is 0 MB, and the source-target database time lag is 0s, manually complete the incremental sync.
    7. (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 Task Management.
    8. After the migration task status becomes Task successful, you can formally cut over the business. For more information, see Cutover Description.
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support