Configure a PostgreSQL data source
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 configuration | Requirements |
|---|---|
| not distinguish environment | The Development Environment and Production Environment share one set of database and credential settings |
| Independent setting | Configure 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
- Go to DataOps Platform > Integration > Data Source and click + Data Source.
- Select PostgreSQL, click Next, and enter a Data Source name and remarks.
- Select not distinguish environment or Independent setting. For independent configuration, enter the Development Environment and Production Environment settings separately.
- Enter the connection parameters below. Under Connectivity Test, click Test. After the test passes, click Finish.
| Configuration Item | Description |
|---|---|
| Connection String | The 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 |
| Database | The 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 |
| Username | An account with permission to access the database and relevant tables |
| Password | The password corresponding to the username |
| Advanced | Enter 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
- Go to DataOps Platform > Integration > Offline Sync and click + Integration plan.
- Under Data Source, select PostgreSQL and the Data Source you created.
- Select a Method and configure the read scope as described below.
- Click Data Preview to check the fields, actual values, and read scope.
- To adjust the amount of data processed in each batch, expand Advanced and set Batch Quantity.
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.
| Method | Requirements |
|---|---|
| Entire Table | Select 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 Query | Select 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 Item | Description |
|---|---|
| Data source name | A PostgreSQL Data Source already created in the current space |
| Database and table | For Entire Table, select the database and table to read. For Custom Query, select the database and enter SQL |
| Shard Key | Optional for Entire Table. Available field types are int2, int4, and int8; Shard Keys are not supported for views |
| Data Filtering | Enable 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 size | An 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 Item | Description |
|---|---|
| Target table | The preset warehouse space table that receives the PostgreSQL data for this sync |
| Partition Information | For a partitioned target table, configure partition values as required by the target table |
| Write mode | Select 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 Item | Description |
|---|---|
| Write Table | The PostgreSQL database and table that receive the data; check the complete schema and table name |
| Write mode | The three modes below are supported; Overwrite is selected by default |
| Batch size | An advanced setting with a default value of 1000; the supported range is 1000–10000 |
| Write mode | Behavior and requirements |
|---|---|
| Append | Append the current data to the target table. Check for conflicts with existing primary keys or unique constraints |
| Overwrite | Clear 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 |
| Upsert | Match 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.
The figure shows field correspondences from an existing plan. It does not replace validation of the current connection and actual sync results.
Supported field types for reading
When PostgreSQL is the Data Source, the following field types are supported. The table uses the type names returned by field parsing.
| Type category | Supported PostgreSQL field types |
|---|---|
| Boolean | bool |
| Integer | int2, smallserial, int4, serial, int8, bigserial |
| Floating-point and fixed-point numbers | float4, float8, numeric |
| String | bpchar, character, varchar, text |
| JSON, UUID, and spatial types | json, jsonb, uuid, geometry, geography |
| Date and time | date, 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, andint8correspond to the familiar namessmallint,integer, andbigint, respectively;float4andfloat8correspond torealanddouble 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
_byteain 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
- 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.
- Use Data Preview to check source fields and sample values, then review the Field Mapping, target types, and target table constraints individually.
- Enter the plan name, owner, and remarks, then click Save.
- 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.
- 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.
- Open the saved plan, click Mount on task flow, and select the target Flow. If it has not been created, see Create a Flow.
- Enter the node name, execution mode, owner, and other details, then click Create and Mount.
- 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.
- 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
| Symptom | What to check |
|---|---|
| Connectivity test fails | Check 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 previewed | Check 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 view | Shard Keys are not supported for views; read the data according to the actual view or query scope |
| Preview works, but execution fails | Check the production credentials, read/write permissions, target schema, table constraints, write mode, partitions, and parameters, and review the execution log |

