Creating more primary nodes v7

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

You co this by creating more publication database definitions subordinate to the MMR type node under the Publication Server node that contains the primary definition node.

After you create the publication database definition, a Publication Database node representing the primary node appears in the replication tree of the Replication Server 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 publication server uses the connection information 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. 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 node. For an EDB Postgres Advanced Server Oracle-compatible installation, select the Postgres Plus Advanced Server option. For PostgreSQL or an EDB Postgres 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 more primary nodes.
  • Password. Password of the database user.
  • Database. 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 more information.
  • Changeset Logging (for Postgres). This setting is baed on 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 and Synchronization replication with the log-based method for more information.
  • 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 and the lowest is 10. See Conflict resolution strategies for more information. As you add each primary node, the default priority level number increases, assigning a lower priority level to each additional node.
  • Replicate Publication Schema. Select this option 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 don't select this option, it's assumed that you already created the table definitions in the primary node. If you're using the offline snapshot technique to create this primary node, don't select this option. See Loading tables from an external data source (offline snapshot) for information on using an offline snapshot.
  • Perform Initial Snapshot. Select this option if you want the publication server to perform a snapshot from the primary definition node to this primary node when you select Save. If you don't select this option, the tables on the primary node aren't loaded until you perform a replication later. If you're using the offline snapshot technique to create this primary node, you already loaded the table rows. Therefore, don't select this option unless you want to reload the data. See Loading tables from an external data source (offline snapshot) for information on using an offline snapshot.
Note

Unless you intend to use the offline snapshot technique (see Loading tables from an external data source (offline snapshot), we suggest that you select Perform Initial Snapshot. You must perform an initial snapshot replication from the primary definition node to every other primary node before performing synchronization replications on demand or by a schedule. (See Performing synchronization replication and Creating a schedule). If a newly added primary node didn't undergo an initial snapshot, any subsequent synchronization replication might fail to apply the transactions to that primary node. You can also take the initial snapshot by performing an on-demand snapshot. See Performing snapshot replication.

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

Step 5 (Optional): If you defined a set of available table filters for the publication, you can enable these filters on this primary node. See Adding a publication for instructions on defining table filters.

Note

See Table settings and restrictions for table filters for table setup requirements for a log-based replication system and general restrictions on using table filters.

Select the Filter Rules tab to apply one or more filter rules to the primary node. At most you can enable one filter rule on any table in the primary node.

Step 6: Select Perform Initial Snapshot if you want the publication server to perform a snapshot from the primary definition node to this primary node when you select Save. If you don't select this option, the tables on the primary node aren't loaded until you perform a replication later.

If you're using the offline snapshot technique to create this primary node, you already loaded the table rows. Therefore don't select this option 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 select *Perform Initial Snapshot, the Verbose Output** option appears.

If you skipped the enabling of table filters as described in Step 5 and you selected Perform Initial Snapshot after Step 4, the Verbose Output option appears. Select Verbose Output if you want to display the output from the snapshot in the dialog box. Don't select this option in a network address translation (NAT) environment as a large amount of output from the snapshot can delay the response from the Snapshot dialog box.

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.

Unlike the primary definition node, the label MDN doesn't 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 select the Perform Initial Snapshot, an initial snapshot replication is performed. If you selected Verbose Output, the log of the snapshot is displayed as well.

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 you expect the conflicts to occur. See Configuration parameter and table setting requirements for more information.

Step 8 (Optional): If users need to access the data in the publication tables residing on this primary node, it's convenient to have one or more group roles containing the required privileges to access these tables. For the trigger-based method, you must also grant privileges on the control schema objects to users who 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 under certain circumstances.

When adding new users, granting these users membership in these roles gives them the privileges to access the publication tables. This approach 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 to do this.