Design considerations v7

Keep the following points in mind when designing a replication system:

• Multi-master replication is supported only on PostgreSQL databases. In addition, EDB Postgres Advanced Server databases must run in the same compatibility mode—either all Oracle or all PostgreSQL.

• An Oracle table can be a member of at most one publication if all publications are subordinate to one publication database definition. However, an Oracle table can be a member of multiple publications if each publication is subordinate to a different publication database definition.

• A Postgres table can be a member of at most one publication.

• Each non-PostgreSQL/EDB Postgres Advanced Server database table used in a publication must have a primary key, with the exception of tables in snapshot-only publications. These tables don't require a primary key.

• Each PostgreSQL/EDB Postgres Advanced Server database table used in a publication must have identity columns (primary key or unique columns), with the exception of tables in snapshot-only publications. These tables don't require identity columns. For example:

  Toggle WrapCopy

  CREATE TABLE dept_pk (
     deptno NUMERIC(2) PRIMARY KEY,
     dname VARCHAR(14)
     loc VARCHAR(13)
  );

  If a table doesn't have a primary key, then it should have a column with UNIQUE NOT NULL constraints. For example:

  Toggle WrapCopy

  CREATE TABLE dept_uk2 (
     deptno NUMERIC(2),
     dname VARCHAR(14) NOT NULL,
     loc VARCHAR(13) NOT NULL,
     CONSTRAINT dept_uk2_dname_loc_key UNIQUE (dname, loc)
  );

• Make sure table definitions are well established before creating publications. Unless you use the DDL change replication feature, as described in Replicating DDL changes, if a table definition changes, you must delete and re-create any publication containing the table along with its associated subscription. Otherwise replication might fail. The same applies for the table definitions in a primary definition node and its associated primary nodes. You can see replication failures in the replication history.

• Views can be members of snapshot-only publications. The views are replicated as tables in the subscription databases.

• Materialized views can be members of snapshot-only publications from Oracle to EDB Postgres Advanced Server databases. The materialized views are replicated as tables in the subscription databases. The following items provide additional information pertinent to performing snapshot-only replication of materialized views:

  1. Use the ‘tables’ parameter for specifying materialized views while creating a publication via EDB Replication Server CLI. While creating a snapshot-only publication via Replication Console, the view type currently appears as a table.

  2. We recommend that users create the views with auto-refresh in Oracle. If the views are not auto-refresh they are marked as invalid upon updating the base table until refreshed explicitly. EDB Replication Server currently does not support subscription creation or snapshot for invalid views.

     The following error appears during a snapshot where the view is invalid:

     Toggle WrapCopy

     INFO: MTK-14001: One or more tables are missing from the source Oracle database.

     With this error, the user must refresh the view before taking the snapshot.

     Note

     Snapshot-only replication for materialized views is currently tested and certified only for Oracle to EDB Postgres Advanced Server databases.

• A publication can have multiple subscriptions.

• A subscription can be associated with at most one publication.

• A database can contain both publications and subscriptions.

• A given publication server can support only one multi-master replication system. All primary nodes created subordinate to a given publication server are assumed to be part of the same multi-master replication system.

• You can use a table that is created as a result of a subscription in another publication. Thus, a publication can replicate data to a subscription which, in turn, you can use in a publication to replicate to another subscription. This scenario creates a cascaded replication architecture.

• For restrictions on the combinations and configurations of database servers that you can use for a publication and its subscription, see EDB Postgres Advanced Server compatibility configuration modes.

• All replication system components must be running for replication to occur or before you configure, operate, or modify the replication system. Use the Replication Server console to configure and modify a replication system. The console doesn't need to be running for replication to occur.

• In general, the order of creating a replication system is as follows:

  1. Create the required physical databases, database user names, tables, and views to use in the replication system.
  2. Define the replication system logical components using the Replication Server console or CLI.
  3. Perform replication.

• In general, the order of removing a single-master replication system is as follows:

  1. Remove the replication system logical components using the Replication Server console or CLI, starting with the subscriptions (Subscription nodes) and then their parent components (Subscription Database nodes).
  2. Unregister the subscription server if you no longer need it.
  3. Repeat the same process for the publications.
  4. After all replication system logical components are removed (except for possibly the publication server and subscription server) you can drop any of the physical database objects in Oracle, SQL Server, or Postgres. Don't drop the control schema objects manually, for example by using an SQL command line utility. Doing so can cause the Replication Server console and CLI to stop working. See Deleting the control schema and control schema objects if this problem occurs. Deleting the replication system logical components using the Replication Server console or CLI drops the control schema objects from the physical database.

• The order of removing a multi-master replication system is as follows:

  1. Remove the replication system logical components using the Replication Server console or CLI starting with the publication database definitions of the non-MDN nodes.
  2. Remove the publication from under the primary definition node.
  3. Remove the publication database definition of the primary definition node. After all replication system logical components are removed (except for possibly the publication server) you can drop any of the physical database objects in Postgres. Don't drop the control schema objects manually, for example by using an SQL command line utility. Doing so can cause the Replication Server console and CLI to stop working.