PostgreSQL data migration

Last updated: 2020-02-19 19:27:34

PDF

Operation scene

Data Transfer Service DTS supports data migration and provides continuous data replication from Client's PostgreSQL database to TencentDB. Users can conduct online hot migration of data in the case of non-stop, and support migration of local IDC with public network IP/Port or Direct connect access to Tencent Cloud or PostgreSQL database on Tencent Cloud CVM.

At present, data migration only supports PostgreSQL database versions 9.3.x and 9.5.x. At the same time, 9.3.x does not support incremental synchronization. 9.5.x needs to be online. Synchronous plug-in To support it. For more information on configuration operation, please see Synchronize plug-in configuration .

Operation step

Create DTS data migration job

Login DTS console To enter the data migration page, click [Create job].

Set source and destination databases

After the Redirect page, Enter job settings, source database settings and target database settings.

Task Configuration

  • Name of job: specify a name for job.
  • Regular execution: specify the start time for the migration of job.

Source library settings and target library settings

Source database types: five source database types are supported: PostgreSQL on public network IP, Client's PostgreSQL on Cloud Virtual Machine, PostgreSQL connected to Tencent Cloud by Direct connect, PostgreSQL connected to VPN, and PostgreSQL to cloud database.

Source database type Description
PostgreSQL with public network IP Can IP Access's PostgreSQL database through the public network. Required information:
  • PostgreSQL host address
  • PostgreSQL port
  • PostgreSQL account
  • PostgreSQL cipher
  • Client's PostgreSQL on Cloud Virtual Machine Supports Client's PostgreSQL database based on Cloud Virtual Machine CVM in basic network and VPC environment. You need to specify the instance ID and network environment of Cloud Virtual Machine CVM when using it. Required information:
  • Region: currently, only CVM Client's PostgreSQL in Intra-region is supported to migrate TencentDB. If CVM and TencentDB are not Intra-region, use CVM public network, and select "PostgreSQL with public network IP" to achieve migration.
  • CVM network: support basic network and VPC
  • VPC: if you choose VPC, you need to choose the VPC and subnet to which you belong.
  • Cloud Virtual Machine instance ID
  • PostgreSQL port
  • PostgreSQL account
  • PostgreSQL cipher
  • The PostgreSQL connected by Direct connect Local IDC Client's PostgreSQL uses Direct connect connects to DC After the service is connected to Tencent Cloud, you can use DTS to migrate data to Tencent Cloud. Required information:
  • Direct connect Gateway: Direct connect Gateway used to access Tencent Cloud's database server, Learn about Direct connect Gateway
  • VPC: VPC to which Direct connect Gateway belongs
  • PostgreSQL host address: PostgreSQL host address in IDC. DTS data migration will be mapped to IP through Direct connect gateway after Access
  • PostgreSQL port
  • PostgreSQL account
  • PostgreSQL cipher
  • PostgreSQL accessed by VPN Local IDC Client's PostgreSQL passed VPN connection service Or after Cloud Virtual Machine's Client's VPN service is connected to Tencent Cloud, you can use DTS to migrate data to Tencent Cloud. Required information:
  • Region: only the VPN service within Intra-region is supported.
  • VPN type: Cloud VPN service Or Cloud Virtual Machine on Client's VPN
  • VPN gateways: only Cloud VPN service Additional VPN gateway information is required. Learn about VPN
  • VPC: VPC, to whom VPN service belongs
  • PostgreSQL host address: PostgreSQL host address in IDC. DTS data migration will be mapped to IP through Direct connect gateway after Access
  • PostgreSQL port
  • PostgreSQL account
  • PostgreSQL cipher
  • TencentDB for PostgreSQL TencentDB for PostgreSQL cloud database instance. Required information:
  • PostgreSQL instance ID
  • PostgreSQL account
  • PostgreSQL cipher
  • Select the database to be migrated

    Select the database to be migrated (you can choose to migrate all or part of Database table).

    Verify Migration Tasks

    Click "next step: verify job" to verify the migration of job information. Only after all the verification items have been passed can Launch transfer job. After the verification is completed, click "Launch".
    There are three states of job check:

    • Pass: indicates that the check is completely passed.
    • Warning: indicates that the verification fails. The normal operation of the database may be affected during or after the migration, but the execution of the migration of job will not be affected.
    • Failed: indicates that the verification failed and the migration cannot be carried out. If the verification fails, please check and modify the migration job information according to the wrong check item, and then try the verification again.

    Launch's migration

    After the verification is passed, return to the data migration list. In the "Operation" column, click "Launch now" to start data migration. It should be noted that if you set a scheduled time for the migration of job, the migration of job will be queued and executed at the set time. If the timing is not set for job, the migration of job will be executed immediately.
    After migrating Launch, you can see the corresponding migration progress information under Migration job. When the mouse points to the exclamation mark prompt after the step, the process required for the migration and the current stage can be displayed.

    Due to the limitations of system design, one-time submission or queuing of multiple migrations job will be carried out sequentially according to the queuing time.

    Incremental synchronization

    The incremental synchronization option is required by default when creating migration job. After the data migration is completed, the target TencentDB for PostgreSQL database will be set as the backup database of the source database, and the new data of the source database during the migration will be synchronized to the target TencentDB for PostgreSQL database through Master/slave synchronization. At this point, changes on the source database are synchronized to the target TencentDB for PostgreSQL.
    After the migration is completed, you must manually click "finish", and the synchronization relationship between the source database and the target database will be broken to complete the migration.

    Data cannot be written to the target database instance before the synchronization is disconnected, otherwise the data of the source database and the target database may be inconsistent and the data comparison may fail, resulting in the failure of the migration.

    Stop migration

    During the migration process, if you need to stop the migration, click "undo" to stop the migration in the "Action" column of job.

    • Again, Launch may cause verification failure or job failure. You may need to manually empty the databases or tables in the target database that may cause conflicts before Launch can migrate job again.
    • When migrating separate tables, you need to ensure that all tables on which foreign keys depend must be migrated.

    Complete the migration

    Synchronize plug-in configuration

    1. Download and copy Dts_decoding Go to lib Directory in the PostgreSQL installation path.
    2. Modify data Directory postgresql.conf configuration file.
    wal_level >= logical
     Available max_replication_slots >= Number of databases migrated
     Available max_wal_senders       >= Number of databases migrated



    3. Modify data Directory pg_hba.conf configuration file.
    Need to configure replication connection

    4. Restart the source instance.

    If you use the specified Database table feature and the table uses rule or Associate other tables, it may cause incremental migration to insert data unsuccessfully, because some SQL is not in the supported features of migration. If this problem occurs, you can use the schema migration feature or the all-instance migration feature.