Prerequisite steps v7

Suggest edits

Before beginning the process of building a multi-master replication system, you must take certain steps to prepare the host environments and the database servers used as primary nodes.

Set heap memory size for the publication server

Replication speed and efficiency can be affected by the heap memory size set for the publication server. The Replication Server startup configuration file sets a parameter controlling the minimum and maximum heap size allocated for the publication server. See Setting heap memory size for the publication and subscription servers for guidelines and information on setting this parameter.

Enable synchronization replication with the log-based method

For Postgres database servers of version 9.4 and later: These settings apply if you plan to use the log-based method of synchronization replication with any primary node running under a given Postgres database server. The following configuration parameter settings are required in the configuration file postgresql.conf of each such Postgres database server:

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 MMR primary nodes on this database server that use the log-based method. In addition, if SMR publication databases runs on this database server, add the number of SMR publication databases that use the log-based method.
max_replication_slots. Specifies the maximum number of replication slots. For support of MMR systems, the minimum is the total number of primary nodes in the multi-master replication system multiplied by the number of primary nodes residing on this database server. For information, see Replication origin. In addition, if SMR publication databases run on this database server, add the number of SMR publication databases that use the log-based method.
track_commit_timestamp. Set to on. This configuration parameter applies only to Postgres database servers of version 9.5 or later. See Configuration parameter and table setting requirements for more information.

See Synchronization replication with the log-based method for information on the log-based method of synchronization replication.

Restart the Postgres database server after modifying any of these configuration parameters.

In addition, the pg_hba.conf file requires an entry for each publication database user of primary nodes that use the log-based method. You must include these database users as a replication database user in the pg_hba.conf file. See Verify host accessibility for more information.

Preparing the primary definition node

When creating the publication database definition for the primary definition node, specify a database user name that has the following characteristics:

The database user can connect to the primary definition node.
The database user has superuser privileges. Superuser privileges are required because the database configuration parameter session_replication_role is modified by the database user when the primary definition node receives updates from other primary nodes during a synchronization replication. The database user temporarily changes session_replication_role to replica to prevent the triggers on the publication tables from activating. This session change also occurs for snapshot operations involving replication of the control schema from one publication database to another.
The database user must be able to modify the system catalog tables. This ability is needed to disable foreign key constraints on the publication tables for snapshots targeted to the publication. It is also needed for the control schema tables for snapshot operations involving the replication of the control schema from one publication database to another. (See Disabling foreign key constraints for snapshot replications for more information on this requirement.)

The examples we use are based on the following primary definition node:

The database user name for the primary 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 primary definition node is edb.

These steps show how to prepare the primary definition node database user.

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

When creating the publication database definition, enter the database user name in the Publication Service – Add Database dialog box (see Adding the primary definition node).

CREATE ROLE pubuser WITH LOGIN SUPERUSER PASSWORD 'password';

