Creating Additional Primary nodes v6.2

Once you have created the primary definition node, you add additional databases to the multi-master replication system by defining additional primary nodes.

This is done by creating additional publication database definitions subordinate to the MMR type node under the Publication Server node that contains the primary definition node.

After the publication database definition is created, a Publication Database node representing the primary node appears in the replication tree of the xDB Replication Console. The publication that was defined under the primary definition node appears 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 same Publication Server node that contains the primary definition node. From the Publication menu, choose Publication Database, and then choose Add Database. Alternatively, click the secondary mouse button on the MMR type node and choose Add Database. The Publication Service – Add Database dialog box appears.

Step 3: Fill in the following fields:

• Database Type. Select PostgreSQL or Postgres Plus Advanced Server for the primary node. For an 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 node is running.
• Port. Port on which the primary node is listening for connections.
• User. The database user name for the primary node created in Step 1 of Preparing Additional Primary nodes.
• Password. Password of the database user.
• Database. Enter the database name of the primary node.
• URL Options (For SSL connectivity). Enter the URL options to establish SSL connectivity to the primary node. See Using Secure Sockets Layer (SSL) Connections for information on using SSL connections.
• Changeset Logging (For Postgres). This setting is predetermined by the selection on the primary definition node (see Adding the Primary definition node). Table Triggers is for the trigger-based method of synchronization replication. WAL Stream is for the log-based method of synchronization replication. See Synchronization Replication with the Trigger-Based Method for information on the trigger-based method. See Synchronization Replication with the Log-Based Method for information on the log-based method.
• Node Priority Level. 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 information on conflict resolution strategies. As each additional primary node is added, the default priority level number increases assigning a lower priority level to each additional node.
• Replicate Publication Schema. Check this box if you want the publication server to create the publication table definitions in the new primary node by copying the definitions from the primary definition node. If you do not check this box, it is assumed that you have already created the table definitions in the primary node. If you are using the offline snapshot technique to create this primary node, do not check this box. See Loading Tables From an External Data Source (Offline Snapshot) for information on using an offline snapshot.
• Perform Initial Snapshot. Check this box if you want the publication server to perform a snapshot from the primary definition node to this primary node when you click the Save button. If you do not check this box, the tables on the primary node will not be loaded until you perform a replication at some later time. If you are using the offline snapshot technique to create this primary node, you should have already loaded the table rows. Therefore do not check this box unless you want to reload the data. See Loading Tables From an External Data Source (Offline Snapshot) for information on using an offline snapshot.

Figure 6-12: Publication Service - Add Database dialog box for an additional primary node

Step 4: Click the Test button. If Test Result: Success appears, click the OK button.

Figure 6-13: Successful primary node test

If an error message appears investigate the cause of the error, correct the problem, and repeat steps 1 through 4.

Step 5 (Optional): If you defined a set of available table filters for the publication, you have the option of enabling these filters on this primary node. See Adding a Publication for instructions on defining table filters. If you do not wish to filter the rows that are replicated to this primary node, go to Step 6.

Click the Filter Rules tab to apply one or more filter rules to the primary node. At most one filter rule may be enabled on any given table in the primary node.

In the following example the filter named dept_10_20_30 is enabled on the dept table and the filter named dept_30 is enabled on the emp table of this primary node.

Figure 6-14: Enabling filter rules on a primary node

Step 6: Check the Perform Initial Snapshot box if you want the publication server to perform a snapshot from the primary definition node to this primary node when you click the Save button. If you do not check this box, the tables on the primary node will not be loaded until you perform a replication at some later time.

If you are using the offline snapshot technique to create this primary node, you should have already loaded the table rows. Therefore do not check this box unless you want to reload the data. See Loading Tables From an External Data Source (Offline Snapshot) for information on using an offline snapshot.

If you do check the Perform Initial Snapshot check box, the Verbose Output check box appears as shown by the following:

Figure 6-15: Initial snapshot with verbose output option

If you skipped the enabling of table filters as described in Step 5, and you checked the Perform Initial Snapshot check box after Step 4, the Verbose Output check box is displayed as well:

Figure 6-16: Initial snapshot with verbose output option

Select the Verbose Output check box only if you want to display the output from the snapshot in the dialog box. This option should be left unchecked in a network address translation (NAT) environment as a large amount of output from the snapshot may delay the response from the Snapshot dialog box.

Click the Save button.

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.

Figure 6-17: Replication tree after adding an additional primary node

Unlike the primary definition node, the label MDN does not appear at the end of the node in the replication tree. The MDN field is set to No in the Property window to indicate this is not the primary definition node.

In addition, a Publication node appears under the newly added primary node. This Publication node represents the publication in the primary definition node, which is replicated to the primary node.

If in Step 6, you checked the Perform Initial Snapshot check box, an initial snapshot replication is performed. If you checked the Verbose Output check box, the log of the snapshot is displayed as well.

Figure 6-18: Adding a primary node with an initial snapshot

If the snapshot is successful, the replicated tables in the primary node are loaded with the rows from the publication tables of the primary definition node.

Step 7: If you expect update/update conflicts, then set the REPLICA IDENTITY option to FULL on those tables where the conflicts are expected to occur. See Configuration Parameter and Table Setting Requirements for additional information.

Step 8 (Optional): If users are to access the data in the publication tables residing on this primary node, it is convenient to have one or more group roles containing the required privileges to access these tables. For the trigger-based method, privileges must also be granted on the control schema objects to users who are to perform inserts, updates, or deletions on the publication tables. When using the log-based method a user needs access to the publication tables and to certain control schema objects as well under certain circumstances.

When adding new users, granting these users membership in these roles gives them the privileges to access the publication tables. This eliminates the need to grant these privileges individually to each new user.

After you perform the replication of the publication schema as shown in Step 3, you can grant the required privileges needed to access the publication tables and its control schema objects. See Step 2 of Postgres Publication Database for information on how this can be accomplished.