PostgreSQL as Destination
1. Applicable Scenarios
This document applies to scenarios where you migrate a DTS-supported data source to a PostgreSQL destination using the Baidu AI Cloud’s Data Transmission Service DTS.
2 Restrictions on Using PostgreSQL Database as DTS destination
- Structure migration from a non-PostgreSQL database to a PostgreSQL database is unavailable
3 Prerequisites for Using PostgreSQL Database as DTS destination
3.1 Environment Requirements
DTS allows you to use the PostgreSQL 9.4 and 10.10 versions of the self-built database as destinations.
3.2 Requirements for Privilege of Database Account
destination:
| Database | Structure migration | Total migration | Incremental migration |
|---|---|---|---|
| Self-built PostgreSQL database | create and usage privileges of migration objects | owner privilege of schema | owner privilege of schema |
For the self-built PostgreSQL database, and the creation and authorization method of a database account, see CREATE USER and GRANT Syntax.
4 Application of PostgreSQL Database as DTS destination
In terms of a PostgreSQL database as the destination, for the operation process of task creation, task configuration, pre-check, task start, task pause, and task termination, see Best Practice Documentation. It is slightly different from other data sources in the task configuration parameters and object mapping parts.
4.1 Task Configuration Parameters
As shown in the figure above, you can self-build a PostgreSQL instance by the public network at the destination of DTS. The instructions for configuration parameters at the PostgreSQL destination are as below:
- Access Type: A self-built PostgreSQL instance that supports Public Network/BCC/BBC/DCC
- **Data Type:** Regularly chose PostgreSQL
- IP/ Port: Access IP and service port of a self-built PostgreSQL database
- Database: Database name to be migrated in the self-built PostgreSQL database
- Account: Account of the self-built PostgreSQL database
- Password: Password corresponding to the account of the database
4.2 Object Mapping
PostgreSQL is a three-level schema. When you migrate data from the source end of the two-level schema to PostgreSQL, you need to map the database name at the source end as the schema name at the destination via DTS. DTS provides a database table name mapping function for you to choose. As shown in the following figure, the selected migration objects may appear in the list of the selected objects on the right side. DTS supports the upstream and downstream database table name mapping, the column filter of blacklist/whitelist, and other functions. You can click “Edit” to configure the mapping and filter rules for each migration object.
After you finish the mapping configuration of the objects, click Save and Pre-check to start the Pre-check of the task.
5 PostgreSQL destination Data Types Supported by DTS
The following table lists the default mapping between the DTS data types supported by DTS and the PostgreSQL destination data types.
For any information on how to view the data types mapped at the source end, see the Data Type Mapping of Source-end Data Sources.
| DTS Field Type | POSTGRESQL Data Type |
|---|---|
| DTS_TYPE_INT1 | smallint |
| DTS_TYPE_INT2 | smallint |
| DTS_TYPE_INT4 | integer |
| DTS_TYPE_INT8 | bigint |
| DTS_TYPE_NUMBER | If precision is<=147455 && scale is<=16383, you should use decimal (p,s), or else, you should use character varying |
| DTS_TYPE_FLOAT | real |
| DTS_TYPE_DOUBLE | double precision |
| DTS_TYPE_BYTES | bytea |
| DTS_TYPE_DATE | date |
| DTS_TYPE_TIME | If the decimal digits are=>0, and are<=6, you should use time, or else, you should use character varying |
| DTS_TYPE_DATETIME | If the decimal digits are=>0, and are<=6, you should use datetime, or else, you should use character varying |
| DTS_TYPE_STRING | In case of fixed length, you should use the character(n); and in case of variable length, you should use character varying(n) |