Restrictions on replicated database objects v7

When you create a subscription in a single-master replication system, the table definitions and most database objects and attributes associated with the publication tables are created in the subscription database by the subscription server.

If you choose, the same process can automatically occur when a primary node is added to a multi-master replication system. You can create the table definitions and most database objects and attributes associated with the publication tables in the newly added primary node by the publication server.

The following is a list of database objects and table attributes that are replicated from the publication in either a single-master or multi-master replication system.

  • Tables
  • Views (for snapshot-only publications) created as tables in the subscription database
  • Primary keys
  • Not null constraints
  • Unique constraints
  • Check constraints
  • Indexes
Note

Foreign key constraints aren't replicated by the publication or subscription server in a single-master replication system. However, in a multi-master replication system, foreign key constraints are replicated from the primary definition node to other primary nodes.

Note

Sequences (database objects created by the CREATE SEQUENCE statement) aren't replicated from the publication database to the subscription databases in a single-master replication system. Sequences also aren't replicated from the primary definition node to other primary nodes in a multi-master replication system.

Replication Server does have some restrictions on the types of tables it can replicate.

Restrictions on Oracle database objects

The following are the restrictions on Oracle database objects:

  • You can replicate certain types of Oracle partitioned tables. See Replicating Oracle partitioned tables for details.

  • You can't replicate Oracle global temporary tables.

  • You can use Oracle tables with the RAW data type in snapshot-only publications but not in synchronization replications.

  • You can't replicate Oracle tables that include the following data types:

    • BFILE
    • BINARY_DOUBLE
    • BINARY_FLOAT
    • MLSLABEL
    • LONG
    • LONG RAW
  • Replication Server 7.0 provides support for LOB column streaming from Oracle to PostgreSQL/EDB Postgres Advanced Server. However, you must create LOB columns to allow NULLs on the source Oracle and target PostreSQL/EDB Postgres Advanced Server database server. The previous version of Replication Server didn't stream the LOB columns. Rows of tables containing LOB columns were replicated during streaming, but LOB columns contained NULL. This limitation was removed.
Note

If you upgrade an existing Replication Server 6.x deployment to 7.0 and the published tables contain LOB columns, you must replublish all the tables with LOB columns to avoid data inconsistency.

  • Oracle provides different methods to insert and update LOB columns. Replication Server supports LOB streaming only if LOB columns are inserted, updated, and deleted using INSERT, UPDATE, and DELETE statements.

  • If you use the command line SQL*Plus tool, the maximum LOB size that you can insert or update is 4K. You can use Java program to insert large values, but you must use an INSERT or UPDATE statement.

  • If JDBC LOB APIs (provided by java.sql.Clob or java.sql.Blob classes) are used, triggers won't activate, and hence streaming for LOB columns won't work.

  • Similarly, if you use functions and procedures provided by the DBMS_LOB package, triggers won't activate, and streaming for LOB columns won't work.

Restrictions on SQL Server database pbjects

You can't replicate SQL Server tables that include the following data types:

  • GEOGRAPHY
  • GEOMETRY
  • SQL_VARIANT
Note

See Replicating the SQL Server SQL_VARIANT data type for a method to replicate tables containing the SQL_VARIANT data type under certain conditions.

You can use SQL Server tables with the following data types in snapshot-only publications but not in synchronization replications:

  • BINARY
  • IMAGE
  • NTEXT
  • NVARCHAR(max)
  • TEXT
  • TIMESTAMP
  • VARBINARY
  • VARBINARY(max)

Restrictions on Postgres database objects

For replicating Postgres partitioned tables, see Replicating Postgres partitioned tables for details. You can't replicate Postgres tables with the following data types in a column that's part of the identity columns:

  • BLOB
  • BYTEA
  • RAW

This restriction applies to primary key or unique columns. See Design considerations.

You can't replicate PostgreSQL or EDB Postgres Advanced Server database tables to the Oracle database that include the following data type:

  • JSONB
  • Geometry data types
  • tsvector, tsquery, txid_snapshot, pg_lsn
  • cidr, inet, mcaddress, mcaddress8, uuid, money
  • ENUM, ARRAY, range data types (such as int4range, tstzrange, numrange, daterange)
  • Any user-defined data type (that is, defined as CREATE TYPE type_name)

You can't replicate Postgres tables that include OID-based large objects. For information on OID-based large objects, see pg_largeobject in the PostgreSQL core documentation.

You can't replicate Postgres tables to an Oracle subscription database that include any geometric data types such as POINT and POLYGON.

You can't replicate Postgres tables to a SQL Server subscription database that include the following data types:

  • ABSTIME
  • ACLITEM
  • CHKPASS
  • Geometric data types (such as LINE, PATH, POLYGON)
  • CIRCLE
  • CUBE
  • JSON
  • JSONB
  • ROWID
  • tsvector, tsquery, txid_snapshot, pg_lsn
  • SEG
  • Any ARRAY data type (that is, defined as data_type[])
  • ENUM, composite type, range data types (such as int4range, tstzrange, numrange, daterange), mcaddress8
  • Any user-defined data type (that is, defined as CREATE TYPE <type_name>)

Restrictions on range data types

Postgres data types called range types were first supported in PostgreSQL version 9.2 and EDB Postgres Advanced Server version 9.2. Built-in range types refer to the following built-in data types: int4range, int8range, numrange, tsrange, tstzrange, and daterange.

You can include Postgres tables containing the built-in range types in the publication of a single-master or multi-master replication system. However, this results in the following restrictions on the subscription databases of a single-master replication system or the additional primary nodes of a multi-master replication system:

  • If a publication table of a single-master replication system contains any built-in range types, then a you can add a database as a subscription database only if the database server of the intended subscription database is Postgres version 9.2 or later.
  • If a publication table of the primary definition node in a multi-master replication system contains any built-in range types, then you can add a database as a primary node only if the database server of this intended primary node is Postgres version 9.2 or later.

Custom range types constructed with the CREATE TYPE AS RANGE command aren't supported in Replication Server.