Skip to main content

PostgreSQL to PolarDB for Oracle Data Replication

When you create a PostgreSQL to PolarDB for Oracle replication task, the console provides schema, full, and incremental replication options. This page describes the current schema-plus-full replication flow.

Before you begin​

  • Add the PostgreSQL source data source and the PolarDB for Oracle target data source to NineData, and make sure that the target data source is available. For instructions, see Add Data Source.

  • When you create the replication task, select PolarDB (compatible with Oracle) as the target data source type.

  • The replication task must be able to access both data sources. The required account privileges are determined by the precheck results when you create the task.

Procedure​

  1. Sign in to the NineData Console.

  2. In the left navigation pane, click Replication > Data Replication.

  3. On the Replication page, click Create Replication.

  4. On the Source & Target tab, configure the fields in the table, and click Next.

    Parameter
    Description
    NameEnter a name for the data synchronization task. To make the task easier to find and manage later, use a meaningful name. Up to 64 characters are supported.
    SourceThe data source that contains the objects to synchronize.
    TargetThe data source that receives the synchronized objects.
    Target DBSelect the target database to which data is synchronized.
    Target Object NameSelect the case conversion rule for object names after they are migrated from the source to the target.
    • Convert all to Lowercase: Regardless of the naming rule on the source, all target names are lowercase.
    • Consistent with Source: Keep the naming rule of the source.
    • Convert all to Uppercase: Regardless of the naming rule on the source, all target names are uppercase.
    TypeSelect the replication type.
    • Schema: Synchronize only the database and table schemas of the source data source, without synchronizing data.
    • Full: Synchronize all objects and data from the source data source, namely full data replication. The switch on the right enables periodic full replication. For more information, see Periodic Full Replication.
    • Incremental: After full synchronization completes, perform incremental synchronization based on the logs of the source data source.The setting icon is used to configure incremental operation types. Clear the operation types to exclude. Cleared operation types will be ignored during incremental synchronization.
    Incremental StartedRequired only when Type is Incremental.
    • From Started: Use the current replication task start time as the baseline for incremental replication.
    • Customized Time: Select the point in time from which incremental replication starts. Select a time zone based on the region of your business. If the configured time point is earlier than the current replication task start time and DDL operations occurred during that period, the replication task will fail.
    SpecThe specification of the replication task. A larger specification provides a higher replication rate. Hover over the details icon to view the rate and configuration information of each specification. If you configure the data replication task before purchasing resources, you can select the required specification here. If you purchase resources before configuring the task, NineData selects the specification specified during purchase, and you cannot change it in the task configuration.
    If target table already exists (Required when Schema is selected)
    • Pre-Check Error and Stop Task: Stop the task when a table with the same name is detected during the precheck stage.
    • Skip and Continue Task: When a table with the same name is detected during the precheck stage, display a message and continue the task. During schema replication, ignore the table with the same name. If you also perform data replication, data is appended to the table with the same name and existing data is not overwritten.
    • Delete Objects and Rewrite: When a table with the same name is detected during the precheck stage, display a message and continue the task. During schema replication, delete the table with the same name in the target database and replicate the table schema again based on the source database. If you also perform data replication, data is written after schema replication completes.
    Target Table Exists Data (Required when Full is selected)
    • Pre-Check Error and Stop Task: Stop the task when data is detected in the target table during the precheck stage.
    • Ignore existing target data and append to it.: When data is detected in the target table during the precheck stage, ignore that data and append other data.
    • Clear target existing data before write: When data is detected in the target table during the precheck stage, delete that data and write it again.
    Incremental data conflict handling strategy for target table (Required when Incremental is selected)
    • Runtime error: During incremental replication, report an error when target data already exists and wait for manual intervention.
    • Do not update target data: During incremental replication, do not write data when target data already exists, and continue subsequent tasks.
    • Update target data: During incremental replication, overwrite the target data when target data already exists.
  5. On the Objects tab, configure the parameters in the table, and click Next.

    Parameter
    Description
    Replication ObjectsSelect the content to replicate. Select All Objects to replicate all content in the source database, or select Customized Object, select the content to replicate in the Source Object list, and click > to add it to the Target Object list on the right.

    To create multiple replication tasks with the same replication objects, import a configuration file. Click Import Config, click Download Template to download the template, edit the file, and then click Upload to upload it and import the objects in bulk. For field descriptions and examples, see Import Replication Object Templates. The configuration file uses these fields:

    Parameter
    DescriptionExample
    source_table_nameThe exact source table name of the object to synchronize.example_tbl_name
    target_table_nameThe target table name that receives the synchronized object.example_tbl_name
    source_database_nameThe source database name of the object to synchronize.test_db
    target_database_nameThe target database name that receives the synchronized object.test_db_bak
    column_listThe list of columns to synchronize.["col1", "clo2", "col3"]
    extra_configurationA JSON string containing additional configuration. Use column_rules for column mappings and value rules: column_name is the source column, target_column_name is the target column, target_column_type is the target column type, and column_value is the column value. Use filter_condition for row-level filtering.{"extra_config": {"column_rules": [...]}}
    tip

    The following JSON represents the two example rows in the downloaded Excel template. When uploading the file, keep the Excel column names and enter the extra_configuration value as a JSON string. This code block is not a JSON file that you can upload directly.

    [
    {
    "source_table_name": "example_tbl_name",
    "target_table_name": "example_tbl_name",
    "source_database_name": "test_db",
    "target_database_name": "test_db_bak",
    "column_list": ["col1", "clo2", "col3"],
    "extra_configuration": {
    "extra_config": {
    "column_rules": [
    {
    "column_name": "example_column_name",
    "target_column_name": "example_target_column_name",
    "target_column_type": "int",
    "column_value": "current_timestamp()"
    },
    {
    "column_name": "example_column_name",
    "target_column_name": "example_target_column_name",
    "target_column_type": "int"
    }
    ],
    "filter_condition": "example_condition_expression"
    }
    }
    },
    {
    "source_table_name": "example_order_tbl",
    "target_table_name": "example_order_tbl",
    "source_database_name": "test_db",
    "target_database_name": "test_db_bak",
    "column_list": ["col1", "clo2", "col3"],
    "extra_configuration": {
    "extra_config": {
    "column_rules": [
    {
    "column_name": "example_column_name",
    "target_column_name": "example_target_column_name",
    "target_column_type": "int",
    "column_value": "current_timestamp()"
    },
    {
    "column_name": "example_column_name",
    "target_column_name": "example_target_column_name",
    "target_column_type": "int"
    }
    ],
    "filter_condition": "example_condition_expression"
    }
    }
    }
    ]
  6. On the Mapping tab, configure the mapping that matches the selected replication type, then click Save and Pre-Check. If source or target metadata changes while you configure mappings, click Refresh Metadata to refresh the metadata.

    • Includes Schema: Configure the table name after synchronization to the target data source.

    • Does not include Schema: NineData selects the database with the same name in the target data source by default. If no such database exists, select the target database manually. The table names and column names in the target database must match the synchronization objects. If they do not match, map the table names and column names manually.

    Other available actions:

    • Click Mapping & Filtering to customize the column names after synchronization to the target data source.
    • On the Mapping & Filtering page, enter a comparison expression in the text box below Data Filter as the filtering condition. Only data that meets the filtering conditions is synchronized to the target data source. For example, if the filtering condition is set to emp_no>=10005, data whose emp_no column value is less than 10005 is not synchronized to the target data source.
    • Click the replace_tablename icon to the right of "Target Table" to search for a table name and replace it with the target name.
    • Enter a table name in the Search Table text box to quickly locate the target table.
    • Click Batch Configuration to define common rules in batches, such as table name and column name case conversion, prefix or suffix addition, and replacement. Use this option to apply mapping configuration to many tables and columns at the same time.
  • Click the drop-down menu to the right of Object Owner to specify the owner of the object. The available options and default value depend on what the current page displays.
  • Click Set Tablespace on the page to specify the Tablespace for tables and indexes in the target data source. This controls the physical storage location of objects in the target database. If you do not configure it, the default Tablespace of the target database user is used.
  1. On the Pre-check tab, wait for the precheck to finish. After it passes, open the saved task's details from the replication task list and start the task.

    • Select Enable data consistency comparison. Based on the selected Type, the system automatically starts a data consistency comparison based on the source data source at the corresponding time. The comparison checks whether the source and target data are consistent. The start time is as follows:

      • Schema: Starts after schema replication completes.
      • Schema+Full: Starts after full replication completes.
      • Full: Starts after full replication completes.
      • Schema+Full+Incremental, Incremental: Starts when incremental data is consistent with the source data source for the first time and Delay is 0 seconds. Click View Details to view synchronization delay on the Details page. sync_delay
    • If the precheck fails, click Details in the Actions column for the failed check item, review the cause, fix the issue, and then click Check Again to run the precheck again until it passes.

    • Items with Warning in Result can be fixed or ignored if required.

  2. After the replication task starts, you can perform the following actions:

    • Click View Details to view the execution status of each stage of the synchronization task.
    • Click Back to list to return to the Replication task list page.

