PostgreSQL Instruction

Data Source

Scheduled Task in FineDataLink of V4.1.3 and later versions supports data reading from partition tables in PostgreSQL databases, as shown in the following figure.

Note:

Reading stored procedures is not supported.

Data Destination and Mapping

For FineDataLink of 4.1.9.3 and later versions:

1. Scheduled Task supports data writing into partition tables in PostgreSQL databases.

2. If you set Target Table to Auto Created Table and select certain fields in the target table as primary keys, you can click Parition Key Setting and configure the partition key.

Note: Only level-one partition is supported.

Notes:

1. This operation is only available for PostgreSQL of versions later than 10.x. PostgreSQL of earlier versions does not support automatic creation of partition tables. Although PostgreSQL 10.x supports declarative partitioning, it does not allow setting primary keys for partition tables. Therefore, you cannot set physical primary keys for partition tables in PostgreSQL 10.x databases in FineDataLink currently.

2. Hash Partitioning is available for PostgreSQL of V11.x and later versions. When you set Partitioning Method to Range Partitioning or List Partitioning, a default partition is available to store data that does not match any partition. Only one default partition is allowed.

3. The following table describes the version requirements and the number of supported partition fields of different partitioning methods.

4. Data types supported by the partition key:

• Date type: such as date and timestamp 

• Text type: such as char, varchar, and text    

• Numeric type: such as tinyint, smallint, int, bigint, float, double, and decimal

Write Method

FineDataLink of V4.2.7.4 and later versions supports Transaction Control when the target end is a PostgreSQL database.

FineDataLink of V4.2.18.2 and later versions supports COPY Loading when the target end is a PostgreSQL database.

Application scenarios of COPY Loading:

• COPY Loading outweighs Common Loading in write speed if the data exceeds 50 thousand rows.

• COPY Loading outweighs Common Loading in write speed if Write Method is set to Write Data into Target Table After Emptying It.

Data Source 

1. If you use a PostgreSQL database as the data source of real-time tasks and real-time pipeline tasks:

• You can set Read Mode to pgoutput when PostgreSQL is of V10 or later versions and FineDataLink is of V4.2.10.1 or later versions.

• In all other cases, you can only set Read Mode to wal2json.

2. When two real-time pipeline tasks use the same PostgreSQL data connection as the source for synchronization, where the read mode of one task is wal2jaon and the other is pgoutput, two separate capture tasks will be created automatically.

Setting Read Mode to wal2jaon

1. In FineDataLink of V4.1.9.3 and later versions, if the target table in a pipeline task is set to Auto Created Table, you can select certain fields in the target table as partition keys, as shown in the following figure.

For details, see the "Data Destination and Mapping" part in the "Data Development - Scheduled Tasks" section of this document.

2. View synchronization is not supported. 

3. Supported table type: regular table

4. In real-time pipeline tasks, synchronization without a primary key is not supported.

5. Unsupported field types: GEOMETRY, GEOGRAPHY, and RASTER

Setting Read Mode to pgoutput

1. View synchronization is not supported.

2. Supported table type: regular table

3. In real-time pipeline tasks, synchronization without a primary key is not supported.

4. Unsupported field types: GEOMETRY, GEOGRAPHY, RASTER, and Generated Columns

Data Destination

You can use real-time pipeline tasks and real-time tasks to write data into existing partitioned tables in PostgreSQL databases. If you have set Target Table to Auto Created Table and set primary keys, you can click Parition Key Setting and configure the partition key. The specific logic is the same as that of scheduled tasks.

From FineDataLink V4.2.10.4, Scheduled Pipeline supports data writing into PostgreSQL databases. For details, see Scheduled Pipeline Overview.

In FineDataLink of V4.1.9.3 and later versions, you can select partition tables in PostgreSQL databases as data sources in Data Service, as shown in the following figure.