PostgreSQL data migration
Last updated: 2020-02-19 19:27:34PDF
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 .
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.
- 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:
|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:
|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:
|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:
|TencentDB for PostgreSQL||TencentDB for PostgreSQL cloud database instance. Required information:
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.
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.
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.
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
- Download and copy Dts_decoding Go to lib Directory in the PostgreSQL installation path.
- 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.