Modify incremental synchronization positions​

For a task whose replication type includes Incremental and whose task details provide position management, you can view and modify synchronization positions. The available position types and input format depend on the data source and replication link. Follow the options displayed for the current task. If a position is shown as - or no position type is available for selection, the current task has no position available for modification.

This feature is intended for specific operations and maintenance scenarios where you need to specify a new log-read position or synchronization-write position for an incremental task. The read position controls where the task continues reading from the source, while the write position controls where it continues processing synchronized writes. After you submit the change, the task pauses and restarts so that the new position can take effect.

  1. In the replication task list, click the Task ID of the target task to open its details.

  2. On the Incremental tab, click Synchronous site management to view the current read site and write site.

  3. Click modify site. Under site type, select the read site or write site to adjust, and then enter the value for Adjustment point according to the position type displayed for the task.

  4. Confirm the value and scope of impact, and click Verify and submit. The task enters a pause and relaunch process. After the task resumes running, open Synchronous site management again to confirm that the position has been updated.

danger

Modifying the synchronization point will adjust the point of log reading or synchronization writing, which may cause data loss, so operate with caution! Modifying either position may cause data loss or inconsistency between the source and target. Perform this operation only in an authorized test environment or during a maintenance window with a backup and rollback plan. A task whose incremental replication has finished cannot have its position reset.

