Replication Server system examples v7

In the diagrams that follow, the logical components, represented by nodes in the replication tree of the Replication Server console, are superimposed on physical component diagrams. The logical components are shaded yellow to help to identify them in the diagrams.

See Introduction to the Replication Server console for more information about the Replication Server console.

Oracle to PostgreSQL or EDB Postgres Advanced Server replication

The following shows a basic Oracle to PostgreSQL or EDB Postgres Advanced Server single-master replication system. A single publication in Oracle contains tables from two schemas that are replicated to a database residing in either PostgreSQL or EDB Postgres Advanced Server.

The following describes the logical components in the preceding diagram: * The publication server to use is identified by registering its network location, user name, and password.

• A publication database definition is created subordinate to the SMR type node under the publication server. The Oracle database user name pubuser is specified in the definition along with the database network location and database identifier. When you create a user named pubuser in Oracle, a schema named pubuser is automatically created by Oracle at the same time. The publication server creates the control schema objects in the pubuser control schema for the replication system’s metadata when you create the publication database definition.
• A publication named pub is created subordinate to the publication database definition. The publication consists of table A in schema S1 and tables B and C in schema S2.
• The subscription server to use is identified by registering its network location, user name, and password.
• A subscription database definition is created subordinate to the subscription server. The Postgres database user name subuser is specified in the definition along with the database network location and database identifier.
• A subscription named sub is created subordinate to the subscription database definition. When the subscription is created, the subscription server creates schemas named S1 and S2 in the subscription database. The table definitions for tables A, B, and C are also created at this time. When replication occurs, the publication server populates these tables with rows from the publication.

SQL Server to PostgreSQL or EDB Postgres Advanced Server replication

The following shows a basic SQL Server to PostgreSQL or EDB Postgres Advanced Server single-master replication system. A single publication in SQL Server contains tables from two schemas that are replicated to a database residing in either PostgreSQL or EDB Postgres Advanced Server.

The following describes the logical components in the diagram:

• The publication server to use is identified by registering its network location, user name, and password.
• A publication database definition is created subordinate to the SMR type node under the publication server. The SQL Server login pubuser is specified in the definition along with the database network location and database identifier. The schema pubuser was created during the publication database preparation step as described in SQL Server publication database. The pubuser schema along with the control schema consisting of three physical schemas _edb_replicator_pub, _edb_replicator_sub, and _edb_scheduler are populated with the control schema objects for the replication system’s metadata when you create the publication database definition.
• A publication named pub is created subordinate to the publication database definition. The publication consists of table A in schema S1 and tables B and C in schema S2.
• The subscription server to use is identified by registering its network location, user name, and password.
• A subscription database definition is created subordinate to the subscription server. The Postgres database user name subuser is specified in the definition along with the database network location and database identifier.
• A subscription named sub is created subordinate to the subscription database definition. When the subscription is created, the subscription server creates schemas named S1 and S2 in the subscription database. The table definitions for tables A, B, and C are also created at this time. When replication occurs, the publication server populates these tables with rows from the publication.

The following image shows how the logical components of this replication system appear in the Replication Server console replication tree.

EDB Postgres Advanced Server to Oracle replication

The following shows a basic EDB Postgres Advanced Server to Oracle single-master replication system. A single publication in a EDB Postgres Advanced Server database contains tables from two schema that are replicated to an Oracle database.

The following describes the logical components in the diagram:

• The publication server to use is identified by registering its network location, user name, and password.
• A publication database definition is created subordinate to the SMR type node under the publication server. The Postgres database user name pubuser is specified in the definition along with the database network location and database identifier. The publication server creates the control schema consisting of three physical schemas _edb_replicator_pub, _edb_replicator_sub, and _edb_scheduler and populates them with the control schema objects for the replication system’s metadata when you create the publication database definition.
• A publication named pub is created subordinate to the publication database definition. The publication consists of table A in schema S1 and tables B and C in schema S2.
• The subscription server to use is identified by registering its network location, user name, and password.
• A subscription database definition is created subordinate to the subscription server. The Oracle database user name subuser is specified in the definition along with the database network location and database identifier.
• A subscription named sub is created subordinate to the subscription database definition. When you create a user named subuser in Oracle, a schema named subuser is automatically created by Oracle at the same time. The table definitions for tables A, B, and C are created in schema subuser when you create subscription sub. When replication occurs, the publication server populates these tables with rows from the publication.

The following image shows how the logical components of this replication system appear in the Replication Server console replication tree.

PostgreSQL to Oracle replication

The following shows a basic PostgreSQL to Oracle single-master replication system. A single publication in a PostgreSQL database contains tables from two schemas that are replicated to an Oracle database. WAL-based method as well as trigger-based method is supported in this type of replication.

