Postgres Plus xDB Replication Server with Multi-Master User's Guide : 5.1 Prerequisite Steps

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server xDB Replication Server User's Guide

 

5.1 Prerequisite Steps

Certain steps must be taken to prepare the host environments as well as the publication and subscription database servers before beginning the process of building a single-master replication system. This section describes these steps.

5.1.1 Enabling Access to the Database Servers

The following sections describe configuration steps required to use xDB Replication Server on various types of database servers.

The following section describes the steps to enable access to Oracle. See Section 5.1.1.2 for enabling access to SQL Server.

No special steps are required to enable access to a Postgres database server.

5.1.1.1 Enabling Access to Oracle

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.

The following is an example when xDB Replication Server is installed in the same directory where Postgres is installed (that is, XDB_HOME is equivalent to POSTGRES_INSTALL_HOME):

$ su root
Password: 
$ cd /opt/PostgresPlus/9.1AS/lib/jdbc
$ cp /home/user/Downloads/ojdbc14.jar .
$ ls -l
total 2868
-rw-r--r-- 1 root daemon  554871 Nov  2 07:23 edb-jdbc14.jar
-rw-r--r-- 1 root daemon  285638 Nov  2 07:23 jtds-1.2.jar
-rw-r--r-- 1 root root   1569316 Nov  7 20:21 ojdbc14.jar
-rw-r--r-- 1 root daemon  502118 Nov  2 07:23 postgresql-8.4-702.jdbc3.jar

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.

5.1.1.2 Enabling Access to SQL Server

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.

xDB Replication Server uses SQL Server Agent for certain operations such as for scheduled shadow table history cleanup (see Section 7.4.1).

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.

5.1.2 Preparing the Publication Database

This section discusses the preparation of a database that contains tables and views that will become members of publications.

The tables and views to be used for any given publication must all reside in the same database. This database becomes the publication database of that publication. A publication database user name must be created or already exist with the following characteristics:

    ● The publication database user can connect to the publication database.

    ● The publication database user has the privileges to create database objects to store metadata used for controlling and tracking the replication process.

    ● The publication database user can read the tables and views that are to become members of publications.

    ● For publications that will use synchronization replication, the publication database user can create triggers on the publication tables. (For Oracle, the publication database user must have trigger creation privilege regardless of the replication method used, though triggers will only be created for publications using synchronization replication.)

The examples used throughout the rest of this user’s guide are based on the following:

    ● The publication database user name is pubuser.

    ● The tables and view used in publications reside in a schema named edb.

    ● 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.)

For preparing an Oracle publication database, see the next section. For preparing a SQL Server publication database, see Section 5.1.2.2. For preparing a Postgres publication database, see Section 5.1.2.3.

5.1.2.1 Oracle Publication Database

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.

When creating the publication database definition, the publication database user name is entered in the Publication Service – Add Database dialog box (see Section 5.2.2).

CREATE USER pubuser IDENTIFIED BY password;
GRANT CONNECT TO pubuser;

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 the schema owned by, and with the same name as the publication database user.

GRANT RESOURCE TO pubuser;

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.

GRANT CREATE ANY TRIGGER TO pubuser;

Step 4: The publication database user must be able to read the tables and views that are to be included in publications.

GRANT SELECT ON edb.dept TO pubuser;
GRANT SELECT ON edb.emp TO pubuser;
GRANT SELECT ON edb.jobhist TO pubuser;
GRANT SELECT ON edb.salesemp TO pubuser;

5.1.2.2 SQL Server Publication Database

In SQL Server, an application gains access to the database server by supplying a SQL Server login and its associated password.

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.