Data Type Mapping​

The table applies to the listed source type declarations and parameter combinations from PostgreSQL 15.7 to PolarDB for PostgreSQL 14.18.37.0 (Oracle-compatible mode). Target types and catalog attributes correspond to the destination column metadata after replication. The table contains 124 entries: 44 core and parameterized types, 32 special types, 12 range and multirange types, 10 additional aliases and unbounded types, and 26 additional declarations and system catalog types. Non-NULL values were copied for the special-type and range batches; the other scalar mappings describe column types only and do not establish serial defaults or conversion of every value. Separate tables below list PostgreSQL 15.2 core types, additional built-in scalar and system types, built-in arrays, user-defined type examples, and manually repaired vector mappings; they are not merged with the 15.7 batch.

The additional types, system catalog types, and 17 supplemental declarations came from empty test tables. These results confirm only target column types and catalog attributes; they do not verify serial sequence/default behavior or value conversion for these empty-table batches.

The table above applies only to the listed PostgreSQL 15.7 source types; the following core-type and array tables record separate PostgreSQL 15.2 replication batches. Do not infer behavior for unlisted internal types, extensions, or user-defined types, or for other parameters.

The PostgreSQL 15 documentation states that pseudo-types cannot be used as table columns or as components of container types. Therefore, this mapping does not list replication mappings for pseudo-types. See Pseudo-Types and The PostgreSQL Type System.

