Chat now with support
Chat with Support

SharePlex 11.0 - SharePlex Installation and Setup Guide

About this Guide Conventions used in this guide Revision History SharePlex pre-installation checklist Download the SharePlex installer Installation and setup for Oracle cluster Installation and setup for remote capture Install SharePlex on Linux and UNIX Assign SharePlex users to security groups Set up an Oracle environment for replication Set up replication from Oracle to a different target type Generic SharePlex demonstration-all platforms Advanced SharePlex demonstrations for Oracle Advanced SharePlex Demonstrations for PostgreSQL Solve Installation Problems Database Setup Utilities General SharePlex Utilities Uninstall SharePlex Advanced installer options Install SharePlex as root Run the installer in unattended mode SharePlex installed items

Set up Replication from PostgreSQL to PostgreSQL

Note: These instructions apply to all implementations of PostgreSQL unless otherwise noted.

Overview

SharePlex can post replicated PostgreSQL data to a PostgreSQL target database through an Open Database Connectivity (ODBC) interface. SharePlex supports all implementations of the PostgreSQL open-source database.

These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.

For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.

Configure SharePlex on the source and target

Set up SharePlex and the database on the PostgreSQL source and target system as follows.

Configure replication

On the source, create a SharePlex configuration file that specifies capture and routing information.

Datasource:r.source_DB

src_schema.table

tgt_schema.table

host@r.database_name

where:

  • source_DB is the database name of the source database.
  • src_schema.table is the schema and name of the source table.
  • tgt_schema.table is the schema and name of the target table.*
  • host is the name of the target system.
  • database_name is the name of the target database.
* Important! 

Target schema or table name defined in the database is case sensitive, be certain to:

  • Type the name in the correct case.
  • Enclose the name in quotation marks, for example "MySchema"."MyTable".

Note: This is a basic one-source, one-target configuration using no additional SharePlex configuration features. See "Configure SharePlex to replicate data" in the SharePlex Administration Guide for important information about creating a configuration file and for additional setup instructions for more complex replication scenarios.

Source configuration example

The following configuration file replicates table HR.Emp from PostgreSQL instance dbname112 to target table region1.emp in database mydb on target system hostB. The source table is case-sensitive.

Datasource:r.dbname112

HR."Emp" region1.emp hostB@r.mydb

Set up Replication from PostgreSQL to Oracle

Overview

SharePlex can post replicated PostgreSQL data to an Oracle target database through an Open Database Connectivity (ODBC) interface. SharePlex supports all implementations of the PostgreSQL open-source database.

These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.

For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.

Configure SharePlex on the source and target

Set up SharePlex and the database on the PostgreSQL source and an Oracle target system as follows.

Configure replication

On the source, create a SharePlex configuration file that specifies capture and routing information.

Datasource:r.dbname

src_schema.table

tgt_owner.table

host@o.database_name

where:

  • source_DB is the database name of the source database.
  • src_schema.table is the schema and name of the source table.
  • tgt_owner.table is the schema and name of the target table.*
  • host is the name of the target system.
  • database_name is the name of the target database.
* Important! 

Target schema or table name defined in the database is case sensitive, be certain to:

  • Type the name in the correct case.
  • Enclose the name in quotation marks, for example "MySchema"."MyTable".

Note: This is a basic one-source, one-target configuration using no additional SharePlex configuration features. See "Configure SharePlex to replicate data" in the SharePlex Administration Guide for important information about creating a configuration file and for additional setup instructions for more complex replication scenarios.

An example of a config file for PostgreSQL to Oracle replication:

# data2k configuration file

datasource:r.source_databasename

"demo"."data2k" "demo"."data2k" target_system@o.target_databasename

Use double quotes ("...") for table name and column names while creating table in the Oracle target database.

create table "data2k"(
"id" number(19) primary key,
"cust_gender" char,
"order_name" varchar2(2000),
"units_sold" NUMBER,
"unit_price" BINARY_DOUBLE not null,
"delivery_id" NUMBER(5),
"order_id" number(10),
"order_price" binary_float not null,
"cust_dob" date,
"created_date" timestamp,
"updated_date" timestamp with time zone,
"delivery_date" timestamp(3),
"order_name2" varchar2(2000) DEFAULT ' ' NOT NULL,
"units_sold2" NUMBER,
"unit_price2" BINARY_DOUBLE not null,
"delivery_id2" NUMBER(5),
"order_id2" number(10),
"order_price2" binary_float not null,
"cust_dob2" date,
"created_date2" timestamp);

Set up Replication from PostgreSQL to Kafka

Overview

The SharePlex Post process can connect and write to a Kafka broker. The data can be written in JSON or XML output as a sequential series of operations as they occurred on the source, which can then be consumed by a Kafka consumer.

(missing or bad snippet)(missing or bad snippet)

Kafka category

Supported targets

Kafka

Command options

Use the below command options on the SP_CTRL for Kafka:

target x.kafka set kafka broker= 10.250.40.42:9092 (IP address of Kafka server:port number)

target x.kafka set kafka topic= kafkaTarget (topic name)

target x.kafka set format record = xml/JSON

target x.kafka show

Guidelines for Posting to Kafka

  • A SharePlex Post process acts as a Kafka producer. A SharePlex Post process can write to one or more topics that have one or more partitions.
  • The SharePlex Post process does not create a topic itself, but you can configure the Kafka broker to auto-create topics.

Configure SharePlex on the source

When replicating data to Kafka, configure the source database and SharePlex on the source system as follows.

Configure replication

On the source, create a SharePlex configuration file that specifies capture and routing information. The structure that is required in a configuration file varies, depending on your replication strategy, but this shows you the required syntax for routing data to Kafka.

