Creating a publication v7

Creating a publication requires:

• Registering the publication server
• Adding the primary definition node
• Creating a publication by choosing the tables for the publication along with the conflict resolution options
• Defining table filters to be enabled on any primary nodes

Registering a publication server

Register a publication server in a same way as for single-master replication. See Registering a publication server for details.

After you successfully register a publication server, the replication tree of the Replication Server console displays a Publication Server node under which are the SMR and MMR type nodes.

Continue to build the multi-master replication system under the MMR type node.

Adding the primary definition node

The first database to identify to Replication Server is the primary definition node. You do this by creating a publication database definition subordinate to the MMR type node under the Publication Server node.

After you create the publication database definition, a Publication Database node representing the primary definition node appears in the replication tree of the Replication Server console. You can then create a publication containing tables residing in this database under the Publication Database node.

You must enter database connection information such as the database server network address, database identifier, and database login user name and password when you create the publication database definition. The connection information is used by the publication server to access the publication tables when it performs replication.

Step 1: Make sure the database server for the primary definition node is running and accepting client connections.

Step 2: Select the MMR type node under the Publication Server node. Select Publication > Publication Database > Add Database.

Step 3: In the Publication Service – Add Database dialog box, fill in the following fields:

• Database Type. Select PostgreSQL or Postgres Plus Advanced Server for the primary definition node. For an EDB Postgres Advanced Server Oracle-compatible installation, select the Postgres Plus Advanced Server option. For PostgreSQL or an Advanced Server PostgreSQL-compatible installation, select the PostgreSQL option.
• Host. IP address of the host on which the primary definition node is running.
• Port. Port on which the primary definition node is listening for connections.
• User. The database user name for the primary definition node created in Step 1 of Preparing the primary definition node.
• Password. Password of the database user.
• Database. The database name of the primary definition node.
• URL Options (For SSL connectivity). Enter the URL options to establish SSL connectivity to the primary definition node. See Using secure sockets layer (SSL) connections for more information.
• Changeset Logging (For Postgres). Select Table Triggers to use the trigger-based method of synchronization replication. Select WAL Stream to use the log-based method of synchronization replication. See Synchronization replication with the trigger-based method and Synchronization replication with the log-based method for information on the log-based method.
• Node Priority Level. Enter an integer from 1 to 10, which is the priority level assigned to this primary node for conflict resolution based on node priority. The highest priority is 1 while the lowest is 10. See Conflict resolution strategies for more information. The default is 1 for the primary definition node.

Step 4: Select Test. When Test Result: Success appears, select OK, and then select Save.

When the publication database definition is successfully saved, a Publication Database node is added to the replication tree under the MMR type node of the Publication Server node.

The label MDN appears at the end of the node in the replication tree. In addition, the MDN field is set to Yes in the Property window to indicate this is the primary definition node.

Adding a publication

Subordinate to the primary definition node, you create a publication that contains tables of the database.

Step 1: Select the Publication Database node. Select Publication > Create Publication.

Step 2: In the Create Publication dialog box, on the Create Publication tab, fill in the following fields:

• Publication Name. Enter a name that is unique among all publications.
• Publish. Select the boxes next to the tables to include in the publication. You can optionally select Use Wildcard Selection to use wildcard pattern matching for selecting publication tables.
• Select All. Select this option to include all tables in the Available Tables list in the publication.
• Use Wildcard Selection. Select to use the wildcard selector to choose tables for the publication. See Selecting tables with the wildcard selector for more information.

Step 3 (Optional): Table filters consist of a set of filter rules that control the selection criteria for rows replicated between primary nodes during a snapshot or a synchronization replication.

A filter rule consists of a filter name and a SQL WHERE clause (omitting the WHERE keyword) called the filter clause, which you specify for a table that defines the selection criteria for rows to include during a replication.

You can define multiple filter rules for each table in the publication. If you don't define a filter rule for a given table, then you cannot enable filtering later on that corresponding table in any primary node of the multi-master replication system.

After you define the filter rules for a publication table, you can later choose whether to enable those filter rules on any primary node in the replication system in accordance with the following rules:

• You can enable at most one filter rule on a given table in a given primary node.
• You can enable the same filter rule on the same given table in several different primary nodes.
• You can enable different filter rules on the same given table but in different primary nodes.

If you want to define table filters on the publication tables, select the Table Filters tab. Select the table from the Table/View list for which you want to add a filter rule. Select Add Filter.

In the Filter dialog box, enter a descriptive filter name and the filter clause to select the rows you want to replicate. The filter name and filter clause must meet the following conditions:

• For any given table, each filter rule must be assigned a unique filter name.
• For any given table, the filter clauses must have different syntaxes (that is, the filtering criteria must be different).

To remove a filter rule, select the filter rule you want to remove, and then select Remove Filter.

You can also modify the filter name or filter clause of a filter rule listed in the Table Filters tab. Double-click the cell of the filter name or filter clause you want to change and enter the text for your change.

When creating more primary nodes, you can selectively enable these table filters on the corresponding tables in the added primary nodes. See Creating more primary nodes for more information.

Step 4 (Optional): If you want to modify or see the current conflict resolution options, select the Conflict Resolution Options tab. For each table, you can select the primary conflict resolution strategy and a standby strategy by selecting from a list of options.

If, during synchronization replication, conflicting changes are pending against the same row from different primary nodes, the conflict resolution strategy determines which of the conflicting changes is accepted and replicated to all primary nodes. The conflicting changes that aren't accepted are discarded.

If the selection from the Conflict Resolution Strategy column doesn't resolve the conflict, the selection from the Standby Conflict Resolution Strategy column is applied. If neither strategy resolves the conflict, the event is marked as Pending in the Conflict History tab. See Viewing conflict history for more information.

An example of a conflict is when the same column of the same row is changed by transactions in two different primary nodes. Depending on the conflict resolution strategy in effect for the table, one of the transactions is accepted and replicated to all primary nodes. The other transaction is discarded and not replicated to any primary node.

The following is a summary of each conflict-resolution strategy:

• Earliest Timestamp. The conflicting change with the earliest timestamp is accepted and replicated to all other primary nodes. All other conflicting changes are discarded.
• Latest Timestamp. The conflicting change with the latest timestamp is accepted and replicated to all other primary nodes. All other conflicting changes are discarded.
• Node Priority. The conflicting change occurring on the primary node with the highest priority level is accepted and replicated to all other primary nodes. All other conflicting changes are discarded.
• Custom. Update/update conflicts are resolved with a PL/pgSQL custom conflict-handling program.
• Manual. The conflict remains unresolved. Conflicting changes remain applied in each primary node where they originated but aren't replicated to other primary nodes. You must apply the proper adjustments in each primary node.

See Conflict resolution strategies for more information.

Step 5: If you expect update/update conflicts, then set the REPLICA IDENTITY option to FULL on those tables where you expect the conflicts to occur. See Configuration parameter and table setting requirements for more information.

Step 6: Select Create. When Publication Created Successfully appears, select OK.

Upon successful publication creation, a Publication node is added to the replication tree.

• On this page
• Registering a publication server
• Adding the primary definition node
• Adding a publication