The source type inventory follows the PostgreSQL 15 data types, array types, range types, object identifier types, pg_type system catalog, and the pg_index field definitions for int2vector and oidvector.

PostgreSQL source typePolarDB target column typeTarget catalog attributes
smallintsmallint (int2)precision 16, scale 0
int2smallint (int2)precision 16, scale 0
integerinteger (int4)precision 32, scale 0
intinteger (int4)precision 32, scale 0
int4integer (int4)precision 32, scale 0
bigintbigint (int8)precision 64, scale 0
int8bigint (int8)precision 64, scale 0
bigserialbigint (int8)precision 64, scale 0
serial8bigint (int8)precision 64, scale 0
smallserialsmallint (int2)precision 16, scale 0
serial2smallint (int2)precision 16, scale 0
serialinteger (int4)precision 32, scale 0
serial4integer (int4)precision 32, scale 0
numericnumericNo precision or scale reported
decimalnumericNo precision or scale reported
numeric(12,4)numericprecision 12, scale 4
decimal(12,4)numericprecision 12, scale 4
numeric(20,5)numericprecision 20, scale 5
decimal(20,5)numericprecision 20, scale 5
realreal (float4)precision 24
float4real (float4)precision 24
float(24)real (float4)precision 24
double precisiondouble precision (float8)precision 53
floatdouble precision (float8)precision 53
float8double precision (float8)precision 53
float(25)double precision (float8)precision 53
booleanboolean (bool)—
boolboolean (bool)—
character(3)character (bpchar)length 3
char(3)character (bpchar)length 3
character(8)character (bpchar)length 8
char(8)character (bpchar)length 8
charactercharacter (bpchar)length 1
character varying(20)character varying (varchar)length 20
varchar(20)character varying (varchar)length 20
character varying(32)character varying (varchar)length 32
varchar(32)character varying (varchar)length 32
character varyingtextNo maximum length reported
bpchartextNo maximum length reported
texttext—
byteabytea—
datedate—
timetime without time zone (time)datetime precision 6
time(3) without time zonetime without time zone (time)datetime precision 3
time(3) with time zonetime with time zone (timetz)datetime precision 3
timetztime with time zone (timetz)datetime precision 6
timestamp without time zonetimestamp without time zone (timestamp)datetime precision 6
timestamp(3) without time zonetimestamp without time zone (timestamp)datetime precision 3
timestamp(3) with time zonetimestamp with time zone (timestamptz)datetime precision 6 (source declaration precision: 3)
timestamptztimestamp with time zone (timestamptz)datetime precision 6
timestamptz(3)timestamp with time zone (timestamptz)datetime precision 6
intervalintervalThe catalog does not report precision or scale
interval(3)intervaldatetime precision 6
interval year to monthintervalThe catalog reports the year to month field qualifier; no precision or scale is reported
interval day to second(3)intervalThe catalog reports the day to second field qualifier; no precision or scale is reported
bit(4)bitlength 4
bit(8)bitlength 8
bitbitlength 1
bit varying(8)bit varying (varbit)length 8
varbit(8)bit varying (varbit)length 8
bit varyingbit varying (varbit)No maximum length reported
varbitbit varying (varbit)No maximum length reported
varchartextNo maximum length reported
uuiduuid—
jsonjson—
jsonbjsonb—
jsonpathjsonpath—
xmlxml—
moneymoney—
pointpoint—
lineline—
lseglseg—
boxbox—
pathpath—
polygonpolygon—
circlecircle—
cidrcidr—
inetinet—
macaddrmacaddr—
macaddr8character varying (varchar)length 255
namename—
"char"character (bpchar)length 1
tsquerytsquery—
tsvectortsvector—
pg_lsnpg_lsn—
pg_snapshotpg_snapshot—
txid_snapshottxid_snapshot—
oidoid—
regclassregclass—
regcollationregcollation—
regconfigregconfig—
regdictionaryregdictionary—
regnamespaceregnamespace—
regoperregoper—
regoperatorregoperator—
regprocregproc—
regprocedureregprocedure—
regroleregrole—
regtyperegtype—
xidxid—
xid8xid8—
tidtid—
refcursorrefcursor—
int4rangeint4range—
int8rangeint8range—
numrangenumrange—
tsrangetsrange—
tstzrangetstzrange—
daterangedaterange—
int4multirangeint4multirange—
int8multirangeint8multirange—
nummultirangenummultirange—
tsmultirangetsmultirange—
tstzmultirangetstzmultirange—
datemultirangedatemultirange—
aclitemaclitem (aclitem)No length or precision reported
cidcid (cid)No length or precision reported
gtsvectorgtsvector (gtsvector)No length or precision reported
pg_brin_bloom_summarypg_brin_bloom_summary (pg_brin_bloom_summary)No length or precision reported
pg_brin_minmax_multi_summarypg_brin_minmax_multi_summary (pg_brin_minmax_multi_summary)No length or precision reported
pg_dependenciespg_dependencies (pg_dependencies)No length or precision reported
pg_mcv_listpg_mcv_list (pg_mcv_list)No length or precision reported
pg_ndistinctpg_ndistinct (pg_ndistinct)No length or precision reported
pg_node_treepg_node_tree (pg_node_tree)No length or precision reported

