1. Oracle GoldenGate Microservices Documentation
  2. Configure
  3. PostgreSQL
  4. PostgreSQL: Supported Data Types, Objects, and Operations

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.

Topics:

Parent topic: PostgreSQL

Supported Databases

The following are supported databases and limitations for Oracle GoldenGate for PostgreSQL:

  • 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).

Parent topic: PostgreSQL: Supported Data Types, Objects, and Operations

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

  • citext

  • date

  • decimal

  • double precision

  • inet

  • integer

  • interval

  • json

  • jsonb

  • 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, or bytea 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.

  • 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 a bit/varbit column on PostgreSQL target.

  • The following limitations are applicable to both timestamp with time zone and timestamp 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 for timestamp columns are not supported.

  • The following are the limitations when using interval:

    • The capture of mixed sign interval data from interval type columns is not supported. You can use DBOPTIONS ALLOWNONSTANDARDINTERVALDATA in the Extract parameter file to capture the mixed sign interval data (or any other format of interval data, which is not supported by Oracle GoldenGate) as a string (not as standard interval 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 a char or a binary 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 for date 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: PostgreSQL: Supported Data Types, Objects, and Operations

Non-Supported PostgreSQL Data Types

Oracle GoldenGate for PostgreSQL does not support the following data types:

  • Arrays

  • box

  • circle

  • Composite Types

  • Domain Types

  • Enumerated Types

  • line

  • lseq

  • Object Identifiers Types

  • path

  • pg_lsn

  • pg_snapshot

  • point

  • polygon

  • Pseudo-Types

  • Range Types

  • tsquery

  • tsvector

  • User-defined Types (UDTs)

  • Extensions and Additional Supplied Modules listed at: https://www.postgresql.org/docs/current/contrib.html

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.

Note:

If an Extension or Additional Supplied Module is supported, it will be explicitly added to the Supported PostgreSQL data types list.

Parent topic: PostgreSQL: Supported Data Types, Objects, and Operations

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 and SELECT * 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” and ADD TRANDATA appschema.”MixedCaseTable” would be required to support a case-sensitive table name.

Topics:

Parent topic: PostgreSQL: Supported Data Types, Objects, and Operations

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 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.

Parent topic: Supported Objects and Operations for PostgreSQL

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 to GENERATED 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 different MINVALUE 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 an INCREMENT BY of 2.

    Database2, set the MINVALUE at 2 with an INCREMENT 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 an INCREMENT BY of 3.

    Database2, set the MINVALUE at 2 with an INCREMENT BY of 3.

    Database3, set the MINVALUE at 3 with an INCREMENT BY of 3.

Parent topic: Supported Objects and Operations for PostgreSQL