2.2 RepCLI Commands
2.2.1 Getting Help (help)
2.2.14 Adding a Filter (addfilter)
2.2.20 Reloading a Snapshot
2.2.26 Creating a Role (createrole)
2.2.27 Creating a User (createuser)
2.2.31 List Databases (listdb)
2.2.32 List Publications (listpub)
4 Security
4.1.2 Permissions and Roles
4.1.2.1 Assigning Permissions
4.1.2.2 Using Roles
4.2 TLS/SSL Security
5.1 Metrics
5.1.1 Broker Metrics
5.1.3 Client Metrics
5.1.4 Summary of Metrics
5.1.1.1 Under-Replicated Partitions
5.1.1.2 Active Controller Count
5.1.1.4 All Topics Bytes In
5.1.1.5 All Topics Bytes Out
5.1.1.6 All Topics Messages In
5.1.1.7 Partition Count
5.1.1.8 Leader Count
5.1.1.9 Offline Partition Count
5.1.3.1 Record Error Rate
5.1.3.2 Request Latency Average
5.1.3.3 Fetch Latency Average
6.1.1 Primary Key Uniqueness
6.1.2 Foreign Key Constraint
6.1.1.1 Stop Conflict Resolution Policy
6.1.1.2 Skip Conflict Resolution Policy
6.1.1.3 Retry Conflict Resolution Policy
6.2.1 Security Group Access
6.2.2 Firewall Port Additions
9.1.1 RepCLI Error Messages
9.1.13 Unable to Create Publication
9.4.1 Java Runtime Errors
9.4.1.1 Starting the Publication Server
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.
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.
./runServer.sh --host host_ip_address--config EPRS_HOME/server/etcThe 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 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.
2.2 RepCLI CommandsThe 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:2.2.1 Getting Help (help)The help command provides a syntax summary of all RepCLI commands.The version command provides the EDB Replication Server CLI’s version number.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:
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.
4 Security
4.2 TLS/SSL SecurityKafka 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.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.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.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.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:In section v3_req, add the indicated new field followed by the addition of the alternate_names section and its list of fields: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.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.Step 6b: Generate the certificate signing request:Step 6c: Import ca-cert to server keystore:Step 6d: Import the signed server certificate back to server 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.Step 7b: Generate the certificate signing request:Step 7c: Import ca-cert to client keystore:Step 7d: Import the signed client certificate back to client keystore: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.Modify all other ssl parameters to point to the proper directory locations and specify the passwords.Modify the Path and Password parameters to point to the proper directory locations and specify the passwords.Modify the kafkastore.ssl parameters to point to the proper directory locations and specify the passwords.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.Modify the Path and Password parameters to point to the proper directory locations and specify the passwords.Modify all other ssl parameters to point to the proper directory locations and specify the passwords.Modify all other ssl parameters to point to the proper directory locations and specify the passwords.
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.Monitoring is based on a group of attributes referred to as metrics, which measure various characteristics during the operation of the Kafka brokers.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:
5.1 Metrics
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=8778ngen.sender.email=alert_sender_email_address
ngen.sender.email.encrypted.password=encrypted_sender_passwd
ngen.recipient.email=recipient_email_addressEncrypt 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_fileNote: 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.The following is an example of the monitor.properties file: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: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/binNote: 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.Step 4: If a critical problem occurs, the monitoring output appears as follows:The monitor.properties file is configured as shown by the following: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=8778ngen.sender.email=alert_sender_email_address
ngen.sender.email.encrypted.password=encrypted_sender_passwd
ngen.recipient.email=recipient_email_addressStep 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_fileStep 3: Sign in to your Gmail account.Step 4: Go to Settings > Accounts > Google Account settings > security.Step 5: Enable Less secure app access.Step 6: Run the ./runMonitor.sh command to send an email.
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.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.
Step 1: When you install the EDB Postgres Replication Server, the components for the Data Validator are installed as well.
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)../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../runValidation.sh –ss schema[ -ts schema ][ -ld log_directory_path ][ -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.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.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.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).The type of the source database server. Supported types are oracle, enterprisedb, sqlserver, sybase, and mysql.The port number on which the source database server is listening for connections.The port number on which the target database server is listening for connections.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.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 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.The following lists the tables in schema EDB along with the content of tables DEPT and EMP in the source database: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:
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.Step 2: Setup replication origin session for the current transaction, before performing any other (query) operation.Step 3: Make the changes in the application by running user-specific queries (intended for bulk change).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.Step 4: Reset replication origin.
9.1.1 RepCLI Error Messages
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 6.2.2 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 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.1 Java Runtime ErrorsIf 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/sysconfigThe following is an example of the content of the EDB Replication Server startup configuration file:9.4.1.1 Starting the Publication ServerStep 2: Check the log file of the database server running the controller database for errors.