PostgreSQL 15.2 Core Scalar and Parameterized Types​

This table lists 52 tested source type declarations and parameter combinations from PostgreSQL 15.2 to PolarDB for PostgreSQL 14.18.37.0 (Oracle-compatible mode). It does not extend to other PostgreSQL versions or unlisted declarations.

For this version combination, time-zone timestamp values are displayed as UTC at the target while representing the same instants; the JSON representation of date includes midnight; interval formatting is normalized; JSONB whitespace may differ while the parsed value matches; and insignificant trailing zeros are not retained for numeric. When the XML column value is NULL, only its column type is established; conversion of non-NULL XML values is outside the scope of this table. These value-representation boundaries are separate from the column type mapping.

PostgreSQL 15.2 source declarationPolarDB Oracle target catalog typeTarget catalog attributes
smallintsmallint (int2)precision 16, scale 0
int2smallint (int2)precision 16, scale 0
integerinteger (int4)precision 32, scale 0
intinteger (int4)precision 32, scale 0
int4integer (int4)precision 32, scale 0
bigintbigint (int8)precision 64, scale 0
int8bigint (int8)precision 64, scale 0
numericnumericNo precision or scale reported
decimalnumericNo precision or scale reported
numeric(12,4)numericprecision 12, scale 4
numeric(20,6)numericprecision 20, scale 6
realreal (float4)precision 24
float4real (float4)precision 24
float(24)real (float4)precision 24
double precisiondouble precision (float8)precision 53
floatdouble precision (float8)precision 53
float8double precision (float8)precision 53
float(25)double precision (float8)precision 53
booleanboolean (bool)—
boolboolean (bool)—
character(3)character (bpchar)length 3
char(3)character (bpchar)length 3
character(8)character (bpchar)length 8
character varying(20)character varying (varchar)length 20
varchar(20)character varying (varchar)length 20
character varying(32)character varying (varchar)length 32
texttextoctet length 1073741824
byteabytea—
datedate—
time(3) without time zonetime without time zone (time)datetime precision 3
time(3) with time zonetime with time zone (timetz)datetime precision 3
time without time zonetime without time zone (time)datetime precision 6
time with time zonetime with time zone (timetz)datetime precision 6
timestamp(3) without time zonetimestamp without time zone (timestamp)datetime precision 3
timestamp(3) with time zonetimestamp with time zone (timestamptz)datetime precision 6 (source declaration precision: 3)
timestamptz(3)timestamp with time zone (timestamptz)datetime precision 6 (source declaration precision: 3)
timestamp without time zonetimestamp without time zone (timestamp)datetime precision 6
timestamp with time zonetimestamp with time zone (timestamptz)datetime precision 6
interval(3)intervaldatetime precision 6
interval year to monthintervaldatetime precision 6; field qualifier not exposed by the target catalog query
interval day to second(3)intervaldatetime precision 6; field qualifier not exposed by the target catalog query
bit(4)bitlength 4
bit(8)bitlength 8
bit varying(8)bit varying (varbit)length 8
varbit(8)bit varying (varbit)length 8
bit varying(16)bit varying (varbit)length 16
uuiduuid—
jsonjson—
jsonbjsonb—
jsonpathjsonpath—
xmlxml—
moneymoney—

