SharePlex 11.4 - Installation and Setup guide

Post to PaaS Cloud from the Source Server for PostgreSQL

These instructions help you set up SharePlex to replicate to a target cloud database directly from the source server. All of the SharePlex processes will run on this server. In a high-volume transactional environment, this may generate unacceptable overhead on the production system. In that case, you should use an intermediary server.

See also:

Post to PaaS cloud from an intermediary server for PostgreSQL

Overview of SharePlex setup on cloud databases

Source PostgreSQL to cloud PostgreSQL

All steps are performed on the source server:

1. Complete the SharePlex pre-installation checklist.

2. Install SharePlex. See Install SharePlex on Linux/Unix for PostgreSQL Database.

3. Run pg_setup to establish connection information for the source PostgreSQL database. See Database Setup for PostgreSQL.

4. Add an entry in the local ODBC.ini or ODBCINST.ini files that points to the target cloud database. This enables you to query the target database from the source server.

5. Run pg_setup to establish connection information for the target cloud database. Run the utility as the master user that was created when the service was created. This user has the required DBA privileges.

6. Specify the following in the routing map of the SharePlex configuration file:

   • The name of the source host to specify the target system.

   • The PostgreSQL database name of the target cloud database.

   In the following example, source1 is the source system and postgresqldatabasename is the alias from ODBC files of the target cloud database.

   datasource: r.orcldbprim
   #source tables	target tables	routing map
   splex.demo_src	splex.demo_dest	source1@r.postgresqldatabasename

For more information, see Configure SharePlex to replicate data in the SharePlex Administration Guide.

Source PostgreSQL to cloud Open Target

All the below steps are performed on the source server:

1. Complete the SharePlex preinstallation checklist.

2. Install SharePlex. See: SharePlex on Linux/Unix for PostgreSQL Database.

3. Run the appropriate database setup utility for the source database. See:

   • Database Setup for PostgreSQL.

4. Install the appropriate ODBC client of the target cloud database.

5. Run the appropriate database setup utility for the target cloud database. See Database Setup Utilities.

6. Specify the following in the routing map of the SharePlex configuration file:

   1. the name of the source server as the target host.

   2. the name of the cloud database as the target database

For more information about how to specify configuration components, see Configure SharePlex to replicate data in the SharePlex Administration Guide.

Post to PaaS Cloud from an Intermediary Server for PostgreSQL

These instructions help you set up SharePlex to replicate to a target cloud database from an intermediary server that runs the SharePlex Import and Post components.

See also:

Post to PaaS cloud from the source server for PostgreSQL

Overview of SharePlex setup on cloud databases.

Source PostgreSQL to cloud Open Target

1. Complete the SharePlex preinstallation checklist.

2. On the source server, install SharePlex for the source database. See: Install SharePlex on Linux/Unix for PostgreSQL Database.

3. On the source server, run the appropriate database setup utility for the source database. See: Database Setup for PostgreSQL.

4. On the intermediary server, install the appropriate ODBC client of the target cloud database.

5. On the intermediary server, install SharePlex for the target cloud database. See: Install SharePlex on Linux/Unix for PostgreSQL Database.

6. On the intermediary server, run the appropriate database setup utility for the target cloud database. See Database Setup Utilities.

7. Specify the following in the routing map of the SharePlex configuration file:

   1. the name of the intermediary server as the target host.

   2. the name of the cloud database as the target database.

In the following example using a MySQL target, intermediary3 is the intermediary system and sptest3 is the target cloud database.

For more information, see Configure SharePlex to replicate data in the SharePlex Administration Guide.

Installation and Setup for Remote Capture for PostgreSQL

Using PostgreSQL as a source with remote capture requires that the database instances be accessible from the servers on which SharePlex is installed.

To set up SharePlex for remote capture:

1. Run PG Setup on the SharePlex system according to the instructions provided in the Database Setup Utilities for PostgreSQL section of the SharePlex Reference Guide.

   • When prompted with "Is DB hosted over Azure Single Server?", reply with "No" as Azure Single Server is not supported as a source.

2. Complete all the setup tasks as mentioned in the Database Setup for PostgreSQL Database as a Service.

3. After completing the setup, start sp_cop and sp_ctrl.

4. For configuring and starting replication, refer to the SharePlex Administration Guide.

Install SharePlex on PostgreSQL High Availability Cluster

SharePlex supports CrunchyData High Availability cluster environment setup.

Follow the below configuration steps:

1. Setup the CrunchyData High Availability cluster environment according to the CrunchyData setup documentation.

2. Install or upgrade to SharePlex 11.1.

3. Run the pg_setup utility and enter a slot name.

4. Activate the configuration. The user input slot name will be created in the database after a successful activation.

5. Add the slot name to the respective CrunchyData config [YML or YAML] file to monitor in failover or switchover scenario.

6. Run the deactivate configuration or cleanup [pg_cleansp] utility to remove the dedicated slot name from the database. Users need to remove SharePlex dedicated slot name from the CrunchyData config file.

7. Remove the SharePlex dedicated slot name from the CrunchyData config file.

Example of CrunchyData config command: patronictl -c /etc/patroni/crunchy-demo.yml edit-config

NOTE: User need to add SharePlex dedicated slot name to the respective CrunchyData config.

Limitation: SharePlex internally uses PostgreSQL logical replication with a PostgreSQL database over cloud services. In the event of a failover to the standby server, the logical replication slots are not copied over to the standby server on cloud database services; hence, SharePlex will not handle logical slot re-creation and maintenance with cloud database services. This applies to the AWS Multi-AZ cluster setup of the RDS PostgreSQL, and the Aurora PostgreSQL databases.