Skip to main content

Configure a PostgreSQL data source

Last updated 11/03/2026

The PostgreSQL Data Source reads PostgreSQL tables or query results into the platform's preset warehouse through Offline Sync. It can also write preset warehouse space tables to PostgreSQL. This article covers connection settings, source and target configuration, Field Mapping, supported field types, sync validation, and Flow scheduling.

Limitations and prerequisites​

Before you configure the Data Source, check the following:

  • The supported directions are PostgreSQL → preset warehouse and preset warehouse → PostgreSQL. This plan does not support direct synchronization between PostgreSQL and other external Data Sources.
  • Prepare the PostgreSQL server address, port, database name, and the username and password used to connect. The platform's runtime environment must be able to reach the address, and the database connection access controls and network allowlist must permit connections from the platform servers.
  • The read account must be able to connect to the database, access the relevant schema, and read the selected tables, views, or data referenced by the query. A write account also needs the target table permissions required by the selected write mode. Ask the database administrator to grant permissions for the required operations. See PostgreSQL privileges.
  • Prepare the target table and check its field types, non-null constraints, and primary key. When reading into the preset warehouse, see Create and manage space tables. When writing to PostgreSQL, also confirm that the target database and schema already exist.
Environment configurationRequirements
not distinguish environmentThe Development Environment and Production Environment share one set of database and credential settings
Independent settingConfigure the Development Environment and Production Environment separately; both configurations must have access to the database, schema, and tables selected in the plan

Data preview uses the Development Environment configuration, while manual execution uses the Production Environment configuration. Check the database, table permissions, and network access for each configuration.

Transfer passwords securely, and keep credentials out of screenshots, logs, and public documentation. After replacing credentials or changing permissions, validate the Data Source connection and sync plan again.

Create a PostgreSQL Data Source​

  1. Go to DataOps Platform > Integration > Data Source and click + Data Source.
  2. Select PostgreSQL, click Next, and enter a Data Source name and remarks.
  3. Select not distinguish environment or Independent setting. For independent configuration, enter the Development Environment and Production Environment settings separately.
  4. Enter the connection parameters below. Under Connectivity Test, click Test. After the test passes, click Finish.
Configure the PostgreSQL connection string, database, and access credentials
Configuration ItemDescription
Connection StringThe PostgreSQL JDBC connection address, for example, jdbc:postgresql://<host>:<port>/<database>. Replace the placeholders with the actual address, port, and database. For connection parameters, see pgJDBC connection configuration
DatabaseThe PostgreSQL database name used for the connectivity test; it must match the connection address and account permissions. Enter a database name here, not a schema name
UsernameAn account with permission to access the database and relevant tables
PasswordThe password corresponding to the username
AdvancedEnter any required extended connection parameters in JSON format; leave this blank if no additional settings are needed

Databases and schemas are different levels in PostgreSQL. When configuring a sync plan, select the database first, then check the schema containing the table to avoid selecting a table with the same name in another schema.

After the connectivity test passes, load the tables, preview the data, and run the sync plan to validate table permissions and actual read and write results.

Configure PostgreSQL as the source​

  1. Go to DataOps Platform > Integration > Offline Sync and click + Integration plan.
  2. Under Data Source, select PostgreSQL and the Data Source you created.
  3. Select a Method and configure the read scope as described below.
  4. Click Data Preview to check the fields, actual values, and read scope.
  5. To adjust the amount of data processed in each batch, expand Advanced and set Batch Quantity.
Example PostgreSQL source table, filter, and batch settings from an existing plan

This figure shows settings from an existing plan and does not replace validation of the current connection and data read. After configuring the plan, use data preview to check the actual results.

MethodRequirements
Entire TableSelect the database and table, and check the schema containing the table. Optionally select a Shard Key, or enable Data Filtering and enter a filter condition beginning with WHERE
Custom QuerySelect the database and enter SQL to read the data. Specify any field expressions or read scope explicitly in the query. Check the schemas, tables, and result field names referenced by the SQL; do not execute statements that modify data
Configuration ItemDescription
Data source nameA PostgreSQL Data Source already created in the current space
Database and tableFor Entire Table, select the database and table to read. For Custom Query, select the database and enter SQL
Shard KeyOptional for Entire Table. Available field types are int2, int4, and int8; Shard Keys are not supported for views
Data FilteringEnable this as needed for Entire Table and use a WHERE condition to limit the read scope. When disabled, this filter condition is not applied
Batch sizeAn advanced setting with a default value of 1000; the supported range is 1000–50000

After changing the Data Source, database, source table, or query, check the read scope, Shard Key, preview results, and Field Mapping again. If fields are added, deleted, or changed in the source table, parse the fields again and review the mapping. If the plan uses space parameters, see Parameter management and check their actual values at execution time.

Configure the data target​

Read PostgreSQL data into the preset warehouse​

Under Data Target, select the platform's Preset Warehouse, then select the target space table. If the target table has not been created, go to Tables and create it with the required field and partition definitions. See Create and manage space tables.

Configuration ItemDescription
Target tableThe preset warehouse space table that receives the PostgreSQL data for this sync
Partition InformationFor a partitioned target table, configure partition values as required by the target table
Write modeSelect Append or Overwrite; Overwrite is selected by default, so adjust it to meet your data retention requirements

Before execution, confirm the target table, target partition, and write mode again to avoid overwriting data that must be retained.

