15 Understanding What's Supported for PostgreSQL
This chapter contains information on supported features for Oracle GoldenGate for PostgreSQL:
Topics:
- Supported Databases
- Details of Supported PostgreSQL Data Types
- Supported Objects and Operations for ProtgreSQL
Parent topic: Using Oracle GoldenGate for PostgreSQL
15.1 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 my Oracle Support note, Oracle GoldenGate Procedures for PostgreSQL HA Failover (Doc ID 2818379.1).
-
Parent topic: Understanding What's Supported for PostgreSQL
15.2 Details of Supported PostgreSQL Data Types
This topic describes data types that are supported by Oracle GoldenGate, as well as those that are not supported.
Parent topic: Understanding What's Supported for PostgreSQL
15.2.1 Supported PostgreSQL Data Types
Here's a list of PostgreSQL data types that Oracle GoldenGate supports along with the limitations of this support.
-
bigint
-
bigserial
-
bit(n)
-
bit varying(n)
-
boolean
-
bytea
-
char (n)
-
cidr
-
date
-
decimal
-
double precision
-
inet
-
integer
-
interval
-
macaddr
-
macaddr8
-
money
-
numeric
-
real
-
serial
-
smallint
-
smallserial
-
text
-
time
with/without timezone -
timestamp
with/without timezone -
uuid
-
varchar(n)
-
varbit
-
xml
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. -
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.
Parent topic: Details of Supported PostgreSQL Data Types
15.2.2 Non-Supported PostgreSQL Data Types
-
Arrays
-
box
-
circle
-
citext
-
Composite Types
-
Domain Types
-
Enumerated Types
-
json
-
jsonb
-
line
-
lseq
-
Object Identifiers Types
-
path
-
pg_lsn
-
pg_snapshot
-
point
-
polygon
-
Pseudo-Types
-
Range Types
-
tsquery
-
tsvector
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.Parent topic: Details of Supported PostgreSQL Data Types
15.3 Supported Objects and Operations for ProtgreSQL
This topic describes objects and operations that are supported by Oracle GoldenGate.
-
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 includes:
-
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.
-
Parent topic: Understanding What's Supported for PostgreSQL
15.3.1 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 supports the capture of an explicit partition of a partitioned table, and the partitioned table name must be listed in Extract and enabled for
TRANDATA
. -
Oracle GoldenGate for PostgreSQL supports delivery to 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 partitioned tables if only the base table is listed in the Extract.
Parent topic: Supported Objects and Operations for ProtgreSQL
15.3.2 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.
Parent topic: Supported Objects and Operations for ProtgreSQL