Datasource:r.dbname
src_schema.table !kafka (missing or bad snippet)

where:

  • dbname is a PostgreSQL database name of source
  • src_schema.table is the schema and name of the source table.
  • !kafka is a required keyword indicating SharePlex is posting to Kafka.
  • (missing or bad snippet)

Note: See Configure SharePlex to replicate data in the (missing or bad snippet).

Source configuration example

Datasource:r.testdb

MY_SCHEMA.MY_TABLE !kafka targetHost

Configure SharePlex on the target

These instructions configure the SharePlex Post process to connect to Kafka. You must have a running Kafka broker.

Perform these steps to configure post to Kafka:

  1. Create a Kafka topic.
  2. Start sp_cop. (Do not activate the configuration yet.)
  3. Run sp_ctrl.

  4. Issue the target command to configure posting to a Kafka broker and topic. The following are example commands.

    sp_ctrl> target x.kafka set kafka broker=10.250.40.42:9092

    sp_ctrl> target x.kafkaset kafka topic=shareplex

    See Set up Replication from PostgreSQL to Kafka for command explanations and options.

Set the Kafka Record Format

SharePlex can output to either XML or JSON format as input to Kafka. XML is the default. To set the input format and specify format options, use one of the following target commands:

target x.kafka set format record=json

or:

target x.kafka set format record=xml

To view samples of these formats, see the format category of the target command documentation in the (missing or bad snippet).

View and change Kafka settings

To view current property settings for output to Kafka, use the following target command:

target x.kafka show

Table 5: Kafka target properties

Property Input Value Default
broker=broker

Required. The host and port number of the Kafka broker, or a comma delimited list of multiple brokers. This list is the bootstrap into the Kafka cluster. So long as Post can connect to one of these brokers, it will discover any other brokers in the cluster.

 localhost:9092
client_id=ID

Optional. A user-defined string that Post will send in each request to help trace calls.

None
compression.code={none, gzip, snappy} Optional. Controls whether data is compressed in Kafka. Options are none, gzip or snappy. None
partition={number | rotate | rotate trans}

Required. One of the following:

  • A fixed partition number: Directs Post to post messages only to the specified partition number. For example, setting it to 0 directs Post to post only to partition 0. This option is suitable for use in testing or if the target has multiple channels of data posting to the same Kafka topic.
  • The keyword rotate: Directs Post to apply messages to all of the partitions of a topic in a round-robin fashion. The partition changes with each new message. For example if a topic has three partitions, the messages are posted to partitions 0,1,2,0,1,2, and so on in that order.
  • The keyword rotate trans: This is similar to the rotate option, except that the partition is incremented with each transaction rather than with each message. For example, if a topic has three partitions, the messages are posted to partition 0 until the commit, then to partition 1 until the commit, and so on in that order. This option is suitable if you are replicating multiple tables to a single topic. It allows you to distribute data across several partitions, while still preserving all of the operations of a transaction together in a single partition. This enables a consumer that reads from a single partition to receive a stream of complete transactions.
0
request.required.acks=value

Optional. This is a Kafka client parameter. By default it is set to a value of -1, which means all. Consult the Kafka documentation about this subject, because all really means all in-sync replicas. This parameter can be used in conjunction with the min.insync.replicas broker parameter to tune behavior between availability and data consistency.

Important: It is possible for data to be lost between a Kafka producer (SharePlex in this case) and a Kafka cluster, depending on these settings.

 -1
topic=topic_name

Required. The name of the target Kafka topic.

This string may contain the special sequences %o or %t. The %o sequence is replaced by the schema name of the table that is being replicated. The %t sequence is replaced by the table name of the table that is being replicated. This feature may be used in conjunction with a Kafka server setting of auto.create.topics.enable set to 'true'. Also view your server settings for default.replication.factor and num.partitions because these are used as defaults when topics are auto created.

Important! If using multiple topics, you must also set the following properties with the target command:

  • The output must be in JSON. Set the record property of the format category to json:

    target x.kafka set format record=json

  • Commits must be disabled. Set the commit property of the json category to no:

    target x.kafka set json commit=no

 shareplex

* To avoid latency, if Post detects no more incoming messages, it sends the packet to Kafka immediately without waiting for the threshold to be satisfied.

Set up Replication from PostgreSQL to SQL Server

Overview

SharePlex can post replicated source data to a Microsoft SQL Server target database through an Open Database Connectivity (ODBC) interface.

These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.

For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.

Configure SharePlex on the source

When replicating data to SQL Server, configure the source database and SharePlex on the source system as follows.

Configure replication

On the source, create a SharePlex configuration file that specifies capture and routing information. The structure that is required in a configuration file varies, depending on your replication strategy, but this shows you the required syntax for routing data to SQL Server.

Datasource:r.dbname
src_schema.table tgt_owner.table (missing or bad snippet)

where:

  • dbname is a PostgreSQL database name of source
  • src_schema.table is the schema and name of the source table.

  • tgt_owner.table is the owner and name of the target table.

  • (missing or bad snippet)

Note: See Configure SharePlex to replicate data in the (missing or bad snippet).

Source configuration example

The following example replicates table SCOTT.EMP from PostgreSQL instance dbnameA to the MS SQL Server on host sysprod

Datasource: r.testdb
SCOTT.EMP    SCOTT.EMP   sysprod@r.mydb

Configure SharePlex on the target

Perform the following steps to configure SharePlex on the target:

  1. Make certain that the database setup meets all of the requirements in Open Target Checklist .

  2. Run Database Setup for SQL Server (mss_setup) to establish a database account and connection information for SharePlex. For more information, see Database Setup for SQL Server.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating