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

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server xDB Replication Server User's Guide

 

6.1 Prerequisite Steps

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

6.1.1 Preparing the Master Definition Node

This section discusses the preparation of a database to be used as the master definition node.

When creating the publication database definition for the master definition node, a database user name must be specified that has the following characteristics:

    ● The database user can connect to the master definition node.

    ● The database user has superuser privileges.

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

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

    ● The database user name for the master definition node is pubuser.

    ● The tables used in the publication reside in a schema named edb.

    ● Three tables named dept, emp, and jobhist are members of schema edb.

    ● The database name of the master definition node is edb.

The following steps illustrate the preparation of the master definition node database user.

Step 1: Create a user name with login and superuser privileges for the master definition node. This user becomes the owner of xDB Replication Server metadata database objects that will be created in the master definition node to track, control, and record the replication process and history. The xDB Replication Server metadata database objects are created in a schema named _edb_replicator_pub.

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

CREATE ROLE pubuser WITH LOGIN SUPERUSER PASSWORD 'password';

6.1.2 Preparing Additional Master Nodes

The following steps illustrate the creation of a database user and a database for an additional master node.

When creating the publication database definition for an additional master node, a database user name must be specified that has the following characteristics:

    ● The database user can connect to the master node.

    ● The database user has superuser privileges.

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

There are also two possible options available with respect to how the publication tables are to be created in the master node:

    ● Allow the publication server to create the publication table definitions in the master node by copying the definitions from the master definition node at the time you add the publication database definition for the master node.

    ● Define the publication tables in the master node beforehand by running SQL DDL statements in the PSQL command line utility program or by using Postgres Enterprise Manager Client to create the tables.

If you create the table definitions “manually” as described in the second bullet point, be sure the publication tables are defined identically to the tables in the master definition node including schema names, table names, number of columns, column names, column data types, column lengths, primary key definitions, unique constraints, foreign key constraints, etc.

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

    ● The database user name of the second master node is mmruser.

    ● The database name of the second master node is mmrnode.

Step 1: Create a database user name for the master node. This user becomes the owner of xDB Replication Server metadata database objects that will be created in the master node to track, control, and record the replication process and history. The xDB Replication Server metadata database objects are created in a schema named _edb_replicator_pub.

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

CREATE ROLE mmruser WITH LOGIN SUPERUSER PASSWORD 'password';

Step 2: Create a database that will be used as the master node if such a database does not already exist.

CREATE DATABASE mmrnode;

6.1.3 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 to this topic.

    ● For a discussion of firewalls see Section 5.1.4.1.

    ● For a discussion of network IP addresses see Section 5.1.4.2.

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 the Postgres database server that contains the xDB Control database

    ● On each Postgres database server that contains a master node

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 aforementioned cases are discussed in the following sections.

xDB Control Database

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

host control_dbname control_dbuser  pub_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 host running the publication 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 value you substitute for pub_ipaddr is the network IP address where the publication server is running.

Note: The network IP address you substitute for pub_ipaddr must not be the loopback address 127.0.0.1. However, the publication server does 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 shows an example of the pg_hba.conf file:

# 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.10.102/32     md5
host    all         all           127.0.0.1/32          md5
# IPv6 local connections:
host    all         all           ::1/128               md5

Master Nodes

On each database server running a master node, the following is needed to allow access to the database:

host masternode_db   masternode_user   pub_ipaddr/32   md5

The value you substitute for masternode_db is the name of the database you intend to use as the master node. The value you substitute for masternode_user is the database user name you created in Step 1 of Section 6.1.1 or Step 1 of Section 6.1.2.

For a 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.10.102/32     md5
host    edb         pubuser       192.168.10.102/32     md5
host    all         all           127.0.0.1/32          md5
# IPv6 local connections:
host    all         all           ::1/128               md5

In the preceding example, the xDB Control database xdb and a master node database edb are running on the same database server.

For a master node using database name mmrnode with database user name mmruser running on a separate host than where the xDB Control database is running, the pg_hba.conf file on this 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    mmrnode     mmruser       192.168.10.102/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