Write preset warehouse data to PostgreSQL​

Under Data Source, select the preset warehouse space table to read. Under Data Target, select PostgreSQL and the Data Source you created, then select the target database and table. Check the schema containing the target table. If no schema is specified, public is used; missing schemas are not created automatically.

Configuration ItemDescription
Write TableThe PostgreSQL database and table that receive the data; check the complete schema and table name
Write modeThe three modes below are supported; Overwrite is selected by default
Batch sizeAn advanced setting with a default value of 1000; the supported range is 1000–10000
Write modeBehavior and requirements
AppendAppend the current data to the target table. Check for conflicts with existing primary keys or unique constraints
OverwriteClear the existing data from the target table, then write the current data. Before execution, confirm the scope to be cleared and the backup requirements. Do not assume that the original data will be restored automatically if execution fails partway through
UpsertMatch records using the target table's actual primary key, update existing records, and insert new records. The target table must have a primary key for matching, and all primary key fields must be mapped

Source and target field names can differ, but their correspondence must be defined through Field Mapping. Before writing to PostgreSQL, also check target field lengths, precision, non-null constraints, and other table constraints.

Field Mapping and supported field types​

After configuring the source and target, use Field Mapping to map source fields to target fields. You can select fields individually, or use Map by Name or Map by Row and then check each mapping. Each target field can map to only one source field.

Example PostgreSQL Field Mapping from an existing plan

The figure shows field correspondences from an existing plan. It does not replace validation of the current connection and actual sync results.

When PostgreSQL is the Data Source, the following field types are supported. The table uses the type names returned by field parsing.

Type categorySupported PostgreSQL field types
Booleanbool
Integerint2, smallserial, int4, serial, int8, bigserial
Floating-point and fixed-point numbersfloat4, float8, numeric
Stringbpchar, character, varchar, text
JSON, UUID, and spatial typesjson, jsonb, uuid, geometry, geography
Date and timedate, timestamp, timestamptz
Array_bool, _bytea, _int2, _int4, _int8, _float4, _float8, _bpchar, _character, _varchar, _text

bytea, time, timetz, and other unrecognized types are not supported. Only the array types listed above are supported; types such as _numeric, _date, _timestamp, and _jsonb are outside the supported scope.

Supported field types for writing​

When PostgreSQL is the Data Target, the supported PostgreSQL field types are the same as in the table above. bytea, time, timetz, unlisted array types, and other unrecognized types are not supported.

Before writing, check the selected PostgreSQL table's field definitions, primary key, non-null constraints, and other constraints. Support for a field type does not mean that arbitrary content can be written to JSON, UUID, spatial, or array fields.

Field type limitations​

  • PostgreSQL field parsing may return metadata type names. For example, int2, int4, and int8 correspond to the familiar names smallint, integer, and bigint, respectively; float4 and float8 correspond to real and double precision. Use the names actually returned by parsing.
  • For fields with length, precision, or scale parameters, check the complete definition. For time fields, also check time zone and precision requirements.
  • For arrays, check element types, dimensions, and null element requirements. Inclusion of _bytea in the supported scope does not mean that arbitrary binary elements or array structures have been validated.
  • After the source or target table structure changes, parse the fields again and check their correspondences. A field being visible in the data preview does not mean that its type is supported for synchronization.

Saving, validation, and Workflow Schedule​

Save and validate the sync results​

  1. Check the source table or SQL, filter conditions, target table and partitions, write mode, and Field Mapping. When writing to PostgreSQL, check the schema, primary key, and table constraints again.
  2. Use Data Preview to check source fields and sample values, then review the Field Mapping, target types, and target table constraints individually.
  3. Enter the plan name, owner, and remarks, then click Save.
  4. Click Manual Execution, select the execution mode as prompted, and check the parameter values before starting. Manual execution uses the Production Environment configuration; the execution mode does not switch between the Development Environment and Production Environment.
  5. Check the execution status and logs under Manual Execution Records. After the task succeeds, check the data volume, field correspondence, and sample numeric, time, array, and null values in the target table.

With independent environment configurations, successfully loading or previewing data during development does not mean that production credentials and read/write permissions are ready. Before the first execution, check both configurations and the tables, partitions, and parameters in the target environment.

Mount on a Flow​

After manual validation passes, you can mount the plan on a Flow and run it on a schedule.

  1. Open the saved plan, click Mount on task flow, and select the target Flow. If it has not been created, see Create a Flow.
  2. Enter the node name, execution mode, owner, and other details, then click Create and Mount.
  3. Click Go to Flow, check the node and its upstream and downstream dependencies, and verify the tables, filter conditions, and space parameters used for scheduling.
  4. Complete the Flow schedule settings, save, and publish the Flow. Mounting the node does not mean that it has been published online.

After a plan is mounted on a Flow, it can no longer be run manually from the plan details page. Execute and validate the node from the Flow instead.

FAQ​

SymptomWhat to check
Connectivity test failsCheck the connection string, database, username, password, platform server network access, and database connection access controls
The connection passes, but the table cannot be found or previewedCheck the selected database, schema, table name, and the account's read permissions. For Custom Query, check the SQL and its referenced objects
A Shard Key cannot be selected for a viewShard Keys are not supported for views; read the data according to the actual view or query scope
Preview works, but execution failsCheck the production credentials, read/write permissions, target schema, table constraints, write mode, partitions, and parameters, and review the execution log
Was this page helpful?