Table of Contents Previous Next


5 Single-Master Replication Operation : 5.1 Prerequisite Steps

On a 32-bit system, the initial heap size is set to 128 megabytes (-Xms128m) and the maximum limit is set to 512 megabytes (-Xmx512m). On a 64-bit system the initial heap size is 256 megabytes (-Xms256m) and the maximum limit is 1536 megabytes (-Xmx1536m).
The default values can be modified by changing the JAVA_HEAP_SIZE parameter setting in the xDB Startup Configuration file. Be sure to restart the publication server and the subscription server (see sections 5.2.1 and 5.3.1) after making such changes.
Minimum RAM Size. For a 32-bit system, use 4 gigabytes; for a 64-bit system use 8 gigabytes.
Recommended RAM Size. For a 32-bit system, use 8 gigabytes; for a 64-bit system use 16 gigabytes.
wal_level. Set to logical.
max_wal_senders. Specifies the maximum number of concurrent connections (that is, the maximum number of simultaneously running WAL sender processes). Set at minimum, to the number of SMR publication databases on this database server that will use the log-based method. In addition, if MMR master nodes are to run on this database server, also add the number of MMR master nodes that will use the log-based method.
max_replication_slots. Specifies the maximum number of replication slots. Set at minimum, to the number of SMR publication databases on this database server that will use the log-based method. In addition, if MMR master nodes are to run on this database server with the log-based method, see Section 2.2.10.4 for information on the additional number of replication slots required.
See Section 2.2.10 for information on the log-based method of synchronization replication.
In addition, the pg_hba.conf file requires an entry for each publication database user of publication databases that are to use the log-based method. Such database users must be included as a replication database user in the pg_hba.conf file. See Section 5.1.6.3 for additional information.
Note: The directions in this section apply only if Oracle will be used as the publication or subscription database.
An Oracle JDBC driver jar file such as, ojdbc5.jar, must be accessible to the Java virtual machine (JVM) on the host running the publication server and the subscription server. If the publication server and subscription server are running on separate hosts, the Oracle JDBC driver must be accessible to the JVM on each host. Oracle JDBC driver version ojdbc5 or later must be used.
Step 1: Download the Oracle JDBC driver, for example, ojdbc5.jar, from the Oracle download site to the host that will be running the publication server.
Step 2: Copy file ojdbc5.jar to the directory XDB_HOME/lib/jdbc.
Note: You may also copy the ojdbc5.jar file to the jre/lib/ext subdirectory of the location where you installed your Java runtime environment.
Step 3: If the subscription server is running on a different host than the publication server, repeat steps 1 and 2 for the subscription server host.
Note: The directions in this section apply only if SQL Server will be used as the publication or subscription database.
Step 1: Be sure SQL Server Authentication mode is enabled on your SQL Server database engine. SQL Server Authentication mode allows the use of SQL Server logins such as the built-in system administrator login, sa.
Using the default settings for SQL Server installation, only Windows Authentication mode is enabled, which utilizes the accounts of the Windows operating system for authentication.
In order to permit SQL Server Authentication mode, you must change the authentication mode to Mixed Mode Authentication, which permits both Windows Authentication and SQL Server Authentication.
This can be done using SQL Server Management Studio. Refer to the appropriate SQL Server documentation for using SQL Server Management Studio.
Step 2: Be sure SQL Server is accepting TCP/IP connections. In the SQL Server Configuration Manager, under SQL Server Network Configuration, be sure the TCP/IP protocol for the SQL Server instance is set to Enabled. The typical, default SQL Server instance names are MSSQLSERVER or SQLEXPRESS.
Step 3 (Required only for a SQL Server publication database): Be sure SQL Server Agent is enabled and running. SQL Server Agent is a Windows service that controls job scheduling and execution with SQL Server.
SQL Server Agent can be started by using SQL Server Configuration Manager. Refer to the appropriate SQL Server documentation for using SQL Server Configuration Manager.
Three tables named dept, emp, and jobhist are members of schema edb.
One view named salesemp is a member of schema edb. This view is a SELECT statement over the emp table.
The Oracle system identifier (SID) of the publication database is xe. The SQL Server publication database name is edb. The Postgres publication database name is edb. (The cases of Oracle as the publication database, SQL Server as the publication database, and Postgres as the publication database are presented with examples in this section.)
Step 1: Create a database user name for the publication database user. The publication database user name must have a password, and it must have the ability to create a database session. The publication database user becomes the owner of the control schema objects that will be created in the publication database to track, control, and record the replication process and history.
Step 2: Grant the privileges needed to create the control schema objects.
Step 3: Grant the privileges required to create triggers on the publication tables. The CREATE ANY TRIGGER privilege must be granted to the publication database user.
Step 4: Grant the privileges required to lock publication tables when creating triggers. The LOCK ANY TABLE privilege must be granted to the publication database user.
Step 5: The publication database user must be able to read the tables and views that are to be included in publications.
Step 6 (Optional): Create one or more “group” roles containing the required privileges to access the tables and views of the publications that will be needed by application users.
When an application connects to a particular database, the application assumes the identity and privileges of a database user that has been defined in that database. The database users in any given database are independent of database users in other databases with respect to their properties such as their role memberships and privileges. In fact, the same database user name can be defined in more than one database, each with its own distinct properties.
In each database, a database user can be mapped to a SQL Server login. When an application connects to a database using a SQL Server login to which a database user has been mapped, the application assumes the identity and privileges of that database user.
A database user must exist in the msdb database that is mapped to the SQL Server login used by the publication server. This database user must have certain privileges to execute jobs in the dbo schema of the msdb database. (The msdb database is used by SQL Server Agent to schedule alerts and jobs. SQL Server Agent runs as a Windows service.)
The control schema used to contain certain control schema objects created by the publication server is pubuser. Other control schema objects are always created in _edb_replicator_pub, _edb_replicator_sub, and _edb_scheduler.
The database user mapped to SQL Server login pubuser in database msdb is pubuser_msdb.
Note: The sqlcmd utility program is used to execute the SQL statements in these examples. The USE command establishes the database to which the subsequent statements are to apply. The GO command executes the preceding SQL statements as a batch. Placement of the GO command within a stream of SQL statements sometimes has significance depending upon the particular SQL statements.
Step 1: Create a SQL Server login for the xDB Replication Server publication database user. The login must have a password.
Step 2: Create the database user and its required privileges for job scheduling in database msdb:
Step 3: Create the database user for the control schema object creation and ownership. The control schema objects are created in the publication database to track, control, and record the replication process and history. This example assumes some of the control schema objects are to be created in the schema named pubuser.
Note: The schema name you specify in the WITH DEFAULT_SCHEMA clause must be the schema you choose in Step 5. This schema does not have to exist before using it in the CREATE USER FOR LOGIN WITH DEFAULT_SCHEMA statement.
Note: The remaining steps assume that the commands are given in the publication database (that is, the USE edb command has been previously given to establish the publication database edb as the current database.)
Step 4: Grant the database level privileges needed by the publication database user to create the control schema objects.
Step 5: Choose the control schema where some of the control schema objects are to reside.
Step 6: Grant the privileges required to create triggers on the publication tables. The publication database user must have the ALTER privilege on the publication tables.
Step 7: The publication database user must be able to read the tables and views that are to be included in publications.
Step 8 (Optional): Create one or more “group” roles containing the required privileges to access the tables and views of the publications that will be needed by application users.
Note: Creation of these roles can only be done after the SQL Server publication database definition has been created using the xDB Replication Console or xDB Replication Server CLI. (For example, see Section 5.2.2 for the xDB Replication Console usage.)
The following example shows the creation of the role appgroup and the granting of privileges on the publication tables to the role. The example assumes that in Step 5, schema pubuser was chosen as the control schema to store some of the control schema objects.
Note (Granting privileges to individual users): As previously described, each application database user that is to modify the data in any of the publication tables must be granted certain privileges on the publication tables and the control schema objects. Using a group role for this purpose as described earlier in this step helps simplify this process.
Note: Instead of using the preceding statements, which grant privileges at the schema level, a more granular level of privileges can be issued at the database object level using the following statements:
The database user has superuser privileges. Superuser privileges are required because the database configuration parameter session_replication_role is altered by the database user to replica for snapshot operations involving replication of the control schema from one publication database to another.
Step 1: Create a database superuser for the publication database user. The publication database user name must have a password, and it must have the ability to create a database session. The publication database user becomes the owner of the control schema objects that will be created in the publication database to track, control, and record the replication process and history.
Step 2 (Optional): Create one or more “group” roles containing the required privileges to access the tables and views of the publications that will be needed by application users.
Note: The process described in this step is applicable to Postgres publications in both single-master and multi-master replication systems.
The following example shows the creation of the role appgroup and the granting of privileges on the publication tables to the role.
In addition, for the log-based method of synchronization replication, if the TRUNCATE command is to be permitted on the publication tables, grant the following additional privileges:
Also for the log-based method of synchronization replication for usage of the TRUNCATE command, grant the following privileges after creation of the publication database definition. (See Section 5.2.2 for information on creating the publication database definition for a single-master replication system. For a multi-master replication system, see Section 6.2.2.)
Note (Granting privileges to roles after publication creation): Roles for containing publication table privileges should be created before you create the publication. (See Section 5.2.3 for information on creating a publication for a single-master replication system. For a multi-master replication system, see Section 6.2.3.)
USAGE privilege on schema _edb_replicator_pub.
USAGE privilege on sequence rrep_tx_seq.
INSERT privileges on the shadow tables corresponding to publication tables in which the role will be inserting, updating, or deleting rows. Shadow tables follow the naming convention rrst_schema_table. Note that shadow tables exist only if the trigger-based method of synchronization is to be used.
USAGE privilege on schema _edb_replicator_pub.
INSERT privilege on table _edb_replicator_pub.rrep_wal_events_queue.
In addition, if the TRUNCATE command is to be permitted on the publication tables, grant the following additional privileges:
See Section 5.1.5.1 for preparation of a Postgres subscription database. See Section 5.1.5.2 for preparation of an Oracle subscription database. See Section 5.1.5.3 for preparation of a SQL Server subscription database.
The subscription database user must also have the ability to run the TRUNCATE command on the subscription tables. This requires the following:
Use the Postgres user name postgres created upon installation of PostgreSQL (enterprisedb for Advanced Server installed in Oracle compatible configuration mode) for the subscription database user name. If you choose this option, skip Step 1 and proceed to Step 2.
Step 1: Create a superuser as the subscription database user.
Step 2: Create or choose the subscription database.
For a SQL Server publication database: If the schema containing the publication tables and views in SQL Server is named dbo, then the subscription server creates a schema named dbo_sql in the Postgres subscription database for the subscription tables. (Schema dbo is a special reserved schema in Postgres.)
Note: Support of single-master replication systems where Oracle is the subscription (that is, the target) database has been deprecated and may be removed in a future release. It is recommended to avoid creation of a single-master replication system where Oracle is the subscription database.
Step 1 (Optional): If you do not have an existing database that you want to use as your subscription database, create a new database. This step can be fairly complicated. Refer to the appropriate Oracle documentation for performing this task.
Step 2: Create a database user name for the subscription database user. The subscription database user name must have a password, and it must have the ability to create a database session. The subscription database user becomes the owner of the replicated database objects.
Step 3: Grant the privileges needed to create the replicated database objects.
Note: Support of single-master replication systems where SQL Server is the subscription (that is, the target) database has been deprecated and may be removed in a future release. It is recommended to avoid creation of a single-master replication system where SQL Server is the subscription database.
Step 1: Create or choose the subscription database.
Note: If the schema containing the publication tables and views is named public, then the subscription server creates a schema named public_sql in the SQL Server subscription database for the subscription tables.
Step 2: Create a SQL Server login for the subscription database user. The login must have a password.
Step 3: In the subscription database, a database user must exist that is to be the creator and owner of the subscription tables. This database user must be mapped to the SQL Server login created in Step 2.
Step 4: Grant the database level privileges needed by the subscription database user to create the schema and tables for the subscription.
The publication server uses the port number you specified on the Publication Server Details screen in Step 16 of Section 3.1 as well the port offset by a value of 2 greater than this specified port number. So for a default publication server installation, access is required for port numbers 9051 and 9053.
The subscription server uses the port number you specified on the Subscription Server Details screen in Step 17 of Section 3.1 as well as the port offset by a value of 2 greater than this specified port number. So for a default subscription server installation, access is required for port numbers 9052 and 9054.
If you want to use different port numbers, modify the PUBPORT and SUBPORT entries in the xDB Startup Configuration file and restart the publication server and subscription server.
Note: If you change the port numbers for the publication server or subscription server for which there are existing replication systems, there are additional updates you must perform upon these existing replication systems. See Section 7.6.1.2 for changes that must be made for the publication server metadata in the control schema if the port number used by the subscription server has been changed. See Section 5.5.3 for changes that must be made for the subscription metadata in the control schema if the port number used by the publication server has been changed.
For Linux only: Use the /sbin/ifconfig command.
For Windows only: Open a Command Prompt window and use the ipconfig command.
For Linux only: You may need to modify the /etc/hosts file so that a host’s network IP address is associated with the host’s name.
Note: For an alternative to modifying the /etc/hosts file see Section 10.4.1.7.
This is also verified by using the hostname -i command, which returns the IP address associated with the host name:
If the loopback address 127.x.x.x is returned such as in the preceding example, edit the /etc/hosts file so that the network IP address is associated with the host name instead.
The following example shows the modified /etc/hosts file so that the host name localhost is now associated with the network IP address 192.168.2.22 instead of the loopback address 127.0.0.1:
On some Linux systems, you may need to restart the network service after you have modified the /etc/hosts file. This may be done a number of different ways depending upon the Linux system you are using as shown by the following variations:
The hostname -i command now returns the network IP address of the host:
A Postgres database server uses the host-based authentication file, pg_hba.conf, to control access to the databases in the database server.
You need to modify the pg_hba.conf file in the following locations:
The modifications needed to the pg_hba.conf file for each of the aforementioned cases are discussed in the following sections.
host pub_dbname pub_dbuser pub_ipaddr/32 md5
host pub_dbname pub_dbuser sub_ipaddr/32 md5
The value you substitute for pub_dbname is the name of the Postgres publication database you intend to use. The value you substitute for pub_dbuser is the publication database user name you created in Step 1 of Section 5.1.4.3.
For a Postgres publication database named edb, the resulting pg_hba.conf file appears as follows:
Note: The preceding example assumes the publication server and the subscription server are running on the same host, hence the single entry for database edb. If the publication server and subscription server are running on separate hosts, then the pg_hba.conf file on the publication database server would look like the following:
In addition, the preceding examples assume publication database edb is using the trigger-based method of synchronization replication. If the log-based method is used, the pg_hba.conf file must contain an additional entry with the DATABASE field set to replication for pub_dbname, pub_dbuser, and pub_ipaddr to allow replication connections from the publication server on the host on which it is running.
See sections 2.2.10 and 5.1.2 for additional information on synchronization replication with the log-based method.
host sub_dbname sub_dbuser pub_ipaddr/32 md5
host sub_dbname sub_dbuser sub_ipaddr/32 md5
The values you substitute for sub_dbuser and sub_dbname are the subscription database user name and the subscription database name you created in steps 1 and 2 of Section 5.1.5.1.
For a Postgres subscription database named subdb, the resulting pg_hba.conf file appears as follows:
Note: The preceding example assumes that the publication server and the subscription server are running on the same host hence, only one entry is needed for database subdb. If the publication server and subscription server are running on separate hosts, then the pg_hba.conf file on the subscription database server looks like the following:

5 Single-Master Replication Operation : 5.1 Prerequisite Steps

Table of Contents Previous Next