When using a SQL Server database as the publication database, a number of database users must be defined and mapped to a SQL Server login according to the following rules:

    ● A SQL Server login must exist that is to be used by the publication server to connect to SQL Server. The SQL Server login and password are specified when creating the publication database definition.

    ● In the publication database, a database user must exist that is to be the creator and owner of the xDB Replication Server metadata database objects. This database user must be mapped to the SQL Server login used by the publication server.

    ● A schema must exist to contain the xDB Replication Server metadata database objects. The database user, described in the preceding bullet point, must either own this schema or have certain privileges on this schema so that the database user can create and update the metadata database objects in this schema. This schema must also be defined as the default schema of that database user.

    ● The SQL Server database users that update the data in the application tables that are to be replicated must have certain privileges on the xDB Replication Server metadata database objects. When an update on a replicated table occurs, a trigger fires that updates certain metadata database objects using the privileges associated with the application user performing the update. These privileges are granted to existing SQL Server database users, however, if new SQL Server database users are added who require update access to the application tables, then privileges must be granted to these users on the xDB Replication Server metadata database objects.

    ● 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.)

This example uses the following SQL Server login, database users, and mappings to comply with the aforementioned rules:

    ● The publication tables reside in database edb.

    ● The database user owning the schema containing the publication tables and the publication tables, themselves, is edb.

    ● The SQL Server login used by the publication server to connect to SQL Server is pubuser.

    ● The database user owning the xDB Replication Server metadata database objects and mapped to SQL Server login pubuser in database edb is pubuser.

    ● The schema used to contain the metadata database objects created by the publication server is pubuser.

    ● 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.

When creating the publication database definition, the SQL Server login is entered in the Publication Service – Add Database dialog box (see Section 5.2.2).

USE master;
GO
CREATE LOGIN pubuser WITH PASSWORD = 'password';
GO

Step 2: Create the database user and its required privileges for job scheduling in database msdb:

USE msdb;
GO
CREATE USER pubuser_msdb FOR LOGIN pubuser;
GO
GRANT EXECUTE ON SCHEMA :: dbo TO pubuser_msdb;
GRANT SELECT ON SCHEMA :: dbo TO pubuser_msdb;
GO

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.

USE edb;
GO
CREATE USER pubuser FOR LOGIN pubuser WITH DEFAULT_SCHEMA = pubuser;
GO

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.

GRANT CREATE TABLE TO pubuser;
GRANT CREATE PROCEDURE TO pubuser;
GO

Step 5: Choose the schema where the xDB Replication Server metadata database objects are to reside.

To create the metadata database objects in a new schema owned by the publication database user and created exclusively for this purpose (recommended approach) issue the following command:

CREATE SCHEMA pubuser AUTHORIZATION pubuser;
GO

Alternatively, to create the metadata database objects in an existing schema such as in the same schema containing the publication tables use the following commands:

GRANT ALTER   ON SCHEMA :: edb TO pubuser;
GRANT EXECUTE ON SCHEMA :: edb TO pubuser;
GRANT SELECT  ON SCHEMA :: edb TO pubuser;
GRANT INSERT  ON SCHEMA :: edb TO pubuser;
GRANT UPDATE  ON SCHEMA :: edb TO pubuser;
GRANT DELETE  ON SCHEMA :: edb TO pubuser;
GO

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.

GRANT ALTER ON edb.dept TO pubuser;
GRANT ALTER ON edb.emp TO pubuser;
GRANT ALTER ON edb.jobhist TO pubuser;
GO

Step 7: The publication database user must be able to read the tables and views that are to be included in publications.

GRANT SELECT ON edb.dept TO pubuser;
GRANT SELECT ON edb.emp TO pubuser;
GRANT SELECT ON edb.jobhist TO pubuser;
GRANT SELECT ON edb.salesemp TO pubuser;
GO

Step 8 (For application database users added after the publication has been created): For each application database user that is to modify the data in any of the application tables to be replicated (by SQL INSERT, UPDATE, or DELETE statements), privileges must be granted on the xDB Replication Server metadata database objects to these users. In the following example, new_user is a new database user that will be updating the application tables.

GRANT EXECUTE ON SCHEMA :: pubuser TO new_user;
GRANT SELECT  ON SCHEMA :: pubuser TO new_user;
GRANT INSERT  ON SCHEMA :: pubuser TO new_user;
GO

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:

