PostgreSQL: Supported Data Types, Objects, and Operations
Oracle GoldenGate for PostgreSQL supports capture and delivery of initial load and transactional data for supported PostgreSQL database versions.
Oracle GoldenGate for PostgreSQL supports the mapping, filtering, and transformation of source data, unless noted otherwise in this document, as well as replicating data derived from other source databases supported by Oracle GoldenGate, into PostgreSQL databases.
Supported Databases
-
Only user databases are supported for capture and delivery.
-
Oracle GoldenGate does not support capture from archived logs.
-
Capture and delivery are not supported against replica, standby databases.
-
High Availability:
-
Oracle GoldenGate Extract does not support seamless role transitioning from a primary to a secondary Extract with PostgreSQL high availability configurations. However, manual procedural operations could be followed to provide continuity from the new primary Extract.
-
For more information, see the details available in the Oracle Support note, Oracle GoldenGate Procedures for PostgreSQL HA Failover (Doc ID 2818379.1).
-
Supported PostgreSQL Data Types
Here's a list of PostgreSQL data types that Oracle GoldenGate supports along with the limitations of this support.
-
array
-
bigint
-
bigserial
-
bit(n)
-
bit varying(n)
-
boolean
-
bytea
-
char (n)
-
cidr
-
citext
-
date
-
decimal
-
double precision
-
Enumerated Types
-
inet
-
integer
-
interval
-
json
-
jsonb
-
macaddr
-
macaddr8
-
money
-
numeric
-
pgvector extension
-
real
-
serial
-
smallint
-
smallserial
-
text
-
time
with/without timezone -
timestamp
with/without timezone -
tsquery
-
tsvector
-
uuid
-
varchar(n)
-
varbit
-
xml
Handling Array Data Type
PostgreSQL supports array
of various built-in and UDT
types. Starting with Oracle GoldenGate release 23ai, Oracle GoldenGate Extract and
Replicat support PostgreSQL array
data type of following types:
-
boolean
-
bigint
-
bit
-
char
-
date
-
double precision
-
enum
-
int
-
interval
-
numeric
-
money
-
real
-
smallint
-
timestamp
,timestampz
,time
,timetz
-
varbit
-
varchar
-
text
-
tsvector
-
tsquery
Both initial load and CDC Extract support these array types.
Note:
Initial load Extract does not support PostgreSQL
array
of time
datatype.
Limitations of Support
-
If columns of
char
,varchar
,text
, orbytea
data types are part of a primary or unique key, then the maximum individual lengths for these columns must not exceed 8191 bytes. -
Columns of data type
CITEXT
that are part of the Primary Key are supported up to 8000 bytes in size.CITEXT
columns that are greater than 8000 bytes and are part of the Primary Key are not supported. -
Extract cannot process records with
bytea
columns of more than 512 MB in size. -
real
,double
,numeric
,decimal: NaN
input values are not supported. -
The following limitations apply to
bit
/varbit
data types:-
They are supported up to 4k in length. For lengths greater than 4k the data is truncated and only the lower 4k bits are captured.
-
The source bit(n) column can be applied only onto a character type column on a non-PostgreSQL target and can be applied onto a
char
type or abit/varbit
column on PostgreSQL target.
-
-
The following limitations are applicable to both
timestamp
with time zone andtimestamp
without time zone:-
The
timestamp
data with BC or AD tags in the data is not supported. -
The
timestamp
data older than 1883-11-18 12:00:00 is not supported. -
The
timestamp
data with more than 4 digits in the YEAR component is not supported. -
Infinity/-Infinity
input strings fortimestamp
columns are not supported.
-
-
The following are the limitations when using
interval
:-
The capture of mixed sign
interval
data frominterval
type columns is not supported. You can useDBOPTIONS
ALLOWNONSTANDARDINTERVALDATA
in the Extract parameter file to capture the mixed signinterval
data (or any other format ofinterval
data, which is not supported by Oracle GoldenGate) as a string (not as standardinterval
data).The following are a few examples of data that gets written to the trail file, on using the
DBOPTIONS
ALLOWNONSTANDARDINTERVALDATA
in the Extract param file:
-
+1026-9 +0 +0:0:22.000000
is interpreted as 1026 years, 9 months, 0 days, 0 hours, 0 minutes, 22 seconds. -
-0-0 -0 -8
is interpreted as 0 years, 0 months, 0 days, -8 hours. -
+1-3 +0 +3:20
is interpreted as 1 year, 3 months, 0 days, 3 hours, 20 minutes.
-
-
Replicat: If the source interval data was captured using
DBOPTIONS
ALLOWNONSTANDARDINTERVALDATA
and written as a string to the trail, the corresponding source column is allowed to be mapped to either achar
or abinary
type column on the target. -
date
limitations are:-
The
date
data with BC or AD tags in the data is not supported. -
Infinity
/-Infinity
input strings fordate
columns are not supported.
-
-
Columns of
text
,json
,xml
,bytea
,char (>8191)
,varchar (>8191)
are treated as LOB columns and have the following limitations:-
When using
GETUPDATEBEFORES
, the before image of LOB columns is never logged. -
When using
NOCOMPRESSUPDATES
,LOB
columns are logged in the after image only if they were modified.
-
-
The support of range and precision for floating-point numbers depends on the host machine. In general, the precision is accurate to 16 significant digits, but you should review the database documentation to determine the expected approximations. Oracle GoldenGate rounds or truncates values that exceed the supported precision.
Non-Supported PostgreSQL Data Types
-
box
-
circle
-
Composite Types
-
Domain Types
-
line
-
lseq
-
Object Identifiers Types
-
path
-
pg_lsn
-
pg_snapshot
-
point
-
polygon
-
Pseudo-Types
-
Range Types
-
User-defined Types (UDTs)
-
Extensions and Additional Supplied Modules listed at: https://www.postgresql.org/docs/current/contrib.html are not supported by Oracle GoldenGate unless explicitly listed under Supported PostgreSQL Data Types.
Note:
If the Extract parameter file contains a table with unsupported data types, the Extract will stop with an error message. To resume replication, remove the table from the Extract file or remove the column from the table with an unsupported data type.Supported Objects and Operations for PostgreSQL
-
Oracle GoldenGate for PostgreSQL only supports DML operations (Insert/Update/Deletes). DDL replication is not supported.
-
Oracle GoldenGate for PostgreSQL supports replication of truncate operations beginning with PostgreSQL 11 and above, and requires the
GETTRUNCATES
parameter in Extract and Replicat. -
Case-Sensitive/Insensitive names Usage:
-
Unquoted names are case-insensitive and are implicitly lowercase. For example,
CREATE TABLE MixedCaseTable
andSELECT * FROM mixedcasetable
are equivalent. -
Quoted table and column names are case-sensitive and need to be listed correctly in Extracts and Replicats and with Oracle GoldenGate commands.
For example,
TABLE appschema.”MixedCaseTable”
andADD TRANDATA appschema.”MixedCaseTable”
would be required to support a case-sensitive table name.
-
Tables and Views
Tables to be included for capture and delivery must meet the following requirements and must only include data types listed under Supported PostgreSQL Data Types.
-
Oracle GoldenGate for PostgreSQL supports capture of transactional DML from user tables, and delivery to user tables.
-
Oracle GoldenGate for PostgreSQL, versions 21.14.0.0.0 and after, supports capture and delivery to base partitioned tables.
-
Globalization is supported for object names (table /schema/database/column names) and column data.
Limitations:
-
Oracle GoldenGate for PostgreSQL does not support capture and delivery for views.
-
Oracle GoldenGate for PostgreSQL does not support capture from individual partitions of a partitioned table.
Sequences and Identity Columns
-
Sequences are supported on source and target tables for unidirectional, bidirectional, and multi- directional implementations.
-
Identity columns created using the
GENERATED BY DEFAULT AS IDENTITY
clause, are supported on source and target tables, for unidirectional, bidirectional, and multi- directional implementations. -
Identity columns created using the
GENERATED ALWAYS AS IDENTITY
clause, are not supported in target database tables and the Identity property should be removed from target tables or changed toGENERATED BY DEFAULT AS IDENTITY
. -
For bidirectional and multi-directional implementations, define the Identity columns and sequences with an
INCREMENT BY
value equal to the number of servers in the configuration, with a differentMINVALUE
for each one.For example,
MINVALUE
/INCREMENT BY
values for a bidirectional, two-database configuration would be as follows:Database1, set the
MINVALUE
at 1 with anINCREMENT BY
of 2.Database2, set the
MINVALUE
at 2 with anINCREMENT BY
of 2.For example,
MINVALUE
/INCREMENT BY
values for a multi-directional, three-database configuration would be as follows:Database1, set the
MINVALUE
at 1 with anINCREMENT BY
of 3.Database2, set the
MINVALUE
at 2 with anINCREMENT BY
of 3.Database3, set the
MINVALUE
at 3 with anINCREMENT B
Y of 3.