The following describes the logical components in the diagram:

• The publication server to use is identified by registering its network location, user name, and password.
• A publication database definition is created subordinate to the SMR type node under the publication server. The Postgres database user name pubuser is specified in the definition along with the database network location and database identifier. The publication server creates the control schema consisting of three physical schemas _edb_replicator_pub, _edb_replicator_sub, and _edb_scheduler and populates them with the control schema objects for the replication system’s metadata when you create the publication database definition.
• A publication named pub is created subordinate to the publication database definition. The publication consists of table A in schema S1 and tables B and C in schema S2.
• The subscription server to use is identified by registering its network location, user name, and password.
• A subscription database definition is created subordinate to the subscription server. The Oracle database user name subuser is specified in the definition along with the database network location and database identifier.
• A subscription named sub is created subordinate to the subscription database definition. When you create a user named subuser in Oracle, a schema named subuser is automatically created by Oracle at the same time. The table definitions for tables A, B, and C are created in schema subuser when you create subscription sub. When replication occurs, the publication server populates these tables with rows from the publication.

The following image shows how the logical components of this replication system appear in the Replication Server console replication tree.

PostgreSQL or EDB Postgres Advanced Server to SQL Server replication

The following shows a basic PostgreSQL or EDB Postgres Advanced Server to SQL Server single-master replication system. A single publication in a PostgreSQL or EDB Postgres Advanced Server database contains tables from two schemas that are replicated to a SQL Server database.

The following describes the logical components in the diagram:

• The publication server to use is identified by registering its network location, user name, and password.
• A publication database definition is created subordinate to the SMR type node under the publication server. The Postgres database user name pubuser is specified in the definition along with the database network location and database identifier. The publication server creates the control schema consisting of three physical schemas _edb_replicator_pub, _edb_replicator_sub, and _edb_scheduler and populates them with the control schema objects for the replication system’s metadata when you create the publication database definition.
• A publication named pub is created subordinate to the publication database definition. The publication consists of table A in schema S1 and tables B and C in schema S2.
• The subscription server to use is identified by registering its network location, user name, and password.
• A subscription database definition is created subordinate to the subscription server. The SQL Server login subuser is specified in the definition along with the database network location and database identifier.
• A subscription named sub is created subordinate to the subscription database definition. When the subscription is created, the subscription server creates schemas named S1 and S2 in the subscription database. The table definitions for tables A, B, and C are also created at this time. When replication occurs, the publication server populates these tables with rows from the publication.

The following image shows how the logical components of this replication system appear in the Replication Server console replication tree.

Postgres multi-master replication

The following shows a basic Postgres multi-master replication system. A publication in a Postgres primary definition node contains tables from two schemas that are initially replicated to two other Postgres primary nodes. The tables in all three primary nodes can then be updated and synchronized with each other.

The following describes the logical components in the diagram:

• The publication server to use is identified by registering its network location, user name, and password.
• A publication database definition is created subordinate to the MMR type node under the publication server. This first publication database definition identifies the primary definition node. The Postgres database user name MMRuser_a is specified in the definition along with the database network location and database identifier. The publication server creates the control schema consisting of three physical schemas _edb_replicator_pub, _edb_replicator_sub, and _edb_scheduler and populates them with the control schema objects for the replication system’s metadata when you create the publication database definition.
• A publication named pub is created subordinate to the publication database definition. The publication consists of table A in schema S1 and tables B and C in schema S2.
• A second primary node is added by creating another publication database definition subordinate to the MMR type node of the publication server under which the primary definition node resides. The Postgres database user name MMRuser_b is specified in the definition along with the database network location and database identifier to create the second primary node.
• When you add the second primary node, you can choose to have the publication server create schemas S1 and S2 and the table definitions for A, B, and C for you, or you can manually create the schemas and table definitions beforehand. The publication server creates the control schema consisting of three physical schemas _edb_replicator_pub, _edb_replicator_sub, and _edb_scheduler under which it creates the control schema objects to store the primary node’s metadata. When defining the primary node, you can choose to have the publication server populate these tables with rows from the publication at that time, or you can defer table loading to later.
• A third primary node is added in a similar manner using the Postgres database user name MMRuser_c.

The following image shows how the logical components of this replication system appear in the Replication Server console replication tree.

• On this page
• Oracle to PostgreSQL or EDB Postgres Advanced Server replication
• SQL Server to PostgreSQL or EDB Postgres Advanced Server replication
• EDB Postgres Advanced Server to Oracle replication
• PostgreSQL to Oracle replication
• PostgreSQL or EDB Postgres Advanced Server to SQL Server replication
• Postgres multi-master replication