GRANT EXECUTE ON pubuser.nextval to new_user;
GRANT SELECT ON pubuser.rrep_tx_seq TO new_user;
GRANT INSERT ON pubuser.rrep_tx_seq TO new_user;
GRANT INSERT ON pubuser.RRST_edb_dept TO new_user;
GRANT INSERT ON pubuser.RRST_edb_emp TO new_user;
GRANT INSERT ON pubuser.RRST_edb_jobhist TO new_user;
GO

Using this approach, however, requires you to issue additional privileges for each application table that is added to a publication.

5.1.2.3 Postgres Publication Database

The following steps assume you wish to use a non-superuser as the publication database user. These steps can be omitted if you choose to use a superuser as the publication database user. Alternatively, most if not all of these steps can probably be omitted if you use the publication database owner as the publication database user. You will need to check that the publication database user you choose has all of the privileges described in these steps.

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.

When creating the publication database definition, the publication database user name is entered in the Publication Service – Add Database dialog box (see Section 5.2.2).

CREATE ROLE pubuser WITH LOGIN PASSWORD 'password';

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.

GRANT CREATE ON DATABASE edb TO pubuser;

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.

GRANT CREATE, USAGE ON SCHEMA edb TO pubuser;
ALTER TABLE edb.dept OWNER TO pubuser;
ALTER TABLE edb.emp OWNER TO pubuser;
ALTER TABLE edb.jobhist OWNER TO pubuser;

Step 4: The publication database user must be able to read the tables and views that are to be included in publications.

GRANT SELECT ON edb.dept TO pubuser;
GRANT SELECT ON edb.emp TO pubuser;
GRANT SELECT ON edb.jobhist TO pubuser;
GRANT SELECT ON edb.salesemp TO pubuser;

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.

GRANT USAGE ON SCHEMA edb TO pubuser;

5.1.3 Preparing the Subscription Database

This section discusses the preparation of a database that will be used as a subscription database.

The tables and views in a given publication must all be replicated to the same database. This database is called the subscription database. A subscription database user name must be created with the following characteristics:

    ● The subscription database user can connect to the subscription database.

    ● The subscription database user has the privileges to create database objects for the replicated tables and views from publications.

    ● The subscription database user has the privileges necessary to execute the TRUNCATE command on the replicated tables.

See Section 5.1.3.1 for preparation of a Postgres subscription database. See Section 5.1.3.2 for preparation of an Oracle subscription database. See Section 5.1.3.3 for preparation of a SQL Server subscription database.

5.1.3.1 Postgres Subscription Database

A database user name must be chosen or created to serve as the subscription database user. The user name must have a password. The subscription database user becomes the owner of the replicated database objects.

When creating the subscription database definition, the subscription database user name is entered in the Subscription Service – Add Database dialog box (see Section 5.3.2).

The subscription database user must also have the ability to run the TRUNCATE command on the subscription tables. This requires the following:

    ● The subscription database user must have superuser privileges.

    ● The subscription database user must have the ability to modify the system catalog tables in order to disable foreign key constraints on subscription tables. (See appendix Section 9.4.4 for more information on this requirement.)

You have the following two choices for choosing the subscription database user name:

    ● 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.

    ● Create a new subscription database user name. For this option, proceed to Step 1.

Step 1: Create a superuser as the subscription database user.

CREATE ROLE subuser WITH LOGIN SUPERUSER PASSWORD 'password';

Step 2: Create or choose the subscription database.

The names of the schemas containing the publication tables and views become the names of the Postgres schemas for the subscription tables. The subscription server creates these schemas in the subscription database when the subscription is created. If schemas with these names already exist in the subscription database, the existing schemas will be used to store the subscription tables.

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.)

The existing schemas must not contain any tables or views with the same names as the publication tables and views. The subscription server returns an error if there are already identically named tables or views. You must delete or rename these tables and views before the subscription can be created.

A new subscription database owned by the subscription database user subuser can be created with the following:

CREATE DATABASE subdb OWNER subuser;

5.1.3.2 Oracle 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.

When creating the subscription database definition, the subscription database user name is entered in the Subscription Service – Add Database dialog box (see Section 5.3.2).

CREATE USER subuser IDENTIFIED BY password;
GRANT CONNECT TO subuser;

Step 3: Grant the privileges needed to create the replicated database objects.

The replicated database objects are created in the schema owned by, and with the same name as the subscription database user.

GRANT RESOURCE TO subuser;

5.1.3.3 SQL Server Subscription Database

Step 1: Create or choose the subscription database.

The names of the schemas containing the publication tables and views become the names of the SQL Server schemas for the subscription tables. The subscription server creates these schemas in the subscription database when the subscription is created. If schemas with these names already exist in the subscription database, the existing schemas will be used to store the subscription tables.

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.

The existing schemas must not contain any tables or views with the same names as the publication tables and views. The subscription server returns an error if there are already identically named tables or views. You must delete or rename these tables and views before the subscription can be created.

A new subscription database can be created as shown by the following:

USE master;
GO
CREATE DATABASE subdb;
GO

Step 2: Create a SQL Server login for the subscription database user. The login must have a password.

When creating the subscription database definition, the SQL Server login is entered in the Subscription Service – Add Database dialog box (see Section 5.3.2).

CREATE LOGIN subuser WITH PASSWORD = 'password';
GO

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.

In this example, the database user is given the same name as the SQL Server login subuser.

USE subdb;
GO
CREATE USER subuser FOR LOGIN subuser;
GO

Step 4: Grant the database level privileges needed by the subscription database user to create the schema and tables for the subscription.

GRANT CREATE SCHEMA TO subuser;
GRANT CREATE TABLE TO subuser;
GO

5.1.4 Verifying Host Accessibility

If more than one computer is used to host the components of the replication system, each computer must be able to communicate with the others on a network.

There are a number of different aspects of this topic as discussed in the following sections.

5.1.4.1 Firewalls

Verify that the firewalls on the hosts allow access from the other hosts running replication system components. Refer to the directions for your host’s operating system to enable accessibility.

5.1.4.2 Network IP Addresses

When configuring a replication system, you must supply the network location of various components such as the publication server, subscription server, publication database server, and subscription database server. This information, consisting of the component’s IP address and port number, is stored as part of the replication system’s metadata.

When one component needs to access another, it refers to the network location in the metadata that you supply during configuration.

During replication system configuration it is strongly suggested that you supply the actual network IP address of each component and avoid the usage of the loopback address, localhost or 127.x.x.x, even if all components are running on the same host.

You can obtain the network IP address using the following command:

For Linux only: Use the /sbin/ifconfig command.

For Windows only: Open a Command Prompt window and use the ipconfig command.

The loopback address works as long as the communicating components are on the same host, but if at some future point, you decide to move a component to a different host on the network, the loopback address stored as the component’s network address in the metadata will no longer work for the component trying to make the connection.

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.6.

The default configuration on Linux systems associates the host name with the loopback address in the /etc/hosts file as shown by the following example:

127.0.0.2       opensuse-vm.vmplanet.net opensuse-vm

This is also verified by using the hostname -i command, which returns the IP address associated with the host name:

$ hostname -i
127.0.0.2

In these circumstances, certain xDB Replication Server components will have trouble locating its other components on the network as in the following cases:

    ● When the user interface attempts to connect to the publication server or subscription server

    ● When the subscription server attempts to connect to the publication server

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:

192.168.2.7   opensuse-vm.vmplanet.net opensuse-vm
#127.0.0.2       opensuse-vm.vmplanet.net opensuse-vm

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:

service network restart
/etc/init.d/networking restart
sudo /etc/init.d/networking restart

The following example illustrates the service network command:

$ su root
Password:
$ service network restart

