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.

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, you can select certain fields in the target table as partition keys.

Note: Only level-one partition is supported.

Notes:

• 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.

• 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.

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

  Database	Range Partitioning	List Partitioning	Hash Partitioning
  PostgreSQL	Later than 10.x | N fields	Later than 10.x | 1 field	Later than 11.x | N fields

Write Method

FineDataLink of V4.2.7.4 and later versions supports Data Synchronization - Write Method when the target end is a PostgreSQL database.

 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

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.