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 1024 megabytes (-Xmx1024m).
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.
Note: The directions in this section apply only if Oracle will be used as the publication or subscription database.
The JDBC driver for Oracle, ojdbc14.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, an ojdbc14.jar file must be accessible to the JVM on each host.
Step 1: Download the Oracle JDBC driver, ojdbc14.jar, from the Oracle download site to the host that will be running the publication server.
Step 2: Copy file ojdbc14.jar to the directory XDB_HOME/lib/jdbc.
Note: If you installed xDB Replication Server to a location other than the Postgres installation directory, copy the ojdbc14.jar file to the lib/jdbc subdirectory of the location where you installed xDB Replication Server.
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 (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 xDB Replication Server metadata database 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 xDB Replication Server metadata database 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: The publication database user must be able to read the tables and views that are to be included in publications.
Step 5 (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 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 xDB Replication Server metadata database object creation and ownership. The metadata database objects are created in the publication database to track, control, and record the replication process and history. This example assumes the metadata database objects are to be created in a 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 xDB Replication Server metadata database objects.
Step 5: Choose the schema where the xDB Replication Server metadata database 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.
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 to store the metadata database 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 metadata database 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:
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 xDB Replication Server metadata database 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 xDB Replication Server metadata database objects.
The xDB Replication Server metadata database objects are created in a schema named _edb_replicator_pub. In order to create this schema, the publication database user must have the CREATE ON DATABASE privilege on the publication database.
Step 3: Grant the privileges required to create triggers on the publication tables.
The publication database user must be the owner of the publication tables and must have the CREATE and USAGE privileges on the schema containing the publication tables.
Step 4: The publication database user must be able to read the tables and views that are to be included in publications.
Step 5: The publication database user must have the USAGE privilege on the schemas in which the tables and views reside that are to be included in publications.
Step 6 (For Advanced Server version 9.2 in Oracle compatible mode only): If the publication database is an Advanced Server 9.2 database installed in Oracle compatible mode, the publication database user must have SELECT privilege on certain system tables.
Step 7 (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.
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: Views contained in publications do not have corresponding shadow tables. Thus, there is no shadow table for the view edb.salesemp.
See Section 5.1.4.1 for preparation of a Postgres subscription database. See Section 5.1.4.2 for preparation of an Oracle subscription database. See Section 5.1.4.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.)
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.
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.5.1.2 for changes that must be made for the publication server metadata 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 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 9.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 opensuse-vm is now associated with the network IP address 192.168.2.7 instead of the loopback address 127.0.0.2:
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 three aforementioned cases are discussed in the following sections.
host control_dbname control_dbuser pub_ipaddr/32 md5
host control_dbname control_dbuser sub_ipaddr/32 md5
The values you substitute for control_dbname and control_dbuser are the entries for fields database and user in the xDB Replication Configuration file found on the hosts running the publication server and subscription server.
The values you substitute for pub_ipaddr and sub_ipaddr are the network IP addresses where the publication server and the subscription server are running.
Note: The network IP addresses you substitute for pub_ipaddr and sub_ipaddr must not be the loopback address 127.0.0.1. However, the publication and subscription servers do require access to the xDB Control database using the loopback address as well. This access is already granted in the default pg_hba.conf file by the following entry:
The following example shows the pg_hba.conf file for a configuration where the publication server and subscription server are running on the same host, using the same xDB Control database on the host:
If the publication server and subscription server are running on separate hosts, each with their own xDB Control database, then modifications must be made to the pg_hba.conf file on each database server running an xDB Control database as shown by the following example where xdbpub is the xDB Control database on the publication server and xdbsub is the xDB Control database on the subscription server.
Note: For a database server whose xDB Control database is accessed only by the publication server, the entry permitting access from sub_ipaddr is not necessary as shown by the preceding example.
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.4.1.
For a Postgres subscription database, 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 with their own xDB Control database on each host, then the pg_hba.conf file on the subscription database server looks like the following:
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.3.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, each with its own xDB Control database, then the pg_hba.conf file on the publication database server would look like the following:

5 Single-Master Replication Operation : 5.1 Prerequisite Steps

Table of Contents Previous Next