The hostname -i command now returns the network IP address of the host:

$ hostname -i
192.168.2.7

5.1.4.3 Postgres Server Authentication

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:

    ● On each Postgres database server that contains an xDB Control database

    ● On each Postgres database server that contains a Postgres subscription database

    ● On each Postgres database server that contains a Postgres publication database

In a default Postgres installation, this file is located in the directory POSTGRES_INSTALL_HOME/data.

The modifications needed to the pg_hba.conf file for each of the three aforementioned cases are discussed in the following sections.

xDB Control Database

On a database server on which an xDB Control database resides, the following entries must be added to allow access to the xDB Control database:

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 following is an example of the content of an xDB Replication Configuration file:

user=enterprisedb
port=5444
password=ygJ9AxoJEX854elcVIJPTw\=\=
type=enterprisedb
host=localhost
database=xdb

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:

host    all         all         127.0.0.1/32          md5

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:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                                 md5
# IPv4 local connections:
host    xdb         enterprisedb  192.168.2.7/32        md5
host    all         all           127.0.0.1/32          md5
# IPv6 local connections:
host    all         all           ::1/128               md5

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.

On the publication server (192.168.2.7):

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                                 md5
# IPv4 local connections:
host    xdbpub      enterprisedb  192.168.2.7/32        md5
host    all         all           127.0.0.1/32          md5
# IPv6 local connections:
host    all         all           ::1/128               md5

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.

On the subscription server (192.168.2.8):

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                                 md5
# IPv4 local connections:
host    xdbsub      enterprisedb  192.168.2.7/32        md5
host    xdbsub      enterprisedb  192.168.2.8/32        md5
host    all         all           127.0.0.1/32          md5
# IPv6 local connections:
host    all         all           ::1/128               md5

Postgres Subscription Database

For a Postgres subscription database, the following entries are needed to allow access to the subscription database:

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.3.1.

For a Postgres subscription database, the resulting pg_hba.conf file appears as follows:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                                 md5
# IPv4 local connections:
host    xdb         enterprisedb  192.168.2.7/32     md5
host    subdb       subuser       192.168.2.7/32     md5
host    all         all           127.0.0.1/32          md5
# IPv6 local connections:
host    all         all           ::1/128               md5

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:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                                 md5
# IPv4 local connections:
host    xdbsub      enterprisedb  192.168.2.7/32        md5
host    xdbsub      enterprisedb  192.168.2.8/32        md5
host    subdb       subuser       192.168.2.7/32        md5
host    subdb       subuser       192.168.2.8/32        md5
host    all         all           127.0.0.1/32          md5
# IPv6 local connections:
host    all         all           ::1/128               md5

Reload the configuration file after making the modifications.

Choose Reload Configuration (Expert Configuration, then Reload Configuration on Advanced Server) from the Postgres application menu. This will put the modified pg_hba.conf file into effect.

Postgres Publication Database

For a Postgres publication database, the following is needed to allow access to the publication database:

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.2.3.

For a Postgres publication database named edb, the resulting pg_hba.conf file appears as follows:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                                 md5
# IPv4 local connections:
host    xdb         enterprisedb  192.168.2.7/32        md5
host    edb         pubuser       192.168.2.7/32        md5
host    all         all           127.0.0.1/32          md5
# IPv6 local connections:
host    all         all           ::1/128               md5

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:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                                 md5
# IPv4 local connections:
host    xdbpub      enterprisedb  192.168.2.7/32        md5
host    edb         pubuser       192.168.2.7/32        md5
host    edb         pubuser       192.168.2.8/32        md5
host    all         all           127.0.0.1/32          md5
# IPv6 local connections:
host    all         all           ::1/128               md5

Reload the configuration file after making the modifications.

Choose Reload Configuration (Expert Configuration, then Reload Configuration on Advanced Server) from the Postgres application menu. This will put the modified pg_hba.conf file into effect.

Previous PageTable Of ContentsNext Page