PostgreSQL 15.2 Additional Built-In Scalar and System Types​

The table lists the target column types and catalog attributes for 62 source type declarations from PostgreSQL 15.2 to PolarDB for PostgreSQL 14.18.37.0 (Oracle-compatible mode). An em dash means the catalog did not report a length, precision, or scale. This table describes column type mapping only; it does not establish conversion of non-NULL values or sequence/default behavior for serial types.

Independent replications for the same PostgreSQL 15.2 and PolarDB for PostgreSQL 14.18.37.0 (Oracle-compatible mode) version combination produced different target types for six geometric scalars: one task returned same-named target types for box, circle, lseg, path, point, and polygon; two other tasks returned text for all six. line was returned as line in all three tasks. The table below records the tasks that returned text. The target catalog results for six geometric arrays also differed; the cause has not been confirmed. For an actual task, check that task’s generated target DDL and destination column catalog. This mapping describes column types only, not conversion of non-NULL values.

PostgreSQL 15.2 source declarationPolarDB Oracle target catalog typeTarget catalog attributes
bigserialbigint (int8)precision 64, scale 0
serial8bigint (int8)precision 64, scale 0
smallserialsmallint (int2)precision 16, scale 0
serial2smallint (int2)precision 16, scale 0
serialinteger (int4)precision 32, scale 0
serial4integer (int4)precision 32, scale 0
boxtextoctet length 1073741824
circletextoctet length 1073741824
lineline—
lsegtextoctet length 1073741824
pathtextoctet length 1073741824
pointtextoctet length 1073741824
polygontextoctet length 1073741824
bpchartextcharacter length unreported; octet length 1073741824
"char"character (bpchar)character length 1; octet length 4
namename—
cidrcidr—
inetinet—
macaddrmacaddr—
macaddr8character varying (varchar)character length 255; octet length 1020
tsquerytsquery—
tsvectortsvector—
pg_lsnpg_lsn—
pg_snapshotpg_snapshot—
txid_snapshottxid_snapshot—
oidoid—
regclassregclass—
regcollationregcollation—
regconfigregconfig—
regdictionaryregdictionary—
regnamespaceregnamespace—
regoperregoper—
regoperatorregoperator—
regprocregproc—
regprocedureregprocedure—
regroleregrole—
regtyperegtype—
xidxid—
xid8xid8—
cidcid—
tidtid—
int4rangeint4range—
int8rangeint8range—
numrangenumrange—
tsrangetsrange—
tstzrangetstzrange—
daterangedaterange—
int4multirangeint4multirange—
int8multirangeint8multirange—
nummultirangenummultirange—
tsmultirangetsmultirange—
tstzmultirangetstzmultirange—
datemultirangedatemultirange—
gtsvectorgtsvector—
refcursorrefcursor—
aclitemaclitem—
pg_brin_bloom_summarypg_brin_bloom_summary—
pg_brin_minmax_multi_summarypg_brin_minmax_multi_summary—
pg_dependenciespg_dependencies—
pg_mcv_listpg_mcv_list—
pg_ndistinctpg_ndistinct—
pg_node_treepg_node_tree—

PostgreSQL 15.2 User-Defined Types and Arrays (Manually Repaired Path)​