Step 2 (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. You must also grant privileges on the control schema objects to users who perform inserts, updates, or deletions on the publication tables.

When adding users, granting these users membership in these roles gives them the privileges to access the publication tables. This setup eliminates the need to grant these privileges individually to each new user.

See Step 2 of Postgres publication database for information on creating such roles.

Preparing more primary nodes

You can create a database user and a database for an additional primary node.

When creating the publication database definition for an additional primary node, specify a database user name that has the following characteristics:

The database user can connect to the primary node.
The database user has superuser privileges. Superuser privileges are required because the database user modfies the database configuration parameter session_replication_role when the primary node receives updates from other primary nodes during a synchronization replication. The database user temporarily changes session_replication_role to replica to prevent the triggers on the publication tables from activating. This session change also occurs for snapshot operations involving replication of the control schema from one publication database to another.
The database user must be able to modify the system catalog tables. This abiity is needed to disable foreign key constraints on the publication tables for snapshots targeted to the publication. It is also needed for the control schema tables for snapshot operations involving the replication of the control schema from one publication database to another. See Disabling foreign key constraints for snapshot replications for more information on this requirement.
If the added primary node resides on a different database server instance (that is, on a different host or port number) from the primary definition node, then use the same database user name for this additional primary node as used for the primary definition node. This isn't necessary if the publication server configuration option skipTablePrivileges is changed from its default value of false to true. See Skipping grants of table-level user privileges on MMR target tables for information on skipTablePrivileges.

Two possible options are available with respect to how to create the publication tables in the primary node:

Allow the publication server to create the publication table definitions in the primary node by copying the definitions from the primary definition node when you add the publication database definition for the primary node.
Define the publication tables in the primary 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, be sure the publication tables are defined identically to the tables in the primary 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, and so on.

The examples are based on the following:

The database user name of the second primary node is MMRuser.
The database name of the second primary node is MMRnode.

Create a database user name for the primary node. This user becomes the owner of Replication Server metadata database objects created in the primary node to track, control, and record the replication process and history. The Replication Server metadata database objects are created in a schema named _edb_replicator_pub.
When creating the publication database definition for the primary node, enter the database user name in the Publication Service – Add Database dialog box. See Creating Additional Primary nodes.
```
CREATE ROLE MMRuser WITH LOGIN SUPERUSER PASSWORD 'password';
```
Create a database to use as the primary node if this database doesn't already exist.
```
CREATE DATABASE MMRnode;
```

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.

For a discussion of firewalls and access to ports see Firewalls and access to ports.
For a discussion of network IP addresses see Network IP addresses.

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 on each Postgres database server that contains a primary node.

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

Primary nodes

On each database server running a primary node, you need the following to allow access to the database:

host <primarynode_db> <primarynode_user> <pub_ipaddr>/32 md5

The value you substitute for <primarynode_db> is the name of the database you intend to use as the primary node. The value you substitute for <primarynode_user> is the database user name you created in Step 1 of Preparing the primary definition node or Step 1 of Preparing more primary nodes.

For two primary nodes using databases named edb and MMRnode running on the same database server, the resulting pg_hba.conf file appears as follows:

# TYPE  DATABASE    USER           ADDRESS           METHOD

# "local" is for Unix domain socket connections only
local   all         all                                md5
# IPv4 local connections:
host    edb         pubuser        192.168.2.22/32     md5
host    MMRnode     MMRuser        192.168.2.22/32     md5
host    all         all            127.0.0.1/32        md5
# IPv6 local connections:
host    all         all            ::1/128             md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication  enterprisedb                      md5
#host    replication  enterprisedb  127.0.0.1/32        md5
#host    replication  enterprisedb  ::1/128             md5

If the primary node using database MMRnode with database user name MMRuser is running on a separate host from where database edb is running, the pg_hba.conf file on the database server with database MMRnode looks like the following:

# TYPE  DATABASE    USER           ADDRESS           METHOD

# "local" is for Unix domain socket connections only
local   all         all                                md5
# IPv4 local connections:
host    MMRnode     MMRuser        192.168.2.22/32     md5
host    all         all            127.0.0.1/32        md5
# IPv6 local connections:
host    all         all            ::1/128             md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication  enterprisedb                      md5
#host    replication  enterprisedb  127.0.0.1/32        md5
#host    replication  enterprisedb  ::1/128             md5

These examples assume databases edb and MMRnode are using the trigger-based method of synchronization replication. If you're using the log-based method, the pg_hba.conf file must contain additional entries with the DATABASE field set to replication for primarynode_user and pub_ipaddr to allow replication connections from the publication server on the host on which it is running.

The following shows changes to the first example with these added entries as the last two lines in the file:

# TYPE  DATABASE    USER           ADDRESS           METHOD

# "local" is for Unix domain socket connections only
local   all         all                                md5
# IPv4 local connections:
host    edb         pubuser        192.168.2.22/32     md5
host    MMRnode     MMRuser        192.168.2.22/32     md5
host    all         all            127.0.0.1/32        md5
# IPv6 local connections:
host    all         all            ::1/128             md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication  enterprisedb                      md5
#host    replication  enterprisedb  127.0.0.1/32        md5
#host    replication  enterprisedb  ::1/128             md5
host    replication  pubuser       192.168.2.22/32     md5
host    replication  MMRuser       192.168.2.22/32     md5

See Synchronization replication with the log-based method and Enabling synchronization replication with the log-based method for more information on synchronization replication with the log-based method.

Reload the configuration file after making the modifications. From the Postgres application menu, select Reload Configuration (Expert Configuration > Reload Configuration on EDB Postgres Advanced Server). Reloading puts the modified pg_hba.conf file into effect.

← Prev

Multi-master replication operation

Creating a publication