edb postgres replication server reference guide...edb postgres replication server (referred to...
TRANSCRIPT
EDB Postgres™ Replication Server Reference Guide
EDB Postgres™ Replication Server 7
Limited Availability v2 and Beta Program
Aug 8, 2019
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
2
EDB Postgres™ Replication Server Reference Guide by EnterpriseDB® Corporation
Copyright © 2019 EnterpriseDB Corporation. All rights reserved.
EnterpriseDB Corporation, 34 Crosby Drive, Suite 201, Bedford, MA 01730, USA
T +1 781 357 3390 F +1 978 467 1307 E [email protected] www.enterprisedb.com
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
3
Table of Contents
1 Introduction .................................................................................................... 7
1.1 Typographical Conventions Used in this Guide ................................................. 7
1.2 Other Conventions Used in this Guide ............................................................... 8
2 EDB Replication Server Command Line Interface ......................................... 9
2.1 Replication Server Startup Script........................................................................ 9
2.2 Starting Replication Server using systemctl ..................................................... 10
2.3 Stopping Replication Server using systemctl ................................................... 10
2.4 RepCLI Commands .......................................................................................... 11
Getting Help (help) ....................................................................................... 11 2.4.1
Printing the Version Number (version)......................................................... 13 2.4.2
Encrypting Passwords (encrypt) ................................................................... 13 2.4.3
Joining the Network (joinnetwork) ............................................................... 14 2.4.4
Leaving the Network (leavenetwork) ........................................................... 15 2.4.5
Adding a Database (adddb) ........................................................................... 16 2.4.6
Removing a Database (removedb) ................................................................ 18 2.4.7
Creating a Publication (createpub)................................................................ 19 2.4.8
Joining a Publication (joinpub) ..................................................................... 20 2.4.9
2.4.9.1 Table Mapping ...................................................................................... 23
Leaving a Publication (leavepub) ................................................................. 25 2.4.10
Removing a Publication (removepub) .......................................................... 26 2.4.11
Adding Tables to a Publication (addtables) .................................................. 26 2.4.12
Removing Tables from a Publication (removetables) .................................. 28 2.4.13
Adding a Filter (addfilter) ............................................................................. 29 2.4.14
Enabling a Filter (enablefilter) ...................................................................... 30 2.4.15
Updating a Filter (updatefilter) ..................................................................... 31 2.4.16
Disabling a Filter (disablefilter) .................................................................... 33 2.4.17
Removing a Filter (removefilter) .................................................................. 34 2.4.18
Taking a Snapshot (startsnapshot) ................................................................ 34 2.4.19
Reloading a Snapshot .................................................................................... 36 2.4.20
Taking an Offline Snapshot .......................................................................... 37 2.4.21
2.4.21.1 Offline Snapshot Using pg_dump and pg_restore ............................ 38
2.4.21.2 Offline Snapshot Using pg_basebackup ........................................... 41
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
4
2.4.21.3 Offline Snapshot Using File System Level Backup ......................... 44
Checking the Snapshot Status (checksnapshot) ............................................ 44 2.4.22
Start Streaming (startstreaming) ................................................................... 46 2.4.23
Stop Streaming (stopstreaming) .................................................................... 46 2.4.24
Stopping a Replication Server with RepCLI ................................................ 47 2.4.25
2.4.25.1 Stopping a Replication Server with RepCLI using failover Switch . 48
Setting an Administrator Password (setadminpassword) ............................. 48 2.4.26
Creating a Role (createrole) .......................................................................... 49 2.4.27
Creating a User (createuser) .......................................................................... 50 2.4.28
Updating User Privileges (updateuser) ......................................................... 51 2.4.29
Updating User Password (updatepassword) ................................................. 52 2.4.30
List Replication Server (listserver) ............................................................... 53 2.4.31
List Databases (listdb)................................................................................... 54 2.4.32
List Publications (listpub) ............................................................................. 57 2.4.33
List Publication Tables (listpubtable) ........................................................... 59 2.4.34
List Consumers (listconsumer) ..................................................................... 60 2.4.35
List Conflicts (listconflicts) .......................................................................... 63 2.4.36
Display Replication Lag (replicationlag) ...................................................... 64 2.4.37
Display Replication Latency (replicationlatency) ........................................ 66 2.4.38
Adding Kafka ACL (addkafkaacl) ................................................................ 67 2.4.39
Removing Kafka ACL (removekafkaacl) ..................................................... 69 2.4.40
Listing Kafka ACL (listkafkaacl) ................................................................. 71 2.4.41
Listing events added to Kafka (listevents) .................................................... 72 2.4.42
Filtering list of events added to Kafka (listevents) ....................................... 75 2.4.43
3 Filtering ........................................................................................................ 77
4 Security ........................................................................................................ 82
4.1 REST Access Control List (ACL) Security ...................................................... 82
Password File for Non-Prompting of Password............................................ 83 4.1.1
Permissions and Roles .................................................................................. 86 4.1.2
4.1.2.1 Assigning Permissions .......................................................................... 88
4.1.2.2 Using Roles ........................................................................................... 91
4.2 TLS/SSL Security ............................................................................................. 93
Creating Keystores and Truststore for Self-Signed Certificates ................... 93 4.2.1
Enabling Server SSL Authentication and Encrypted Communication ......... 99 4.2.2
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
5
Enabling Client SSL Authentication and Encrypted Communication ........ 101 4.2.3
5 Monitoring .................................................................................................. 104
5.1 Metrics ............................................................................................................ 104
Broker Metrics ............................................................................................ 105 5.1.1
5.1.1.1 Under-Replicated Partitions ................................................................ 105
5.1.1.2 Active Controller Count ...................................................................... 106
5.1.1.3 Request Handler Average Idle Ratio .................................................. 106
5.1.1.4 All Topics Bytes In ............................................................................. 106
5.1.1.5 All Topics Bytes Out .......................................................................... 107
5.1.1.6 All Topics Messages In ....................................................................... 107
5.1.1.7 Partition Count .................................................................................... 107
5.1.1.8 Leader Count ....................................................................................... 107
5.1.1.9 Offline Partition Count ....................................................................... 108
Topic and Partition Metrics ........................................................................ 108 5.1.2
Client Metrics.............................................................................................. 108 5.1.3
5.1.3.1 Record Error Rate ............................................................................... 108
5.1.3.2 Request Latency Average ................................................................... 108
5.1.3.3 Fetch Latency Average ....................................................................... 109
Summary of Metrics ................................................................................... 109 5.1.4
5.2 Starting Monitoring ......................................................................................... 110
Allow Monitoring Apps to Sign in to Google Accounts ............................ 117 5.2.1
6 Miscellaneous Configuration ...................................................................... 120
6.1 Conflict Detection, Handling, and Recovery .................................................. 120
Primary Key Uniqueness ............................................................................ 120 6.1.1
6.1.1.1 Stop Conflict Resolution Policy ......................................................... 122
6.1.1.2 Skip Conflict Resolution Policy ......................................................... 125
6.1.1.3 Retry Conflict Resolution Policy ........................................................ 128
Foreign Key Constraint ............................................................................... 132 6.1.2
6.2 Setting a Replication Network in AWS Cloud Computing ............................ 137
Security Group Access ................................................................................ 138 6.2.1
Firewall Port Additions ............................................................................... 139 6.2.2
Referencing Host Locations in RepCLI Commands .................................. 139 6.2.3
7 Data Validator ............................................................................................ 142
7.1 Installation and Configuration ........................................................................ 143
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
6
7.2 Performing Data Validation ............................................................................ 146
8 Excluding User Transactions ..................................................................... 155
9 Troubleshooting ......................................................................................... 157
9.1 Error Messages and Resolutions ..................................................................... 157
RepCLI Error Messages .............................................................................. 157 9.1.1
Unable to Register the Database ................................................................. 159 9.1.2
Unable to Establish Connection with the EDB Replication Server ............ 159 9.1.3
Connection Refused to the Database Server ............................................... 160 9.1.4
Unable to Connect to the Database Server ................................................. 160 9.1.5
Publication Does Not Exist on the Publication Server ............................... 161 9.1.6
Unable to Remove Database ....................................................................... 161 9.1.7
Unable to Add a Database Connection ....................................................... 161 9.1.8
Unable to Define Filter for Certain Datatypes ............................................ 161 9.1.9
Filter with the Same Name Already Exists on the Table/View .................. 162 9.1.10
Unable to Create Triggers for Publication Tables ...................................... 162 9.1.11
Problem with the Publish Process ............................................................... 162 9.1.12
Unable to Create Publication ...................................................................... 162 9.1.13
Unable to Create Publication Schema......................................................... 163 9.1.14
Replication Slot Unavailable on the Target Database ................................ 163 9.1.15
Error while Running EDB Replication Server as an OS Service ............... 164 9.1.16
9.2 Looking for Errors .......................................................................................... 165
9.3 Common Problem Checklist ........................................................................... 165
9.4 Troubleshooting Areas .................................................................................... 166
Java Runtime Errors .................................................................................... 166 9.4.1
9.4.1.1 Starting the Publication Server ........................................................... 166
10 Automatic Partitioning ................................................................................ 167
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
7
1 Introduction
This document provides more information for certain aspects of EDB Postgres
Replication Server, version 7. EDB Postgres Replication Server (referred to hereafter as
EDB Replication Server) is a replication streaming system available for PostgreSQL®
and for EDB Postgres™ Advanced Server. The latter will be referred to as Advanced
Server.
For general information about EDB Replication Server and basic instructions for
installing, configuring, and setting up a replication network, see the EDB Postgres
Replication Server Getting Started Guide.
This document is organized as follows:
Chapter 1 describes conventions used throughout this guide.
Chapter 2 lists the EDB Replication Server RepCLI commands.
Chapter 3 describes filtering.
Chapter 4 describes security.
Chapter 5 describes monitoring.
Chapter 6 describes additional configuration procedures.
1.1 Typographical Conventions Used in this Guide
Certain typographical conventions are used in this manual to clarify the meaning and
usage of various commands, statements, programs, examples, etc. This section provides a
summary of these conventions.
In the following descriptions, a term refers to any word or group of words that are
language keywords, user-supplied values, literals, etc. A term‟s exact meaning depends
upon the context in which it is used.
Italic font introduces a new term, typically, in the sentence that defines it for the
first time.
Fixed-width (mono-spaced) font is used for terms that must be given
literally such as SQL commands, specific table and column names used in the
examples, programming language keywords, etc. For example, SELECT * FROM emp;
Italic fixed-width font is used for terms for which the user must
substitute values in actual usage. For example, DELETE FROM table_name;
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
8
A vertical pipe | denotes a choice between the terms on either side of the pipe. A
vertical pipe is used to separate two or more alternative terms within square
brackets (optional choices) or braces (one mandatory choice).
Square brackets [ ] denote that one or none of the enclosed terms may be
substituted. For example, [ a | b ] means choose one of “a” or “b” or neither
of the two.
Braces {} denote that exactly one of the enclosed alternatives must be specified.
For example, { a | b } means exactly one of “a” or “b” must be specified.
Ellipses ... denote that the preceding term may be repeated. For example, [ a |
b ] ... means that you may have the sequence, “b a a b a”.
1.2 Other Conventions Used in this Guide
The following is a list of other conventions used throughout this document.
This guide applies to Linux systems. Directory paths are presented in the Linux
format with forward slashes. If supported on Windows systems, start the directory
path with the drive letter followed by a colon and substitute backslashes for
forward slashes.
Most of the information in this document applies to both the PostgreSQL and
EDB Postgres Advanced Server database systems. The term Advanced Server is
used to refer to EDB Postgres Advanced Server. The term Postgres is used to
generically refer to both PostgreSQL and Advanced Server. When a distinction
needs to be made between these two database systems, the specific names,
PostgreSQL or Advanced Server are used.
The installation directory path of the PostgreSQL or Advanced Server products is
referred to as POSTGRES_INSTALL_HOME. For PostgreSQL Linux installations,
this defaults to /opt/PostgreSQL/x.x for version 10 and earlier. For later
versions, use the PostgreSQL community packages. For Advanced Server Linux
installations accomplished using the interactive installer for version 10 and
earlier, this defaults to /opt/edb/asx.x. For Advanced Server Linux
installations accomplished using an RPM package, this defaults to
/usr/edb/asx.x. The product version number is represented by x.x or by xx
for version 10 and later.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
9
2 EDB Replication Server Command Line Interface
The procedure for using EDB Replication Server is by a command line interface run on
the Linux terminal.
There are two scripts that provide the following purposes:
runServer.sh. Setting up and starting a replication server on a host machine to
serve as a replication node
runRepCLI.sh. Creating, configuring, and maintaining a replication network that
provides the snapshot and data streaming between producer and consumer
databases
For information on creating the replication server using runServer.sh, see Section 2.1.
For information on the RepCLI commands for creating the replication network using
runRepCLI.sh, see Section 2.2.
2.1 Replication Server Startup Script
The replication server is a process running as a dedicated RESTful service on an HTTP
server that engages a Kafka broker and ZooKeeper.
As the root account, run the script from the EPRS_HOME/server/bin directory.
Synopsis
./runServer.sh --host host_ip_address
--config EPRS_HOME/server/etc
This must be the first script run on a host to be configured as a replication node.
Parameters
-h, --host host_ip_address
IP address of the host on which this script is being run to start the replication
server.
-c, --config EPRS_HOME/server/etc
The full directory path to the EPRS_HOME/server/etc directory. If you want to
have multiple replication servers running on the same host machine, you need to
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
10
make a copy of the EPRS_HOME/server directory and modify certain properties
files located in the EPRS_HOME/server/etc directory of the additional
replication server.
Note: The general format of the EPRS_HOME directory is /usr/edb/rs-x.x where x.x
is the EDB Replication Server version number, which is initially 7.0.
Example
The following example starts the replication server on host machine 192.168.2.27.
$ cd /usr/edb/rs-7.0/server/bin
$ ./runServer.sh --host 192.168.2.27 --config /usr/edb/rs-7.0/server/etc
.
.
.
logback: 2018-09-13 16:02:54,407 [Primary Embedded Server Thread] INFO
[c.e.n.r.s.EmbeddedRestServer] - EPRS server is started!
2.2 Starting Replication Server using systemctl
To start EDB Replication Server using systemctl use the following command:
Synopsis
systemctl start edb-rs-server.service
2.3 Stopping Replication Server using systemctl
You can stop the replication server in two ways failover mode and planned mode by
specifying the property shutdown.mode=failover in
EPRS_home/server/etc/application.properties. This property controls if
failover should happen or not. To skip failover, specify shutdown.mode=planned. The
default value is shutdown.mode=failover
Note: Make sure to change back the shutdown.mode to default after the server
shutdown is complete. If you keep the shutdown.mode=planned Edb Replication
Server will not failover in case of crash or Network failure.
Synopsis
systemctl stop edb-rs-server.service
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
11
2.4 RepCLI Commands
The EDB Replication Server RepCLI commands are invoked by the runRepCLI.sh
script located where the EDB Replication Server client package has been installed, which
is EPRS_HOME/client/bin.
Generally, RepCLI commands must be run from the EPRS_HOME/client/bin directory
on the host running the replication server as the leader service in order for the effect of
the RepCLI commands to be relevant to the replication network.
Alternatively, if the client package has been installed on a different host than where the
leader service is running, you can run the RepCLI commands from this separate host if
the client.properties file in the EPRS_HOME/client/etc directory is edited to
specify the host IP address and port of the leader service.
The following shows the content of the client.properties file:
# represents IP address of the leader service
leader.host=localhost
# represents port of leader service
leader.port=8082
The following sections summarize each RepCLI command.
Getting Help (help) 2.4.1
The help command provides a syntax summary of all RepCLI commands.
Synopsis
-help
Example=
$ ./runRepCLI.sh -help
Usage: java -jar edb-repcli.jar [COMMANDS]
*** Replication Cluster Setup ***
-joinnetwork -servername <server name> -host <host> -port <port> [-ngxpasspath <ngx
pass file path>] [-user <user>]
-adddb -dbid <database id> [-servername <server name>] -dbtype {enterprisedb |
postgresql} -dbhost <host> -dbport <port> -dbuser <user> {-dbpassword <encpassword> |
dbpassfile <file>} -database <database> [-ngxpasspath <ngx pass file path>] -user
<user>
-createpub -dbid <id> [-nodetype {W|RW}] -pubname <publication name> {-tables
<schema1>.<table1>[,<schema1>.<table2>,...] | -alltables [<schema1>,<schema2>,...]}
[-ngxpasspath <ngx pass file path>] -user <user>
-joinpub -servername <server name> -dbid <target database id> [-nodetype {R|W|RW}] -
pubname <publication name> [-tablenamemapping
<src_schema1>.<src_table1>=<target_schema1>.<target_table1>[,<src_schema1>.<src_table2>
=<target_schema1>.<target_table2>,...]] [-filtername <filter name>]
[-ngxpasspath <ngx pass file path>] -user <user>
-startsnapshot -pubname <publication name> -dbid <target node database id> [-offline]
[-reload][-ngxpasspath <ngx pass file path>] –user <user>
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
12
-startstreaming -pubname <publication name> [-ngxpasspath <ngx pass file path>] -user
<user>
-stopstreaming -pubname <publication name> [-ngxpasspath <ngx pass file path>] -user
<user>
-shutdown [-failover true | false] -user <user>
*** Filter Management ***
-addfilter <filtername> -filtertype R -pubname <pubname> -filtertable <table name> -
filterrule <filterrule> [-ngxpasspath <ngx pass file path>] -user <user>
-enablefilter <filtername> -pubname <pubname> -targetdbid <target database id> [-
ngxpasspath <ngx pass file path>] -user <user>
-updatefilter <filtername> -filtertype R -pubname <pubname> -filtertable <tablename> -
filterrule <filterrule> [-ngxpasspath <ngx pass file path>] -user <user>
-disablefilter <filtername> -pubname <pubname> -targetdbid <target database id> [-
ngxpasspath <ngx pass file path>] -user <user>
-removefilter <filtername> -pubname <pubname> [-ngxpasspath <ngx pass file path>] -user
<user>
*** Cluster Entities Listing ***
-listdb [-parentservername <parent server name>] [-ngxpasspath <ngx pass file path>] [-
format {plain | json}] -user <user>
-listpub [-parentdbid <parent database id>] [-ngxpasspath <ngx pass file path>] [-
format {plain | json}] -user <user>
-listpubtable -pubname <publication name> [-ngxpasspath <ngx pass file path>] [-format
{plain | json}] -user <user>
-listserver [-ngxpasspath <ngx pass file path>] [-format {plain | json}] -user <user>
-listconsumer [-ngxpasspath <ngx pass file path>] [-format {plain | json}] -user <user>
-listconflicts [-pubname <publication name>] [-ngxpasspath <ngx pass file path>] [-
format {plain | json}] -user <user>
-listevents [-severity {info | warn | error | debug | trace}] [-ngxpasspath <ngx pass
file path>] [-format {plain | json}] -user <user>
*** Replication Monitoring ***
-replicationlag {-pubs <pub1>[,<pub2>,...] | -allpubs} [-lagtype {bytes | time | all}]
[-ngxpasspath <ngx pass file path>] -user <user>
-replicationlatency -pubname <publication name> -dbid <target database id>
[-ngxpasspath <ngx pass file path>] -user <user>
*** User Management ***
-createuser <new user> [-roles <role1>[,<role2>,...]] [-permissions
<permission1>[,<permission2>,...]] [-savepassword] [-ngxpasspath <ngx pass file path>]
-user <user>
-updateuser <user> {-roles <role1>[,<role2>,...] | -permissions
<permission1>[,<permission2>,...]} [-ngxpasspath <ngx pass file path>] -user <user>
-createrole <role name> [-permissions <permission1>[,<permission2>,...]] [-ngxpasspath
<ngx pass file path>] -user <user>
-setadminpassword [-savepassword]
-updatepassword -username <user> [-savepassword] [-ngxpasspath <ngx pass file path>]
-user <user>
-encrypt -input <input file name> -output <output file name> -user <user>
*** Kafka ACL Management ***
-listkafkaacl -authorizerproperties zookeeper.connect=<zookeeper host>:<zookeeper port>
-topic <topic name> [-ngxpasspath <ngx pass file path>] -user <user>
-addkafkaacl -authorizerproperties zookeeper.connect=<zookeeper host>:<zookeeper port>
-allowprincipal User:CN=<common name>,OU=<organizational
unit>,O=<organization>,ST=<state or province name>,C=<country name> -allowhost
<host1>[,<host2>,...] -operation <operation1>[,<operation2>,...] -topic <topic name> [-
ngxpasspath <ngx pass file path>] -user <user>
-removekafkaacl -authorizerproperties zookeeper.connect=<zookeeper host>:<zookeeper
port> -allowprincipal User:CN=<common name>,OU=<organizational
unit>,O=<organization>,ST=<state or province name>,C=<country name> -allowhost
<host1>[,<host2>,...] -operation <operation1>[,<operation2>,...] -topic <topic name> -
force [-ngxpasspath <ngx pass file path>] -user <user>
*** Replication Cluster Cleanup ***
-leavepub -dbid <target database id> -pubname <publication name> [-ngxpasspath <ngx
pass file path>] -user <user>
-removepub -pubname <publication name> [-ngxpasspath <ngx pass file path>] -user <user>
-removedb [-servername <server name>] -dbid <database id> [-ngxpasspath <ngx pass file
path>] -user <user>
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
13
-leavenetwork -servername <server name> [-ngxpasspath <ngx pass file path>] -user
<user>
Printing the Version Number (version) 2.4.2
The version command provides the EDB Replication Server CLI‟s version number.
Synopsis
-version
Example
$ ./runRepCLI.sh -version
Version: 7.0.0-mvp
Encrypting Passwords (encrypt) 2.4.3
The encrypt command encrypts the text supplied in an input file and writes the
encrypted result to a specified output file. Use the encrypt command to generate an
encrypted password in a text file that can be referenced by the adddb command that
requires the database user password.
Note: The following permission is required for encrypt command:
Permission Purpose
encrypt Encrypt the password to generate the encrypted form of password
into an output text file.
Synopsis
-encrypt –input infile –output pwdfile –user username
Make sure that infile contains only the text that you want to encrypt and that there are
no extraneous characters or empty lines before the text or after the text that you want to
encrypt.
Parameters
infile
The file containing the text to be encrypted.
pwdfile
The file containing the encrypted form of the text.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
14
username
Replication user running the command.
Example
The following example creates an encrypted password file in the home directory of the
Linux user invoking the runRepCLI.sh script.
$ cat ~/infile
password
$ ./runRepCLI.sh -encrypt -input ~/infile -output ~/pwdfile –user admin
The encrypt command completed successfully.
File pwdfile contains the following:
$ cat ~/pwdfile
ygJ9AxoJEX854elcVIJPTw==
Joining the Network (joinnetwork) 2.4.4
The joinnetwork command adds the specified replication server to the replication
network.
Synopsis
-joinnetwork
-servername servername
-host host
-port port
[ -ngxpasspath directory ]
[ -user username ]
The joinnetwork command invoked for any replication server other than the first
replication server, which starts as the leader service, must be given with the -user
option.
Parameters
servername
Identifier name assigned to the replication server. This name must be unique
within the replication network.
host
The IP address of the host on which the replication server is running.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
15
port
Port of the replication server. The default is 8082.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command. This parameter is omitted when running
the joinnetwork command for the first time in a replication network when
creating the replication server initially to be used as the leader service but is
required for all subsequent usage of joinnetwork. username must be either
admin or a user with the join_network permission.
Example
The following example adds a replication server to be identified by the name
remoteService to the replication network.
$ ./runRepCLI.sh -joinnetwork -servername remoteService -host 192.168.2.29 \
> -port 8082 -user admin
Enter user password:
Server is successfully added in the network.
Leaving the Network (leavenetwork) 2.4.5
The leavenetwork command removes the specified replication server from the
replication network.
Synopsis
-leavenetwork
-servername servername
[ -ngxpasspath directory ]
-user username
Parameters
servername
The replication server to be removed from the network.
directory
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
16
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following is an example of leaving the network for a replication server after all
components of the replication server such as publications and databases have been
removed from the replication server.
$ ./runRepCLI.sh -leavenetwork -servername remoteService -user admin
Enter user password:
Server has successfully left the network.
Adding a Database (adddb) 2.4.6
The adddb command adds a database to a replication server.
Synopsis
-adddb
[ -servername servername ]
-dbid dbid
-dbtype { postgresql | enterprisedb }
-dbhost host
-dbport port
-dbuser user
{ -dbpassword encrypted_pwd | -dbpassfile pwdfile }
-database dbname
[ -ngxpasspath directory ]
-user username
The added database can participate in data replication with other databases that have been
added to replication servers within the same replication network.
Parameters
servername
The replication server to which the database is to be added. If omitted, the default
is the replication server currently acting as the leader service.
dbid
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
17
Specify a unique identifier for the database within the replication network. The
identifier can be any combination of alphanumeric characters.
-dbtype
Specify postgresql if the database is a PostgreSQL database. Specify
enterprisedb if it is an Advanced Server database.
host
The IP address of the host on which the database server is running.
port
The port number on which the database server is listening for connections.
user
The database username.
encrypted_pwd
The encrypted password of the database user. See Section 2.4.3 for encrypting the
password.
pwdfile
The file containing the encrypted password of the database user.
dbname
The database name.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example adds PostgreSQL database node1 to the localService
replication server.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
18
$ ./runRepCLI.sh -adddb -servername localService -dbid db1 \
> -dbtype postgresql -dbhost 192.168.2.27 -dbport 5432 -dbuser pubuser \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node1 -user admin
Enter user password:
Database is registered successfully.
Removing a Database (removedb) 2.4.7
The removedb command removes a database from a replication server.
Synopsis
-removedb
[ -servername servername ]
-dbid dbid
[ -ngxpasspath directory ]
-user username
Before using the removedb command, be sure all publications that had been created in
the database with the createpub command are first removed by using the removepub
command.
Use the leavepub command to disconnect the database from all publications it had been
joined to with the joinpub command.
Parameters
servername
The replication server from which the database is to be removed. If omitted, it is
assumed to be the replication server currently acting as the leader service.
dbid
The database identifier of the database to be removed.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
19
The following example removes the database identified by db1 from replication server
localService.
$ ./runRepCLI.sh -removedb -servername localService -dbid db1 -user admin
Enter user password:
Database is successfully removed from the cluster.
Creating a Publication (createpub) 2.4.8
The createpub command creates a new publication.
Synopsis
-createpub -pubname pubname
-servername servername
-dbid dbid
{ -tables schema_1.table_1[,schema_2.table_2 ]... |
-alltables [ schema_1][,schema_2 ]... }
[ -nodetype { W | RW } ]
[ -ngxpasspath directory ]
-user username
A publication is a set of one or more tables within a given database. The data in these
tables is what can be replicated amongst a group of databases by snapshot or streaming.
Creating a publication defines a unique name within the replication network for this
publication along with the database tables that compose the publication and the
replication server in which the publication is to be created.
This replication server becomes the storage center for the publication implemented as a
Kafka topic. Thus, changed data from the publication is streamed from this topic to other
databases that have been joined to this publication.
Parameters
pubname
Identifier name assigned to the publication.
servername
The replication server in which the publication is to be created.
dbid
The database identifier of the database containing the publication tables.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
20
schema_n.table_n
Comma-separated list of tables to be part of the publication. There must be no
space characters before or after the commas in the table list.
-alltables [ schema_1][,schema_2 ]...
Add all tables in the comma-separated list of schemas to the publication. If no
schemas are listed, then add all tables in the database to the publication. There
must be no space characters before or after the commas in the schema list.
-nodetype { W | RW }
Specify W if the database can only write to this publication, which makes it a
producer only. Specify RW if the database can also accept changed data from other
databases that have joined this publication. The latter makes this database both a
consumer and producer of the publication. If -nodetype is omitted, the default is
W.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Note: All the tables must have a primary key if they have to be replicated, otherwise it
will throw an error while creating a publication.
Example
The following example creates a publication containing two tables within a database that
acts as both a producer and consumer.
$ ./runRepCLI.sh –createpub -pubname deptemppub -servername localService \
> -nodetype RW -dbid db1 -tables public.dept,public.emp -user admin
Enter user password:
Publication is created successfully.
Joining a Publication (joinpub) 2.4.9
The joinpub command specifies a database that is to be a receiver of data from a
publication as well as possibly a contributor of changed data to that publication. After a
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
21
database has joined a publication, it can begin receiving and/or pushing its own changed
data to other databases joined to that publication.
Synopsis
-joinpub
-servername servername
-dbid dbid
-pubname pubname
[ -tablenamemapping
<src_schema1>.<src_table1>=<target_schema1>.<target_table1>
[,<src_schema1>.<src_table2>=<target_schema1>.<target_table2>
,...]]
[ –nodetype { R | W | RW } ]
[ -filtername filtername ]
[ -ngxpasspath directory ]
-user username
Typically, the joinpub command is used when creating the initial replication network
before snapshots are taken to all target databases and streaming has been started.
For an existing, active replication network where streaming replication is already in
process, it may be desired to add additional databases to the replication network.
Make sure the following steps are performed:
Add the database with the adddb command.
Join the publication with the joinpub command.
Take a snapshot to the target database with the startsnapshot command.
Confirm if the snapshot is replicated to the target node with the checksnapshot
command.
Rerun the startstreaming command.
Note that the startstreaming command must be re-executed even if streaming is
already running on the replication network.
Parameters
servername
The replication server from which the database is to receive changed data through
its connection.
dbid
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
22
The database identifier of the database that is to receive publication data and
possibly push its own changed data to other databases joined to that publication as
well depending upon the setting of the -nodetype option.
pubname
The publication from which data is to be received.
-tablenamemapping
The parameter that allows mapping of the source table with a different target table
name.
src_schema
Schema name of the source database.
src_table
Table name of the source database.
target_schema
Schema name of the target database.
target_table
Table name of the target database.
-nodetype { R | W | RW }
Specify R if the database can only read from this publication, which makes it a
consumer only. Specify W if the database can only write to this publication, which
makes it a producer only. Specify RW if the database can stream and receive
changed data to and from other databases that have joined this publication. The
latter makes this database both a consumer and producer of the publication. If -
nodetype is omitted, the default is R.
filtername
The publication filter that is to be enabled on this database joining the publication.
Thus, replication data from the publication database is to be filtered before being
applied to this database.
directory
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
23
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following is an example of joining a publication as both a producer and consumer
database.
$ ./runRepCLI.sh -joinpub -servername remoteService -nodetype RW -dbid db2 \
> -pubname deptemppub -user admin
Enter user password:
Node has successfully joined the Publication.
2.4.9.1 Table Mapping
The tablenamemapping parameter allows you to map the source table with a different
target table name. If the tablenamemapping option is not specified then EDB
Replication Server would assume that the source and target tables have the same name.
Example:
Consider that you have a single database that is used to enter the operational data (for
business transactions) in tables, and management also runs the reporting queries on these
tables (to get business insights). Usually, reporting queries are READ queries which
require tables to have proper indexing. But as tables are in use by operations as well to
run their daily business transactions, maintaining the same tables for WRITE and READ
operations is challenging. There is also a risk of slowing down the database response if
reporting queries are running simultaneously.
The tablenamemapping parameter is useful here as you can have a separate copy of
the same table having the same schema definition. As you cannot have two tables with
the same name, a copy of the table must have a different name. Thus, EDB Replication
Server provides tablenamemapping switch with the joinpub command to facilitate
replicating data from the source table to a target table in the same schema and database.
Synopsis
-joinpub
-servername servername
-dbid dbid
-pubname pubname
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
24
[ -tablenamemapping
<src_schema1>.<src_table1>=<target_schema1>.<target_table1>
[,<src_schema1>.<src_table2>=<target_schema1>.<target_table2>
,...]]
[ –nodetype { R | W | RW } ]
[ -filtername filtername ]
[ -ngxpasspath directory ]
-user username
Example:
The following is an example of two node cluster with the dept table in node1 and node2.
./runRepCLI.sh -joinnetwork -servername service1 -host 192.168.2.66 -port
8082
./runRepCLI.sh -setadminpassword -password edb -servername service1 -host
192.168.2.66 -port 8082 –savepassword
./runRepCLI.sh -joinnetwork -servername service2 -host 192.168.2.66 -port
8085 -user admin
./runRepCLI.sh -adddb -servername service1 -dbid centos.db.postgres -dbtype
postgresql -dbhost 192.168.2.66 -dbport 5432 -dbuser postgres -
dbsimplepassword edb -database node1 -user admin
./runRepCLI.sh -createpub -pubname cust_pub -servername service1 -dbid
centos.db.postgres -tables public.dept -user admin -nodetype RW
./runRepCLI.sh -adddb -servername service2 -dbid macos.db.postgres1 -dbtype
postgresql -dbhost 192.168.2.66 -dbport 5432 -dbuser postgres -
dbsimplepassword edb -database node2 -user admin
Join the publication using the tablenamemapping switch.
./runRepCLI.sh -joinpub -servername service2 -dbid macos.db.postgres1 -
pubname cust_pub -tablenamemapping public.dept=public.dept1 -user admin -
nodetype R
Enter user password:
Node has successfully joined the Publication.
./runRepCLI.sh -startsnapshot -pubname cust_pub -servername service2 -dbid
macos.db.postgres1 -user admin
Enter user password:
Data snapshot is performed successfully.
./runRepCLI.sh -startstreaming -pubname cust_pub -user admin
Enter user password:
Streaming is started successfully for the Publication cust_pub.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
25
Leaving a Publication (leavepub) 2.4.10
The leavepub command specifies a database that is no longer to be a receiver or a
contributor of data for a specified publication. The leavepub command cancels the
initial joinpub command that was used to join the database to the publication.
Synopsis
-leavepub
-dbid dbid
-pubname pubname
[ -ngxpasspath directory ]
-user username
Parameters
dbid
The database identifier of the database that is no longer to receive publication data
nor push its own changed data to other databases joined to that publication.
pubname
The publication from which data is no longer to be received.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following is an example of leaving a publication to which the database had been
previously joined with the joinpub command.
$ ./runRepCLI.sh -leavepub -dbid db2 -pubname jobhistpub -user admin
Enter user password:
Node has successfully left the Publication.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
26
Removing a Publication (removepub) 2.4.11
The removepub command removes a publication from the replication network. The
removepub command cancels the effect of the createpub command that was initially
used to create the publication.
Synopsis
-removepub -pubname pubname
[ -ngxpasspath directory ]
-user username
pubname
The publication to be removed.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following is an example of removing a publication.
$ ./runRepCLI.sh -removepub -pubname deptemppub -user admin
Enter user password:
Publication is removed successfully.
Adding Tables to a Publication (addtables) 2.4.12
To modify an existing publication by adding tables to a publication the addtables
command is used.
Synopsis
-addtables -pubname pubname
{-tables table_1,table_2 ...} [ -ngxpasspath directory ]
-user username
Parameters
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
27
Pubname
The publication to which the tables have to be added.
table_n
Comma-separated list of tables to be added to a publication. There should not be
any space before or after the comma in the table list.
directory
The directory containing the .ngxpass password file. If the option is omitted, the
.ngxpass file will be searched in the user‟s home directory or by setting the
environment variable NGXPASSPATH.
username
Replication user running the command.
The following applies when you use the addtables command:
To use addtables command you should have the create_pub permission.
To add multiple tables to a publication specify the name of those tables separated
by a comma.
When you add tables to a publication it also adds the snapshot related topics for
those tables.
When you add tables to a publication, streaming restarts (if streaming is in
progress).
Example
The following example adds tables to a publication:
$ ./runRepCLI.sh -addtables -pubname testpub -tables public.dept4 -
ngxpasspath /root/.ngxpass -user admin
Tables are added successfully.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
28
Removing Tables from a Publication (removetables) 2.4.13
To modify an existing publication by removing tables from a publication the
removetables command is used.
Synopsis
-removetables -pubname pubname
{-tables table_1,table_2 ...} [ -ngxpasspath directory ]
-user username
Parameters
Pubname
The publication from which the tables have to be removed.
table_n
Comma-separated list of tables to be removed from the publication. There should not be
any space before or after the comma in the table list.
directory
The directory containing the .ngxpass password file. If the option is omitted, the
.ngxpass file will be searched in the user‟s home directory or by setting the
environment variable NGXPASSPATH.
username
Replication user running the command.
The following applies when you use the removetables command:
To use the removetables command you should have the remove_pub
permission. o remove alltables specify all the tables name via comma separated
To remove multiple tables from a publication specify the name of those tables
separated by a comma.
When you remove the tables from the publication it also removes the snapshot
related topics for those tables.
When you remove the tables from the publication, then streaming restarts (if
streaming is in progress).
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
29
When you remove all the tables from a publication and start streaming, the
following message is displayed:
Publication pubname has no table.
Examples
The following example removes a table from a publication.
$ ./runRepCLI.sh -removetables -pubname deptpub –tables public.dept \
> –user admin
Tables are removed successfully.
Adding a Filter (addfilter) 2.4.14
The addfilter command creates a filter for a publication, which defines a selection
rule that rows must satisfy in order to be replicated to a target consumer database. When
the filter is enabled on a target database, the filtering is applied for both snapshots and
changed data streaming.
Note: Filters do not work for an offline snapshot as the data movement takes place
outside the replication server.
Synopsis
-addfilter filtername
-filtertype R
-pubname pubname
-filtertable filtertable
-filterrule "filterrule"
[ -ngxpasspath directory ]
-user username
Parameters
filtername
Identifier name assigned to the filter.
-filtertype
Specify R for row level filtering.
pubname
The publication containing the table to which the filter rule applies.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
30
filtertable
The publication table referenced in the filter rule.
filterrule
The row selection rule formatted as an SQL WHERE clause without the WHERE
keyword. Rows from snapshots or changed data streams that evaluate to true are
replicated to target databases that have enabled the filter. All other rows are not
replicated. Note: Enclose the filterrule text by double quotation marks
("filterrule").
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example creates a filter.
$ ./runRepCLI.sh -addfilter salesdept -filtertype R -pubname deptpub \
> -filtertable public.dept -filterrule "deptno=30" -user admin
Enter user password:
Filter is added successfully.
Enabling a Filter (enablefilter) 2.4.15
The enablefilter command applies and activates a filter on a target database.
Publication database rows from snapshots and changed data streams must then pass the
filter rule in order to be replicated to the target database.
Synopsis
-enablefilter filtername
-pubname pubname
-targetdbid target_dbid
[ -ngxpasspath directory ]
-user username
If the target database has been joined to the publication using the joinpub command
with the -filtername option, then that filter is already enabled on the database, and it
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
31
is not necessary to use the enablefilter command to activate it. See Section 2.4.9 for
the joinpub command.
A filter that has been enabled on the database can be disabled so the filter rule no longer
applies until the filter is enabled again. See Section 2.4.17 for the disablefilter
command.
Parameters
filtername
The filter to be enabled.
pubname
The publication containing the table to which the filter rule applies.
target_dbid
The database identifier of the target database on which to enable the filter.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example enables a filter.
$ ./runRepCLI.sh -enablefilter salesdept -pubname deptpub -targetdbid db2 \
> -user admin
Enter user password:
Updating a Filter (updatefilter) 2.4.16
The updatefilter command changes the attributes of an existing filter.
Synopsis
-updatefilter filtername
-filtertype R
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
32
-pubname pubname
-filtertable filtertable
-filterrule "filterrule"
[ -ngxpasspath directory ]
-user username
Parameters
filtername
The filter to be updated.
-filtertype
Specify R for row level filtering.
pubname
The publication containing the table to which the filter rule applies.
filtertable
The publication table referenced in the filter rule.
filterrule
The row selection rule formatted as an SQL WHERE clause without the WHERE
keyword. Rows from snapshots or changed data streams that evaluate to true are
replicated to target databases that have enabled the filter. All other rows are not
replicated. Note: Enclose the filterrule text by double quotation marks
("filterrule").
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example changes the filter rule to dname='SALES'.
$ ./runRepCLI.sh -updatefilter salesdept -filtertype R -pubname deptpub \
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
33
> -filtertable public.dept -filterrule "dname='SALES'" -user admin
Enter user password:
Disabling a Filter (disablefilter) 2.4.17
The disablefilter command deactivates a filter on the target database. Publication
database rows from snapshots and changed data streams are no longer required to pass
the filter selection rule in order to be replicated to the target database.
Synopsis
-disablefilter filtername
-pubname pubname
-targetdbid target_dbid
[ -ngxpasspath directory ]
-user username
Parameters
filtername
The filter to be disabled.
pubname
The publication containing the table to which the filter rule applies.
target_dbid
The database identifier of the target database on which the filter is to be disabled.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example disables the filter.
$ ./runRepCLI.sh -disablefilter salesdept -pubname deptpub -targetdbid db2 \
> -user admin
Enter user password:
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
34
Removing a Filter (removefilter) 2.4.18
The removefilter command removes the definition of a filter from the publication.
This filter can no longer be applied to any target database.
Synopsis
-removefilter filtername
-pubname pubname
[ -ngxpasspath directory ]
-user username
Parameters
filtername
The filter to be removed.
pubname
The publication containing the table to which the filter rule applied.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example removes the filter.
$ ./runRepCLI.sh -removefilter salesdept -pubname deptpub -user admin
Enter user password:
Taking a Snapshot (startsnapshot) 2.4.19
The startsnapshot command removes any existing rows from the publication tables
in the target database. It then loads the rows from the publication database into the tables
of the target database.
This provides the data consistency amongst the databases joined to the same publication
so that subsequent streaming of changed data remains consistent across the databases.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
35
Synopsis
-startsnapshot
-pubname pubname
[ -reload ]
-dbid target_dbid
[ -ngxpasspath directory ]
–user username
The following applies to usage of the startsnapshot command:
When a new target database is joined to a publication, the startsnapshot command
must first be executed against the target database before streaming is started.
Thus, when attempting to join a database to a publication when streaming is active, a
sequence of steps is shown in the following example where deptpub is the publication,
deptpub was created in localService, the database identifier is db2 of the target
database that has been added to remoteService, and admin is the user:
./runRepCLI.sh –joinpub -servername remoteService
-dbid db2 -pubname deptpub -user admin
./runRepCLI.sh –startsnapshot –pubname deptpub
-dbid db2 –user admin
./runRepCLI.sh –startstreaming –pubname deptpub –user admin
Parameters
pubname
The publication from which the snapshot is to be taken.
target_dbid
The database identifier of the target database, which is to receive the snapshot
data from the publication.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
36
The following is a sample execution of the startsnapshot command.
$ ./runRepCLI.sh -startsnapshot –pubname deptpub -dbid db2 -user admin
Enter user password:
Data snapshot is performed successfully.
Reloading a Snapshot 2.4.20
If you change the cluster configuration (remove a database, publication, and/or
subscription) after a snapshot operation, repeat the snapshot by including the reload
option. This is necessary as the Kafka queues (topics) are re-populated with a fresh copy
of data from the source database.
Stop streaming before you execute the startsnapshot command with the reload
option, otherwise, the operation will fail (an error message is logged in the server). Once
the snapshot is completed explicitly restart the streaming.
Note: The reload option does not work for an offline snapshot.
Run the following command from the EPRS_HOME/client/bin directory:
-startsnapshot -pubname pubname
-reload
-dbid target_dbid
[ -ngxpasspath directory ]
–user username
pubname
The publication from which the snapshot is taken.
target_dbid
The database identifier of the target database, which is to receive the snapshot
data from the publication.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
37
Example
The following is a sample execution of the startsnapshot command with the reload
option.
$ ./runRepCLI.sh -startsnapshot -pubname alltabpub3ngs -servername server3
-reload -dbid centos.db.postgres2 -user admin
Data snapshot has started successfully.
node3=# select * from dept;
deptno | dname | loc
--------------------------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
50 | QUALITY MANAGEMENT | BOSTON
60 | TECHNICAL WRITING | BOSTON
(6 rows)
Taking an Offline Snapshot 2.4.21
Online snapshot is generally suitable if the size of the publication database is small or
medium and the target database is co-located (within the same network). However, for a
large database with high network latency (in a WAN environment), the online snapshot
time increases significantly. Here, an offline snapshot (this is not a part of the EDB
Replication Server process) is a better option. Offline snapshot uses alternate options as
such PostgreSQL backup and restore utilities which reduces the overall time to take the
snapshot.
Note: Filters do not work for an offline snapshot as the data movement takes place
outside the replication server.
Following are the different ways of taking an offline snapshot in EDB Replication
Server:
Using pg_dump and pg_restore. This snapshot method is slow and is
recommended if the size of the source database is up to 100 GB. See section
2.4.21.1 for details.
Using pg_basebackup. This snapshot method is faster than the one using
pg_dump and pg_restore utility and is recommended if the size of the source
database size is up to 1 TB. See section 2.4.21.2 for more details.
Using file system level backup. This snapshot method is faster than the one using
pg_basebackup utility and is recommended if the source database size is over 1
TB. See section 2.4.21.3 for details.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
38
2.4.21.1 Offline Snapshot Using pg_dump and pg_restore
The pg_dump is a utility for backing up a single PostgreSQL database. It takes consistent
backups even if the database is being used concurrently. This snapshot method is slow
and is recommended if the source database size is up to 100 GB.
Follow the steps below for taking an offline snapshot in EDB Replication Server using
pg_dump and pg_restore utilities:
Step 1: To take a backup (only the schema) of the publication database and restore it on
the target database you can use any method that is convenient to you. Here, the pg_dump
utility is used to take a dump of the PostgreSQL publication database.
Run the following command from the
/opt/edb/database_server_installation_directory/bin directory:
./pg_dump -U postgres -p port -Ft -s source_database_name >
source_database_name_schemaonly.sql.tar
database_server_installation_directory
The directory where the database server is installed.
Source_database_name
The name of the database for which snapshot is to be taken.
port
The port number of the database server.
Note: If a partial set of tables are a part of the publication, take a partial backup (for the
selected tables with the –tables option).
Example:
In this example the following command is run from the /opt/edb/as10/bin directory
where Advanced Server 10 is running on port 5432.
$ ./pg_dump -U postgres -p 5432 -Ft -s db1 > db1_schemaonly.sql.tar
Step 2 (Optional): To restore the dump on the target database you can use any method
that is convenient for you. Here, the pg_restore utility is used to restore the dump on
the target PostgreSQL database as follows:
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
39
./pg_restore -U postgres -p port -d target_database_name
source_database_name_schemaonly.sql.tar
Source_database_name
The name of the database for which the snapshot is to be taken.
target_database_name
The name of the database on which the dump is to be restored.
port
The port number on which the database server is running.
Step 3: Create a publication.
Step 4: Join a publication on the target node.
Step 5: Take the snapshot (specify the offline parameter).
Run the following from the EPRS_HOME/client/bin directory:
./runRepCLI.sh –startsnapshot -pubname pubname -offline
-dbid target_dbid [ -ngxpasspath directory ] –user username
pubname
Name of the publication from which the snapshot is to be taken.
target_dbid
The database identifier of the target database, which receives the snapshot data
from the publication.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Step 6: Run the checksnapshot command and note down the exported Snapshot Id.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
40
Example
The following is an example of the execution of the checksnapshot command.
$ ./runRepCLI.sh -checksnapshot –pubname deptpub -dbid db2 -user admin
Publication Id: ngxpub
Consumer DB Id: macos.db.node2
Snapshot Data Publish Status: Offline
Snapshot Data Import Status: Offline
Exported Snapshot Id: 0000000D-000000F7-1
Snapshot Start Time: 2018-12-12 20:36:27
Snapshot End Time: 2018-12-12 20:36:27
Step 7: Take a backup of the publication database (data only) using the exported
Snapshot Id. Skip control schema _ngx_rep_cluster from the backup.
Run the following command from the /opt/edb/
database_server_installation_directory/bin directory after replacing the
Snapshot_Id with the exported Snapshot Id from step 6:
./pg_dump -U postgres -p port -Ft -a --snapshot=Snapshot_Id -
-exclude-schema=_ngx_rep_cluster source_database_name >
source_database_name_dataonly.sql.tar
Source_database_name
The name of the database for which the snapshot is to be taken.
Snapshot_Id
Exported snapshot identifier for the snapshot.
port
The port number on which the database server is running.
Note: The control schema name is configurable. The default control schema name is _ngx_rep_cluster.
Step 8: Restore the database dump (data only) on the subscription database. To restore
the dump on the target database you can use any method that is convenient for you. Here,
the pg_restore utility is used to restore the dump on the target PostgreSQL database as
follows:
./pg_restore -U postgres -p port -d target_database_name
source_database_name_dataonly.sql.tar
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
41
target_database_name
The name of the database on which the dump is to be restored.
Step 9: Start streaming for the target database.
2.4.21.2 Offline Snapshot Using pg_basebackup
The pg_basebackup utility is used to take a backup of a running PostgreSQL database
cluster. The backup is taken over a regular PostgreSQL connection and uses the
replication protocol to create a binary copy of the data files located in the $PGDATA
directory of the server and tablespaces. The copy it creates is consistent. The files exactly
correspond to the state at the end of some particular transaction, but they are not portable.
The database cluster can only be restored in the same PostgreSQL (major version) on the
same operating system and architecture. pg_basebackup makes a binary copy of the
database cluster files while making sure that the system switches in and out of backup
mode automatically. Backups are always taken for the entire database cluster. It is not
possible to back up individual databases or database objects.
Note:
The connection must be made with a superuser or a user having replication
permissions. Also, pg_hba.conf must contain the IP address of the target host
where the pg_basebackup utility is running. See pg_basebackup documentation
for details.
For offline snapshot using pg_basebackup, you might get conflicting INSERT
statements during replication. Make sure to set insert.query.mode to UPSERT
in the EPRS_home/server/etc/application.properties configuration
file to skip any duplicate rows that show up in the snapshot as well as the CDC
changeset data. Any duplicates encountered after the Snapshot CDC changes will
not be recorded.
Once the offline snapshot is completed and the changes after the snapshot are
replicated properly revert insert.query.mode to INSERT (default value) to
record any genuine conflicts.
Follow the steps below for taking an offline snapshot in EDB Replication Server using
pg_basebackup utility:
Prerequisite:
The name of the source and target database should be the same.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
42
Set insert.query.mode to UPSERT in the application.properties file located at
EPRS_home/server/etc/application.properties.
Step 1: Create a publication.
Step 2: Join a publication to the target node.
Step 3: Take the snapshot (specify the offline parameter).
Example
$ ./runRepCLI.sh -startsnapshot –pubname deptpub -dbid db2 –offline -user
admin
This ensures that EDB Replication Server knows the point in time when the
startstreaming command starts streaming from the source database.
Step 4: Run the checksnapshot command.
Example
$ ./runRepCLI.sh -checksnapshot –pubname deptpub -dbid db2 -user admin
Publication Id: ngxpub
Consumer DB Id: macos.db.node2
Snapshot Data Publish Status: Offline
Snapshot Data Import Status: Offline
Exported Snapshot Id: 0000000D-000000F7-1
Snapshot Start Time: 2018-12-12 20:36:27
Snapshot End Time: 2018-12-12 20:36:27
Step 5: Take a backup of the database.
Run the following command from the
/opt/edb/database_server_installation_directory/bin directory:
./pg_basebackup -h dbserver_host -D target_dir
--checkpoint=fast --wal-method=stream -U username
-h dbserver_host
The IP address of the host on which the source database server is running.
-D target_dir
Directory to write the output to. pg_basebackup will create the directory and any
parent directories if required.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
43
-U username
User name to connect as.
./pg_basebackup -h 172.16.254.138 -D /tmp/backup -c fast -X stream -U
enterprisedb
This creates a database data directory in a consistent state as it will open a second stream
fetching the WAL which is created during the backup (to include transactions since the
backup started).
Example
In the following example, backup is taken from the database db1 to db2 (EDB Postgres
Advanced Server v. 11 with the data directory /var/lib/edb/as11/data).
The steps for taking the backup are as follows:
1. Stop the database server db2.
2. Run the utility pg_basebackup with the user as enterprisedb on the host of
server db2 to backup data from server db1:
$ pg_basebackup -h dbserver_host -D /var/lib/edb/as11/data_db1 -c fast -X
stream -U enterprisedb
3. Replace the data directory of server db2 by renaming data_db1 to data
directory (on the host of server db2).
$ cd /var/lib/edb/as11
$ mv data data.orig
$ mv data_db1 data
Note:
Before you replace the target database data directory make sure to take a
backup of the entire target database data directory including the
configuration files. Also, take a backup of content of tables
_ngx_rep_cluster.topic_offset and
_ngx_rep_cluster.events_queue tables in _ngx_rep_cluster
schema.
Once you place the base backup folder on the target database server, the
original configuration files must be placed in this folder otherwise, the
server configurations will be lost. Also, the ownership for the backed up
data directory should be the same as the original data directory. You can
use pg_dump to backup these tables before using pg_basebackup and
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
44
(after replacing the data directory on DB2 with the data backed up from
DB1 and starting DB2 server) use pg_restore to restore the content of
the tables from _ngx_rep_cluster.
4. Start the database server db2.
Step 6: Start streaming for the target database.
$ ./runRepCLI.sh -startstreaming -pubname deptpub -dbid db2 -user admin
Streaming starts from the point in time when the startsnapshot command is executed
and replicates all the changes that are not a part of the backed up data.
2.4.21.3 Offline Snapshot Using File System Level Backup
File system level backup works even while the database server is running. This snapshot
method is faster than the one using pg_dump and pg_restore and is recommended if
the source database size is over 1 TB. For more information refer to the Postgres
documentation for File System Level Backup.
An alternative file system backup approach is to take a consistent snapshot of the data
directory (if the file system supports that functionality) although it has some limitations.
If the database is spread across multiple file systems you might not be able to obtain
simultaneous frozen snapshots for all the volumes. For example, if your data files and
WAL log are on different disks, or if the tablespaces are on different file systems you
might not be able to use snapshot backup because the snapshots must be simultaneous.
The steps for offline snapshot using file system level backup are similar to offline
snapshot using pg_basebackup except Step 5.
Checking the Snapshot Status (checksnapshot) 2.4.22
The checksnapshot command confirms if the data is replicated to the target node.
Synopsis
-checksnapshot -pubname pubname
-dbid target_dbid
[ -ngxpasspath directory ]
–user username
Run the checksnapshot command without first running startsnapshot command.
The status should be pending (for Data Publishing as well as Data Import).
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
45
Now run the startsnapshot command after the checksnapshot command (after a
delay of few seconds) to confirm if data is replicated to target node. The status should be
completed (for Data Publishing as well as Data Import)
Parameters
pubname
The publication from which the snapshot is to be taken.
target_dbid
The database identifier of the target database, which is to receive the snapshot
data from the publication.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following is a sample execution of the checksnapshot command.
$ ./runRepCLI.sh -checksnapshot –pubname deptpub -dbid db2 -user admin
Snapshot Data Publish Status: Completed
Snapshot Data Import Status: Completed
The following is a sample execution of the checksnapshot command for an offline
snapshot.
$ ./runRepCLI.sh -startsnapshot –pubname deptpub –offline –servername server2
-dbid db2 -user admin
$ ./runRepCLI.sh -checksnapshot –pubname deptpub -dbid db2 -user admin
Publication Id: ngxpub
Consumer DB Id: macos.db.node2
Snapshot Data Publish Status: Offline
Snapshot Data Import Status: Offline
Exported Snapshot Id: 0000000D-000000F7-1
Snapshot Start Time: 2018-12-12 20:36:27
Snapshot End Time: 2018-12-12 20:36:27
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
46
Start Streaming (startstreaming) 2.4.23
The startstreaming command begins the streaming of changed data from the
publication to databases that are joined to the publication.
Synopsis
-startstreaming -pubname pubname [-dbid target_dbid ]
[ -ngxpasspath directory ]
-user username
Parameters
pubname
The publication that is the source of the data streaming.
target_dbid
The database identifier of the target database, which is to receive the snapshot
data from the publication.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example starts the streaming for a publication.
$ ./runRepCLI.sh -startstreaming –pubname deptpub -user admin
Enter user password:
Streaming is started successfully for the Publication deptpub.
Stop Streaming (stopstreaming) 2.4.24
The stopstreaming command stops the streaming of changed data from the specified
publication.
Synopsis
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
47
-stopstreaming -pubname pubname [-dbid target_dbid ]
[ -ngxpasspath directory ]
-user username
Parameters
pubname
The publication from which to stop data streaming.
target_dbid
The database identifier of the target database, which is to receive the snapshot
data from the publication.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example stops the streaming for a publication.
$ ./runRepCLI.sh -stopstreaming –pubname emppub -user admin
Enter user password:
Streaming is stopped successfully for the given Publication.
Stopping a Replication Server with RepCLI 2.4.25
The command to shut down EDB Replication Server is as follows:
Synopsis
./runRepCLI -shutdown [-failover true | false] -user <user>
Example:
./runRepCLI -shutdown -user admin
In absence of the switch -failover or -failover set to false failover will not
happen.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
48
Once you restart the server streaming is continued on the same server.
2.4.25.1 Stopping a Replication Server with RepCLI using failover
Switch
If any of the EDB Replication Servers is shut down, due to the failover mechanism one of
the other servers in cluster acts a leader service.
Synopsis
./runRepCLI -shutdown [-failover true | false] -user <user>
Example:
./runRepCLI -shutdown -failover true -user admin
With the failover switch set to true for a three-node cluster if server 3 goes down a
similar error message will be displayed on server 1 and server 2.
Broker 1003 is down, a failover routine will be attempted to
choose next available broker.
Also streaming is continued on the next available server.
Note:
If you shut down the leader server you will have to select any other server as the
leader to run any RepCLI command. Otherwise, you may give the RepCLI commands
only after restarting the leader server.
You should have shut_down_server permission to use this command.
Setting an Administrator Password (setadminpassword) 2.4.26
The setadminpassword command sets an administrator password.
A prompt then appears for the admin user password. The password must then be used for
all subsequent RepCLI commands when -user admin is specified stating that admin is
the user running the command.
Synopsis
-setadminpassword [ -savepassword ]
Parameters
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
49
-savepassword
Save the given password in the .ngxpass file.
Example
The following is an example of setting the password.
$ ./runRepCLI.sh -setadminpassword
Enter admin password:
Admin password is set successfully.
Creating a Role (createrole) 2.4.27
The createrole command creates a role with a set of permissions. When a user is
assigned a role, the user then has the permissions of that role.
Synopsis
-createrole role_name
[ -permissions permission[,...] ]
[ -ngxpasspath directory ]
-user username
Parameters
role_name
The name to be assigned to the role.
permission
The permission to be assigned to the role. Multiple permissions can be listed
separated by commas with no space characters before or after the comma.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
50
The following example creates a role with various permissions for creating a replication
network:
$ ./runRepCLI.sh -createrole repsrvrmaker \
> -permissions join_network,add_db,create_pub,join_pub,start_snapshot,check_snapshot,
start_streaming \
> -user admin
Enter user password:
Role is created successfully.
Creating a User (createuser) 2.4.28
The createuser command creates a new username to be specified by the -user
parameter when the user wishes to run a RepCLI command.
Synopsis
-createuser newusername
[ -permissions permission[,...] ]
[ -roles role[,...] ]
[ -savepassword ]
[ -ngxpasspath directory ]
-user username
Parameters
newusername
The new username to be created for a user.
permission
The permission to be assigned to the new username. Multiple permissions can be
listed separated by commas with no space characters before or after the comma.
role
The role to be assigned to the new username. Multiple roles can be listed
separated by commas with no space characters before or after the comma.
-savepassword
Save the given password in the .ngxpass file.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
51
username
Replication user running the command.
Example
The following creates user smith with privileges from role repsrvrmaker.
$ ./runRepCLI.sh -createuser smith -roles repsrvrmaker -user admin
Enter user password:
Enter password for new user:
User is created successfully.
Updating User Privileges (updateuser) 2.4.29
The updateuser command updates the permissions or roles for the specified username.
Synopsis
-updateuser username_privilege
[ -permissions permission[,...] ]
[ -roles role[,...] ]
[ -ngxpasspath directory ]
-user username
Parameters
username_privilege
The username to be updated with permissions or roles.
permission
The permission to be assigned to the username. Multiple permissions can be listed
separated by commas with no space characters before or after the comma.
role
The role to be assigned to the username. Multiple roles can be listed separated by
commas with no space characters before or after the comma.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
52
Replication user running the command.
Example
The following is an example of updating permissions of a user named streamer to
access publication deptpub with the pub_deptpub permission.
$ ./runRepCLI.sh -updateuser streamer -permissions pub_deptpub \
> -user usermanager
Enter user password:
User is updated successfully.
Updating User Password (updatepassword) 2.4.30
The updatepassword command updates the password for the specified user.
Synopsis
-updatepassword
-username user_for_update
[ -savepassword ]
[ -ngxpasspath directory ]
-user username
Parameters
user_for_update
The user to be updated with a password.
-savepassword
Save the given password in the .ngxpass file.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following is an example of updating the password of user pubcreator.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
53
$ ./runRepCLI.sh -updatepassword -username pubcreator -user admin
Enter user password:
Enter new password:
List Replication Server (listserver) 2.4.31
The listserver command displays information about the replication servers in the
replication network including the status of the EDB Replication Server (Running,
Stopped or Not Accessible). The output can be displayed in plain text and JSON formats
with the format parameter.
Note: If the parameter format is omitted the output is displayed in plain text format.
Synopsis
-listserver
[-format {plain | json}]
[ -ngxpasspath directory ]
–user username
Parameters
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example 1:
The following example displays the information of an MMR setup with three replication
servers running in the replication network in plain text format. The status of the servers
on node 1 and node 3 is Running whereas the status of the server on node 2 is Stopped.
$ ./runRepCLI.sh -listserver -user admin
Enter user password:
--- 1. node1Service ---
Server Id=node1Service
Server Host=172.19.11.110
Server Port=8082
Server Status=Running
--- 2. node2Service ---
Server Id=node2Service
Server Host=172.19.11.170
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
54
Server Port=8082
Server Status=Stopped
--- 3. node3Service ---
Server Id=node3Service
Server Host=172.19.11.111
Server Port=8082
Server Status=Running
Example 2: The following example displays the information of an MMR setup with
three replication servers in plain text format. The status of the servers on node 1 and node
3 is Running whereas the status of the server on node 2 is Not Accessible.
[root@localhost bin]# ./runRepCLI.sh -listserver -user admin
Enter user password:
--- 1. node1Service ---
Server Id=node1Service
Server Host=172.19.11.110
Server Port=8082
Server Status=Running
--- 2. node2Service ---
Server Id=node2Service
Server Host=172.19.11.170
Server Port=8082
Server Status=Not Accessible
--- 3. node3Service ---
Server Id=node3Service
Server Host=172.19.11.111
Server Port=8082
Server Status=Running
Example 3: The following example displays the information of an MMR setup with
three replication servers in JSON format.
[root@localhost bin]# ./runRepCLI.sh -listserver -format json -user admin
Enter user password:
{"node1Service":{"Server Id":"node1Service","Server
Host":"172.19.11.110","Server Port":8082,"Server
Status":"Running"},"node2Service":{"Server Id":"node2Service","Server
Host":"172.19.11.170","Server Port":8082,"Server
Status":"Stopped"},"node3Service":{"Server Id":"node3Service","Server
Host":"172.19.11.111","Server Port":8082,"Server Status":"Running"}}
List Databases (listdb) 2.4.32
The listdb command displays information about the databases added to replication
servers. The output can be displayed in plain text and JSON formats with the format
parameter.
Note: If the parameter format is omitted the output is displayed in plain text format.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
55
Synopsis
-listdb
[-format {plain | json}]
[ -parentservername parentservername ]
[ -ngxpasspath directory ]
–user username
Parameters
parentservername
The replication server of the databases for which information is to be displayed.
These are the databases that were added to this replication server with the adddb
command. If this option is omitted, then all databases in the replication network
are displayed.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example lists the database that was added to the remoteService1
replication server.
$ ./runRepCLI.sh -listdb -parentservername remoteService1 \
> -user admin
Enter user password:
--- 1. db2 ---
DB Id=db2
DB Name=node2
DB Type=postgresql
DB Host=192.168.2.28
DB Port=5432
DB User=postgres
Parent Server Id=remoteService1
The following example lists all databases in the replication network.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
56
$ ./runRepCLI.sh -listdb -user admin
Enter user password:
--- 1. db1 ---
DB Id=db1
DB Name=node1
DB Type=postgresql
DB Host=192.168.2.27
DB Port=5432
DB User=postgres
Parent Server Id=localService
--- 2. db3 ---
DB Id=db3
DB Name=node3
DB Type=postgresql
DB Host=192.168.2.29
DB Port=5432
DB User=postgres
Parent Server Id=remoteService2
--- 3. db2 ---
DB Id=db2
DB Name=node2
DB Type=postgresql
DB Host=192.168.2.28
DB Port=5432
DB User=postgres
Parent Server Id=remoteService1
The following example lists all databases in the replication network in plain text format.
[root@localhost bin]# ./runRepCLI.sh -listdb -format plain -user admin
Enter user password:
--- 1. db1 ---
DB Id=db1
DB Name=node1
DB Type=enterprisedb
DB Host=172.16.254.247
DB Port=5446
DB User=enterprisedb
Parent Server Id=localService
--- 2. db2 ---
DB Id=db2
DB Name=node2
DB Type=enterprisedb
DB Host=172.16.254.247
DB Port=5445
DB User=enterprisedb
Parent Server Id=localService
The following example lists all databases in the replication network in JSON format.
[root@localhost bin]# ./runRepCLI.sh -listdb -format JSON -user admin
Enter user password:
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
57
{"db1":{"DB Id":"db1","DB Name":"node1","DB Type":"enterprisedb","DB
Host":"172.16.254.247","DB Port":5446,"DB User":"enterprisedb","Parent Server
Id":"localService"},"db2":{"DB Id":"db2","DB Name":"node2","DB
Type":"enterprisedb","DB Host":"172.16.254.247","DB Port":5445,"DB
User":"enterprisedb","Parent Server Id":"localService"}
List Publications (listpub) 2.4.33
The listpub command displays information about the publications created in the
specified database. The output can be displayed in plain text and JSON formats by
specifying the format parameter.
Note: If the parameter format is omitted the output is displayed in plain text format.
Synopsis
-listpub
[-format {plain | json}]
[ -parentdbid parentdbid ]
[ -ngxpasspath directory ]
–user username
Parameters
parentdbid
The database identifier of the database containing the publications as created with
the createpub command whose information is to be displayed. If the option is
omitted, then all publications of the replication network are displayed.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example lists all publications in the replication network.
$ ./runRepCLI.sh -listpub -user admin
Enter user password:
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
58
--- 1. deptemppub ---
Publication Name=deptemppub
Parent Server Id=localService
Parent DB Id=db1
Table Count=2
Consumer Count=2
--- 2. jobhistpub ---
Publication Name=jobhistpub
Parent Server Id=localService
Parent DB Id=db1
Table Count=1
Consumer Count=2
The following example lists the publications created in the database identified by db1.
The result happens to be the same as in the previous example.
$ ./runRepCLI.sh -listpub -parentdbid db1 -user admin
Enter user password:
--- 1. deptemppub ---
Publication Name=deptemppub
Parent Server Id=localService
Parent DB Id=db1
Table Count=2
Consumer Count=2
--- 2. jobhistpub ---
Publication Name=jobhistpub
Parent Server Id=localService
Parent DB Id=db1
Table Count=1
Consumer Count=2
The following example attempts to list the publications created in the database identified
by db2. In this example, there are no such publications.
$ ./runRepCLI.sh -listpub -parentdbid db2 -user admin
Enter user password:
No Publication is defined for the given parent database db2 in the
replication network.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
59
The following example lists all publications in the replication network in plain text
format.
[root@localhost bin]# ./runRepCLI.sh -listpub -format plain -user admin
Enter user password:
--- 1. deptpub ---
Publication Name=deptpub
Parent Server Id=localService
Parent DB Id=db1
Table Count=1
Consumer Count=2
The following example lists all publications in the replication network in JSON format.
[root@localhost bin]# ./runRepCLI.sh -listpub -format json -user admin
Enter user password:
{"deptpub":{"Publication Name":"deptpub","Parent Server
Id":"localService","Parent DB Id":"db1","Table Count":1,"Consumer Count":2}}
List Publication Tables (listpubtable) 2.4.34
The listpubtable command lists the tables in the specified publication. The output
can be displayed in plain text and JSON formats by specifying the format parameter.
Note: If the parameter format is omitted the output is displayed in plain text format.
Synopsis
-listpubtable -pubname pubname
[-format {plain | json}]
[ -ngxpasspath directory ]
-user username
pubname
The publication whose tables are to be listed.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
60
username
Replication user running the command.
Example
The following example lists the two tables in publication deptemppub.
$ ./runRepCLI.sh -listpubtable -pubname deptemppub -user admin
Enter user password:
--- deptemppub ---
1. public.dept
2. public.emp
The following example lists the tables in publication deptpub in plain text format.
[root@localhost bin]# ./runRepCLI.sh -listpubtable -pubname deptpub -format
plain -user admin
Enter user password:
--- deptpub ---
1. public.dept
The following example lists the tables in publication deptpub in JSON format.
[root@localhost bin]# ./runRepCLI.sh -listpubtable -pubname deptpub -format
json -user admin
Enter user password:
["public.dept"]
List Consumers (listconsumer) 2.4.35
The listconsumer command lists the consumer databases by publication in the
replication network. The output can be displayed in plain text and JSON formats by
specifying the format parameter.
Note: If the parameter format is omitted the output is displayed in plain text format.
Synopsis
-listconsumer
[-format {plain | json}]
[ -ngxpasspath directory ]
–user username
Parameters
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
61
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example lists the single consumer database of a publication in an SMR
type replication network.
$ ./runRepCLI.sh -listconsumer -user admin
Enter user password:
--- 1. deptpub_db2 ---
Consumer Id=deptpub_db2
Node Type=R
Publication Name=deptpub
Consumer DB Id=db2
The following example lists three consumer databases, each replicating to a single
publication in an MMR type replication network.
$ ./runRepCLI.sh -listconsumer -user admin
Enter user password:
--- 1. deptpub_db1 ---
Consumer Id=deptpub_db1
Node Type=RW
Publication Name=deptpub
Consumer DB Id=db1
--- 2. deptpub_db3 ---
Consumer Id=deptpub_db3
Node Type=RW
Publication Name=deptpub
Consumer DB Id=db3
--- 3. deptpub_db2 ---
Consumer Id=deptpub_db2
Node Type=RW
Publication Name=deptpub
Consumer DB Id=db2
The following example lists two consumer databases, each replicating to a pair of
publications in an MMR type replication network.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
62
$ ./runRepCLI.sh -listconsumer -user admin
Enter user password:
--- 1. deptemppub_db1 ---
Consumer Id=deptemppub_db1
Node Type=RW
Publication Name=deptemppub
Consumer DB Id=db1
--- 2. deptemppub_db2 ---
Consumer Id=deptemppub_db2
Node Type=RW
Publication Name=deptemppub
Consumer DB Id=db2
--- 3. jobhistpub_db1 ---
Consumer Id=jobhistpub_db1
Node Type=RW
Publication Name=jobhistpub
Consumer DB Id=db1
--- 4. jobhistpub_db2 ---
Consumer Id=jobhistpub_db2
Node Type=RW
Publication Name=jobhistpub
Consumer DB Id=db2
The following example lists consumer database in the replication network in plain text
format.
[root@localhost bin]# ./runRepCLI.sh -listconsumer -format plain -user admin
Enter user password:
--- 1. deptpub_db1 ---
Consumer Id=deptpub_db1
Node Type=RW
Publication Name=deptpub
Consumer DB Id=db1
--- 2. deptpub_db2 ---
Consumer Id=deptpub_db2
Node Type=RW
Publication Name=deptpub
Consumer DB Id=db2
The following example lists consumer database in the replication network in JSON
format.
[root@localhost bin]# ./runRepCLI.sh -listconsumer -format json -user admin
Enter user password:
{"deptpub_db1":{"Consumer Id":"deptpub_db1","Node Type":"RW","Publication
Name":"deptpub","Consumer DB Id":"db1"},"deptpub_db2":{"Consumer
Id":"deptpub_db2","Node Type":"RW","Publication Name":"deptpub","Consumer DB
Id":"db2"}}
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
63
List Conflicts (listconflicts) 2.4.36
The listconflicts command lists conflicts that have been detected. The output can
be displayed in plain text and JSON formats by specifying the format parameter.
Note: If the parameter format is omitted the output is displayed in plain text format.
Synopsis
-listconflicts
[-format {plain | json}]
[ -pubname pubname ]
[ -ngxpasspath directory ]
-user username
pubname
The publication whose tables have encountered a conflict. If omitted, table
conflicts of all publications are listed.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example shows the uniqueness conflict displayed.
$ ./runRepCLI.sh -listconflicts -pubname deptpub -user admin
Publication Table Source DB Target DB Conflict Time
Conflict Type Policy Details
=========== ===== ========= ========= =============
============= ====== =======
deptpub public.dept db1 db2 2018-11-26 10:33:27.775
unique-violation STOP insert into public.dept (loc, dname, deptno) values
('DENVER', 'HR', '50.0')
The following example lists the conflict in JSON format.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
64
[root@localhost bin]# ./runRepCLI.sh -listconflicts -format json -user admin
[{"publication":"testpub","conflictTimestamp":1560164929155,"sourceDB":"node2
.db","targetDB":"node1.db","conflictType":"unique-
violation","resolutionPolicy":"STOP","txRecord":{"lastTxRecord":true,"txTimes
tamp":1560166650050,"lsn":3001247592,"commitLSN":3001248616,"offest":0,"txId"
:32498,"sql":"insert into public.dept (loc, dname, deptno) values
('conflict', 'node2-11', '11.0')","topic":"testpub-
node2.db","tableName":"public.dept","conflictFlag":false}},{"publication":"te
stpub","conflictTimestamp":1560164929157,"sourceDB":"node2.db","targetDB":"no
de3.db","conflictType":"unique-
violation","resolutionPolicy":"STOP","txRecord":{"lastTxRecord":true,"txTimes
tamp":1560166650050,"lsn":3001247592,"commitLSN":3001248616,"offest":0,"txId"
:32498,"sql":"insert into public.dept (loc, dname, deptno) values
('conflict', 'node2-11', '11.0')","topic":"testpub-
node2.db","tableName":"public.dept","conflictFlag":false}},{"publication":"te
stpub","conflictTimestamp":1560164930069,"sourceDB":"node1.db","targetDB":"no
de2.db","conflictType":"unique-
violation","resolutionPolicy":"STOP","txRecord":{"lastTxRecord":true,"txTimes
tamp":1560166654187,"lsn":3001248672,"commitLSN":3001249696,"offest":5,"txId"
:32499,"sql":"insert into public.dept (loc, dname, deptno) values
('conflict', 'node1-11', '11.0')","topic":"testpub-
node1.db","tableName":"public.dept","conflictFlag":false}}]
Display Replication Lag (replicationlag) 2.4.37
The replicationlag command lists the size in bytes, the time of the last consumption,
and the Logical Sequence Number (LSN) of the last consumed WAL record by the
consumer database.
Synopsis
-replicationlag
{ -pubs pubname_1[,pubname_2 ]... | -allpubs }
[ -lagtype { bytes | time | all } ]
[ -ngxpasspath directory ]
-user username
The measurement of the lag options is based on the difference from when the WAL
record was produced by the producer database and when the changes are committed into
the consumer database.
Parameters
pubname_n
Comma-separated list of publications. There must be no space characters before
or after the commas in the publication list.
-allpubs
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
65
Display the replication lag for all publications.
-lagtype { bytes | time | all }
Specify bytes to display the replication lag in number of bytes. Specify time to
specify the last time of consumption and the time lag. Specify all to display both
sets of information. If -lagtype is omitted, the default is all.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following examples are from an MMR type replication network with three consumer
databases. The publication is within database db1 while the other two consumer
databases are db2 and db3.
Note: The displayed output format has been modified in order to see the results more
easily in this document.
The following example displays the size lag.
$ ./runRepCLI.sh -replicationlag -pubs deptpub -lagtype bytes -user admin
Enter user password:
Replication lag is calculated successfully.
Publication Id: deptpub
Source DB Id: db1
Source DB Current LSN: 0/38AD650
Consumer Id Size Lag (Bytes) Last Consumed LSN Streaming Status
=========== ================ ================= ================
db3 27367608 0/1E93D98 Active
db2 27219048 0/1EB81E8 Active
The following example displays the time lag.
$ ./runRepCLI.sh -replicationlag -pubs deptpub -lagtype time -user admin
Enter user password:
Replication lag is calculated successfully.
Publication Id: deptpub
Source DB Id: db1
Source DB Last Tx Time: 2018-09-26 09:24:46.562919-04
Consumer Id Time Lag Last Consumed Tx Time Streaming Status
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
66
=========== ======== ===================== ================
db3 -00:01:03.437081 2018-09-26 09:25:50.185 Active
db2 -00:01:19.437081 2018-09-26 09:26:06.088 Active
The final example displays both the size lag and time lag.
Note: The following output format has been significantly modified in order to see the
results more easily in this document.
$ ./runRepCLI.sh -replicationlag -allpubs -lagtype all -user admin
Enter user password:
Replication lag is calculated successfully.
Publication Id: deptpub
Source DB Id: db1
Source DB Current LSN: 0/38AD650
Source DB Last Tx Time: 2018-09-26 09:24:46.562919-04
Consumer Size Last Last Consumed Streaming
Id Lag (Bytes) Time Lag Consumed LSN Tx Time Status
======== =========== ================ ============ ===================== =======
db3 27367608 -00:01:03.437081 0/1E93D98 2018-09-26 09:25:50.185 Active
db2 27219048 -00:01:19.437081 0/1EB81E8 2018-09-26 09:26:06.088 Active
Display Replication Latency (replicationlatency) 2.4.38
The replicationlatency command provides an end-to-end database level latency for
the last transaction that is successfully replicated and applied on the target database.
Synopsis
-replicationlatency
-pubname pubname
-dbid target_dbid
[-ngxpasspath directory ]
-user username
Parameters
pubname
Name of the publication.
target_dbid
Database identifier of the target database.
directory
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
67
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following is an example of execution of the replicationlatency command.
$ ./runRepCLI.sh -replicationlatency -pubname alltabpub3ngs -dbid
centos.db.postgres -user admin
Publication Name: alltabpub3ngs
Target DB Id: centos.db.postgres
— 1. Source DB Id: centos.db.postgres2 Latency: 1.39 secs
— 2. Source DB Id: centos.db.postgres1 Latency: 1.222 secs
Note: The following permission is required for replicationlatency command:
Permission Purpose
replication_latency Determines the latency for the Replication Server.
Adding Kafka ACL (addkafkaacl) 2.4.39
The addkafkaacl command adds the ACL.
Synopsis
-addkafkaacl
-authorizerproperties
zookeeper.connect=zookeeper_host:zookeeper_port
-allowprincipal User:CN=common_name,
OU=organizational_unit,O=organization,
ST=state_or_province_name,C=country_name
-allowhost host_1[,host_2,... ]
-operation operation_1[,operation_2,... ]
-topic topic_name
[ -ngxpasspath directory ]
-user username
The -allowprincipal option represents an SSL user name, for example:
User:CN=127.0.0.1,OU=engineering,O=xyz pvt ltd1.,ST=MA,C=us
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
68
Comma-separated values are allowed for the options -authorizerproperties, -
allowhosts, and -operations.
Parameters
zookeeper_host
The IP address of the host on which ZooKeeper is running.
zookeeper_port
Port of ZooKeeper.
common_name
The common name of the user (that is, the domain).
organizational_unit
The name of the organizational unit.
organization
The name of the organization.
state_or_province_name
State of province of the organization.
country_name
Country of the organization
host_n
IP host address from where the Kafka ACL is invoked.
operation_n
Operation to be performed on the topic.
topic_name
Topic on which the operations are to be performed.
directory
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
69
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following example adds a Kafka ACL allowing principal SSL user represented by
common name 127.0.0.1 to perform operations Read and Write on topic testpub-
test_db1-public.jobhist from IP hosts 172.16.252.3 and 172.16.252.5.
$ ./runRepCLI.sh -addkafkaacl -authorizerproperties zookeeper.connect=localhost:2181 -
allowprincipal User:CN=127.0.0.1,OU=engineering,O=xyz pvt ltd1.,ST=MA,C=us -allowhost
172.16.252.3,172.16.252.5 -operation Read,Write -topic testpub-test_db1-public.jobhist
-user admin
Removing Kafka ACL (removekafkaacl) 2.4.40
The removekafkaacl command removes the ACL.
Synopsis
-removekafkaacl
-authorizerproperties
zookeeper.connect=zookeeper_host:zookeeper_port
-allowprincipal User:CN=common_name,
OU=organizational_unit,O=organization,
ST=state_or_province_name,C=country_name
-allowhost host_1[,host_2,... ]
-operation operation_1[,operation_2,... ]
-topic topic_name
-force
[ -ngxpasspath directory ]
-user username
The -allowprincipal option represents an SSL user name, for example:
User:CN=127.0.0.1,OU=engineering,O=xyz pvt ltd1.,ST=MA,C=us
Comma-separated values are allowed for the options -authorizerproperties, -
allowhosts, and -operations.
Parameters
zookeeper_host
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
70
The IP address of the host on which ZooKeeper is running.
zookeeper_port
Port of ZooKeeper.
common_name
The common name of the user (that is, the domain).
organizational_unit
The name of the organizational unit.
organization
The name of the organization.
state_or_province_name
State of province of the organization.
country_name
Country of the organization
host_n
IP host address from where the Kafka ACL is invoked.
operation_n
Operation to be performed on the topic.
topic_name
Topic on which the operations are to be performed.
-force
If specified, assume yes to all queries and do not prompt.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
71
username
Replication user running the command.
Example
The following example removes the ACLs for topic testpub-test_db1.
$ ./runRepCLI.sh -removekafkaacl -authorizerproperties zookeeper.connect=localhost:2181
-allowprincipal User:CN=127.0.0.1,OU=engineering,O=xyz pvt ltd1.,ST=MA,C=us -allowhost
172.16.252.3,172.16.252.5 -operation Describe,Read -topic testpub-test_db1 -force -user
admin
Listing Kafka ACL (listkafkaacl) 2.4.41
The listkafkaacl command lists the ACL.
Synopsis
-listkafkaacl
-authorizerproperties
zookeeper.connect=zookeeper_host:zookeeper_port
-topic topic_name
[ -ngxpasspath directory ]
-user username
Parameters
zookeeper_host
The IP address of the host on which ZooKeeper is running.
zookeeper_port
Port of ZooKeeper.
topic_name
Topic for which ACLs are to be listed.
directory
The directory containing the .ngxpass password file. If the option is omitted,
the .ngxpass file will be searched for in the user‟s home directory or by the
setting of environment variable NGXPASSPATH.
username
Replication user running the command.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
72
Example
The following example lists ACLs for topic testpub-test_db1.
$ ./runRepCLI.sh -listkafkaacl -authorizerproperties zookeeper.connect=localhost:2181 -
topic testpub-test_db1 -user admin
Listing events added to Kafka (listevents) 2.4.42
The following command lists the events added to Kafka (under topic). This enables you
to view the major events in the cluster, independent of the server in which the events
occur. The output can be displayed in plain text and JSON formats by specifying the
format parameter.
Note: If the parameter format is omitted the output is displayed in plain text format.
Enable adding of events in Kafka
To enable adding events in Kafka uncomment the following line in
EPRS_HOME/server/etc/logback.xml before you start the replication servers.
<! -- <appender-ref ref="KAFKA_APPENDER"/> -->
<appender-ref ref="KAFKA_APPENDER"/>
Synopsis
-listevents
[-format {plain | json}]
[ -ngxpasspath directory ]
-user username
Parameters
directory
The directory containing the .ngxpass password file. If the option is omitted, the
.ngxpass file will be searched for in the user‟s home directory or by the setting of
environment variable NGXPASSPATH.
username
Replication user running the command.
Example
The following is an example of listevents command with MMR (two servers) setup:
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
73
$ ./runRepCLI.sh -listevents -user admin
Total Events=12
--- Event # 1 ---
Log Message=Succesfully created topic _ngx_events
Level=INFO Node Name=firstService Creation Timestamp=2018-11-15
02:16:05.453
Event Type=Event topic created Event Source=NGX Server Setup
--- Event # 2 ---
Log Message=Created event topic producer
Level=INFO Node Name=firstService Creation Timestamp=2018-11-15
02:16:05.529
Event Type=Event Producer created Event Source=NGX Cluster Setup
--- Event # 3 ---
Log Message=The server firstService has been registered with the network.
Level=INFO Node Name=firstService Creation Timestamp=2018-11-15
02:16:06.092
Event Type=Node registered Event Source=NGX Cluster Setup
--- Event # 4 ---
Log Message=Admin password is set successfully.
Level=INFO Node Name=firstService Creation Timestamp=2018-11-15
02:16:08.185
Event Type=Admin password set Event Source=NGX Cluster Security
--- Event # 5 ---
Log Message=The server node1Service has been registered with the network.
Level=INFO Node Name=firstService Creation Timestamp=2018-11-15
02:16:53.43
Event Type=Node registered Event Source=NGX Cluster Setup
--- Event # 6 ---
Log Message=Database mac.db.nGen added successfully.
Level=INFO Node Name=firstService Creation Timestamp=2018-11-15
02:16:56.328
Event Type=Database added Event Source=NGX Cluster Setup
--- Event # 7 ---
Log Message=Database node1.db.nGen1 added successfully.
Level=INFO Node Name=firstService Creation Timestamp=2018-11-15
02:17:00.384
Event Type=Database added Event Source=NGX Cluster Setup
--- Event # 8 ---
Log Message=Publication testpub created successfully.
Level=INFO Node Name=firstService Creation Timestamp=2018-11-15
02:17:09.679
Event Type=Publication Created Event Source=NGX Cluster Setup
--- Event # 9 ---
Log Message=Publication testpub joined successfully.
Level=INFO Node Name=firstService Creation Timestamp=2018-11-15
02:17:18.372
Event Type=Publication joined Event Source=NGX Cluster Membership
--- Event # 10 ---
Log Message=Snapshot performed successfully for Publication testpub to target
database node1.db.nGen1.
Level=INFO Node Name=firstService Creation Timestamp=2018-11-15
02:17:21.91
Event Type=Snapshot completed Event Source=NGX Data Replication
--- Event # 11 ---
Log Message=Streaming started for target database mac.db.nGen.
Level=INFO Node Name=firstService Creation Timestamp=2018-11-15
02:17:44.181
Event Type=Streaming started Event Source=Publisher
--- Event # 12 ---
Log Message=Streaming is started successfully for the Publication testpub.
Level=INFO Node Name=firstService Creation Timestamp=2018-11-15
02:17:44.182
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
74
Event Type=Streaming started Event Source=NGX Data Replication
The following is an example of listevents command with the output in JSON format:
[root@localhost bin]# ./runRepCLI.sh -listevents -format json -user admin
[{"logMessage":"Broker /brokers/ids is added in the
network.","className":"com.edb.ngen.failover.NodeMonitor","logLevel":"INFO","
threadName":"Curator-TreeCache-
0","creationTimestamp":1560161483579,"eventSource":"Unknown Event
Source","eventType":"Unknown Event
Type","nodeName":"node1Service"},{"logMessage":"Broker /brokers/ids/1001 is
added in the
network.","className":"com.edb.ngen.failover.NodeMonitor","logLevel":"INFO","
threadName":"Curator-TreeCache-
0","creationTimestamp":1560161483580,"eventSource":"Unknown Event
Source","eventType":"Unknown Event
Type","nodeName":"node1Service"},{"logMessage":"Successfully created topic
_ngx_events","className":"com.edb.repserver.commands.CommandHandler","logLeve
l":"INFO","threadName":"qtp1139700454-
20","creationTimestamp":1560161484455,"eventSource":"NGX Server
Setup","eventType":"Event topic
created","nodeName":"node1Service"},{"logMessage":"Created event topic
producer","className":"com.edb.repserver.commands.CommandHandler","logLevel":
"INFO","threadName":"qtp1139700454-
20","creationTimestamp":1560161484747,"eventSource":"NGX Cluster
Setup","eventType":"Event producer
created","nodeName":"node1Service"},{"logMessage":"The server node1Service
has been registered with the
network.","className":"com.edb.ngen.rest.resource.extension.commands.Commands
Plugin","logLevel":"INFO","threadName":"qtp1139700454-
20","creationTimestamp":1560161485620,"eventSource":"NGX Cluster
Setup","eventType":"Node
registered","nodeName":"node1Service"},{"logMessage":"Admin password is set
successfully.","className":"com.edb.ngen.rest.resource.extension.commands.Com
mandsPlugin","logLevel":"INFO","threadName":"qtp1139700454-
16","creationTimestamp":1560161487185,"eventSource":"NGX Cluster
Security","eventType":"Admin password
set","nodeName":"node1Service"},{"logMessage":"Database node1.db added
successfully.","className":"com.edb.ngen.rest.resource.extension.commands.Com
mandsPlugin","logLevel":"INFO","threadName":"qtp1139700454-
20","creationTimestamp":1560161490345,"eventSource":"NGX Cluster
Setup","eventType":"Database
added","nodeName":"node1Service"},{"logMessage":"Publication testpub created
successfully.","className":"com.edb.ngen.rest.resource.extension.commands.Com
mandsPlugin","logLevel":"INFO","threadName":"qtp1139700454-
16","creationTimestamp":1560161494625,"eventSource":"NGX Cluster
Setup","eventType":"Publication
Created","nodeName":"node1Service"},{"logMessage":"Database node2.db added
successfully.","className":"com.edb.ngen.rest.resource.extension.commands.Com
mandsPlugin","logLevel":"INFO","threadName":"qtp1139700454-
20","creationTimestamp":1560161497820,"eventSource":"NGX Cluster
Setup","eventType":"Database
added","nodeName":"node1Service"},{"logMessage":"Publication testpub joined
successfully by the database id
node2.db.","className":"com.edb.ngen.rest.resource.extension.commands.Command
sPlugin","logLevel":"INFO","threadName":"qtp1139700454-
16","creationTimestamp":1560161504474,"eventSource":"NGX Cluster
Membership","eventType":"Publication
joined","nodeName":"node1Service"},{"logMessage":"Database node3.db added
successfully.","className":"com.edb.ngen.rest.resource.extension.commands.Com
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
75
mandsPlugin","logLevel":"INFO","threadName":"qtp1139700454-
20","creationTimestamp":1560161507460,"eventSource":"NGX Cluster
Setup","eventType":"Database
added","nodeName":"node1Service"},{"logMessage":"Publication testpub joined
successfully by the database id
node3.db.","className":"com.edb.ngen.rest.resource.extension.commands.Command
sPlugin","logLevel":"INFO","threadName":"qtp1139700454-
16","creationTimestamp":1560161516131,"eventSource":"NGX Cluster
Membership","eventType":"Publication
joined","nodeName":"node1Service"},{"logMessage":"Snapshot started
successfully for Publication testpub to target database
node2.db.","className":"com.edb.ngen.rest.resource.extension.commands.Command
sPlugin","logLevel":"INFO","threadName":"qtp1139700454-
20","creationTimestamp":1560161519668,"eventSource":"NGX Data
Replication","eventType":"Snapshot
started","nodeName":"node1Service"},{"logMessage":"Snapshot for testpub on
node2.db completed
successfully.","className":"com.edb.ngen.data.snapshot.SnapshotImporterTask",
"logLevel":"INFO","threadName":"pool-27-thread-
1","creationTimestamp":1560161521614,"eventSource":"Publisher","eventType":"S
napshot Completed","nodeName":"node1Service"},{"logMessage":"Snapshot started
successfully for Publication testpub to target database
node3.db.","className":"com.edb.ngen.rest.resource.extension.commands.Command
sPlugin","logLevel":"INFO","threadName":"qtp1139700454-
16","creationTimestamp":1560161523970,"eventSource":"NGX Data
Replication","eventType":"Snapshot
started","nodeName":"node1Service"},{"logMessage":"Snapshot for testpub on
node3.db completed
successfully.","className":"com.edb.ngen.data.snapshot.SnapshotImporterTask",
"logLevel":"INFO","threadName":"pool-27-thread-
1","creationTimestamp":1560161524476,"eventSource":"Publisher","eventType":"S
napshot Completed","nodeName":"node1Service"},{"logMessage":"Streaming
started for Publication testpub and target database id
node1.db.","className":"com.edb.repserver.commands.CommandHandler","logLevel"
:"INFO","threadName":"qtp1139700454-
20","creationTimestamp":1560161544618,"eventSource":"Publisher","eventType":"
Streaming started","nodeName":"node1Service"},{"logMessage":"Streaming
started for Publication testpub and target database id
node3.db.","className":"com.edb.repserver.commands.CommandHandler","logLevel"
:"INFO","threadName":"qtp1139700454-
20","creationTimestamp":1560161554979,"eventSource":"Publisher","eventType":"
Streaming started","nodeName":"node1Service"},{"logMessage":"Streaming
started for Publication testpub and target database id
node2.db.","className":"com.edb.repserver.commands.CommandHandler","logLevel"
:"INFO","threadName":"qtp1139700454-
20","creationTimestamp":1560161565500,"eventSource":"Publisher","eventType":"
Streaming started","nodeName":"node1Service"},{"logMessage":"Streaming is
started successfully for the Publication
testpub.","className":"com.edb.ngen.rest.resource.extension.commands.Commands
Plugin","logLevel":"INFO","threadName":"qtp1139700454-
20","creationTimestamp":1560161565500,"eventSource":"NGX Data
Replication","eventType":"Streaming started","nodeName":"node1Service"}]
Filtering list of events added to Kafka (listevents) 2.4.43
The following command filters the events added to Kafka (under topic) based on the
severity level. If the severity argument is not provided listevents displays all the
events.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
76
Synopsis
-listevents
[-severity {info | warn | error | debug | trace}]
[-format {plain | json}]
[ -ngxpasspath directory ]
-user username
Parameters
Severity
A filter which displays the events based on the option provided.
directory
The directory containing the .ngxpass password file. If the option is omitted, the
.ngxpass file will be searched for in the user‟s home directory or by the setting of
environment variable NGXPASSPATH.
username
Replication user running the command.
Note: For getting all the messages uncomment the following line in
EPRS_HOME/server/etc/logback.xml file.
<appender-ref ref="KAFKA_APPENDER"/>
Example
The following is an example of filtering listevents command with severity as error:
$ ./runRepCLI.sh -listevents –severity error -user admin
Total Events=1
--- Event # 1 ---
Log Message=Failed to remove Publication alltabpub3ngs. Please see the logs
for details.
Level=ERROR Node Name=leaderService Creation Timestamp=2018-12-
10 10:14:07.105
Event Type=Remove Publication failure Event Source=NGX Cluster Setup
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
77
3 Filtering
Filtering is the selection of data to be replicated from the publication tables of the
publication database to the target consumer databases that have joined the publication.
The type of filtering that is supported is row level filtering. The selection of rows to
replicate is based upon the column value content of the row. The filtering rule is given in
the format of an SQL WHERE clause.
The following RepCLI commands manage filtering:
addfilter – Defines the selection rule of a filter for a publication table (see
Section 2.4.14)
enablefilter – Applies the filter to the tables of a database that has joined the
publication (see Section 2.4.15)
updatefilter – Change the selection rule of an existing filter (see Section
2.4.16)
disablefilter – Disables the application of the filter on a database that was
previously enabled with the filter (see Section 2.4.17)
removefilter – Removes the filter from the publication, thus preventing its
usage on any database (see Section 2.4.18)
joinpub – When a database is to join a publication, an option can now be used to
immediately apply the publication filter to the database, thus eliminating the
requirement of using the enablefilter command for this database (see Section
2.4.9)
Filtering of rows occurs for both snapshots from the startsnapshot command and
changed data streaming implemented by the startstreaming command.
The following is an example of filtering on a table, which then has its changed data
streamed to two other databases.
Two filters are created on the publication table. One filter is set on one target database
while the other filter is set on the second target database so that each database receives a
different subset of changed data from the publication database.
On each of the three databases, node1, node2, and node3 create the public.emp table
definition:
CREATE TABLE public.emp (
empno NUMERIC(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr NUMERIC(4),
hiredate DATE,
sal NUMERIC(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
78
comm NUMERIC(7,2),
deptno NUMERIC(2)
);
ALTER TABLE public.emp REPLICA IDENTITY FULL;
The publication database node1 initially contains the following rows in the emp table:
node1=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+----------+------+------------+---------+--------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
(4 rows)
The other databases, node2 and node3, contain no rows in the emp table.
Start a replication server on the three hosts.
On the leader service to contain the publication database:
./runServer.sh --host 192.168.2.27 --config /usr/edb/rs-7.0/server/etc
On a second host:
./runServer.sh --host 192.168.2.28 --config /usr/edb/rs-7.0/server/etc
On the third host:
./runServer.sh --host 192.168.2.29 --config /usr/edb/rs-7.0/server/etc
Join the network for the leader service and set the administrator password:
$ ./runRepCLI.sh -joinnetwork -servername localService -host 192.168.2.27 -port 8082
Server is successfully added in the network.
$ ./runRepCLI.sh -setadminpassword
Enter admin password:
Admin password is set successfully.
Join the network for the two other replication servers:
$ ./runRepCLI.sh -joinnetwork -servername researchService -host 192.168.2.28 \
> -port 8082 -user admin
Enter user password:
Server is successfully added in the network.
$ ./runRepCLI.sh -joinnetwork -servername salesService -host 192.168.2.29 \
> -port 8082 -user admin
Enter user password:
Server is successfully added in the network.
Add the database to localService and create the publication:
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
79
$ ./runRepCLI.sh -adddb -servername localService -dbid db1 -dbtype postgresql \
> -dbhost 192.168.2.27 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node1 -user admin
Enter user password:
Database is registered successfully.
$ ./runRepCLI.sh -createpub -pubname emppub -servername localService -dbid db1 \
> -tables public.emp -user admin
Enter user password:
Publication is created successfully.
Now, create two filters for the public.emp table:
$ ./runRepCLI.sh -addfilter research -filtertype R -pubname emppub \
> -filtertable public.emp -filterrule "deptno=20" -user admin
Enter user password:
Filter is added successfully.
$ ./runRepCLI.sh -addfilter sales -filtertype R -pubname emppub \
> -filtertable public.emp -filterrule "deptno=30" -user admin
Enter user password:
Filter is added successfully.
Add the other databases to the other two replication servers:
$ ./runRepCLI.sh -adddb -servername researchService -dbid db2 -dbtype postgresql \
> -dbhost 192.168.2.28 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node2 -user admin
Enter user password:
Database is registered successfully.
$ ./runRepCLI.sh -adddb -servername salesService -dbid db3 -dbtype postgresql \
> -dbhost 192.168.2.29 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node3 -user admin
Enter user password:
Database is registered successfully.
Join the two databases to the publication. The -filtername option is used to
immediately enable the filters on the databases.
$ ./runRepCLI.sh -joinpub -servername researchService -dbid db2 -pubname emppub \
> -filtername research -user admin
Enter user password:
Node has successfully joined the Publication.
$ ./runRepCLI.sh -joinpub -servername salesService -dbid db3 -pubname emppub \
> -filtername sales -user admin
Enter user password:
Node has successfully joined the Publication.
Take an initial snapshot on the target databases.
$ ./runRepCLI.sh -startsnapshot -pubname emppub -dbid db2 -user admin
Enter user password:
Data snapshot is performed successfully.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
80
$ ./runRepCLI.sh -startsnapshot -pubname emppub -dbid db3 -user admin
Enter user password:
Data snapshot is performed successfully.
Confirm if the data is replicated to the target database.
$ ./runRepCLI.sh -checksnapshot -pubname emppub -dbid db2 -user admin
Snapshot Data Publish Status: Completed
Snapshot Data Import Status: Completed
After the snapshots, the target databases contain the filtered rows from the publication
database:
node2=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+---------+------+------------+---------+------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
(2 rows)
node3=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+----------+------+------------+---------+--------+--------
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
(2 rows)
Start the streaming.
$ ./runRepCLI.sh -startstreaming -pubname emppub -user admin
Enter user password:
Streaming is started successfully for the Publication emppub.
The following rows are inserted on the publication database:
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
node1=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
81
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10
(14 rows)
For database node2 on researchService with filter rule deptno=20, the filtered
stream results in the following:
node2=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+---------+------+------------+---------+------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20
(5 rows)
For database node3 on salesService with filter rule deptno=30, the filtered stream
results in the following:
node3=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+----------+------+------------+---------+---------+--------
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30
(6 rows)
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
82
4 Security
EDB Replication Server provides the fundamental aspects of security:
Authentication. Confirmation of the identity of the user requesting access to the
system
Authorization. Identifying the roles and privileges that the authenticated user has
been granted
Access Control. Determining if the user has the roles or privileges to access
requested objects
The user described in the previous bullet points is an EDB replication user created,
stored, and maintained by EDB Replication Server using RepCLI commands. This user is
not a Linux user account nor is it a Postgres database user.
In the remainder of this chapter, reference to a “user” means this replication user.
The implementation of security is accomplished using the following:
REST Security. Implemented using the facilities of JAX-RS 2.0 Jersey to
provide authentication, authorization, and access control.
Kafka Security. Provides TLS certificate-based authentication as specified as
part of the configuration of Kafka components. TLS provides encrypted
communications.
Section 4.1 describes the usage of REST security provided through the RepCLI
commands.
Section 4.2 describes the setup process for TLS certificate-based authentication.
4.1 REST Access Control List (ACL) Security
ACL security is implemented by the requirement that running any RepCLI command
must be done by specifying the user who is invoking that command with the required -
user option.
Those users must first be created with the user‟s password and any privileges given by
specific permissions or roles.
The following RepCLI commands manage ACL security:
setadminpassword – Sets the password for the administrator username admin.
This must be the next RepCLI command executed after the first joinnetwork
command that starts the leader service (see Section 2.4.26).
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
83
createrole – Defines a role based on the role based access control approach
whereby a role is granted a set of permissions. A user can then be assigned to one
or more such roles. This gives the user the permissions included in the roles (see
Section 2.4.27)
createuser – Creates a username to be specified with the -user parameter
when invoking a RepCLI command. The user can perform the RepCLI commands
to which privilege has been granted either by roles or permissions assigned to the
username either by the createuser command or by a subsequent updateuser
command (see Section 2.4.28).
updateuser – Update the permissions or roles assigned to a username (see
Section 2.4.29)
updatepassword – Update the password for a username created by the
createuser command (see Section 2.4.30)
By default, the user is prompted for a password before the RepCLI command can be
executed. In order to avoid password prompting, a password file can be used to supply
the user‟s password as described in the following section.
Password File for Non-Prompting of Password 4.1.1
Each RepCLI command requires specification of the -user option to identify the user
invoking the command. By default, a prompt is then made for the user‟s password.
This inhibits the usage of RepCLI commands in a non-interactive manner. To use
RepCLI commands without password prompting, the initial creation of the user allows
the storing of the password to a file by specifying the -savepassword option.
All subsequent invocation of RepCLI commands by this user then results in the search for
this user‟s password in the password file. If the proper information is found, then the
RepCLI command is executed. If the user‟s information is not found, then the user is
prompted for the password.
The password file has the following characteristics:
The password file is named .ngxpass and stores the passwords in plain text
format if the -savepassword option is specified.
The .ngxpass file is created and updated under the home directory of the Linux
root user who started the replication server.
The .ngxpass file has restricted access permitted only to the user and denied to
the public.
If subsequent RepCLI commands are to be invoked with the runRepCLI.sh
script by a Linux user account who is not the root user, then in order to avoid the
password prompt, the .ngxpass file must exist in this user‟s home directory.
Copy the .ngxpass file from the root home directory to this user‟s home
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
84
directory and then modify the .ngxpass file permissions to allow access only to
this user.
Each individual user password is specified on a separate line with the following format:
ngx_host:ngx_port:username:password
ngx_host
The IP address of the replication server
ngx_port
The port of the replication server
username
The username of the user as defined by the createuser command
password
The password of the user
The user can optionally specify the full path to the directory that contains the .ngxpass
file with the -ngxpasspath option when invoking a RepCLI command. The replication
server searches for the file in the given custom path.
If the file is not found in the custom path or if the -ngxpasspath option is not specified,
the replication server then checks for it in the directory given by the NGXPASSPATH
environment variable.
If the NGXPASSPATH environment variable is not set or if the file is not found under its
custom path, then the password file is searched for under the user‟s home directory.
If the .ngxpass file is not found, then the user is prompted for the password.
Thus, for ongoing simplicity in running RepCLI commands, the -savepassword option
should be specified when creating a user.
The following example shows the creation of the .ngxpass file.
Start the replication server on the host.
./runServer.sh --host 192.168.2.27 --config /usr/edb/rs-7.0/server/etc
Join the network.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
85
$ ./runRepCLI.sh -joinnetwork -servername localService -host 192.168.2.27 -port 8082
Server is successfully added in the network.
Set the administrator password and save it using the -savepassword option.
$ ./runRepCLI.sh -setadminpassword -savepassword
Enter admin password:
Admin password is set successfully.
In response to “Enter admin password”, the text „password‟ was entered. The password
file is created under the root user‟s home directory /root:
[root@localhost ~]# pwd
/root
[root@localhost ~]# ls -la | grep .ngxpass
-rw------- 1 root root 33 Sep 18 12:02 .ngxpass
[root@localhost ~]# cat .ngxpass
192.168.2.27:8082:admin:password
The createuser command is then executed with the -savepassword option for the
new user pubmanager.
$ ./runRepCLI.sh -createuser pubmanager -savepassword \
> -permissions add_db,create_pub,join_pub,start_snapshot,check_snapshot
\start_streaming,stop_streaming
> -user admin
Enter user password: (no text given)
Enter password for new user:
User is created successfully.
The /root/.ngxpass file now contains two usernames and their passwords:
192.168.2.27:8082:admin:password
192.168.2.27:8082:pubmanager:pubpass
Note: For the “Enter user password” prompt for the admin user, no text was given in
response. The admin password was obtained from the .ngxpass file.
The new user pubmanager now invokes a command without supplying a password as it
is also obtained from the .ngxpass file.
$ ./runRepCLI.sh -adddb -servername localService -dbid db1 -dbtype postgresql \
> -dbhost 192.168.2.27 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node1 -user pubmanager
Enter user password: (no text given)
Database is registered successfully.
Note: For the “Enter user password” prompt for the pubmanager user, no text was given
in response. The pubmanager password was obtained from the .ngxpass file.
Note: If the Linux user executing the runRepCLI.sh script is not the root user account
as in this example, then in order to avoid the password prompt for saved passwords copy
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
86
the /root/.ngxpass file to the home directory of the Linux user who is executing the
runRepCLI.sh script. Make sure the ownership and permission for the copied file, (for
example, /home/user/.ngxpass) is permitted only to the Linux user (as represented
by user) who is the executor of the runRepCLI.sh script.
All additional RepCLI commands invoked by user pubmanager will react in the same
manner whereby the password for pubmanager will be obtained from the .ngxpass
file.
Permissions and Roles 4.1.2
Following are the different permissions available related to different functionalities.
The following permissions are required for managing users and roles.
Table 4-1 User Permissions
Permission Purpose
set_admin_password Set administrator password with setadminpassword.
create_role Create a role with createrole.
create_user Create a username with createuser.
set_user_password Update a username password with updatepassword.
update_user Update the username privileges with updateuser.
The following permissions are required for listing information.
Table 4-2 Listing Permissions
Permission Purpose
list_server List replication servers with listserver.
list_db List databases with listdb.
list_pub List publications with listpub.
list_pub_table List publication tables with listpubtable.
list_consumer List consumers with removefilter.
list_conflicts List conflicts with listconflicts.
replication_lag List replication lag with replicationlag.
list_events List events with listevents.
The following permissions are required for filtering.
Table 4-3 Filtering Permissions
Permission Purpose
add_filter Add a filter with addfilter.
enable_filter Enable a filter with enablefilter.
update_filter Update a filter with updatefilter.
disable_filter Disable a filter with disablefilter.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
87
Permission Purpose
remove_filter Remove a filter with removefilter.
The following are the permissions required for creating and managing the components of
a replication network.
Table 4-4 Replication Network Permissions
Permission Purpose
join_network Join the network with joinnetwork.
leave_network Leave the network with leavenetwork.
add_db Add a database with adddb.
remove_db Remove a database with removedb.
remove_pub Remove tables from a publication with removetables.
db_dbid Access permission to database identified by dbid. (See the
following Note)
create_pub Create a publication with createpub.
Add tables to a publication with addtables.
join_pub Join a publication with joinpub.
leave_pub Leave a publication with leavepub.
remove_pub Remove a publication with removepub.
pub_pubname Access permission to the publication named pubname. (See the
following Note)
start_snapshot Start a snapshot with startsnapshot.
check_snapshot Confirm if data is replicated to target node.
start_streaming Start streaming with startstreaming.
stop_streaming Stop streaming with stopstreaming.
replication_latency Displays the latency for the Replication Server.
shut_down_server Shuts down the Replication Server.
Note: When a database is added or a publication is created, resource type permission is
created. For any database added, the name of the permission is db_dbid where dbid is
the database identifier assigned to the database with the adddb command. For any
publication created the name of the permission is pub_pubname where pubname is the
publication name assigned with the createpub command.
The user who creates a resource like a database or a publication is automatically assigned
the permission for that resource. If any other user needs to access any resource that may
be a database or a publication, the permission for that resource must be assigned to that
user unless the user has the administrator role.
The following permissions are required for using Kafka ACL.
Table 4-5 Kafka ACL Permissions
Permission Purpose
add_kafka_acl Add a Kafka ACL with addkafkaacl.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
88
Permission Purpose
remove_kafka_acl Remove a Kafka ACL with removekafkaacl.
list_kafka_acl List a Kafka ACL with listkafkaacl.
4.1.2.1 Assigning Permissions
Starting with the administrator user admin, additional users can be created with various
levels of permissions.
The following example shows the creation of usermanager with the create_user
permission who then has permissions to create other users with various permissions.
The usermanager is then used to create user pubcreator with permissions to create
the replication network components.
On each of the two databases, node1 and node2, create the public.dept table
definition:
CREATE TABLE dept (
deptno NUMERIC(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname VARCHAR(14) CONSTRAINT dept_dname_uq UNIQUE,
loc VARCHAR(13)
);
ALTER TABLE dept REPLICA IDENTITY FULL;
On the source database, node1, the public.dept table is populated with four rows:
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
Start the replication server on the host.
./runServer.sh --host 192.168.2.27 --config /usr/edb/rs-7.0/server/etc
Join the network and set the administrator password:
$ ./runRepCLI.sh -joinnetwork -servername localService -host 192.168.2.27 -port 8082
Server is successfully added in the network.
$ ./runRepCLI.sh -setadminpassword
Enter admin password:
Admin password is set successfully.
Create the user usermanager assigning it the create_user, set_user_password,
and update_user permissions:
$ ./runRepCLI.sh -createuser usermanager \
> -permissions create_user,set_user_password,update_user \
> -user admin
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
89
Enter user password:
Enter password for new user:
User is created successfully.
With usermanager as the user given with the -user option, create user pubcreator
with permissions to add a database, create a publication, join a publication, and start a
snapshot, and check the snapshot.
$ ./runRepCLI.sh -createuser pubcreator \
> -permissions add_db,create_pub,join_pub,start_snapshot, check_snapshot -user
usermanager
Enter user password:
Enter password for new user:
User is created successfully.
Also using usermanager as the user, create user streamer with permissions to control
streaming.
$ ./runRepCLI.sh -createuser streamer \
> -permissions start_streaming,stop_streaming -user usermanager
Enter user password:
Enter password for new user:
User is created successfully.
Now, complete the replication node as user pubcreator.
$ ./runRepCLI.sh -adddb -servername localService -dbid db1 -dbtype postgresql \
> -dbhost 192.168.2.27 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node1 -user pubcreator
Enter user password:
Database is registered successfully.
$ ./runRepCLI.sh -createpub -pubname deptpub -servername localService -dbid db1 \
> -tables public.dept -user pubcreator
Enter user password:
Publication is created successfully.
$ ./runRepCLI.sh -adddb -servername localService -dbid db2 -dbtype postgresql \
> -dbhost 192.168.2.27 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node2 -user pubcreator
Enter user password:
Database is registered successfully.
$ ./runRepCLI.sh -joinpub -servername localService -dbid db2 -pubname deptpub \
> -user pubcreator
Enter user password:
Node has successfully joined the Publication.
As user pubcreator, perform an initial snapshot.
$ ./runRepCLI.sh -startsnapshot -pubname deptpub -dbid db2 -user pubcreator
Enter user password:
Data snapshot is performed successfully.
Confirm if the data is replicated to the target node with checksnapshot.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
90
$ ./runRepCLI.sh -checksnapshot -pubname deptpub -dbid db2 -user pubcreator
Snapshot Data Publish Status: Completed
Snapshot Data Import Status: Completed
The four rows initially present in node1 are now in node2.
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
(4 rows)
User streamer must be updated to provide permission to access publication deptpub
upon which streaming is to be started because the publication was created by user
pubcreator and not by streamer. The permission to be granted is pub_deptpub.
The user streamer is updated by usermanager who has the update_user
permission.
$ ./runRepCLI.sh -updateuser streamer \
> -permissions pub_deptpub -user usermanager
Enter user password:
User is updated successfully.
User streamer can now start the streaming.
$ ./runRepCLI.sh -startstreaming -pubname deptpub -user streamer
Enter user password:
Streaming is started successfully for the Publication deptpub.
A row is inserted in database node1.
node1=# INSERT INTO dept VALUES (50,'HR','DENVER');
INSERT 0 1
node1=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
50 | HR | DENVER
(5 rows)
The row appears in database node2.
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
50 | HR | DENVER
(5 rows)
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
91
The following section shows how roles can be used to simplify the usage of permissions.
4.1.2.2 Using Roles
The createrole command can be used to assign a group of permissions to one specific
role, which then can be assigned to individual users.
This simplifies the organization of permissions that may be required for multiple users.
The following is an example of creating a role, assigning it to a new user, and then using
this new user to create a replication node.
On each of the two databases, node1 and node2, create the public.dept table
definition:
CREATE TABLE dept (
deptno NUMERIC(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname VARCHAR(14) CONSTRAINT dept_dname_uq UNIQUE,
loc VARCHAR(13)
);
ALTER TABLE dept REPLICA IDENTITY FULL;
On the source database, node1, the public.dept table is populated with four rows:
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
Start the replication server on the host.
./runServer.sh --host 192.168.2.27 --config /usr/edb/rs-7.0/server/etc
Join the network and set the administrator password:
$ ./runRepCLI.sh -joinnetwork -servername localService -host 192.168.2.27 -port 8082
Server is successfully added in the network.
$ ./runRepCLI.sh -setadminpassword
Enter admin password:
Admin password is set successfully.
Create the role repsrvrmaker with permissions join_network, add_db,
create_pub, join_pub, start_snapshot, check_snapshot, and
start_streaming. Create the user smith assigning it the repsrvrmaker role
privilege:
$ ./runRepCLI.sh -createrole repsrvrmaker \
> -permissions
join_network,add_db,create_pub,join_pub,start_snapshot,check_snapshot,start_streaming \
> -user admin
Enter user password:
Role is created successfully.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
92
$ ./runRepCLI.sh -createuser smith -roles repsrvrmaker -user admin
Enter user password:
Enter password for new user:
User is created successfully.
Now, use the new user smith to complete the replication node by adding the producer
database, creating the publication, adding the consumer database, and joining the
publication:
$ ./runRepCLI.sh -adddb -servername localService -dbid db1 -dbtype postgresql \
> -dbhost 192.168.2.27 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node1 -user smith
Enter user password:
Database is registered successfully.
$ ./runRepCLI.sh -createpub -pubname deptpub -servername localService -dbid db1 \
> -tables public.dept -user smith
Enter user password:
Publication is created successfully.
$ ./runRepCLI.sh -adddb -servername localService -dbid db2 -dbtype postgresql \
> -dbhost 192.168.2.27 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node2 -user smith
Enter user password:
Database is registered successfully.
$ ./runRepCLI.sh -joinpub -servername localService -dbid db2 -pubname deptpub \
> -user smith
Enter user password:
Node has successfully joined the Publication.
Take an initial snapshot with user smith. The node2 database now shows the rows
initially inserted on node1.
$ ./runRepCLI.sh -startsnapshot -pubname deptpub \
> -dbid db2 -user smith
Enter user password:
Data snapshot is performed successfully.
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
(4 rows)
Start the streaming and insert another row into the node1 database table.
$ ./runRepCLI.sh -startstreaming -pubname deptpub -user smith
Enter user password:
Streaming is started successfully for the Publication deptpub.
node1=# INSERT INTO dept VALUES (50,'HR','DENVER');
INSERT 0 1
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
93
node1=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
50 | HR | DENVER
(5 rows)
The new row now appears in node2:
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
50 | HR | DENVER
(5 rows)
This shows that all of the RepCLI commands were accepted when invoked by user
smith.
4.2 TLS/SSL Security
Kafka supports server and client authentication using Transport Layer Security
(TLS)/Secure Sockets Layer (SSL) certificates. The TLS configurations for the Kafka
brokers and clients (producer/consumer) must be provided along with the configuration
of the keystore and truststore.
The following are the instructions to generate the required TLS certificates for the server
and clients and the SSL configurations to enable SSL authentication for both the server
and client and encrypted communication.
Creating Keystores and Truststore for Self-Signed Certificates 4.2.1
The following are the steps to generate the server and client keystores, a certification
authority (CA) to self-sign server/client certificates, and importation of the CA certificate
into the truststore so that the signed certificate can be trusted.
Note: In the following example, the EDB Replication Server client and server will be
running on the same, single host machine with one replication server. Thus, only a single
set of files are generated. If the replication network is to contain multiple replication
servers, then this process must be repeated for each broker of each separate replication
server.
Step 1: Generate the keystores, which is a key pair used for encryption along with the
certificate to identify the machine for the server and client.
Java‟s keytool utility can be used to generate the keystores.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
94
On the server host:
$ keytool -keystore server.keystore.jks -alias servercert \
> -validity 365 -genkey \
> -dname "CN=enterprisedb.com, OU=Engineering, O=EnterpriseDB, L=Bedford, ST=MA, C=US"
\
> -keypass testpass -storepass testpass -ext SAN=dns:localhost,ip:192.168.2.27
On the client host:
$ keytool -keystore client.keystore.jks -alias clientcert -validity 365 -genkey \
> -dname "CN=postgresrocks.com, OU=Engineering, O=EnterpriseDB, L=Bedford, ST=MA, C=US"
\
> -keypass testpass -storepass testpass -ext SAN=dns:localhost,ip:192.168.2.27
Step 2: Create a certificate authority (CA) to self-sign the server and client certificates.
Note that the password testpass is given in all of the following examples.
$ openssl req -new -x509 -keyout ca-key -out ca-cert -days 365
Generating a 2048 bit RSA private key
......................................................................................+
++
.......+++
writing new private key to 'ca-key'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:US
State or Province Name (full name) []:MA
Locality Name (eg, city) [Default City]:Bedford
Organization Name (eg, company) [Default Company Ltd]:EnterpriseDB
Organizational Unit Name (eg, section) []:Engineering
Common Name (eg, your name or your server's hostname) []:enterprisedb.com
Email Address []:.
Step 3: The certificate authority created in Step2 is to be added to the server and client
truststore so that certificates signed with this certificate authority can be trusted by
servers and clients. The same truststore will be used for both server and client machines.
The following creates a truststore file.
$ keytool -keystore kafka.truststore.jks -alias CARoot -import -file ca-cert
Enter keystore password:
Re-enter new password:
Owner: CN=enterprisedb.com, OU=Engineering, O=EnterpriseDB, L=Bedford, ST=MA, C=US
Issuer: CN=enterprisedb.com, OU=Engineering, O=EnterpriseDB, L=Bedford, ST=MA, C=US
Serial number: e877b277867e76e0
Valid from: Sun Jul 01 10:49:50 EDT 2018 until: Mon Jul 01 10:49:50 EDT 2019
Certificate fingerprints:
MD5: D4:82:D8:1C:68:32:E9:67:13:6B:2F:F6:77:37:E6:EA
SHA1: 0C:48:3D:78:24:B9:CA:FF:6F:38:FB:EF:87:7A:39:CD:C8:0E:F5:B2
SHA256:
89:9B:29:BB:CC:BD:C4:66:9E:B6:D5:88:39:43:6D:95:73:58:80:72:06:97:44:0A:0B:CD:37:86:79:
C3:02:B1
Signature algorithm name: SHA256withRSA
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
95
Version: 3
Extensions:
#1: ObjectId: 2.5.29.35 Criticality=false
AuthorityKeyIdentifier [
KeyIdentifier [
0000: 1A 7C 55 2A 79 2B 6D A3 76 83 8E 7A 91 51 CA 6F ..U*y+m.v..z.Q.o
0010: 44 3D B7 9F D=..
]
]
#2: ObjectId: 2.5.29.19 Criticality=false
BasicConstraints:[
CA:true
PathLen:2147483647
]
#3: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: 1A 7C 55 2A 79 2B 6D A3 76 83 8E 7A 91 51 CA 6F ..U*y+m.v..z.Q.o
0010: 44 3D B7 9F D=..
]
]
Trust this certificate? [no]: yes
Certificate was added to keystore
Step 4: Make some modifications to the openssl.cnf file. This file may be found in
the directory location /etc/pki/tls.
Assuming the keystore and truststore files are located in directory /home/user/cert,
perform the edits in the section of file openssl.cnf for parameters dir,
certificate, and private_key as shown by the following:
[ CA_default ]
dir = /home/user/cert # Where everything is kept <== Edit directory
certs = $dir/certs # Where the issued certs are kept
crl_dir = $dir/crl # Where the issued crl are kept
database = $dir/index.txt # database index file.
#unique_subject = no # Set to 'no' to allow creation of
# several certificates with same subject.
new_certs_dir = $dir/newcerts # default place for new certs.
certificate = $dir/ca-cert # The CA certificate <== Edit file name
serial = $dir/serial # The current serial number
crlnumber = $dir/crlnumber # the current crl number
# must be commented out to leave a V1 CRL
crl = $dir/crl.pem # The current CRL
private_key = $dir/ca-key # The private key <== Edit file name
RANDFILE = $dir/private/.rand # private random number file
Edit the following section in the openssl.cnf file changing the indicated parameters to
optional.
[ policy_match ]
countryName = optional <=== Change to optional
stateOrProvinceName = optional <=== Change to optional
organizationName = optional <=== Change to optional
organizationalUnitName = optional
commonName = supplied
emailAddress = optional
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
96
In section v3_req, add the indicated new field followed by the addition of the
alternate_names section and its list of fields:
[ v3_req ]
# Extensions to add to a certificate request
basicConstraints = CA:FALSE
keyUsage = nonRepudiation, digitalSignature, keyEncipherment
subjectAltName = @alternate_names <=== Add new field
[ alternate_names ] <=== Add new section and its fields
DNS.1 = localhost
DNS.2 = www.example.com
IP.1 = 192.168.2.27
IP.2 = 192.168.2.27
Step 5: In the directory where you have created the keystore and truststore files, perform
the following modifications. It is assumed this directory is /home/user/certs.
$ cd /home/user/cert
$ mkdir certs crl newcerts private
$ chmod 700 private
$ touch index.txt
$ echo 1000 > serial
$ ls -l
total 24
-rw-rw-r-- 1 user user 1354 Jul 1 10:49 ca-cert
-rw-rw-r-- 1 user user 1834 Jul 1 10:49 ca-key
drwxrwxr-x 2 user user 6 Jul 1 10:52 certs
-rw-rw-r-- 1 user user 1344 Jul 1 10:48 client.keystore.jks
drwxrwxr-x 2 user user 6 Jul 1 10:52 crl
-rw-rw-r-- 1 user user 0 Jul 1 10:52 index.txt
-rw-rw-r-- 1 user user 1022 Jul 1 10:50 kafka.truststore.jks
drwxrwxr-x 2 user user 6 Jul 1 10:52 newcerts
drwx------ 2 user user 6 Jul 1 10:52 private
-rw-rw-r-- 1 user user 5 Jul 1 10:52 serial
-rw-rw-r-- 1 user user 1344 Jul 1 10:47 server.keystore.jks
Final Steps for the Server: Export the server certificate from its keystore to sign it and
import it back.
Step 6a: Export the server certificate.
$ keytool -keystore server.keystore.jks -alias servercert -certreq \
> -ext SAN=dns:localhost,ip:192.168.2.27 -file server-cert-file.csr -storepass testpass
Step 6b: Generate the certificate signing request:
$ openssl ca -policy policy_match -out server-cert-signed.crt \
> -config /etc/pki/tls/openssl.cnf -extensions v3_req -infiles server-cert-file.csr
Using configuration from /etc/pki/tls/openssl.cnf
Enter pass phrase for /home/user/cert/ca-key:
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 4096 (0x1000)
Validity
Not Before: Jul 1 14:54:23 2018 GMT
Not After : Jul 1 14:54:23 2019 GMT
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
97
Subject:
countryName = US
stateOrProvinceName = MA
organizationName = EnterpriseDB
organizationalUnitName = Engineering
commonName = enterprisedb.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
X509v3 Key Usage:
Digital Signature, Non Repudiation, Key Encipherment
X509v3 Subject Alternative Name:
DNS:localhost, DNS:www.example.com, IP Address:192.168.2.27, IP
Address:192.168.2.27
Certificate is to be certified until Jul 1 14:54:23 2019 GMT (365 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
Step 6c: Import ca-cert to server keystore:
$ keytool -keystore server.keystore.jks -alias CARoot -import -file ca-cert -storepass
testpass
Owner: CN=enterprisedb.com, OU=Engineering, O=EnterpriseDB, L=Bedford, ST=MA, C=US
Issuer: CN=enterprisedb.com, OU=Engineering, O=EnterpriseDB, L=Bedford, ST=MA, C=US
Serial number: e877b277867e76e0
Valid from: Sun Jul 01 10:49:50 EDT 2018 until: Mon Jul 01 10:49:50 EDT 2019
Certificate fingerprints:
MD5: D4:82:D8:1C:68:32:E9:67:13:6B:2F:F6:77:37:E6:EA
SHA1: 0C:48:3D:78:24:B9:CA:FF:6F:38:FB:EF:87:7A:39:CD:C8:0E:F5:B2
SHA256:
89:9B:29:BB:CC:BD:C4:66:9E:B6:D5:88:39:43:6D:95:73:58:80:72:06:97:44:0A:0B:CD:37:86:79:
C3:02:B1
Signature algorithm name: SHA256withRSA
Version: 3
Extensions:
#1: ObjectId: 2.5.29.35 Criticality=false
AuthorityKeyIdentifier [
KeyIdentifier [
0000: 1A 7C 55 2A 79 2B 6D A3 76 83 8E 7A 91 51 CA 6F ..U*y+m.v..z.Q.o
0010: 44 3D B7 9F D=..
]
]
#2: ObjectId: 2.5.29.19 Criticality=false
BasicConstraints:[
CA:true
PathLen:2147483647
]
#3: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: 1A 7C 55 2A 79 2B 6D A3 76 83 8E 7A 91 51 CA 6F ..U*y+m.v..z.Q.o
0010: 44 3D B7 9F D=..
]
]
Trust this certificate? [no]: yes
Certificate was added to keystore
Step 6d: Import the signed server certificate back to server keystore:
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
98
$ keytool -import -keystore server.keystore.jks -file server-cert-signed.crt \
> -alias servercert -storepass testpass
Certificate reply was installed in keystore
Final Steps for the Client: Export the client certificate from its keystore to sign it and
import it back.
Step 7a: Export the client certificate.
$ keytool -keystore client.keystore.jks -alias clientcert -certreq \
> -ext SAN=dns:localhost,ip:192.168.2.27 -file client-cert-file.csr -storepass testpass
Step 7b: Generate the certificate signing request:
$ openssl ca -policy policy_match -out client-cert-signed.crt \
> -config /etc/pki/tls/openssl.cnf -extensions v3_req -infiles client-cert-file.csr
Using configuration from /etc/pki/tls/openssl.cnf
Enter pass phrase for /home/user/cert/ca-key:
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 4097 (0x1001)
Validity
Not Before: Jul 1 14:59:43 2018 GMT
Not After : Jul 1 14:59:43 2019 GMT
Subject:
countryName = US
stateOrProvinceName = MA
organizationName = EnterpriseDB
organizationalUnitName = Engineering
commonName = postgresrocks.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
X509v3 Key Usage:
Digital Signature, Non Repudiation, Key Encipherment
X509v3 Subject Alternative Name:
DNS:localhost, DNS:www.example.com, IP Address:192.168.2.27, IP
Address:192.168.2.27
Certificate is to be certified until Jul 1 14:59:43 2019 GMT (365 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
Step 7c: Import ca-cert to client keystore:
$ keytool -keystore client.keystore.jks -alias CARoot -import -file ca-cert \
> -storepass testpass
Owner: CN=enterprisedb.com, OU=Engineering, O=EnterpriseDB, L=Bedford, ST=MA, C=US
Issuer: CN=enterprisedb.com, OU=Engineering, O=EnterpriseDB, L=Bedford, ST=MA, C=US
Serial number: e877b277867e76e0
Valid from: Sun Jul 01 10:49:50 EDT 2018 until: Mon Jul 01 10:49:50 EDT 2019
Certificate fingerprints:
MD5: D4:82:D8:1C:68:32:E9:67:13:6B:2F:F6:77:37:E6:EA
SHA1: 0C:48:3D:78:24:B9:CA:FF:6F:38:FB:EF:87:7A:39:CD:C8:0E:F5:B2
SHA256:
89:9B:29:BB:CC:BD:C4:66:9E:B6:D5:88:39:43:6D:95:73:58:80:72:06:97:44:0A:0B:CD:37:86:79:
C3:02:B1
Signature algorithm name: SHA256withRSA
Version: 3
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
99
Extensions:
#1: ObjectId: 2.5.29.35 Criticality=false
AuthorityKeyIdentifier [
KeyIdentifier [
0000: 1A 7C 55 2A 79 2B 6D A3 76 83 8E 7A 91 51 CA 6F ..U*y+m.v..z.Q.o
0010: 44 3D B7 9F D=..
]
]
#2: ObjectId: 2.5.29.19 Criticality=false
BasicConstraints:[
CA:true
PathLen:2147483647
]
#3: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: 1A 7C 55 2A 79 2B 6D A3 76 83 8E 7A 91 51 CA 6F ..U*y+m.v..z.Q.o
0010: 44 3D B7 9F D=..
]
]
Trust this certificate? [no]: yes
Certificate was added to keystore
Step 7d: Import the signed client certificate back to client keystore:
$ keytool -import -keystore client.keystore.jks -file client-cert-signed.crt \
> -alias clientcert -storepass testpass
Certificate reply was installed in keystore
Enabling Server SSL Authentication and Encrypted 4.2.2Communication
For enabling server SSL based authentication and encrypted communication, which is not
enabled by default, the following SSL configuration parameters must be set in the
following files.
Note: In the following example, the EDB Replication Server client and server will be
running on the same, single host machine with one replication server. Thus, only a single
set of properties files are modified. If the replication network is to contain multiple
replication servers, then the properties files must be modified for each broker of each
separate replication server.
server.properties
The following parameters must be uncommented with the following settings:
ssl.client.auth=required
security.inter.broker.protocol=SSL
ssl.enabled.protocols=TLSv1.2,TLSv1.1,TLSv1
authorizer.class.name=kafka.security.auth.SimpleAclAuthorizer
allow.everyone.if.no.acl.found=true
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
100
Modify all other ssl parameters to point to the proper directory locations and specify the
passwords.
#hostname=localhost
#port=9092
#brokerid=broker1
#log.dir=/tmp/kafka-logs/broker1
#enable.zookeeper=false
#zookeeper.connect=localhost:2181
.
.
.
#authorizer.class.name=kafka.security.auth.SimpleAclAuthorizer
#listeners=PLAINTEXT://:9092
#security.inter.broker.protocol=SASL_PLAINTEXT
#sasl.mechanism.inter.broker.protocol=PLAIN
#sasl.mechanism=PLAIN
#sasl.enabled.mechanisms=PLAIN
#listeners=SSL://:9092
ssl.client.auth=required
ssl.keystore.location=/home/user/cert/server.keystore.jks
ssl.keystore.password=testpass
ssl.key.password=testpass
ssl.truststore.location=/home/user/cert/kafka.truststore.jks
ssl.truststore.password=testpass
security.inter.broker.protocol=SSL
ssl.enabled.protocols=TLSv1.2,TLSv1.1,TLSv1
authorizer.class.name=kafka.security.auth.SimpleAclAuthorizer
#With the default behavior, if an ACL is not associated with a Kafka resource,
#no one (other than super user) can access it. Uncomment and enable (true) below
#property to grant access to everyone.
allow.everyone.if.no.acl.found=true
#super.users=User:CN=127.0.0.1,OU=engineering,O=xyz pvt ltd1.,ST=MA,C=us
server-config.json
The following parameters must be set with the following settings:
"protocol": "https"
"needClientAuth": "true"
"needServerAuth": "true"
Modify the Path and Password parameters to point to the proper directory locations
and specify the passwords.
{
"runtimeMode": "test",
"application": "rest",
"description": "REST Proof of Concept",
"host": "0.0.0.0",
"protocol": "https",
"port": "8082",
"defaultQueryLimit": 20,
"tokenLifespan": 600000,
"packages": [
"com.edb.ngen.rest",
"com.edb.ngen.rest.filter",
"com.edb.ngen.rest.resource",
"com.edb.ngen.rest.resource.v2"
],
"connectionInfo": [],
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
101
"identities": [
{
"name": "admin",
"password": "admin",
"roles": "admin, user, view"
}
],
"keyStorePath": "/home/user/cert/server.keystore.jks",
"keyStorePassword": "testpass",
"keyManagerPassword": "testpass",
"trustStorePath": "/home/user/cert/kafka.truststore.jks",
"trustStorePassword": "testpass",
"needClientAuth": "true",
"needServerAuth": "true"
}
schema-registry.properties
The following parameters must be uncommented with the following settings:
kafkastore.security.protocol=SSL
ssl.client.auth=false
listeners=http://0.0.0.0:8081
schema.registry.inter.instance.protocol=http
Modify the kafkastore.ssl parameters to point to the proper directory locations and
specify the passwords.
#kafkastore.bootstrap.servers=PLAINTEXT://192.168.2.27:9092
#kafkastore.bootstrap.servers=PLAINTEXT://192.168.2.27:9092
debug=false
kafkastore.topic=_schemas
kafkastore.security.protocol=SSL
kafkastore.ssl.truststore.location=/home/user/cert/kafka.truststore.jks
kafkastore.ssl.truststore.password=testpass
kafkastore.ssl.keystore.location=/home/user/cert/server.keystore.jks
kafkastore.ssl.keystore.password=testpass
kafkastore.ssl.key.password=testpass
# Kafka clients producer/consumer communicate with schema-registry,
# if 'ssl.client.auth'
# configuration property is set to true, schema-registry authenticates its clients
# through ssl certificate. However currently enabling it is not working, probably
# some other configurations is also needed to make this client authentication work.
# So for now this is disabled.
ssl.client.auth=false
listeners=http://0.0.0.0:8081
schema.registry.inter.instance.protocol=http
Enabling Client SSL Authentication and Encrypted 4.2.3Communication
For enabling client SSL based authentication and encrypted communication, which is not
enabled by default, the following SSL configuration parameters must be set in the
following files.
Note: In the following example, the EDB Replication Server client and server will be
running on the same, single host machine with one replication server. Thus, only a single
set of properties files are modified. If the replication network is to contain multiple
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
102
replication servers, then the properties files must be modified for each broker of each
separate replication server.
rest-client-ssl-config.json
The following parameters must be set with the following settings:
"protocol": "https"
"needClientAuth": "true"
Modify the Path and Password parameters to point to the proper directory locations
and specify the passwords.
{
"protocol":"https",
"needClientAuth": "true",
"keyStoreType": "JKS",
"algorithmType": "SunX509",
"socketProtocol": "TLSv1",
"keyStorePath": "/home/user/cert/client.keystore.jks",
"keyStorePassword": "testpass",
"trustStorePath": "/home/user/cert/kafka.truststore.jks",
"trustStorePassword": "testpass",
"keyPassword": "testpass"
}
consumer.properties
The following parameter must be uncommented with the following setting:
security.protocol=SSL
Modify all other ssl parameters to point to the proper directory locations and specify the
passwords.
#bootstrap.servers=SSL://localhost:9092
bootstrap.servers=localhost:9092
#group.id=group1
#client.id=client1
enable.auto.commit=false
#key.deserializer=org.apache.kafka.common.serialization.StringDeserializer
#value.deserializer=org.apache.kafka.common.serialization.StringDeserializer
#On high latency, best speed is achieved with 3000 records
max.poll.records=3000
#heartbeat.interval.ms=500
# fast session timeout makes it more fun to play with failover
session.timeout.ms=10000
# These buffer sizes seem to be needed to avoid consumer switching to
# a mode where it processes one bufferful every 5 seconds with multiple
# timeouts along the way. No idea why this happens.
#fetch.min.bytes=5000
fetch.max.bytes=4388608
receive.buffer.bytes=262144
max.partition.fetch.bytes=4388608
#fetch.message.max.bytes=209715200
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
103
# latest,earliest,none
auto.offset.reset=earliest
#offsets.topic.replication.factor=1
security.protocol=SSL
ssl.truststore.location=/home/user/cert/kafka.truststore.jks
ssl.truststore.password=testpass
ssl.keystore.location=/home/user/cert/server.keystore.jks
ssl.keystore.password=testpass
ssl.key.password=testpass
producer.properties
The following parameter must be uncommented with the following setting:
security.protocol=SSL
Modify all other ssl parameters to point to the proper directory locations and specify the
passwords.
#bootstrap.servers=localhost:9091
#bootstrap.servers=SSL://localhost:9091
enable.idempotence=true
acks=all
retries=2147483647
max.in.flight.requests.per.connection=1
#auto.commit.interval.ms=1000
#linger.ms=0
batch.size=516384
#key.serializer=org.apache.kafka.common.serialization.StringSerializer
#value.serializer=org.apache.kafka.common.serialization.StringSerializer
#block.on.buffer.full=true
#default 200MB
#message.max.bytes=209715200
#default 200MB
#replica.fetch.max.bytes=219715200
#default 200MB
#max.request.size=209715200
#default 200MB
#BackPressureThresholdBytes=209715200
#security.protocol=PLAINTEXT
security.protocol=SSL
ssl.truststore.location=/home/user/cert/kafka.truststore.jks
ssl.truststore.password=testpass
ssl.keystore.location=/home/user/cert/server.keystore.jks
ssl.keystore.password=testpass
ssl.key.password=testpass
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
104
5 Monitoring
Monitoring is the process of examining, logging, and reacting to certain information
related to the performance of a replication network based upon its internal components
such as the Kafka brokers and message exchanges to and from consumers and producers.
The objective is to issue alerts if any information indicates a problem with the replication
network.
Alerts appear as email messages as well as output from the monitoring application.
Monitoring is based on a group of attributes referred to as metrics, which measure
various characteristics during the operation of the Kafka brokers.
If a particular value of a metric indicates the possibility of a problem, then that is when an
alert is issued.
Metrics are read using a feature called Jolokia to read MBeans using the JMX interface.
Information on Jolokia and its architecture is available from the following website:
https://jolokia.org/
The remainder of this chapter contains the following sections:
Section 5.1 gives an overview of the metrics that are examined.
Section 5.2 provides the instructions for configuring and starting monitoring.
The following is the metrics overview.
5.1 Metrics
There are various Kafka components for which metrics provide information. Some of the
metrics that may be monitored and potentially reported are described in the following
sections:
Broker Metrics. See Section 5.1.1.
Topic and Partition Metrics. See Section 5.1.2.
Client Metrics. See Section 5.1.3.
Information about metrics can be found at the following websites:
https://docs.confluent.io/current/kafka/monitoring.html
Also, see Chapter 6.6 “Monitoring” at the following website:
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
105
https://kafka.apache.org/documentation/
EDB replication server monitors a subset of these metrics that are considered to be of
significant importance. For a summary of the metrics considered for monitoring using
EDB replication server, see Section 5.1.4.
The following sections give a brief overview of the metrics and the possible issues that
may be reported.
Broker Metrics 5.1.1
This section describes the metrics related to the Kafka brokers.
5.1.1.1 Under-Replicated Partitions
This measurement, provided on each broker in a cluster, gives a count of the number of
partitions for which the broker is the leader replica where the follower replicas are not
caught up.
This is the single most important metric as it provides insights into a number of problems
with the Kafka cluster from a broker being down to resource exhaustion.
A steady, unchanging number of under-replicated partitions reported by many of the
brokers in a cluster normally indicates that one of the brokers in the cluster is offline.
The count of under-replicated partitions across the entire cluster will equal the number of
partitions that are assigned to that broker, and the broker that is down will not report a
metric.
If the number of under-replicated partitions is fluctuating, or if the number is steady but
there are no brokers offline, this typically indicates a performance issue in the cluster.
The first step is to try and determine if the problem relates to a single broker or to the
entire cluster.
If the under-replicated partitions are on a single broker, then that broker is
typically the problem. Other brokers are having a problem replicating messages
from that one.
If several brokers have under-replicated partitions: 1) It could be a cluster
problem; 2) It could be a single broker. In this case, it would be because a single
broker is having problems replicating messages from everywhere.
One way to do this is by using kafka-topics.sh tool with the --zookeeper, --
describe, and --under-replicated options. See the Kafka documentation for
instructions on its usage.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
106
5.1.1.2 Active Controller Count
At all times, only one broker should be the controller, and the confirmed one broker must
always be the controller in the cluster. The metric will either be 0 or 1 for a broker.
Case 1: More than one active controller
If two brokers say that they are currently the controller, this means that you have a
problem where a controller thread that should have exited has become stuck. This can
cause problems with not being able to execute administrative tasks, such as partition
moves, properly. To remedy this, you will need to restart both brokers at the very least.
However, when there is an extra controller in the cluster, there will often be problems
performing a controlled shutdown of a broker.
Case 2: No active controller
If no broker claims to be the controller in the cluster, the cluster will fail to respond
properly in the face of state changes, including a topic or partition creation, or broker
failures. In this situation, you must investigate further to find out why the controller
threads are not working properly. For example, a network partition from the ZooKeeper
cluster could result in a problem like this. Once that underlying problem is fixed, it is
wise to restart all the brokers in the cluster in order to reset state for the controller
threads.
5.1.1.3 Request Handler Average Idle Ratio
The request handler threads are responsible for servicing the client request itself, which
includes reading or writing the messages to disk. As such, as the brokers get more heavily
loaded, there is a significant impact on this thread pool.
The request handler idle ratio metric indicates the percentage of time the request handlers
are not in use. The lower this number, the more loaded the broker is. Based on
experience, it is recommended that idle ratios lower than 20% indicate a potential
problem, and lower than 10% is usually an active performance problem.
5.1.1.4 All Topics Bytes In
This is a measurement of how much message traffic the brokers are receiving from
producing clients. This helps determine when you need to expand the cluster or do other
growth-related work. It is also useful for evaluating if one broker in a cluster is receiving
more traffic than the others, which would indicate that it is necessary to rebalance the
partitions in the cluster.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
107
5.1.1.5 All Topics Bytes Out
Bytes out rate shows the rate at which consumers are reading messages out. The
outbound bytes rate may scale differently than the inbound bytes rate. There are many
deployments of Kafka where the outbound rate can easily be six times the inbound rate.
Note: The outbound bytes rate also includes the replica traffic. This means that if all of
the topics are configured with a replication factor of 2, you will see bytes out rate equal to
the bytes in rate when there are no consumer clients. If you have one consumer client
reading all the messages in the cluster, then the bytes out rate will be twice the bytes in
rate. This can be confusing when looking at the metrics if you‟re not aware of what is
counted.
5.1.1.6 All Topics Messages In
Shows the number of individual messages, regardless of their size, produced per second.
A different measure of producer traffic similar to bytes in. Shows imbalance in brokers. It
can also be used in conjunction with the bytes in rate to determine an average message
size.
5.1.1.7 Partition Count
It is the total number of partitions assigned to that broker (includes every replica the
broker has, regardless of whether it is a leader or follower for that partition). Monitoring
it is beneficial in case automatic topic creation is enabled as that can leave the creation of
topics outside of the control of the person running the cluster.
5.1.1.8 Leader Count
The leader count metric shows the number of partitions that the broker is currently the
leader for. As with most other measurements in the brokers, this one should be generally
even across the brokers in the cluster. It is much more important to check the leader count
on a regular basis, possibly alerting on it, as it will indicate when the cluster is
imbalanced even if the number of replicas is perfectly balanced in count and size across
the cluster.
This is because a broker can drop leadership for a partition for many reasons, such as a
ZooKeeper session expiration, and it will not automatically take leadership back once it
recovers (except if you have enabled automatic leader rebalancing). In these cases, this
metric will show fewer leaders, or often zero, which indicates that you need to run a
preferred replica election to rebalance leadership in the cluster.
A useful way to consume this metric is to use it along with the partition count to show a
percentage of partitions that the broker is the leader for. In a well-balanced cluster that is
using a replication factor of 2, all brokers should be leaders for approximately 50% of
their partitions. If the replication factor in use is 3, this percentage drops to 33%.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
108
5.1.1.9 Offline Partition Count
This shows the number of partitions in the cluster that currently have no leader. Along
with the under-replicated partitions count, the offline partitions count is a critical metric
for monitoring. This measurement is only provided by the broker that is the controller for
the cluster (all other brokers will report 0).
Partitions without leaders can happen for two main reasons:
All brokers hosting replicas for this partition are down.
No in-sync replica can take leadership due to message-count mismatches (with
unclean leader election disabled).
In a production Kafka cluster, an offline partition may be impacting the producer clients,
losing messages or causing back-pressure in the application. This is most often a “site
down” type of problem and will need to be addressed immediately.
Topic and Partition Metrics 5.1.2
These are metrics related to individual topics and partitions.
For all the per-topic metrics, the measurements are very similar to the broker metrics
described previously. In fact, the only difference is the provided topic name, and that the
metrics will be specific to the named topic.
Client Metrics 5.1.3
These are metrics related to the interaction of the producer and consumer with the broker.
5.1.3.1 Record Error Rate
This metric should always be zero, and if it is anything greater than that, the producer is
dropping messages it is trying to send to the Kafka brokers. There is also a record-
retry-rate attribute that can be tracked, but it is less critical than the error rate because
retries are normal.
5.1.3.2 Request Latency Average
This is the average amount of time a producer request sent to the brokers takes. You
should be able to establish a baseline value for what this number should be in normal
operations, and set an alert threshold above that.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
109
5.1.3.3 Fetch Latency Average
This is the average amount of time a consumer fetch from the brokers takes. This latency
is governed by the consumer configurations fetch.min.bytes and
fetch.max.wait.ms.
Summary of Metrics 5.1.4
The following table lists a summary of the various metrics. Most of these may be seen
from the monitoring output.
Table 5-1 Summary of Metrics to Monitor
Metric Related To Monitor Alert Comment
Under-Replicated Partitions Broker Yes Yes
Active Controller Count Broker Yes Yes
Request Handler Average Idle
Percentage Broker Yes
Bytes In Per Second Broker Yes
Bytes Out Per Second Broker Yes
Messages In Per Second Broker Optional Analogous to Bytes In Per Second
Partition Count Broker Yes Monitor if automatic topic
creation is enabled
Leader Count Broker Yes
Offline Partition Count Broker Yes Yes
“Site down” type of problem
and will need to be addressed
immediately.
Record Error Rate Producer Yes
Producer Per-Topic and Per-
Broker Metrics Producer Optional Used for debugging purposes
Request Latency Average Producer Yes Yes Establish a baseline value and
set an alert threshold above it.
Fetch Latency Average Consumer Yes Optional
Latency is governed by the
consumer configurations
fetch.min.bytes and fetch.max.wait.ms
Consumer Per-Topic and Per-
Broker Metrics Consumer Optional Used for debugging purposes
Consumer Coordination
Metrics Consumer Yes
Establish a baseline expected
value for commit-latency-
avg and alert on it.
Throttle Time Average
Both
producer &
consumer
Yes
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
110
5.2 Starting Monitoring
To begin monitoring, perform the following steps.
Step 1: Monitoring can be performed from a host on which a replication server is running
or from a separate host with no replication server.
To accomplish the latter, install the edb-rs-libs and the edb-rs-monitor packages
on the host to perform the monitoring. See the EDB Postgres Replication Server Getting
Started Guide for installation instructions.
Step 2: In the monitor.properties file located in directory
EPRS_HOME/monitor/etc, set the following parameters:
ngen.total.nodes=number_of_replication_servers
ngen.jolokia.host.node.n=replication_server_host_ip
ngen.jolokia.port.node.n=8778
ngen.sender.email=alert_sender_email_address
ngen.sender.email.encrypted.password=encrypted_sender_passwd
ngen.recipient.email=recipient_email_address
Encrypt the sender email password for ngen.sender.email.encrypted.password
with the encrypt command executed with the runMonitor.sh script in the
EPRS_HOME/monitor/bin directory:
./runMonitor.sh –encrypt –input unencrypted_password_file
–output encrypted_password_file
Note: You may also be required to permit access to the sender email account by this
monitoring application in order for it to send an email.
In addition, the following adjustments may also be made:
Filter Timeout
This feature is to prevent continuous alerts of the same type that result in emails sent
during each monitoring run.
The alert email filter timeout is expressed in minutes and is 10 by default.
ngen.alert.filter.timeout=10
Thus, if an email for the metric alert of the Active Controller Count is sent, the next email
for the same metric will arrive only after 10 minutes. Intermediate generated alerts of the
same type will be filtered out.
Alerts on Request Latency Average
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
111
This value represents the average request latency in milliseconds (ms) for a producer. If
the observed metric value is above the specified setting, an email alert is generated.
ngen.producer.request-latency-avg=1000.0
The following is an example of the monitor.properties file:
#Cluster related settings
#Time interval between each monitoring run expressed in ms
ngen.monitor.interval=5000
#Total number of nodes in the cluser. Change this and uncomment and change related
#*.host.node.x and *.port.node.x properties below as per your cluster configuration
#and IP/Port information.
ngen.total.nodes=1
ngen.jolokia.host.node.1=192.168.2.27
#Jolokia port for REST API defaults to 8778 unless changed while attaching agent
ngen.jolokia.port.node.1=8778
#ngen.jolokia.host.node.2=192.168.0.1
#ngen.jolokia.port.node.2=8778
#ngen.jolokia.host.node.3=192.168.0.2
#ngen.jolokia.port.node.3=8778
#Source email address from where alerts are triggered
#Specify encrypted password for sender email id. To encrypt the password, use
#RepCLI -encrypt command (-encrypt -input <input file location> -output <output file
location>)
ngen.sender.email.encrypted.password=ygJ9AxoJEX854elcVIJPTw==
#Please enter your gmail adddress to get cluster alerts
#request-latency-avg - The average request latency in ms for a producer
ngen.producer.request-latency-avg=1000.0
#Alert email filter timeout expressed in minutes
ngen.alert.filter.timeout=10
The monitor logging information is stored in the directory and file specified by the
property element of the EPRS_HOME/monitor/etc/logback.xml file as shown by
the following:
<property name="MONITORING_LOG_DIR"
value="/var/log/edb/rs/replication-monitor" />
<property name="LOG_FILE_NAME" value="ngx-monitor.log"/>
This logging information is also displayed on the terminal from which the
runMonitor.sh -start command is invoked as described in the next step.
Step 3: Using the root user account or any Linux user account that has read and execute
permissions on the EPRS_HOME/monitor/bin directory, start the monitoring:
cd EPRS_HOME/monitor/bin
./runMonitor.sh -start
There will then be an ongoing output from each monitoring cycle.
Note: Throughout the following examples, the text between the square brackets
[c.e.n.m.m.JolokiaMonitorThread] has been deleted so the metrics can be more
easily readable in this document.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
112
$ ./runMonitor.sh -start
2018-10-09 11:51:14,261 [c.e.n.m.m.JMXMonitorApp] - configHome: /usr/edb/rs-7.0/monitor
2018-10-09 11:51:14,418 [c.e.n.m.c.c.StartCommand] - monitorInterval:5000
2018-10-09 11:51:14,826 [] - #### Critical Alert Metrics at 2018-10-09 11:51:14 for
node running on IP:192.168.2.27 ####
2018-10-09 11:51:15,124 [] - ActiveControllerCount : 1
2018-10-09 11:51:15,124 [] - OfflinePartitionsCount : 0
2018-10-09 11:51:15,124 [] - UnderReplicatedPartitions : 0
2018-10-09 11:51:25,195 [] - producer-1->record-error-rate : 0.0
2018-10-09 11:51:25,196 [] - producer-1->request-latency-avg : 0.0
2018-10-09 11:51:25,339 [] - AvroProducer_deptpub-db1->record-error-rate : 0.0
2018-10-09 11:51:25,339 [] - AvroProducer_deptpub-db1->request-latency-avg : 0.0
2018-10-09 11:51:25,387 [] - AvroProducer__ngx_consumer->record-error-rate : 0.0
2018-10-09 11:51:25,387 [] - AvroProducer__ngx_consumer->request-latency-avg : 0.0
2018-10-09 11:51:25,474 [] - Producer__ngx_events->record-error-rate : 0.0
2018-10-09 11:51:25,475 [] - Producer__ngx_events->request-latency-avg : 0.0
2018-10-09 11:51:25,525 [] - RequestHandlerAvgIdlePercent : 0.9973194965344082
2018-10-09 11:51:25,525 [] - LeaderCount : 106
2018-10-09 11:51:25,525 [] - PartitionCount : 106
2018-10-09 11:51:25,525 [] - BytesInPerSec : 22.961624829773186
2018-10-09 11:51:25,525 [] - BytesOutPerSec : 17.18848685462421
2018-10-09 11:51:25,807 [] - producer-1->_schemas->record-error-rate : 0.0
2018-10-09 11:51:25,807 [] - AvroProducer_deptpub-db1->deptpub-db1->record-error-rate :
0.0
2018-10-09 11:51:25,807 [] - AvroProducer_deptpub-db1->_ngx_pub_deptpub->record-error-
rate : 0.0
2018-10-09 11:51:25,808 [] - AvroProducer__ngx_consumer->_ngx_consumer->record-error-
rate : 0.0
2018-10-09 11:51:25,808 [] - End of monitoring cycle
2018-10-09 11:51:25,814 [] - #### Critical Alert Metrics at 2018-10-09 11:51:25 for
node running on IP:192.168.2.27 ####
2018-10-09 11:51:25,999 [] - ActiveControllerCount : 1
2018-10-09 11:51:26,000 [] - OfflinePartitionsCount : 0
2018-10-09 11:51:26,000 [] - UnderReplicatedPartitions : 0
2018-10-09 11:51:26,072 [] - producer-1->record-error-rate : 0.0
2018-10-09 11:51:26,072 [] - producer-1->request-latency-avg : 0.0
2018-10-09 11:51:26,121 [] - AvroProducer_deptpub-db1->record-error-rate : 0.0
2018-10-09 11:51:26,121 [] - AvroProducer_deptpub-db1->request-latency-avg : 0.0
2018-10-09 11:51:26,165 [] - AvroProducer__ngx_consumer->record-error-rate : 0.0
2018-10-09 11:51:26,165 [] - AvroProducer__ngx_consumer->request-latency-avg : 0.0
2018-10-09 11:51:26,210 [] - Producer__ngx_events->record-error-rate : 0.0
2018-10-09 11:51:26,210 [] - Producer__ngx_events->request-latency-avg : 0.0
2018-10-09 11:51:26,262 [] - RequestHandlerAvgIdlePercent : 0.9969779497478809
2018-10-09 11:51:26,262 [] - LeaderCount : 106
2018-10-09 11:51:26,262 [] - PartitionCount : 106
2018-10-09 11:51:26,263 [] - BytesInPerSec : 22.897105562186727
2018-10-09 11:51:26,263 [] - BytesOutPerSec : 17.14016492650297
2018-10-09 11:51:26,533 [] - producer-1->_schemas->record-error-rate : 0.0
2018-10-09 11:51:26,534 [] - AvroProducer_deptpub-db1->deptpub-db1->record-error-rate :
0.0
2018-10-09 11:51:26,534 [] - AvroProducer_deptpub-db1->_ngx_pub_deptpub->record-error-
rate : 0.0
2018-10-09 11:51:26,534 [] - AvroProducer__ngx_consumer->_ngx_consumer->record-error-
rate : 0.0
2018-10-09 11:51:26,534 [] - End of monitoring cycle
.
.
.
Step 4: If a critical problem occurs, the monitoring output appears as follows:
2018-10-09 11:51:56,020 [] - #### Critical Alert Metrics at 2018-10-09 11:51:56 for
node running on IP:192.168.2.27 ####
2018-10-09 11:51:56,044 [] - Exception while running monitoring code:Cannot connect to
http://192.168.2.27:8778/jolokia/: Connect to 192.168.2.27:8778 [/192.168.2.27] failed:
Connection refused (Connection refused) type:J4pConnectException
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
113
2018-10-09 11:51:56,059 [c.e.n.m.a.NodeDownOrUnreachableAlert] - Alert! Node running on
IP 192.168.2.27 is either down or unreachable.
2018-10-09 11:51:56,098 [c.e.n.m.a.MetricAlert] - Alert! Metric ActiveControllerCount
has value 0
2018-10-09 11:51:56,134 [] - End of monitoring cycle
2018-10-09 11:51:58,399 [c.e.n.m.a.MetricAlert] - Alert email has been sent for metric:
ActiveControllerCount
2018-10-09 11:51:59,158 [c.e.n.m.a.NodeDownOrUnreachableAlert] - An alert email has
been sent for 'Node Down or Unreachable' event for node running on IP:192.168.2.27
The following are the two email alerts sent to the recipient email address when the
replication server aborted.
First email alert:
Subject: Kafka Cluster Alert, Metric:ActiveControllerCount
Admin,
Alert for Kafka Cluster!
Following metric shows deviation from normal value -
Metric name:ActiveControllerCount, Current value:0
Expected value for this metric: 1
Please monitor the cluster for possible problems.
- Thanks,
EnterpriseDB NGen Cluster Monitor
Second email alert:
Subject: Kafka Cluster Alert, Node Down or Unreachable: 192.168.2.27
Admin,
Alert for Kafka Cluster!
Following node appears to be either down or is not reachable -
NGen node running on IP:192.168.2.27
Please monitor the cluster for possible problems
- Thanks,
EnterpriseDB NGen Cluster Monitor
The preceding example is from an SMR replication network consisting of one replication
server on a single host.
The following example is the logging output from an MMR cluster with three replication
servers, each on its own separate host.
The monitor.properties file is configured as shown by the following:
#Cluster related settings
#Time interval between each monitoring run expressed in ms
ngen.monitor.interval=5000
#Total number of nodes in the cluser. Change this and uncomment and change related
#*.host.node.x and *.port.node.x properties below as per your cluster configuration
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
114
#and IP/Port information.
ngen.total.nodes=3
ngen.jolokia.host.node.1=192.168.2.27
#Jolokia port for REST API defaults to 8778 unless changed while attaching agent
ngen.jolokia.port.node.1=8778
ngen.jolokia.host.node.2=192.168.2.28
ngen.jolokia.port.node.2=8778
ngen.jolokia.host.node.3=192.168.2.29
ngen.jolokia.port.node.3=8778
#Source email address from where alerts are triggered
#Specify encrypted password for sender email id. To encrypt the password, use
#RepCLI -encrypt command (-encrypt -input <input file location> -output <output file
location>)
ngen.sender.email.encrypted.password=ygJ9AxoJEX854elcVIJPTw==
#Please enter your gmail adddress to get cluster alerts
#request-latency-avg - The average request latency in ms for a producer
ngen.producer.request-latency-avg=1000.0
#Alert email filter timeout expressed in minutes
ngen.alert.filter.timeout=10
The metrics from each replication server are displayed:
2018-10-23 15:38:23,999 [c.e.n.m.m.JMXMonitorApp] - configHome: /usr/edb/rs-7.0/monitor
2018-10-23 15:38:24,110 [c.e.n.m.c.c.StartCommand] - monitorInterval:5000
2018-10-23 15:38:24,597 [] - #### Critical Alert Metrics at 2018-10-23 15:38:24 for
node running on IP:192.168.2.27 ####
2018-10-23 15:38:33,340 [] - ActiveControllerCount : 1
2018-10-23 15:38:33,341 [] - OfflinePartitionsCount : 0
2018-10-23 15:38:33,341 [] - UnderReplicatedPartitions : 0
2018-10-23 15:38:35,259 [] - producer-1->record-error-rate : 0.0
2018-10-23 15:38:35,259 [] - producer-1->request-latency-avg : 0.0
2018-10-23 15:38:35,307 [] - Producer__ngx_events->record-error-rate : 0.0
2018-10-23 15:38:35,308 [] - Producer__ngx_events->request-latency-avg : 0.0
2018-10-23 15:38:35,361 [] - RequestHandlerAvgIdlePercent : 0.9994098173229592
2018-10-23 15:38:35,362 [] - LeaderCount : 19
2018-10-23 15:38:35,362 [] - PartitionCount : 41
2018-10-23 15:38:35,362 [] - BytesInPerSec : 2.5101108438473623
2018-10-23 15:38:35,363 [] - BytesOutPerSec : 7.147617107113453
2018-10-23 15:38:35,551 [] - producer-1->_schemas->record-error-rate : 0.0
2018-10-23 15:38:35,553 [] - #### Critical Alert Metrics at 2018-10-23 15:38:35 for
node running on IP:192.168.2.28 ####
2018-10-23 15:38:37,589 [] - ActiveControllerCount : 0
2018-10-23 15:38:37,590 [] - OfflinePartitionsCount : 0
2018-10-23 15:38:37,590 [] - UnderReplicatedPartitions : 0
2018-10-23 15:38:39,354 [] - producer-1->record-error-rate : 0.0
2018-10-23 15:38:39,354 [] - producer-1->request-latency-avg : 0.0
2018-10-23 15:38:39,422 [] - RequestHandlerAvgIdlePercent : 0.999577776614883
2018-10-23 15:38:39,423 [] - LeaderCount : 16
2018-10-23 15:38:39,423 [] - PartitionCount : 38
2018-10-23 15:38:39,423 [] - BytesInPerSec : 0.930111402192363
2018-10-23 15:38:39,424 [] - BytesOutPerSec : 0.0
2018-10-23 15:38:39,569 [] - producer-1->_schemas->record-error-rate : 0.0
2018-10-23 15:38:39,571 [] - #### Critical Alert Metrics at 2018-10-23 15:38:39 for
node running on IP:192.168.2.29 ####
2018-10-23 15:38:41,594 [] - ActiveControllerCount : 0
2018-10-23 15:38:41,594 [] - OfflinePartitionsCount : 0
2018-10-23 15:38:41,595 [] - UnderReplicatedPartitions : 0
2018-10-23 15:38:41,680 [] - producer-1->record-error-rate : 0.0
2018-10-23 15:38:41,681 [] - producer-1->request-latency-avg : 0.0
2018-10-23 15:38:41,734 [] - RequestHandlerAvgIdlePercent : 0.9995174745448492
2018-10-23 15:38:41,734 [] - LeaderCount : 22
2018-10-23 15:38:41,734 [] - PartitionCount : 38
2018-10-23 15:38:41,735 [] - BytesInPerSec : 0.4787473692240654
2018-10-23 15:38:41,735 [] - BytesOutPerSec : 0.0
2018-10-23 15:38:41,875 [] - producer-1->_schemas->record-error-rate : 0.0
2018-10-23 15:38:41,876 [] - End of monitoring cycle
.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
115
.
.
When the replication server on host 192.168.2.28 aborts, the monitoring output displays
the following:
2018-10-23 15:40:17,204 [] - #### Critical Alert Metrics at 2018-10-23 15:40:17 for
node running on IP:192.168.2.28 ####
2018-10-23 15:40:17,212 [] - Exception while running monitoring code:Cannot connect to
http://192.168.2.28:8778/jolokia/: Connect to 192.168.2.28:8778 [/192.168.2.28] failed:
Connection refused (Connection refused) type:J4pConnectException
2018-10-23 15:40:17,212 [c.e.n.m.a.NodeDownOrUnreachableAlert] - Alert! Node running on
IP 192.168.2.28 is either down or unreachable.
2018-10-23 15:40:17,240 [] - #### Critical Alert Metrics at 2018-10-23 15:40:17 for
node running on IP:192.168.2.29 ####
2018-10-23 15:40:18,355 [] - ActiveControllerCount : 0
2018-10-23 15:40:18,356 [] - OfflinePartitionsCount : 0
2018-10-23 15:40:18,356 [] - UnderReplicatedPartitions : 0
2018-10-23 15:40:20,655 [] - producer-1->record-error-rate : 0.0
2018-10-23 15:40:20,655 [] - producer-1->request-latency-avg : 0.0
2018-10-23 15:40:20,705 [] - AvroProducer__ngx_consumer->record-error-rate : 0.0
2018-10-23 15:40:20,705 [] - AvroProducer__ngx_consumer->request-latency-avg : 0.0
2018-10-23 15:40:20,749 [] - AvroProducer_deptpub-db3->record-error-rate : 0.0
2018-10-23 15:40:20,750 [] - AvroProducer_deptpub-db3->request-latency-avg : 15.0
2018-10-23 15:40:20,818 [] - RequestHandlerAvgIdlePercent : 0.9993989983599045
2018-10-23 15:40:20,818 [] - LeaderCount : 40
2018-10-23 15:40:20,819 [] - PartitionCount : 72
2018-10-23 15:40:20,819 [] - BytesInPerSec : 4.005992659422693
2018-10-23 15:40:20,819 [] - BytesOutPerSec : 5.214809559035466
2018-10-23 15:40:21,051 [] - producer-1->_schemas->record-error-rate : 0.0
2018-10-23 15:40:21,051 [] - AvroProducer__ngx_consumer->_ngx_consumer->record-error-
rate : 0.0
2018-10-23 15:40:21,051 [] - AvroProducer_deptpub-db3->deptpub-db3->record-error-rate :
0.0
2018-10-23 15:40:21,052 [] - AvroProducer_deptpub-db3->_ngx_pub_deptpub->record-error-
rate : 0.0
2018-10-23 15:40:21,052 [] - End of monitoring cycle
2018-10-23 15:40:21,053 [] - #### Critical Alert Metrics at 2018-10-23 15:40:21 for
node running on IP:192.168.2.27 ####
2018-10-23 15:40:22,145 [c.e.n.m.a.NodeDownOrUnreachableAlert] - An alert email has
been sent for 'Node Down or Unreachable' event for node running on IP:192.168.2.28
2018-10-23 15:40:22,882 [] - ActiveControllerCount : 1
2018-10-23 15:40:22,882 [] - OfflinePartitionsCount : 0
2018-10-23 15:40:22,882 [] - UnderReplicatedPartitions : 16
2018-10-23 15:40:22,882 [c.e.n.m.a.MetricAlert] - Alert! Metric
UnderReplicatedPartitions has value 16
2018-10-23 15:40:23,001 [] - producer-1->record-error-rate : 0.0
2018-10-23 15:40:23,002 [] - producer-1->request-latency-avg : 0.0
2018-10-23 15:40:23,050 [] - AvroProducer_deptpub-db1->record-error-rate : 0.0
2018-10-23 15:40:23,050 [] - AvroProducer_deptpub-db1->request-latency-avg : 0.0
2018-10-23 15:40:23,094 [] - AvroProducer__ngx_consumer->record-error-rate : 0.0
2018-10-23 15:40:23,094 [] - AvroProducer__ngx_consumer->request-latency-avg : 0.0
2018-10-23 15:40:23,148 [] - Producer__ngx_events->record-error-rate : 0.0
2018-10-23 15:40:23,148 [] - Producer__ngx_events->request-latency-avg : 0.0
2018-10-23 15:40:23,200 [] - RequestHandlerAvgIdlePercent : 0.9994203768635388
2018-10-23 15:40:23,200 [] - LeaderCount : 52
2018-10-23 15:40:23,200 [] - PartitionCount : 76
2018-10-23 15:40:23,200 [] - BytesInPerSec : 2.877900092333613
2018-10-23 15:40:23,200 [] - BytesOutPerSec : 6.515814989288797
2018-10-23 15:40:23,486 [] - producer-1->_schemas->record-error-rate : 0.0
2018-10-23 15:40:23,486 [] - AvroProducer_deptpub-db1->deptpub-db1->record-error-rate :
0.0
2018-10-23 15:40:23,486 [] - AvroProducer_deptpub-db1->_ngx_pub_deptpub->record-error-
rate : 0.0
2018-10-23 15:40:23,486 [] - AvroProducer__ngx_consumer->_ngx_consumer->record-error-
rate : 0.0
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
116
2018-10-23 15:40:23,487 [] - #### Critical Alert Metrics at 2018-10-23 15:40:23 for
node running on IP:192.168.2.28 ####
2018-10-23 15:40:23,490 [] - Exception while running monitoring code:Cannot connect to
http://192.168.2.28:8778/jolokia/: Connect to 192.168.2.28:8778 [/192.168.2.28] failed:
Connection refused (Connection refused) type:J4pConnectException
2018-10-23 15:40:23,493 [c.e.n.m.a.AlertAdmin] - Previous alert for Node Down or
Unreachable Alert Type:192.168.2.28 was sent at 2018-10-23 15:40:22 and alert filter
timeout is 10 minutes, so current alert will be ignored.
2018-10-23 15:40:23,494 [] - #### Critical Alert Metrics at 2018-10-23 15:40:23 for
node running on IP:192.168.2.29 ####
2018-10-23 15:40:24,689 [c.e.n.m.a.MetricAlert] - Alert email has been sent for metric:
UnderReplicatedPartitions
2018-10-23 15:40:25,207 [] - ActiveControllerCount : 0
2018-10-23 15:40:25,207 [] - OfflinePartitionsCount : 0
2018-10-23 15:40:25,207 [] - UnderReplicatedPartitions : 38
2018-10-23 15:40:25,208 [c.e.n.m.a.AlertAdmin] - Previous alert for
UnderReplicatedPartitions was sent at 2018-10-23 15:40:24 and alert filter timeout is
10 minutes, so current alert will be ignored.
2018-10-23 15:40:27,484 [] - producer-1->record-error-rate : 0.0
2018-10-23 15:40:27,484 [] - producer-1->request-latency-avg : 0.0
2018-10-23 15:40:27,535 [] - AvroProducer__ngx_consumer->record-error-rate : 0.0
2018-10-23 15:40:27,535 [] - AvroProducer__ngx_consumer->request-latency-avg : 0.0
2018-10-23 15:40:27,579 [] - AvroProducer_deptpub-db3->record-error-rate : 0.0
2018-10-23 15:40:27,579 [] - AvroProducer_deptpub-db3->request-latency-avg : 15.0
2018-10-23 15:40:27,629 [] - RequestHandlerAvgIdlePercent : 0.9994212102673165
2018-10-23 15:40:27,629 [] - LeaderCount : 56
2018-10-23 15:40:27,629 [] - PartitionCount : 72
2018-10-23 15:40:27,629 [] - BytesInPerSec : 3.9824240360211713
2018-10-23 15:40:27,629 [] - BytesOutPerSec : 5.184129161723588
2018-10-23 15:40:27,853 [] - producer-1->_schemas->record-error-rate : 0.0
2018-10-23 15:40:27,853 [] - AvroProducer__ngx_consumer->_ngx_consumer->record-error-
rate : 0.0
2018-10-23 15:40:27,853 [] - AvroProducer_deptpub-db3->deptpub-db3->record-error-rate :
0.0
2018-10-23 15:40:27,853 [] - AvroProducer_deptpub-db3->_ngx_pub_deptpub->record-error-
rate : 0.0
2018-10-23 15:40:27,854 [] - End of monitoring cycle
Several email alerts are sent.
First email alert:
Admin,
Alert for Kafka Cluster!
Following node appears to be either down or is not reachable -
NGen node running on IP:192.168.2.28
Please monitor the cluster for possible problems
- Thanks,
EnterpriseDB NGen Cluster Monitor
Second email alert:
Admin,
Alert for Kafka Cluster!
Following metric shows deviation from normal value -
Metric name:UnderReplicatedPartitions, Current value:16
Expected value for this metric: 0
Please monitor the cluster for possible problems.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
117
- Thanks,
EnterpriseDB NGen Cluster Monitor
The alerts continue as the Kafka cluster remains down.
Allow Monitoring Apps to Sign in to Google Accounts 5.2.1
To enable sending alerts from your Gmail account follow the steps below:
Step 1: In the monitor.properties file located in the directory set the following
parameters in EPRS_HOME/monitor/etc:
ngen.total.nodes=number_of_replication_servers
ngen.jolokia.host.node.n=replication_server_host_ip
ngen.jolokia.port.node.n=8778
ngen.sender.email=alert_sender_email_address
ngen.sender.email.encrypted.password=encrypted_sender_passwd
ngen.recipient.email=recipient_email_address
Step 2: Encrypt the sender email password for
ngen.sender.email.encrypted.password with the encrypt command executed
with the runMonitor.sh script in the EPRS_HOME/monitor/bin directory:
./runMonitor.sh –encrypt –input unencrypted_password_file
–output encrypted_password_file
#Source email address from where alerts are triggered
#Specify encrypted password for sender email id. To encrypt the password, use
#RepCLI -encrypt command (-encrypt -input <input file location> -output <output file
location>)
ngen.sender.email.encrypted.password=ygJ9AxoJEX854elcVIJPTw==
#Please enter your gmail adddress to get cluster alerts
Step 3: Sign in to your Gmail account.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
118
Step 4: Go to Settings > Accounts > Google Account settings > security.
Figure 5-1: Google security settings
Step 5: Enable Less secure app access.
Allow less secure apps: ON
Step 6: Run the ./runMonitor.sh command to send an email.
./runMonitor.sh -sendmail
[root@localhost bin]# ./runMonitor.sh –sendmail
2019-01-18 01:13:11,853 [c.e.n.m.m.JMXMonitorApp] - configHome: /usr/edb/rs-7.0/monitor
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
119
2019-01-18 01:13:12,328 [c.e.n.m.a.TestAlert] - Sending test alert to
2019-01-18 01:13:18,327 [c.e.n.m.a.TestAlert] - A test email has been sent to
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
120
6 Miscellaneous Configuration
This chapter describes various other configuration processes that may be required, which
are the following:
Conflict Detection, Handling, and Recovery. Primary key uniqueness and
foreign key constraint violations (see Section 6.1)
AWS Cloud Computing. AWS instance configuration for a replication network
(see Section 6.2)
The next sections cover these topics.
6.1 Conflict Detection, Handling, and Recovery
During changed data streaming between producer and consumer databases, there may be
a situation where an attempted execution of a SQL command against a target database
results in a constraint violation in the target database.
The types of constraint violations that may be detected and handled are the following:
Primary key uniqueness violation due to an insert-insert conflict where an
attempted insert of a row in a target table results in the violation of the primary
key constraint whereby all primary key values amongst all rows must be unique
(see Section 6.1.1).
Foreign key constraint violation where the foreign key relationship defined
between the parent and child tables is violated by an attempted change to the rows
of the parent or child table (see Section 6.1.2).
The following sections describe these conflicts.
Primary Key Uniqueness 6.1.1
A primary key uniqueness violation is caused by an insert-insert uniqueness conflict,
which is an attempted insert of a row that would result in a pair of rows with the same
primary key value in the same table.
Handling of the primary key uniqueness conflict can be done in one of the following
manners:
Log the conflict and stop any further replication of changed data (see Section
6.1.1.1).
Log the conflict, skip the replication of the conflicting row, and continue
replication of the remaining changed data (see Section 6.1.1.2).
Log the conflict and periodically retry by a certain time interval, the transaction
containing the conflicting row (see Section 6.1.1.3).
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
121
Usage of the stop, skip or retry action is determined by the setting of the following
parameter in the application.properties file located in the
EPRS_HOME/server/etc directory located on the host of the replication server to
which the database that may contain the conflict has been added:
unique.conflict.resolution.policy={ STOP | SKIP | RETRY }
The default policy is STOP.
The following parameter defines the retry period in seconds when the RETRY policy has
been set:
unique.conflict.retry.period=number_of_seconds
The default retry period is 60 seconds. The following shows these parameters in the
application.properties file:
# Conflict Resolution settings
#
# Defines the conflict resolution policy for unique-violations (i.e. insert-insert
conflicts)
# Valid values are STOP, SKIP, RETRY
#unique.conflict.resolution.policy=STOP
# Defines the retry period in seconds for the RETRY conflict resolution policy
#unique.conflict.retry.period=60
The conflict logging information is stored in the directory and file specified by the
property element of the EPRS_HOME/server/etc/logback.xml file as shown by
the following:
<property name="LOG_DIR"
value="/var/log/edb/rs/replication-server" />
<property name="LOG_FILE_NAME" value="ngx-server.log"/>
This logging information is also displayed by the output of the runServer.sh
command used to start the replication server.
Note that this conflict logging information is contained only in the
EPRS_HOME/server/etc directory of the host machine running the replication server
whose added database has encountered the conflict.
Conflicts can also be displayed by the listconflicts RepCLI command (see Section
2.4.36).
The following sections show the usage of conflict resolution policies.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
122
6.1.1.1 Stop Conflict Resolution Policy
When the conflict resolution policy parameter is set to the following or left commented
for the default action, the streaming is halted.
unique.conflict.resolution.policy=STOP
The cause of the uniqueness conflict should be manually corrected in the database and
then you can restart the streaming.
Note: For a three-node cluster the replication stops only for the target node that has any
conflicts. In case of a database without any conflicts replication will be active on it.
The following is an example in an SMR cluster with a producer database and a single
consumer database, each added to its own separate replication server on separate host
machines.
The producer and consumer databases, node1 and node2, contain the public.dept
table definition:
CREATE TABLE dept (
deptno NUMERIC(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname VARCHAR(14) CONSTRAINT dept_dname_uq UNIQUE,
loc VARCHAR(13)
);
ALTER TABLE dept REPLICA IDENTITY FULL;
On the source database, node1, the public.dept table is populated with the following
rows:
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
The replication server is started on the host.
./runServer.sh --host 192.168.2.27 --config /usr/edb/rs-7.0/server/etc
The network is joined and the administrator password is set and saved:
$ ./runRepCLI.sh -joinnetwork -servername localService -host 192.168.2.27 -port 8082
Server is successfully added in the network.
$ ./runRepCLI.sh –setadminpassword -savepassword
Enter admin password:
Admin password is set successfully.
The database is added and the publication created on the producer database:
$ ./runRepCLI.sh -adddb -servername localService -dbid db1 -dbtype postgresql \
> -dbhost 192.168.2.27 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node1 -user admin
Database is registered successfully.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
123
$ ./runRepCLI.sh -createpub -pubname deptpub -servername localService -dbid db1 \
> -tables public.dept -user admin
Publication is created successfully.
The replication server is started on the host machine for the consumer database.
./runServer.sh --host 192.168.2.29 --config /usr/edb/rs-7.0/server/etc
The started replication server is joined to the replication network, the consumer database
is added to this remoteService replication server, and the publication joined on the
consumer database:
$ ./runRepCLI.sh -joinnetwork -servername remoteService -host 192.168.2.29 \
> -port 8082 -user admin
Server is successfully added in the network.
$ ./runRepCLI.sh -adddb -servername remoteService -dbid db2 -dbtype postgresql \
> -dbhost 192.168.2.29 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node2 -user admin
Database is registered successfully.
$ ./runRepCLI.sh -joinpub -servername remoteService -dbid db2 -pubname deptpub \
> -user admin
Node has successfully joined the Publication.
The initial snapshot is performed and streaming is started. The consumer database now
has the same rows as the producer database.
$ ./runRepCLI.sh -startsnapshot -pubname deptpub \
> -dbid db2 -user admin
Data snapshot is performed successfully.
$ ./runRepCLI.sh -startstreaming -pubname deptpub -user admin
Streaming is started successfully for the Publication deptpub.
The following insert of a row in the consumer database will cause a subsequent
uniqueness conflict. Since this database was not given write permission to the publication
with the -nodetype RW option in the joinpub command, this insert is not streamed to
the producer database.
node2=# INSERT INTO dept VALUES (50,'HUMAN RESOURCE','ST. LOUIS');
INSERT 0 1
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+----------------+-----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
50 | HUMAN RESOURCE | ST. LOUIS
(3 rows)
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
124
Now the following inserts in the producer database are attempted to stream to the
consumer, but the uniqueness conflict on the deptno primary key value of 50 results in
the cause of the conflict.
node1=# INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT 0 1
node1=# INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
INSERT 0 1
node1=# INSERT INTO dept VALUES (50,'HR','DENVER');
INSERT 0 1
node1=# INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
INSERT 0 1
node1=# INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
INSERT 0 1
node1=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+-------------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
50 | HR | DENVER
60 | FINANCE | CHICAGO
70 | MARKETING | LOS ANGELES
(7 rows)
None of these newly inserted rows are applied to the consumer database. Only the
original rows by the snapshot and the manually inserted row that created the conflict are
present.
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+----------------+-----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
50 | HUMAN RESOURCE | ST. LOUIS
(3 rows)
The listconflicts command displays the conflicting information.
$ ./runRepCLI.sh -listconflicts -pubname deptpub -user admin
Publication Table Source DB Target DB Conflict Time Conflict Type
Policy Details
=========== ===== ========= ========= ============= =============
====== =======
deptpub public.dept db1 db2 2018-11-26 10:33:27.775 unique-
violation STOP insert into public.dept (loc, dname, deptno) values ('DENVER', 'HR',
'50.0')
To correct the situation, the conflicting row in the consumer database is manually
deleted.
node2=# DELETE FROM dept WHERE deptno = 50;
DELETE 1
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
(2 rows)
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
125
Streaming is then restarted.
$ ./runRepCLI.sh -startstreaming -pubname deptpub -user admin
Streaming is started successfully for the Publication deptpub.
The consumer database now contains the rows that initially were halted from replicating
to the consumer database because of the uniqueness conflict.
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+-------------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
50 | HR | DENVER
60 | FINANCE | CHICAGO
70 | MARKETING | LOS ANGELES
(7 rows)
The log file from the remoteService replication server contains the following warning
regarding the uniqueness conflict:
2018-11-26 10:33:27,775 [pool-33-thread-2] WARN [c.e.n.c.ChangeDataCaptureWriterTask]
- A unique conflict is detected for Publication deptpub in table public.dept across
source database id db1 and target database id db2.
2018-11-26 10:33:27,775 [pool-33-thread-2] INFO [c.e.n.c.ChangeDataCaptureWriterTask]
- The unique conflict resolution strategy is configured as STOP. Streaming of
Publication deptpub will be stopped for target database id db2.
6.1.1.2 Skip Conflict Resolution Policy
When the conflict resolution policy parameter is set to the following, the streaming
continues, but the transaction with the conflict is skipped.
unique.conflict.resolution.policy=SKIP
As streaming continues, the change data capture continues and is replicated to the target
databases.
The row in the target database that resulted in the uniqueness conflict should be manually
corrected.
The following is an example in an SMR cluster with a producer database and a single
consumer database, each added to its own separate replication server on separate host
machines.
The producer and consumer databases, node1 and node2, contain the public.dept
table definition:
CREATE TABLE dept (
deptno NUMERIC(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname VARCHAR(14) CONSTRAINT dept_dname_uq UNIQUE,
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
126
loc VARCHAR(13)
);
ALTER TABLE dept REPLICA IDENTITY FULL;
On the source database, node1, the public.dept table is populated with the following
rows:
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
The replication server is started on the host.
./runServer.sh --host 192.168.2.27 --config /usr/edb/rs-7.0/server/etc
The network is joined and the administrator password is set and saved:
$ ./runRepCLI.sh -joinnetwork -servername localService -host 192.168.2.27 -port 8082
Server is successfully added in the network.
$ ./runRepCLI.sh –setadminpassword -savepassword
Enter admin password:
Admin password is set successfully.
The database is added and the publication created on the producer database:
$ ./runRepCLI.sh -adddb -servername localService -dbid db1 -dbtype postgresql \
> -dbhost 192.168.2.27 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node1 -user admin
Database is registered successfully.
$ ./runRepCLI.sh -createpub -pubname deptpub -servername localService -dbid db1 \
> -tables public.dept -user admin
Publication is created successfully.
The replication server is started on the host machine for the consumer database.
./runServer.sh --host 192.168.2.29 --config /usr/edb/rs-7.0/server/etc
The started replication server is joined to the replication network, the consumer database
is added to this remoteService replication server, and the publication joined on the
consumer database:
$ ./runRepCLI.sh -joinnetwork -servername remoteService -host 192.168.2.29 \
> -port 8082 -user admin
Server is successfully added in the network.
$ ./runRepCLI.sh -adddb -servername remoteService -dbid db2 -dbtype postgresql \
> -dbhost 192.168.2.29 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node2 -user admin
Database is registered successfully.
$ ./runRepCLI.sh -joinpub -servername remoteService -dbid db2 -pubname deptpub \
> -user admin
Node has successfully joined the Publication.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
127
The initial snapshot is performed and streaming is started. The consumer database now
has the same rows as the producer database.
$ ./runRepCLI.sh -startsnapshot -pubname deptpub localService \
> -dbid db2 -user admin
Data snapshot is performed successfully.
$ ./runRepCLI.sh -startstreaming -pubname deptpub -user admin
Streaming is started successfully for the Publication deptpub.
The following insert of a row in the consumer database will cause a subsequent
uniqueness conflict. Since this database was not given write permission to the publication
with the -nodetype RW option in the joinpub command, this insert is not streamed to
the producer database.
node2=# INSERT INTO dept VALUES (50,'HUMAN RESOURCE','ST. LOUIS');
INSERT 0 1
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+----------------+-----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
50 | HUMAN RESOURCE | ST. LOUIS
(3 rows)
Now the following inserts in the producer database are streamed and applied to the
consumer database except for the row affected by the uniqueness conflict on the deptno
primary key value of 50.
node1=# INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT 0 1
node1=# INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
INSERT 0 1
node1=# INSERT INTO dept VALUES (50,'HR','DENVER');
INSERT 0 1
node1=# INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
INSERT 0 1
node1=# INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
INSERT 0 1
node1=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+-------------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
50 | HR | DENVER
60 | FINANCE | CHICAGO
70 | MARKETING | LOS ANGELES
(7 rows)
All of these newly inserted rows are applied to the consumer database except for the row
with deptno of 50 as the original conflicting row manually inserted into the consumer
database is still present.
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+----------------+-------------
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
128
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
50 | HUMAN RESOURCE | ST. LOUIS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
60 | FINANCE | CHICAGO
70 | MARKETING | LOS ANGELES
(7 rows)
This row in the consumer database should be manually modified to be identical to the
same row in the producer database.
The listconflicts command displays the conflicting information.
$ ./runRepCLI.sh -listconflicts -pubname deptpub -user admin
Publication Table Source DB Target DB Conflict Time Conflict Type
Policy Details
=========== ===== ========= ========= ============= =============
====== =======
deptpub public.dept db1 db2 2018-11-26 14:48:52.906 unique-
violation SKIP insert into public.dept (loc, dname, deptno) values ('DENVER', 'HR',
'50.0')
The log file from the remoteService replication server contains the following warning
regarding the uniqueness conflict:
2018-11-26 14:48:52,906 [pool-33-thread-2] WARN [c.e.n.c.ChangeDataCaptureWriterTask]
- A unique conflict is detected for Publication deptpub in table public.dept across
source database id db1 and target database id db2.
2018-11-26 14:48:52,912 [pool-33-thread-2] ERROR [c.e.n.c.ChangeDataCaptureWriterTask]
- Batch entry 2 insert into public.dept (loc, dname, deptno) values ('DENVER', 'HR',
'50.0') was aborted: ERROR: duplicate key value violates unique constraint "dept_pk"
Detail: Key (deptno)=(50) already exists. Call getNextException to see other errors
in the batch.
com.edb.ngen.exception.UniqueConflictException: Batch entry 2 insert into public.dept
(loc, dname, deptno) values ('DENVER', 'HR', '50.0') was aborted: ERROR: duplicate key
value violates unique constraint "dept_pk"
.
.
.
2018-11-26 14:48:52,912 [pool-33-thread-2] INFO [c.e.n.c.ChangeDataCaptureWriterTask]
- The unique conflict resolution strategy is configured as SKIP. The transaction for
Publication deptpub will be re-attempted on target database id db2 by skipping the
conflicted row.
6.1.1.3 Retry Conflict Resolution Policy
When the conflict resolution policy parameter is set to the following, the transaction is
repeatedly retried.
unique.conflict.resolution.policy=RETRY
The time interval between reattempts is defined by the following parameter:
unique.conflict.retry.period=number_of_seconds
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
129
The row in the database that resulted in the uniqueness conflict should be manually
corrected so that a retry will succeed.
Note: For a three-node cluster the replication stops only for the target node that has any
conflicts. In case of a database without any conflicts replication will be active on it.
The following is an example in an SMR cluster with a producer database and a single
consumer database, each added to its own separate replication server on separate host
machines.
The producer and consumer databases, node1 and node2, contain the public.dept
table definition:
CREATE TABLE dept (
deptno NUMERIC(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname VARCHAR(14) CONSTRAINT dept_dname_uq UNIQUE,
loc VARCHAR(13)
);
ALTER TABLE dept REPLICA IDENTITY FULL;
On the source database, node1, the public.dept table is populated with the following
rows:
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
The replication server is started on the host.
./runServer.sh --host 192.168.2.27 --config /usr/edb/rs-7.0/server/etc
The network is joined and the administrator password is set and saved:
$ ./runRepCLI.sh -joinnetwork -servername localService -host 192.168.2.27 -port 8082
Server is successfully added in the network.
$ ./runRepCLI.sh –setadminpassword -savepassword
Enter admin password:
Admin password is set successfully.
The database is added and the publication created on the producer database:
$ ./runRepCLI.sh -adddb -servername localService -dbid db1 -dbtype postgresql \
> -dbhost 192.168.2.27 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node1 -user admin
Database is registered successfully.
$ ./runRepCLI.sh -createpub -pubname deptpub -servername localService -dbid db1 \
> -tables public.dept -user admin
Publication is created successfully.
The replication server is started on the host machine for the consumer database.
./runServer.sh --host 192.168.2.29 --config /usr/edb/rs-7.0/server/etc
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
130
The started replication server is joined to the replication network, the consumer database
is added to this remoteService replication server, and the publication joined on the
consumer database:
$ ./runRepCLI.sh -joinnetwork -servername remoteService -host 192.168.2.29 \
> -port 8082 -user admin
Server is successfully added in the network.
$ ./runRepCLI.sh -adddb -servername remoteService -dbid db2 -dbtype postgresql \
> -dbhost 192.168.2.29 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node2 -user admin
Database is registered successfully.
$ ./runRepCLI.sh -joinpub -servername remoteService -dbid db2 -pubname deptpub \
> -user admin
Node has successfully joined the Publication.
The initial snapshot is performed and streaming is started. The consumer database now
has the same rows as the producer database.
$ ./runRepCLI.sh -startsnapshot -pubname deptpub \
> -dbid db2 -user admin
Data snapshot is performed successfully.
$ ./runRepCLI.sh -startstreaming -pubname deptpub -user admin
Streaming is started successfully for the Publication deptpub.
The following insert of a row in the consumer database will cause a subsequent
uniqueness conflict. Since this database was not given write permission to the publication
with the -nodetype RW option in the joinpub command, this insert is not streamed to
the producer database.
node2=# INSERT INTO dept VALUES (50,'HUMAN RESOURCE','ST. LOUIS');
INSERT 0 1
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+----------------+-----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
50 | HUMAN RESOURCE | ST. LOUIS
(3 rows)
Now the following inserts are made in the producer database.
node1=# INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT 0 1
node1=# INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
INSERT 0 1
node1=# INSERT INTO dept VALUES (50,'HR','DENVER');
INSERT 0 1
node1=# INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
INSERT 0 1
node1=# INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
INSERT 0 1
node1=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+-------------
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
131
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
50 | HR | DENVER
60 | FINANCE | CHICAGO
70 | MARKETING | LOS ANGELES
(7 rows)
None of these inserts are applied to the consumer database since there is now a conflict
on the row with primary key value 50. The transaction is retried every 60 seconds but
does not occur on the consumer database.
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+----------------+-----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
50 | HUMAN RESOURCE | ST. LOUIS
(3 rows)
The listconflicts command displays the conflicting information.
$ ./runRepCLI.sh -listconflicts -pubname deptpub -user admin
Publication Table Source DB Target DB Conflict Time Conflict Type
Policy Details
=========== ===== ========= ========= ============= =============
====== =======
deptpub public.dept db1 db2 2018-11-27 15:21:54.846 unique-
violation RETRY insert into public.dept (loc, dname, deptno) values ('DENVER', 'HR',
'50.0')
Finally, the conflict is corrected by deleting the row from the consumer database.
node2=# DELETE FROM dept WHERE deptno = 50;
DELETE 1
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
(2 rows)
After the next 60 second retry interval, all the rows inserted on the producer database are
successfully streamed into the consumer database.
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+-------------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
50 | HR | DENVER
60 | FINANCE | CHICAGO
70 | MARKETING | LOS ANGELES
(7 rows)
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
132
The log file from the remoteService replication server contains the following warning
regarding the uniqueness conflict:
2018-11-27 15:21:54,847 [pool-33-thread-2] WARN [c.e.n.c.ChangeDataCaptureWriterTask]
- A unique conflict is detected for Publication deptpub in table public.dept across
source database id db1 and target database id db2.
2018-11-27 15:21:54,847 [pool-33-thread-2] ERROR [c.e.n.c.ChangeDataCaptureWriterTask]
- Batch entry 2 insert into public.dept (loc, dname, deptno) values ('DENVER', 'HR',
'50.0') was aborted: ERROR: duplicate key value violates unique constraint "dept_pk"
Detail: Key (deptno)=(50) already exists. Call getNextException to see other errors
in the batch.
com.edb.ngen.exception.UniqueConflictException: Batch entry 2 insert into public.dept
(loc, dname, deptno) values ('DENVER', 'HR', '50.0') was aborted: ERROR: duplicate key
value violates unique constraint "dept_pk"
.
.
.
2018-11-27 15:21:54,850 [pool-33-thread-2] INFO [c.e.n.c.ChangeDataCaptureWriterTask]
- The unique conflict resolution strategy is configured as RETRY. The transaction for
Publication deptpub will be re-attempted on target database id db2 after 60 seconds.
Foreign Key Constraint 6.1.2
A foreign key constraint is a rule whereby every row of a child or dependent table must
have a corresponding row in its parent table where the foreign key value of the child row
matches the value of the specified column of a parent row.
If a change is attempted that disobeys this rule, then a foreign key constraint violation
occurs resulting in the rejection of the change.
If such a violation is detected in a changed data stream from a source to a target database,
the foreign key conflict resolution policy is enacted.
This policy is a repeated retry of the stream for every time interval as set by the following
parameter in the application.properties file located in the
EPRS_HOME/server/etc directory located on the host of the replication server to
which the database that may contain the conflict has been added:
foreign.key.conflict.retry.period=number_of_seconds
The default number_of_seconds setting is 10 seconds.
The following is an example in an SMR cluster with a producer database and a single
consumer database, each added to its own separate replication server on separate host
machines.
The producer and consumer databases, node1 and node2, contain the public.dept
and public.emp table definitions where the dept table is the parent while the emp table
is the child:
CREATE TABLE dept (
deptno NUMERIC(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname VARCHAR(14) CONSTRAINT dept_dname_uq UNIQUE,
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
133
loc VARCHAR(13)
);
ALTER TABLE dept REPLICA IDENTITY FULL;
CREATE TABLE emp (
empno NUMERIC(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr NUMERIC(4),
hiredate DATE,
sal NUMERIC(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm NUMERIC(7,2),
deptno NUMERIC(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
ALTER TABLE emp REPLICA IDENTITY FULL;
For every row in the emp table, the deptno value must have a corresponding, existing
row in the dept table with the identical deptno primary key value.
On the source database, node1, the dept table is populated with the following rows:
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
There are no initial rows in the emp table of node1.
For the consumer database node2, there are no rows in either table.
The replication server is started on the host.
./runServer.sh --host 192.168.2.27 --config /usr/edb/rs-7.0/server/etc
The network is joined and the administrator password is set and saved:
$ ./runRepCLI.sh -joinnetwork -servername localService -host 192.168.2.27 -port 8082
Server is successfully added in the network.
$ ./runRepCLI.sh –setadminpassword -savepassword
Enter admin password:
Admin password is set successfully.
The database is added and the publication created on the producer database:
$ ./runRepCLI.sh -adddb -servername localService -dbid db1 -dbtype postgresql \
> -dbhost 192.168.2.27 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node1 -user admin
Database is registered successfully.
$ ./runRepCLI.sh -createpub -pubname deptemppub -servername localService -dbid db1 \
> -tables public.dept,public.emp -user admin
Publication is created successfully.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
134
The replication server is started on the host machine for the consumer database.
./runServer.sh --host 192.168.2.29 --config /usr/edb/rs-7.0/server/etc
The started replication server is joined to the replication network, the consumer database
is added to this remoteService replication server, and the publication joined on the
consumer database:
$ ./runRepCLI.sh -joinnetwork -servername remoteService -host 192.168.2.29 \
> -port 8082 -user admin
Server is successfully added in the network.
$ ./runRepCLI.sh -adddb -servername remoteService -dbid db2 -dbtype postgresql \
> -dbhost 192.168.2.29 -dbport 5432 -dbuser postgres \
> -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node2 -user admin
Database is registered successfully.
$ ./runRepCLI.sh -joinpub -servername remoteService -dbid db2 -pubname deptemppub \
> -user admin
Node has successfully joined the Publication.
The initial snapshot is performed and streaming is started.
$ ./runRepCLI.sh -startsnapshot -pubname deptemppub \
> -dbid db2 -user admin
Data snapshot is performed successfully.
$ ./runRepCLI.sh -startstreaming -pubname deptemppub -user admin
Streaming is started successfully for the Publication deptemppub.
The consumer database now has the same rows as the producer database, but then a row
in the dept table is deleted. Since this database was not given write permission to the
publication with the -nodetype RW option in the joinpub command, this deletion is
not streamed to the producer database.
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
(4 rows)
node2=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)
node2=# DELETE FROM dept WHERE deptno = 30;
DELETE 1
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
40 | OPERATIONS | BOSTON
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
135
(3 rows)
Now the following inserts are made in the producer database.
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
node1=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10
(14 rows)
Since there is now a foreign key violation since the parent dept row with primary key
value of 30 does not exist, the replication of the emp rows into node2 does not occur.
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
40 | OPERATIONS | BOSTON
(3 rows)
node2=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)
The listconflicts command displays the conflicting information. Note that the same
SQL command resulting in the foreign key violation is repeatedly displayed.
$ ./runRepCLI.sh -listconflicts -pubname deptemppub -user admin
Publication Table Source DB Target DB Conflict Time Conflict Type
Policy Details
=========== ===== ========= ========= ============= =============
====== =======
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
136
deptemppub public.emp db1 db2 2018-11-27 09:56:30.606 foreign-key-
violation RETRY insert into public.emp (ename, comm, mgr, empno, job, hiredate,
deptno, sal)
values ('ALLEN', '300.0', '7698.0', '7499.0', 'SALESMAN', '1981-02-20', '30.0',
'1600.0')
deptemppub public.emp db1 db2 2018-11-27 09:56:41.073 foreign-key-
violation RETRY insert into public.emp (ename, comm, mgr, empno, job, hiredate,
deptno, sal)
values ('ALLEN', '300.0', '7698.0', '7499.0', 'SALESMAN', '1981-02-20', '30.0',
'1600.0')
deptemppub public.emp db1 db2 2018-11-27 09:56:51.082 foreign-key-
violation RETRY insert into public.emp (ename, comm, mgr, empno, job, hiredate,
deptno, sal)
values ('ALLEN', '300.0', '7698.0', '7499.0', 'SALESMAN', '1981-02-20', '30.0',
'1600.0')
deptemppub public.emp db1 db2 2018-11-27 09:57:01.088 foreign-key-
violation RETRY insert into public.emp (ename, comm, mgr, empno, job, hiredate,
deptno, sal)
values ('ALLEN', '300.0', '7698.0', '7499.0', 'SALESMAN', '1981-02-20', '30.0',
'1600.0')
At some later point, the missing row is manually reinserted into the dept table to correct
the violation, and then the replication of the emp rows occurs after a 10 second interval.
node2=# INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT 0 1
node2=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
40 | OPERATIONS | BOSTON
30 | SALES | CHICAGO
(4 rows)
node2=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)
node2=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10
(14 rows)
Thus, the foreign key constraint violation has been resolved.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
137
The log file from the remoteService replication server contains the following warning
regarding the foreign key constraint violation:
2018-11-27 09:56:30,606 [pool-34-thread-2] ERROR [c.e.n.c.ChangeDataCaptureWriterTask]
- Batch entry 1 insert into public.emp (ename, comm, mgr, empno, job, hiredate, deptno,
sal) values ('ALLEN', '300.0', '7698.0', '7499.0', 'SALESMAN', '1981-02-20', '30.0',
'1600.0') was aborted: ERROR: insert or update on table "emp" violates foreign key
constraint "emp_ref_dept_fk"
Detail: Key (deptno)=(30) is not present in table "dept". Call getNextException to
see other errors in the batch.
com.edb.ngen.exception.ForeignKeyConflictException: Batch entry 1 insert into
public.emp (ename, comm, mgr, empno, job, hiredate, deptno, sal) values ('ALLEN',
'300.0', '7698.0', '7499.0', 'SALESMAN', '1981-02-20', '30.0', '1600.0') was aborted:
ERROR: insert or update on table "emp" violates foreign key constraint
"emp_ref_dept_fk"
.
.
.
2018-11-27 09:56:30,606 [pool-34-thread-2] WARN [c.e.n.c.ChangeDataCaptureWriterTask]
- A foreign key conflict is detected for Publication deptemppub in table public.emp
across source database id db1 and target database id db2.
2018-11-27 09:56:30,607 [pool-34-thread-2] INFO [c.e.n.c.ChangeDataCaptureWriterTask]
- The foreign key conflict resolution strategy is configured as RETRY. The transaction
for Publication deptemppub will be re-attempted on target database id db2 after 10
seconds.
6.2 Setting a Replication Network in AWS Cloud Computing
There may be circumstances where you would like to set up a replication network in the
Amazon Web Services (AWS) cloud computing environment. For information on how to
start this process, see the AWS website:
https://aws.amazon.com/
Once you have gotten to the point where you can create instances in the AWS cloud
environment, there are certain configuration methods that must be done.
The following are basic configuration issues that must be addressed for a replication
network:
The AWS security group of the instance must allow access to various ports used
by the replication network. See Section 6.2.1.
The firewall must allow access to ports used by the replication network. See
Section 6.2.2.
When using the RepCLI commands, references to hosts must be done in a certain
manner depending upon whether the replication network is entirely contained
within a single region or is spread across multiple regions. See Section 6.2.3.
The following sections provide information regarding the preceding requirements.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
138
Security Group Access 6.2.1
A security group acts as a virtual firewall for your instance to control inbound and
outbound traffic. For each security group, you add rules that control the inbound traffic to
instances. For information about security groups, see the following website:
https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-network-security.html
The following shows the screen for editing the inbound rules. These rules must include
the ports that are required by a replication server in an instance.
Figure 6-1 Security group inbound traffic
The following table lists the required port numbers along with their relationship to the
properties files of the replication server. (The required port list has a fewer ports than the
list of ports shown in Figure 6-1.)
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
139
Table 6-1 Security Group Inbound Required Ports
Port Properties Parameter Properties File Component
22 n/a n/a SSH communication
5432 n/a n/a Database server
2181 zookeeper.1.client.port
zookeeper.connect
application.properties
server.properties ZooKeeper
2881 zookeeper.1.peer.port application.properties ZooKeeper
3881 zookeeper.1.election.port application.properties ZooKeeper
2182 zookeeper.2.client.port application.properties ZooKeeper
2882 zookeeper.2.peer.port application.properties ZooKeeper
3882 zookeeper.2.election.port application.properties ZooKeeper
8081 schemaregistry.port application.properties Schema registry
8082 ngen.server.port application.properties Replication server
9092
broker.port
port
bootstrap.servers
application.properties
server.properties
consumer.properties
Kafka broker
The following section shows some additional modifications for the AWS instance.
Firewall Port Additions 6.2.2
Execute firewall commands such as the following on the AWS instance and finally,
iptables --flush:
firewall-cmd --zone=public --add-port=2181/udp
--add-port=2181/tcp –permanent
firewall-cmd --zone=public --add-port=2881/udp
--add-port=2881/tcp --permanent
firewall-cmd --zone=public --add-port=3881/udp
--add-port=3881/tcp –permanent
firewall-cmd --zone=public --add-port=8081/udp
--add-port=8081/tcp –permanent
firewall-cmd --zone=public --add-port=8082/udp
--add-port=8082/tcp –permanent
iptables --flush
The replication server can now be set up.
Referencing Host Locations in RepCLI Commands 6.2.3
When referencing host locations within RepCLI commands with options such as -host
or -dbhost, only certain references may be used.
If the replication network is entirely contained within a single region, then either
the private IP address or the public DNS may be used. Do not use the public IP
address.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
140
If the replication network crosses multiple regions, then the public DNS must be
used. Do not use the private IP address or the public IP address.
If the replication network is to consist of replication servers running in multiple, different
AWS regions, then references to the host must be done using the public DNS. In other
words, the replication servers of the replication network are located in different
geographical locations as identified by their AWS regions.
For information about AWS regions and their availability zones, see the following
website:
https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availability-
zones.html
The following example shows the RepCLI commands for the creation of a replication
node, which needs access across regions. Wherever a host needs to be specified, use the
public DNS. Do not use a public or private IP address. For example, use the public DNS:
ec2-54-234-249-50.compute-1.amazonaws.com. Do not use the public IP: 54-
234-249-50.
./runRepCLI.sh -joinnetwork -servername server1 \
-host ec2-54-234-249-50.compute-1.amazonaws.com -port 8082
./runRepCLI.sh –setadminpassword
./runRepCLI.sh -joinnetwork -servername server2 \
-host ec2-13-210-156-184.ap-southeast-2.compute.amazonaws.com -port 8082 -user admin
./runRepCLI.sh -joinnetwork -servername server3 \
-host ec2-35-183-37-224.ca-central-1.compute.amazonaws.com -port 8082 -user admin
./runRepCLI.sh -adddb -servername server1 -dbid db1 -dbtype postgresql \
-dbhost ec2-54-234-249-50.compute-1.amazonaws.com -dbport 5432 -dbuser postgres \
-dbpassword ygJ9AxoJEX854elcVIJPTw== -database node1 -user admin
./runRepCLI.sh -createpub -pubname testpub -servername server1 -dbid db1 -tables
public.pgbench_accounts,public.pgbench_branches,public.pgbench_history,public.pgbench_t
ellers -nodetype RW -user admin
./runRepCLI.sh -adddb -servername server2 -dbid db2 -dbtype postgresql \
-dbhost ec2-13-210-156-184.ap-southeast-2.compute.amazonaws.com -dbport 5432 \
-dbuser postgres -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node2 -user admin
./runRepCLI.sh -joinpub -servername server2 -dbid db2 -pubname testpub \
-nodetype RW -user admin
./runRepCLI.sh -adddb -servername server3 -dbid db3 -dbtype postgresql \
-dbhost ec2-35-183-37-224.ca-central-1.compute.amazonaws.com -dbport 5432 \
-dbuser postgres -dbpassword ygJ9AxoJEX854elcVIJPTw== -database node3 \
-user admin
./runRepCLI.sh -joinpub -servername server3 -dbid db3 -pubname testpub \
-nodetype RW -user admin
./runRepCLI.sh -startsnapshot -pubname testpub -servername server1 -dbid db2 -user
admin
./runRepCLI.sh -startsnapshot -pubname testpub -servername server1 -dbid db3 -user
admin
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
141
./runRepCLI.sh -startstreaming -pubname testpub -user admin
You should now be able to perform snapshots and streaming between replication servers
running in three separate regions represented by the following public DNS‟:
ec2-54-234-249-50.compute-1.amazonaws.com
ec2-13-210-156-184.ap-southeast-2.compute.amazonaws.com
ec2-35-183-37-224.ca-central-1.compute.amazonaws.com
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
142
7 Data Validator
Data Validator is a utility that compares the rows of one or more tables within a schema
of a database against the rows of the tables with the same names within a schema of
another database. Data Validator generates a summary of the comparison noting the
number of rows whose column values differ. A file containing detailed information
regarding any differences is also generated.
The two databases being compared are referred to as the source database and the target
database. The source database can be of type EnterpriseDB. The target database must
also be of type EnterpriseDB.
An EnterpriseDB database type means either an Advanced Server database or a
PostgreSQL database.
The tables available for comparison are those found in the schema of the source database.
Tables in the target database that do not exist in the source database schema are ignored.
Note: The Data Validator does not validate columns having the following data types.
Tables containing one or more columns of these types will only be partially validated.
BFILE
STRUCT
REF
ARRAY
BLOB
CLOB
RAW
LONG RAW
Note: Make sure that the data streaming between the source and target EDB Postgres
Replication Server tables has been completed before using the Data Validator in EDB
Postgres Replication Server (single-master or multi-master). If streaming is still in
progress, it is possible that the Data Validator will show differences in tables.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
143
7.1 Installation and Configuration
Step 1: When you install the EDB Postgres Replication Server, the components for the
Data Validator are installed as well. See EDB Postgres Replication Getting Started Guide
for information on installing the EDB Postgres Replication Server.
Also, when you uninstall the EDB Postgres Replication Server, the Data Validator
components are uninstalled as well.
The following components that you use to run the Data Validator are installed when you
install the EDB Postgres Replication Server.
Table 7-1 - Data Validator Files
File Name Location Description
datavalidator.properties EPRS_HOME/datavalidator/etc Data Validator Properties file
runValidation.sh (Linux) EPRS_HOME/datavalidator/bin Data Validator execution script
Note: EPRS_HOME is the directory where EDB Postgres Replication Server is installed.
This may or may not be the same as the Postgres home directory depending upon how
EDB Postgres Replication Server is installed. The general format of the EPRS_HOME
directory is /usr/edb/rs-x.x where x.x is the EDB Replication Server version
number, which is initially 7.0.
Step 3: Edit the datavalidator.properties file located in the EPRS_HOME/
datavalidator/etc directory and specify the connection information for the source
and target databases you want to compare.
Any of these parameters can be overridden by an option when you invoke the Data
Validator script. See Section 7.2 for additional information on invoking the Data
Validator.
The following are the parameters in the datavalidator.properties file.
Table 7-2 - Data Validator Properties File Parameters
Parameter Description
source_dbms Type of the source database. Values may be enterprisedb.
source_host IP address or server name of the host running the database
server of the source database
source_port Port number on which the database server of the source
database listens for requests
source_database Database name of the source database
source_user Database user name of the source database
source_password Unencrypted password of the source database user
target_dbms Type of the target database. Values may be enterprisedb.
target_host IP address or server name of the host running the database
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
144
Parameter Description
server of the target database
target_port Port number on which the database server of the target
database listens for requests
target_database Database name of the target database
target_user Database user name of the target database
target_password Unencrypted password of the target database user
The following is the initial content of the datavalidator.properties file after
installation:
###############################################################
# Source database connection #
###############################################################
#
#source_dbms=(enterprisedb | oracle | sqlserver | sybase | mysql)
#
source_dbms=oracle
source_host=localhost
source_port=1521
source_database=xe
source_user=hr
source_password=hr
#source_dbms=mysql
#source_host=localhost
#source_port=3306
#source_database=test
#source_user=root
#source_password=
#source_dbms=sqlserver
#source_host=localhost
#source_port=1433
#source_database=pubs
#source_user=sa
#source_password=
#source_dbms=sybase
#source_host=localhost
#source_port=5004
#source_database=test
#source_user=sa
#source_password=
###############################################################
# Target database connection #
###############################################################
#
#target_dbms=(enterprisedb | oracle)
#
target_dbms=enterprisedb
target_host=localhost
target_port=5444
target_database=edb
target_user=enterprisedb
target_password=edb
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
145
Step 4: Determine the location for the Data Validator logs directory.
Before invoking the Data Validator for the first time, make sure that you have determined
where the Data Validator logs directory should be located.
The Data Validator generates a log file with a name formatted as
datavalidator_yymmdd-hhmiss.log in the logs directory for each run.
If there are row differences between the source and target tables, a file with a name
formatted as datavalidator_yymmdd-hhmiss.diff is also generated that contains
the output of the errors in diff format. Use a graphical diff tool like Kompare to view this
file to highlight the specific differences.
Data Validator attempts to create a subdirectory named logs within the EPRS_HOME/
datavalidator/bin directory the first time you invoke the Data Validator without the
-ld option. If you do not invoke the Data Validator as the root account, it is likely that
the run will fail as it attempts to create subdirectory logs in the EPRS_HOME/
datavalidator/bin directory where typically only the root account has this
privilege.
Choices for determining and setting the Data Validator directory for the log and diff files
are the following:
Run the Data Validator as the root account. This enables the Data Validator to
create the logs subdirectory within the EPRS_HOME/datavalidator/bin
directory, and then to create the log and diff files in the logs subdirectory.
Create the EPRS_HOME/datavalidator/bin/logs directory structure before
running the Data Validator. Modify the permissions on directory
EPRS_HOME/datavalidator/bin/logs so the operating system account you
use to run the Data Validator has the privilege to create files in the directory.
Use the -ld log_directory_path option to allow the Data Validator to create
the log and diff files in the specified directory location log_directory_path.
Be sure the operating system account you use to run the Data Validator has the
proper privileges to either create the lowest level subdirectory specified by
log_directory_path if it does not already exist or to create files within the
specified directory if the full directory path already does exist.
Once you have determined and verified that your operating system account you plan to
use to run the Data Validator can create files in the log directory, you can proceed with
performing data validation.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
146
7.2 Performing Data Validation
The current working directory from which you invoke the Data Validator script
runValidation.sh must be the bin subdirectory containing the script (that is,
EPRS_HOME/datavalidator/bin).
Example
Run the following command before invoking the Data Validator for EDB Postgres
Replication Server version 7.0:
$ cd /usr/edb/rs-7.0/datavalidator/bin
The general command format for invoking the Data Validator is as follows:
./runValidation.sh { –ss | --source-schema } schema_name
[ option ] ...
schema_name is the name of the schema in the source database containing the tables to
be validated. The choices for option are listed later in this section within the Options
subsection.
The following option displays the Data Validator version:
./runValidation.sh { –v | --version }
The version is displayed as follows:
$ ./runValidation.sh --version
7.
The following option displays the help information.
./runValidation.sh { –h | --help }
$ ./runValidation.sh –help
Usage:
runValidation.sh (-v | --version) | (-h | --help)
runValidation.sh (-ss | --source-schema) SOURCE_SCHEMA [OPTIONS]
CONNECTION_INFO_FILE
OPTIONS:
(-ts | --target-schema) target-schema-name
(-it | --include-tables) comma-seperated-tables-name
(-et | --exclude-tables) comma-seperated-tables-name
(-ld | --logging-dir) logging-dir-path
(-ds | --display-summary) (true|false)
(-srs | --skip-rowsonlyin-source) (true|false)
(-srt | --skip-rowsonlyin-target) (true|false)
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
147
(-srb | --skip-rowsin-both) (true|false)
(-fs | --fetch-size) row count
(-bs | --batch-size) row count
(-sdbms | --source-dbms) source database type
(-sh | --source-host) source database server name/IP
(-sp | --source-port) source database server port
(-sdb | --source-database) source database name
(-su | --source-user) source database user id
(-spw | --source-password) source database user password
(-tdbms | --target-dbms) target database type
(-th | --target-host) target database server name/IP
(-tp | --target-port) target database server port
(-tdb | --target-database) target database name
(-tu | --target-user) target database user id
(-tpw | --target-password) target database user password
The general syntax for --version and --help is shown by the following:
./runValidation.sh –ss schema
[ -ts schema ]
[ -it table_1 [,table_2 ] ... ]
[ -et table_1 [,table_2 ] ... ]
[ -srs { true | false } ]
[ -srt { true | false } ]
[ -srb { true | false } ]
[ -ld log_directory_path ]
[ -ds { true | false } ]
[ -sdbms database_type ]
[ -sh host ]
[ -sp port ]
[ -sdb dbname ]
[ -su user ]
[ -spw password ]
[ -tdbms database_type ]
[ -th host ]
[ -tp port ]
[ -tdb dbname ]
[ -tu user ]
[ -tpw password ]
[ -bs row_count ]
[ -fs row_count ]
For clarity, the preceding syntax diagram shows only the single-character form of the
option. The Options subsection lists both the single-character and multi-character forms
of the options.
Specification of any database connection option (-sdbms through -tpw listed in the
preceding syntax diagram) overrides the corresponding parameter in the
datavalidator.properties file. See Section 7.1 for information on the
datavalidator.properties file.
Options
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
148
-ss, --source-schema schema
The schema of the source database containing the tables to be compared against
the target database.
-ts, --target-schema schema
The schema of the target database containing the tables to be compared against
the source database. If omitted, the schema of the target database is the same
schema as specified for the source database with the -ss option.
-it, --include-tables table_1 [,table_2 ] ...
The tables within the source schema that are to be included for comparison. If
omitted, all tables within the source schema are compared against tables in the
target schema with the exception of those tables excluded from comparison using
the -et option. Note: There must be no white space between the comma and
table names.
-et, --exclude-tables table_1 [,table_2 ] ...
The tables within the source schema that are to be excluded from the comparison.
If omitted, only those tables specified with the -it option are included for
comparison. If both the -it and -et options are omitted, all source schema tables
are included for comparison. Note: There must be no white space between the
comma and table names.
-srs, --skip-rowsonlyin-source { true | false }
When true is specified, the logging of differences for rows that exist only in the
source database table are skipped. The default is false.
-srt, --skip-rowsonlyin-target { true | false }
When true is specified, the logging of differences for rows that exist only in the
target database table are skipped. The default is false.
-srb, --skip-rowsin-both { true | false }
When true is specified, the logging of differences for rows that exist both in the
source and target database tables with the same primary key, but with different
non-primary key values are skipped. The default is false.
-ld, --logging-dir log_directory_path
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
149
Directory path to where the Data Validator log and diff files are to be created and
stored. If log_directory_path does not exist, Data Validator attempts to
create it. If a full directory path is not specified log_directory_path is
created or assumed to be located relative to the EPRS_HOME/datavalidator/
bin subdirectory where the runValidation.sh script is invoked. (That is, the
logs directory is EPRS_HOME/datavalidator/bin/log_directory_path.)
Be sure the operating system account used to invoke the runValidation.sh
script has the privileges to create the directory if it does not already exist, or to
create files in the specified directory if it does already exist. If omitted, the default
is the EPRS_HOME/bin/logs directory.
-ds, --display-summary { true | false }
Specify true to display only the Data Validator summary. This omits the source
and target database connection information as well as the detailed breakdown of
the results by a source database table. Specify false to display all of the Data
Validator results. The type and amount of information that is displayed at the
command line console when the Data Validator is invoked is the same
information that is also stored in the log file for that run. If omitted, the default is
false (that is, all of the Data Validator results is displayed).
-sdbms, --source-dbms database_type
The type of the source database server. Supported types are oracle,
enterprisedb, sqlserver, sybase, and mysql.
-sh, --source-host host
The IP address or server name of the host on which the source database server is
running.
-sp, --source-port port
The port number on which the source database server is listening for connections.
-sdb, --source-database dbname
The database name of the source database.
-su, --source-user user
The database user name for connecting to the source database.
-spw, --source-password password
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
150
The password of the source database user in unencrypted form.
-tdbms, --target-dbms database_type
The type of the target database server. Supported types are enterprisedb and
oracle.
-th, --target-host host
The IP address or server name of the host on which the target database server is
running.
-tp, --target-port port
The port number on which the target database server is listening for connections.
-tdb, --target-database dbname
The database name of the target database.
-tu, --target-user user
The database user name for connecting to the target database.
-tpw, --target-password password
The password of the target database user in unencrypted form.
-bs, --batch-size row_count
The -bs option specifies the number of rows to group in a batch to be used for
comparison across the source and target database tables. For example, if a table
contains 1000 rows, then a -bs setting of 100 requires 10 batch iterations to
complete the comparison across the source and target databases. The Data
Validator reads 100 rows, both from the source and target tables, and adds them in
source and target buffers. The validation thread then reads the 100 rows from the
source and target buffers and performs the comparison. It will then move to read
and prepare the next 100 rows for comparison and so on. Note that the actual
database round trips required to bring in 100 rows from the database depends on
the -fs option for the fetch size. For example, an -fs setting of 100 needs just
one round trip whereas an -fs setting of 10 requires 10 database round trips.
-fs, --fetch-size row_count
Performing data validation for tables that are quite large in size may cause the
Data Validator to terminate with an out of heap space error when using the default
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
151
fetch size of 5000 rows. Use the -fs option to specify a smaller fetch size to help
avoid the out of heap space issue. The result set iteration will bring in as many
rows as represented by the row_count value in a single database round trip.
Examples
The following examples use an Advanced Server source and target database to compare
the tables in schema EDB.
The following lists the tables in schema EDB along with the content of tables DEPT and
EMP in the source database:
edb=# SELECT table_name FROM user_tables;
TABLE_NAME
------------------------------
Advanced
DEPT
EMP
JOBHIST
edb=# SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 FINANCE CHICAGO
edb=# SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
9001 SMITH ANALYST 7566 8500 20
9002 ROGERS SALESMAN 7698 8000 4000 30
16 rows selected.
The following lists the tables in the schema public along with the content of tables
dept and emp in the Advanced Server edb target database:
edb=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+--------------
public | dept | table | enterprisedb
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
152
public | emp | table | enterprisedb
public | jobhist | table | enterprisedb
(3 rows)
edb=# SELECT * FROM dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
(4 rows)
edb=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+--------------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10
9001 | SMITH | SALESMAN | 7698 | | 8000.00 | 4000.00 | 30
9002 | ROGERS | SALESMAN | 7698 | | 9500.00 | 4000.00 | 30
(16 rows)
The following differences are observed:
The Source DEPT table contains one extra row with DEPTNO 50 that does not exist
in the target Advanced Server dept table.
The rows in the EMP table with EMPNO values 9001 and 9002 have column values
that differ between the Source and target Advanced Server tables
In this example, the JOBHIST table contains identical rows for both the source
and target Advanced Server tables.
The content of the datavalidator.properties file is set as follows:
###############################################################
# Source database connection #
###############################################################
#
#source_dbms=(enterprisedb | oracle | sqlserver | sybase | mysql)
#
source_dbms=enterprisedb
source_host=localhost
source_port=5444
source_database=node1
source_user=enterprisedb
source_password=password
###############################################################
# Target database connection #
###############################################################
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
153
#
#target_dbms=(enterprisedb | oracle)
#
target_dbms=enterprisedb
target_host=localhost
target_port=5444
target_database=node2
target_user=enterprisedb
target_password=password
The following example compares all tables in the EDB schema against the public
schema.
The Data Validator log files are created in directory
/EPRS_home/datavalidator/bin/logs/ as specified with the -ld option. The
operating system account used to invoke the runValidation.sh script has write access
to the EPRS_home directory so the Data Validator can create the
/EPRS_home/datavalidator/bin/logs subdirectory.
$ cd /usr/edb/rs-7.0/datavalidator/bin
$ pwd
/usr/edb/rs-7.0/datavalidator/bin
$ ./runValidation.sh -ss edb -ts public –ld /usr/ edb/rs-7.0/datavalidator/bin/logs
7.0
----------------------------------------------------------------------------
Source and target databases connection information
----------------------------------------------------------------------------
Source database:
DBMS: ENTERPRISEDB
Host: localhost
Port: 5444
Database: node1
User: enterprisedb
Target database:
DBMS: ENTERPRISEDB
Host: localhost
Port: 5444
Database: node2
User: enterprisedb
----------------------------------------------------------------------------
Databases data validation process started...
----------------------------------------------------------------------------
Validating Table DEPT
Rows validated: 5
Finished validating table DEPT with 1 errors.
Logging errors details in the diff file...
Validating Table EMP
Rows validated: 16
Finished validating table EMP with 2 errors.
Logging errors details in the diff file...
Validating Table JOBHIST
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
154
Rows validated: 17
Finished validating table JOBHIST with 0 errors.
----------------------------------------------------------------------------
Data validation process has completed.
----------------------------------------------------------------------------
****************************************************************************
DataValidator Summary
****************************************************************************
All tables count: 3
Validated tables count: 3
Rows count: 38
Errors count: 3
Tables having only unsupported datatypes count: 0
Tables having primary key limitation count: 0
Total time(s): 0.678
Rows per second: 56
****************************************************************************
The Data Validator output indicates the following:
There is one error in the DEPT table (the missing row).
There are two errors in the EMP table (the two rows with mismatching column
values)
The JOBHIST table contains no errors.
The following shows the files created in the Data Validator logs directory:
$ pwd
/ usr/edb/rs-7.0/datavalidator/bin/logs
[root@localhost logs]# ls -lrt
total 16
-rw-r--r--. 1 root root 484 Apr 15 11:25 datavalidator_20190415-112534.log
-rw-r--r--. 1 root root 484 Apr 15 11:25 datavalidator_20190415-112556.log
-rw-r--r--. 1 root root 485 Apr 15 11:27 datavalidator_20190415-112739.log
-rw-r--r--. 1 root root 1556 Apr 15 11:30 datavalidator_20190415-113010.log
The log file contains the same content as displayed when the Data Validator is invoked.
The diff file compares the differences where errors were detected.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
155
8 Excluding User Transactions
Excluding specific user transactions from replication is useful in avoiding lag specifically
where UPDATE is required in thousands of rows in a database. For example, if you need
to apply a bulk change, like a single UPDATE query which results in modification of
thousands of rows. Applying such change directly on each of the cluster databases will be
faster than replicating it. In such a case the user can opt to exclude the transaction from
replication.
Note:
To utilize this procedure in the context of a SMR publication, the publication
should be created in read-write mode (that is, specify -nodetype as RW). By
default, a SMR publication will capture all the changes that include direct as well
as replicated changes applied on a given Publication database.
This procedure is applicable only for CDC changes (introduced after initial data
snapshot).
To exclude user transaction from replication follow the steps below:
A control replication origin will be auto-created as part of the Publication database
registration process. The origin will be named after the database name that is
_ngx_DBNAME for example _ngx_inventory. The control replication origin will be
removed once the Publication database is unregistered.
Note: In case the replication origin fails to create or remove, there will not be any impact
on the relevant adddb or removedb operation and only a warning message will be
logged.
Step 1: Open a SQL terminal (for example psql) and start the user transaction.
BEGIN;
Step 2: Setup replication origin session for the current transaction, before performing any
other (query) operation.
SELECT pg_replication_origin_session_setup('_ngx_DBNAME');
DBNAME
The database name.
Step 3: Make the changes in the application by running user-specific queries (intended
for bulk change).
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
156
Example
Add multiple rows in the table exclude_user_test which is also a part of the
Publication. Simulate bulk changes (5K) that are to be skipped from replication. This
table will also be part of EPRS7 cluster Publication.
create table exclude_user_test(id integer primary key, name varchar(30));
INSERT INTO exclude_user_test(id, name) SELECT id, 'n'||id::text FROM
generate_series (100001,105000) AS id;
Step 4: Reset replication origin.
SELECT pg_replication_origin_session_reset();
Step 5: Commit and complete the transaction.
COMMIT;
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
157
9 Troubleshooting
This section describes common issues that you might encounter while using EDB
Replication Server and provides possible solutions for combating those issues.
9.1 Error Messages and Resolutions
The following is a list of certain error messages that can appear while installing and
configuring EDB Replication Server.
RepCLI Error Messages 9.1.1
1. Error: Set admin password first before execution of other command(s).
Cause: The admin password is not set before adding a database to the replication
server.
Workaround: Set the admin password before adding a database to the replication
server.
2. Error: Unable to encrypt password. Reason Invalid user name or password.
Cause: Incorrect username or password given.
Workaround: Provide the correct username or password.
3. Error: Error encountered: Failed to add database with id database_Id.
Logs - Driver connect
WARNING: Connection error: null
Causes: This error can occur in the following scenarios
Incorrect encrypted password provided
Incorrect IP address provided
Database server not running
Workaround: Provide the correct encrypted password. Provide the correct IP
address (IP address of the database host). Check if the database server is running
or not. Encrypt the database password using encrypt command and use the
same while adding the database.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
158
4. Error: Failed to add database with id db1. WARNING: Connection error: FATAL: no pg_hba.conf entry for host
"x.x.x.x", user "xxx", database "database_Id", SSL off.
Cause: The IP address for the database host is not present in pg_hba.conf file.
Workaround: Add the IP address of the database host in the pg_hba.conf file
located at var/lib/edb/asx/. For a cluster the pg_hba.conf file is located
at /var/lib/edb/asx/clusterx.
where,
asx is the EDB Advanced Server Version and
clusterx is the cluster on which the database is running, for example, cluster1
or cluster2 and so on.
5. Error: The database id database_Id is not registered with the network.
Cause: The database does not exist or an incorrect database id is provided.
Workaround: Provide the correct database id or create the database with the
create database command.
6. Error: The server server_name is not registered with the network.
Cause: Incorrect servername is provided or the server does not exist in the
network.
Workaround: Provide the correct servername or register the server in the
network with the joinnetwork command.
7. Error: Publication publication_name not found.
Snapshot failed for Publication publication_name to target
database database_Id.
Cause: Publication does not exist or an incorrect publication name is given.
Workaround: Provide the correct publication name or create a publication.
8. Error: Subscription with DB id database_Id not found for Publication publication_name.
Snapshot failed for Publication publication_name to target
database database_Id.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
159
Cause: Incorrect database_Id provided or the database does not exist.
Workaround: Provide the correct database_Id or create the database if it does
not exist.
9. Error: Snapshot cannot be performed, Publication database id database_Id is same as the Consumer database id
database_Id.
Cause: The publication database is the same as the consumer database.
Workaround: The publication and consumer database should be different.
10. Error: One or more Subscriptions are associated with Publication publication_name. Please un-subscribe (via
leavepub command) before removing the Publication.
Failed to remove Publication publication_name.
Cause: removepub command is run before the publication leaves the network.
Workaround: Run leavepub command for all the subscription databases before
removing the publication.
Unable to Register the Database 9.1.2
Error: Cannot register database because it is already registered by a publication service.
Cause: Database can be registered with the replication cluster only once.
Workaround: Database is already registered. You can create a publication with
required tables with the createpub command.
Unable to Establish Connection with the EDB Replication 9.1.3Server
Error: Exception in thread "main" javax.ws.rs.ProcessingException: java.net.ConnectException:
Connection refused (Connection refused
Cause: Occurs whenever a connection cannot be made to the EDB Replication
Server.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
160
Workaround: Check that you have entered the correct host IP address and port
number of the server. Check that the server is running. Check that in the
pg_hba.conf file, the hostname is mapped to the correct network IP address, which
matches the IP address returned by the Linux ifconfig command. Check in the
pg_hba.conf file if the publication server has access to the database.
Connection Refused to the Database Server 9.1.4
Error: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP
connections.
Cause: Occurs when attempting to save a publication database definition. The
publication server cannot connect to the database server network location.
Workaround: Verify that the correct IP address and port for the database server are
given. Verify that the database server is running and is accessible from the host
running the publication server.
Unable to Connect to the Database Server 9.1.5
Error: Could not connect to the database server. Reason: FATAL: number of requested standby connections exceeds
max_wal_senders (currently n)
Cause: Occurs when attempting a snapshot replication from a publication database
which is by default configured with the log-based method of synchronization
replication (that is, WAL based logical replication), and the additional concurrent
connection for logical replication exceeds the current setting, n, of the
max_wal_senders configuration parameter in the postgresql.conf file.
Workaround: Increase the value of max_wal_senders in the
postgresql.conf file for the database server running the publication database.
Restart the database server containing the publication database.
The path for the postgresql.conf file is /var/lib/edb/asx/clusterx.
where,
asx is the EDB Advanced Version and
clusterx is the cluster on which the database is running, for example, cluster1
or cluster2 and so on.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
161
Publication Does Not Exist on the Publication Server 9.1.6
Error: Currently no publication exists on the publication server. Please create at least one publication on the server
and then retry.
Cause: If you join a publication when there are no publications on the specified
publication server, then this error message is thrown.
Workaround: Create a publication with the createpub command and then join a
publication.
Unable to Remove Database 9.1.7
Error: Database cannot be removed. Reason: Publication database connection cannot be removed as one or more publications are
defined against it.
Cause: There are existing publications on the database.
Workaround: Make sure all the publications pertaining to the publication database
have been removed. Use leavepub command to leave the publication and
removepub command to remove the publication.
Unable to Add a Database Connection 9.1.8
Error: Database connection cannot be added. FATAL: no pg_hba.conf entry for host "xxx.xxx.xx.xxx", user "user_name",
database "database_name", SSL off
Cause: Occurs when attempting to save a database definition using the adddb
command.
Workaround: Verify that the database host IP address, port number, database user
name, password, and database identifier are correct. Verify there is an entry in the
pg_hba.conf file permitting access to the database by the given user name
originating from the IP address where the EDB Replication Server is running.
Unable to Define Filter for Certain Datatypes 9.1.9
Error: Filter cannot be defined for Binary data type column(s) e.g. BYTEA, BLOB, RAW.
Cause: Occurs when attempting to define a filter rule on a column with a binary data
type in a publication table. Filter rules are not permitted on such columns.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
162
Workaround: Do not add filters on binary data type columns.
Filter with the Same Name Already Exists on the 9.1.10Table/View
Error: Filter with same name/clause already exist on table/view: schema.table_name
Cause: When adding a filter rule on a publication table, the same filter name or the
same filter clause (WHERE clause) cannot be used more than once on a given table.
Workaround: Modify the duplicate filter name or filter clause so it is unique for the
table.
Unable to Create Triggers for Publication Tables 9.1.11
Error: The triggers creation failed for one or more publication tables. Make sure the database is in valid state and user is
granted the required privileges.
Cause: Either the user does not have the trigger creation privilege or there is a
database server problem. The database server message is displayed as part of the
error.
Workaround: Provide a username with sufficient privileges while adding a database.
Problem with the Publish Process 9.1.12
Error: Problem occurred in publish process. Reason: Connection refused. Check that the hostname and port are correct and that
the postmaster is accepting TCP/IP connections.
Resolution: Occurs when attempting synchronization replication and the controller
database is not accessible by the publication server.
Workaround: Verify that the correct IP address and the port have been defined in the
publication database definition of the controller database. Verify that the database server
is running and is accessible from the host running the publication server.
Unable to Create Publication 9.1.13
You can get an error that the Publication cannot be created for the following
scenarios:
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
163
1. Error: Publication cannot be created. Publication publication_name already exists on the publisher server.
Please choose a different name and then proceed.
Cause: Publication names must be unique within a publication server.
Workaround: Enter a different publication name.
2. Error: Publication cannot be created. Table schema.table_name replica identity is set to replica_identity_setting. To define
a Filter, the table replica identity should be set to FULL.
Cause: Occurs when a table filter is attempted to be defined on a publication table
used in a log-based replication system.
Workaround: Use the ALTER TABLE statement to change REPLICA IDENTITY to
FULL.
3. Error: Publication cannot be created. Table table_name does not contain a primary key. Transactional replication is not
supported for a non-pk table.
Cause: All tables used for synchronization replication must have primary keys.
Workaround: Create a primary key on the table.
Unable to Create Publication Schema 9.1.14
Error: The publication schema cannot be created. Reason: ERROR: Permission denied for database db_name.
Cause: Occurs when attempting to create the publication database definition and the
specified publication database user does not have the privilege to create a schema in
database db_name.
Workaround: Grant the CREATE privilege on the database to the publication database
user.
Replication Slot Unavailable on the Target Database 9.1.15
Error: A replication slot is not available on the target database server. Please configure the max_replication_slots GUC on the
database server.
Cause: Occurs when attempting to add a publication database definition with the log-
based method of synchronization replication, and the max_replication_slots configuration
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
164
parameter in the postgresql.conf file is not set to a large enough value to
accommodate the additional database.
Workaround: Increase the value of the max_replication_slots parameter and restart the
database server.
Error while Running EDB Replication Server as an OS 9.1.16Service
Error: main" javax.ws.rs.ProcessingException: java.net.ConnectException: Connection refused (Connection
refused)
Cause: EDB Replication Server is not running.
Workaround:
1. Stop the EDB Replication Server service.
systemctl start edb-rs-server.service
2. Kill all the EDB Replication Server processes that are running.
pkill -f 'ngen'
3. Clean the system as follows:
rm -rf /var/log/edb/rs/replication*
rm -rf /var/lib/edb/rs/*
4. Start the EDB Replication Server service again.
systemctl start edb-rs-server.service
5. Verify the following values in /usr/edb/rs-
7.0/server/etc/application.properties file:
ngen.server.host=x.x.x.x
ngen.server.port=8082
Note: Make sure that the following directories are present after installing EDB
Replication Server.
cd /var/log/edb/rs/
[root@localhost rs]# ls
edb-rs-server ngx-x.0
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
165
9.2 Looking for Errors
Check the following log files to look for any errors encountered:
Table 8-1 Log Files
File Name Location Description
ngx-server.log /var/log/edb/rs/replication
-server Replication server logs
ngx-cli-client.log /var/log/edb/rs/replication
-client Replication client logs
/var/lib/edb/rs/data/kafka-
logs Kafka logs
edb-YY-MM-DD_xxxxxx.log /var/lib/edb/asx/data/log Database server logs
9.3 Common Problem Checklist
Use the following checklist to verify that the proper configuration steps have been
followed. Omitting one or more of these steps is a common source of errors.
Step 1: Verify that the database servers participating in the replication cluster are all
running.
Step 2: Verify that the EDB Replication Server is running.
Step 3: For the master definition node in a multi-master replication system, verify that
the publication database user is a superuser and has the privilege to modify pg_catalog
tables.
Step 4: Verify that the network IP address returned by the ifconfig command matches
the IP address set for ngen.server.host in application.properties.
Step 5: Verify that the ports are not preoccupied. If the ports are preoccupied change the
ports in application.properties and server.properties. Refer to Section 2.1.5
of the EDB Postgres Replication Server Getting Started Guide for more details.
Step 6: Verify that sufficient free disk space is available otherwise EDB Replication
Server will run into problems while installing and configuring.
Step 7: If you have configured a firewall make sure that the firewall settings allow access
to the ports used by EDB Replication Server in a cluster setup with EDB Replication
Servers running on different machines.
Step 8: For a geographically distributed network make sure to substantially increase the
value of the parameter zookeeper.session.timeouts in the server.properties
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
166
file located at /usr/edb/rs-x.0/server/etc. Otherwise, there would be frequent
outages between the broker and the zookeeper. The default value is 6000.
9.4 Troubleshooting Areas
The following topics provide information on specific problem areas you may encounter.
Java Runtime Errors 9.4.1
If you run into errors regarding the Java Runtime Environment such as the Java program
cannot be found or Java heap space errors, check the parameters set in the EDB
Replication Server startup configuration file ngxReplicationServer-7.config file located
at /usr/edb/rs-x.0/common/etc/sysconfig
The following is an example of the content of the EDB Replication Server startup
configuration file:
[root@localhost sysconfig]# cat ngxReplicationServer-7.config
#!/bin/sh
JAVA_EXECUTABLE_PATH=`which java`
JAVA_MINIMUM_VERSION=1.8
JAVA_BITNESS_REQUIRED=64
JAVA_HEAP_SIZE="-Xms1024m -Xmx4096m"
If you make any changes to the parameters in the EDB Replication Server startup
configuration file, make sure to restart the publication server after making the changes.
9.4.1.1 Starting the Publication Server
If you cannot start the publication server perform the following steps:
Step 1: Check the ngx-server.log file for errors.
Step 2: Check the log file of the database server running the controller database for
errors.
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
167
10 Automatic Partitioning
The automatic partitioning feature in EDB Replication Server detects when a new
partition is added in a published partition table and enables replication of related data
without requiring any explicit action from the user.
Note:
Make sure to explicitly define a primary key for the newly added partition table. If
you add a partition later using the Alter Table command then make sure to add a
primary key constraint explicitly.
Automatic partitioning is not supported for EDB Postgres Advanced Server 9.6.
The steps for automatic partitioning are as follows:
Step 1: Add a new partition to an existing publication partition table.
The following is the syntax is for range partitioning with sub partitioning as
list:
CREATE TABLE [ schema. ]table_name
table_definition
PARTITION BY RANGE(column[, column ]...)
[SUBPARTITION BY {RANGE|LIST|HASH} (column[, column ]...)]
(list_partition_definition[,
list_partition_definition]...);
Where list_partition_definition is:
PARTITION [partition_name]
VALUES (value[, value]...)
[TABLESPACE tablespace_name]
[(subpartition, ...)]
Example: The following is an example of an MMR with three nodes cluster node1,
node2 and, node3. In this case partition by range is used with subpartition as list.
1. Create the following tables on the three databases node1, node2 and node3:
create table date_format_range_part(invoice_no Numeric ,
customer Numeric,
invoice_date DATE ,
amount Numeric,
comments VARCHAR(500) )PARTITION BY RANGE (invoice_date) ;
CREATE TABLE part1 PARTITION OF date_format_range_part(invoice_date
primary key ) FOR VALUES FROM ('01/04/2014') TO ('01/06/2014') ;
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
168
CREATE TABLE part2 PARTITION OF date_format_range_part(invoice_date
primary key) FOR VALUES FROM ('01/04/2015') TO ('01/06/2015') ;
2. Setup the cluster by joining all the nodes to the network.
./runRepCLI.sh -joinnetwork -servername leaderService -host localhost -
port 8082
./runRepCLI.sh -joinnetwork -servername server2 -host localhost -port
8085 -user admin
./runRepCLI.sh -joinnetwork -servername server3 -host localhost -port
8085 -user admin
3. Add the database to the replication server. In the following example database,
node1 is added. Similarly, add node2 and node3 as well.
./runRepCLI.sh -setadminpassword -servername leaderService -host
localhost -port 8082 –savepassword
./runRepCLI.sh -adddb -servername leaderService -dbid centos.db.postgres
-dbtype enterprisedb -dbhost 192.168.203.133 -dbport 5444 -dbuser
enterprisedb -dbsimplepassword edb -database node1 -user admin
4. Create publication on one of the nodes and then use joinpub command for
the other nodes.
./runRepCLI.sh -createpub -pubname alltabpub3ngs -servername
leaderService -dbid centos.db.postgres -tables
public.invoices_r,public.tp_sales_31620,public.emp_34445 -user admin -
nodetype RW
5. Start streaming.
6. Check that the events_queue table is present across all the nodes:
Node1=# \dt _ngx_rep_cluster.events_queue
List of relations
Schema | Name | Type | Owner
----------------------------------+---------
_ngx_rep_cluster | events_queue | table | postgres
(1 row)
Node2=# \dt _ngx_rep_cluster.events_queue
List of relations
Schema | Name | Type | Owner
----------------------------------+---------
_ngx_rep_cluster | events_queue | table | postgres
(1 row)
Node3=# \dt _ngx_rep_cluster.events_queue
List of relations
Schema | Name | Type | Owner
----------------------------------+---------
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
169
_ngx_rep_cluster | events_queue | table | postgres
(1 row)
7. Insert the following data on node1 database and validate the data across all the
nodes:
INSERT INTO date_format_range_part VALUES(1,1,'01/04/2014',10000,'FIRST
QUARTER');
INSERT INTO date_format_range_part VALUES(2,2,'01/04/2015',10000,'FIRST
QUARTER');
./runValidation.sh -ss public -ts public
7.0
-------------------------------------------------------------------------
---
Source and target databases connection information
-------------------------------------------------------------------------
---
Source database:
DBMS: POSTGRESQL
Host: localhost
Port: 5432
Database: node1
User: postgres
Target database:
DBMS: POSTGRESQL
Host: localhost
Port: 5432
Database: node2
User: postgres
-------------------------------------------------------------------------
---
Databases data validation process started...
-------------------------------------------------------------------------
---
Validating Table DATE_FORMAT_RANGE_PART
Rows validated: 8
Finished validating table date_format_range_part with 0 errors.
DataValidator found 0 error across source and target databases.
-------------------------------------------------------------------------
---
Data validation process has completed.
-------------------------------------------------------------------------
---
*************************************************************************
***
DataValidator Summary
*************************************************************************
***
All tables count: 1
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
170
Validated tables count: 1
Rows count: 8
Errors count: 0
Missing tables on the target database count: 0
Tables having only unsupported datatypes count: 0
Tables having primary key limitation count: 0
Total time(s): 0.29
Rows per second: 28
8. Add the following partition on all the three databases:
CREATE TABLE part3 PARTITION OF date_format_range_part(invoice_date
primary key) FOR VALUES FROM ('01/04/2016') TO ('01/05/2017') ;
Step 2: Insert or update the data in the partitioned table. Check if the changes in the
partition table replicate to the target database table(s) as well.
Example:
1. Insert the following data for the added partitioned table on node1.
INSERT INTO date_format_range_part
VALUES(3,3,'01/04/2016',10000,'part3');
INSERT INTO date_format_range_part
VALUES(4,4,'02/04/2016',10000,'part3');
INSERT INTO date_format_range_part
VALUES(5,5,'03/04/2016',10000,'part3');
INSERT INTO date_format_range_part
VALUES(6,6,'05/04/2016',10000,'part3');
INSERT INTO date_format_range_part
VALUES(7,7,'06/04/2016',10000,'part3');
INSERT INTO date_format_range_part
VALUES(8,8,'07/04/2016',10000,'part3');
2. Perform the following transactions on node1 and node2 databases:
Node1:
update date_format_range_part set invoice_date='01/04/2017' where
invoice_no=3;
update date_format_range_part set invoice_date='01/05/2014' where
invoice_no=1;
delete from date_format_range_part where invoice_no=4;
Node2:
update date_format_range_part set invoice_date='01/03/2017' where
invoice_no=5;
update date_format_range_part set invoice_date='01/05/2015' where
invoice_no=2;
delete from date_format_range_part where invoice_no=6;
EDB Postgres Replication Server Reference Guide
Copyright © 2018 EnterpriseDB Corporation. All rights reserved.
171
3. Validate the data across all the nodes.
Step 3: Verify that after adding the database the following control objects are created
under _ngx_rep_cluster control schema in the given publication database.
create_table_event_trigger
create_table_event_trigger function
events_queue
Step 4: Verify that after removing the database the following control objects are removed
from _ngx_rep_cluster control schema in the given publication database.
create_table_event_trigger
Create_table_event_trigger function
events_queue