The results below apply only to the specific type definitions listed. The automatically generated structure DDL did not complete when it used USER-DEFINED declarations for the enum, composite, and range columns. Structure and full replication completed after corresponding target type definitions were present and those three declarations were changed to reference the target types. The domain scalar column mapped to its base type, integer, and did not use the custom domain type. Custom-type arrays had target catalog type ARRAY, with udt_name identifying the corresponding target-side array type. This table records the manually repaired mapping and does not represent the default automatic DDL path.

PostgreSQL 15.2 source type definitionPolarDB Oracle target column catalog typeTarget catalog attributes
Enum type with two labelsUSER-DEFINEDTarget catalog type category: enum (typtype=e)
Integer domain with a positive-value check constraintinteger (int4)Column uses the base type; the custom domain and its constraint are not applied to this target column
Composite type with integer and text fieldsUSER-DEFINEDTarget catalog type category: composite (typtype=c)
Range type with integer subtypeUSER-DEFINEDTarget catalog type category: range (typtype=r)
Enum type arrayARRAYudt_name identifies the corresponding target enum-array type
Integer domain arrayARRAYudt_name identifies the corresponding target domain-array type
Composite type arrayARRAYudt_name identifies the corresponding target composite-array type
Integer range type arrayARRAYudt_name identifies the corresponding target range-array type

PostgreSQL 15.2 Built-in Array Types​

The table lists destination catalog types and udt_name for PostgreSQL 15.2 arrays copied to the same target version. In the task recorded here, box[], circle[], lseg[], path[], point[], and polygon[] all returned _text; an earlier independent task returned _box, _circle, _lseg, _path, _point, and _polygon, respectively. The cause of the difference between the two tasks has not been confirmed. Check the generated target DDL and destination column catalog for the actual task. Array values in the recorded batches were NULL, so these rows confirm column types only and do not establish conversion of non-NULL or empty arrays. Structure replication for interval[] required a correction to the generated target DDL.

PostgreSQL 15.2 source array typePolarDB target column typeTarget catalog udt_name
aclitem[]ARRAY_aclitem
bigint[]ARRAY_int8
bit varying[]ARRAY_varbit
bit(1)[]ARRAY (bit(1)[])_bit
boolean[]ARRAY_bool
box[]ARRAY_text
bytea[]ARRAY_bytea
"char"[]ARRAY (character(1)[])_bpchar
character varying[]ARRAY_varchar
character(1)[]ARRAY (character(1)[])_bpchar
cid[]ARRAY_cid
cidr[]ARRAY_cidr
circle[]ARRAY_text
date[]ARRAY_date
datemultirange[]ARRAY_datemultirange
daterange[]ARRAY_daterange
double precision[]ARRAY_float8
gtsvector[]ARRAY_gtsvector
inet[]ARRAY_inet
int2vector[]ARRAY_int2vector
int4multirange[]ARRAY_int4multirange
int4range[]ARRAY_int4range
int8multirange[]ARRAY_int8multirange
int8range[]ARRAY_int8range
integer[]ARRAY_int4
interval[]ARRAY_interval
json[]ARRAY_json
jsonb[]ARRAY_jsonb
jsonpath[]ARRAY_jsonpath
line[]ARRAY_line
lseg[]ARRAY_text
macaddr[]ARRAY_macaddr
macaddr8[]ARRAY_varchar
money[]ARRAY_money
name[]ARRAY_name
numeric[]ARRAY_numeric
nummultirange[]ARRAY_nummultirange
numrange[]ARRAY_numrange
oid[]ARRAY_oid
oidvector[]ARRAY_oidvector
path[]ARRAY_text
pg_lsn[]ARRAY_pg_lsn
pg_snapshot[]ARRAY_pg_snapshot
point[]ARRAY_text
polygon[]ARRAY_text
real[]ARRAY_float4
refcursor[]ARRAY_refcursor
regclass[]ARRAY_regclass
regcollation[]ARRAY_regcollation
regconfig[]ARRAY_regconfig
regdictionary[]ARRAY_regdictionary
regnamespace[]ARRAY_regnamespace
regoper[]ARRAY_regoper
regoperator[]ARRAY_regoperator
regproc[]ARRAY_regproc
regprocedure[]ARRAY_regprocedure
regrole[]ARRAY_regrole
regtype[]ARRAY_regtype
smallint[]ARRAY_int2
text[]ARRAY_text
tid[]ARRAY_tid
time with time zone[]ARRAY_timetz
time without time zone[]ARRAY_time
timestamp with time zone[]ARRAY_timestamptz
timestamp without time zone[]ARRAY_timestamp
tsmultirange[]ARRAY_tsmultirange
tsquery[]ARRAY_tsquery
tsrange[]ARRAY_tsrange
tstzmultirange[]ARRAY_tstzmultirange
tstzrange[]ARRAY_tstzrange
tsvector[]ARRAY_tsvector
txid_snapshot[]ARRAY_txid_snapshot
uuid[]ARRAY_uuid
xid[]ARRAY_xid
xid8[]ARRAY_xid8
xml[]ARRAY_xml

