Homogeneous replication
The following section describes how Data Movement gateway handles replication from PostgreSQL to PostgreSQL-based targets. Note that homogeneous replication is only relevant with Replication data projects.
In homogeneous replication, source column and table collations will be replicated to the target as described in Column and table collation.
Limitations and considerations
Homogeneous replication is not supported with the following Data Movement gateway configurations or operations:
- Apply Changes only tasks
- Stopping and resuming a task
- Starting a task from a timestamp
- When the If target table already exists in the task settings' Full Load Settings tab is set to Do nothing
Data type considerations and exceptions
When replicating from PostgreSQL to a PostgreSQL-based target, source and target data types are identical for all supported PostgreSQL versions, with the following exceptions:
| PostgreSQL source | PostgreSQL target |
|---|---|
|
ENUM |
STRING |
|
COMPOSITE |
STRING |
|
NUMERIC |
NUMERIC (28,6) |
|
NUMERIC (p,s) A numeric array with precision and scale. |
NUMERIC A numeric array without precision and scale. |
| TINTERVAL |
Information note
As PostgreSQL have discontinued support for the TINTERVAL data type, tables with this data type will not be created on the target if the target database version does not support it. |
| INT4MULTIRANGE | STRING (255) |
| INT8MULTIRANGE | STRING (255) |
| NUMMULTIRANGE | STRING (255) |
| TSMULTIRANGE | STRING (255) |
See also the note about JSON data type replication in Supported data types.
Column and table collation
When replicating from PostgreSQL to a PostgreSQL-based target, column and table collations will be replicated to the target.
To support collation replication, the DBA must ensure that the collations defined for the source PostgreSQL database are the same as those defined for the PostgreSQL-based target database.
Non-nullable columns and primary/unique index names
Non-nullable columns and Primary/Unique Index names are preserved during homogeneous replication.
Replication of Partitioned Tables
In order to replicate partitioned tables from PostgreSQL to a PostgreSQL-based target, you need to do the following:
- Manually create the parent and child tables in the target database.
- Define a separate task to replicate to those tables.
- Set the Truncate before loading option in the Full load.