PostgreSQL 15.2 Parameterized Array Columns​

The following destination catalog results are from the existing PostgreSQL 15.2 to PolarDB PostgreSQL 14.18.37.0 (Oracle-compatible mode) replication task. The target data_type is ARRAY for every column; format_type shows which type modifiers were retained at the target. The target reports interval day to second(3)[] as interval[], without the source range or precision qualifier. The source table was empty and the full-copy task completed with 0 records, so this confirms destination column types only, not array-value conversion.

PostgreSQL 15.2 source array typeTarget catalog data_typeTarget catalog udt_nameTarget catalog format_type
numeric(20,6)[]ARRAY_numericnumeric(20,6)[]
bit(8)[]ARRAY_bitbit(8)[]
character(8)[]ARRAY_bpcharcharacter(8)[]
time(6) without time zone[]ARRAY_timetime(6) without time zone[]
time(6) with time zone[]ARRAY_timetztime(6) with time zone[]
timestamp(6) without time zone[]ARRAY_timestamptimestamp(6) without time zone[]
timestamp(6) with time zone[]ARRAY_timestamptztimestamp(6) with time zone[]
interval day to second(3)[]ARRAY_intervalinterval[]

PostgreSQL 15.2 Vector Types (Manually Repaired DDL)​

For schema and full replication of int2vector, oidvector, and their array columns, the mapping configuration displayed these source columns as ARRAY. The generated target DDL failed because the bare ARRAY type was rejected. After the four vector-related target columns were changed to VARCHAR2(4000) in the task's repair dialog, schema and full replication completed. The destination catalog reported VARCHAR with data_length 4000. The table below records the manually repaired target types, not the default generated mapping.

The scalar sample values were read back unchanged at the target. The source values for both array columns were NULL, so this confirms only their destination column types, not conversion of non-NULL or empty array values. This was a separate PostgreSQL 15.2 batch from the ARRAY catalog results in the array table above.

PostgreSQL 15.2 source typeSource catalog udt_nameSource sample valuePolarDB target catalog type after manual repairTarget sample value
int2vectorint2vector1 2 3VARCHAR (data_length=4000)1 2 3
oidvectoroidvector10 20 30VARCHAR (data_length=4000)10 20 30
int2vector[]_int2vectorNULLVARCHAR (data_length=4000)NULL
oidvector[]_oidvectorNULLVARCHAR (data_length=4000)NULL

Appendix: Precheck items​

Check itemWhat NineData checks
"Source datasource connection check"Gateway status, instance reachability, username, and password for the source data source.
Target datasource connection checkGateway status, instance reachability, username, and password for the target data source.
Source database privilege checkWhether the source database account has the required privileges.
Cyclic replication checkWhether a replication loop exists.

Data Replication overview