SharePlex 11.4 - Installation and Setup guide

Configure, activate, and verify replication on the PostgreSQL source

Note: Before proceeding, make certain the SharePlex demonstration objects are installed.

In this demonstration, you will create and activate a replication configuration, load data to the source table, and then confirm that the data was replicated successfully to the target table. You will also repair a table that is out of synchronization.

Create a configuration file

Note: In this demonstration, the demonstration objects are assumed to be in the schema named demo. Substitute the actual schema, if different.

Perform these steps on the source system:

1. Run sp_ctrl .

2. Issue the following command to create a configuration file named sample_config that replicates the sample_department, sample_salary, sample_timesheet, and sample_employee tables to target tables of the same names on the target system..

   sp_ctrl(source)>create config sample_config

3. In the text editor, build your configuration file based on the following template. Allow no spaces between the characters in a component (source specification, target specification, routing map), and place at least one space between each component.

where:

• # denotes a comment.
• source_databasename is the PostgreSQL database name of the source database.
• target_system is the name or IP address of the target system.
• target_databasename is the PostgreSQL database name of the target database.

4. Save the file, then exit the editor. SharePlex automatically saves the file in the config sub-directory.

5. In sp_ctrl, verify that the configuration file will activate successfully.

   sp_ctrl(source)>verify config sample_config

Syntax configuration

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

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

Activate the configuration

Perform these steps on the source system. When you activate a configuration, SharePlex is ready to capture transactional changes that are made to the specified source data.

1. Activate the configuration.

   sp_ctrl(source)>activate config sample_config

   Note: Configuration names are case-sensitive.

2. Confirm that the configuration activated successfully. The name config sample_config should appear under File Name, and the word Active should appear under State.

   sp_ctrl(source)>list config

Database Setup for PostgreSQL

Pre-requisites to configure PostgreSQL database setup as a source:

1. In the PostgreSQL configuration file, set the wal_level parameter to logical.
   For example: wal_level = logical # minimal, replica, or logical
2. Add an entry of SharePlex user in the pg_hba.conf file so that SharePlex Capture can establish a connection with the walsender process of PostgreSQL
   For example: # TYPE DATABASE USER ADDRESS METHOD
   host replication all 10.10.10.10/32 trust
3. For all the tables which are going to be part of replication, set the 'replica identity' to 'full' before activation.
   For example: alter table "DEMO_SRC" replica identity full;
4. Navigate to the /var/lib/pgsql/13/data directory and edit the PostgreSQL file.
   Remove the comment and increase the below two parameters' value as below:
   wal_sender_timeout = 6000s
   wal_receiver_timeout = 6000s

5. Set the value of logical_decoding_work_mem = 4096MB (recommended). This specifies the maximum amount of memory to be used by logical decoding.

6. Navigate to the bin directory and restart database: cd /usr/pgsql-13/bin/./pg_ctl -D /var/lib/pgsql/13/data/ restart

Pre-requisites to configure PostgreSQL database setup as a target:

Fujitsu Enterprise PostgreSQL

Fujitsu PostgreSQL contains a customized ODBC driver designed to run with the shared libraries supplied by Fujitsu PostgreSQL. In order to ensure the proper configuration, you must create a symbolic link to ensure that the correct libraries are found at runtime.

Issue Description:

If you attempt to run the pg_setup program prior to creating the symbolic link, pg_setup will fail with the following error message:

Error: [unixODBC]invalid connection option "target_server"

To create a symbolic link:

To ensure that the proper library gets loaded, first locate the path to the directory where the Fujitsu PostgreSQL libraries are installed (for example /opt/fsepv95server64/lib). Then create a symbolic link in the SharePlex OPTDIR/lib/hooks directory pointing to the libpq.so.5 library in the Fujitsu PostgreSQL library directory. For example:

cd /opt/splex/lib/hooks

ln –s /opt/fsepv95server64/lib/libpq.so.5

Overview

Run the PG Setup utility for PostgreSQL (pg_setup) to establish a user account, schema, and tables for use by SharePlex.

Guidelines for use

• Run the PG Setup utility on PostgreSQL instances in the SharePlex replication configuration.
• Within a server cluster, run the PG Setup utility on the node to which the shared disk that contains the SharePlex variable-data directory is mounted.
• For consolidated replication, run the PG Setup utility for each variable-data directory.

• Provide a DSN (data source name).

  Connection Type on Linux

  What to do

  DSN

  If you have a DSN defined, and you want to use it for the SharePlex connection, copy or link the ODBC files in which that DSN is defined (odbc.ini and odbcinst.ini) to the odbc subdirectory of the SharePlex variable-data directory. This prevents connection errors when the SharePlex processes connect to the database. If you do not have a DSN defined but want to use one, you can create it in the template files provided in the odbc subdirectory.

Required Privilege

Review the following requirements to ensure that the setup succeeds.

• The Database Setup utility must be run as a PostgreSQL Administrator in order to grant SharePlex the required privileges to operate on the database and to create the SharePlex database account.

• (Symfoware only) If the person who is running the setup utility is not a Fujitsu Enterprise PostgreSQL owner, set the environment variable LD_LIBRARY_PATH to include the path to the lib subdirectory in the Fujitsu Enterprise PostgreSQL installation directory. The LD_LIBRARY_PATH is set in the .bash_profile file of the Fujitsu Enterprise PostgreSQL owner.

  Example:

  export LD_LIBRARY_PATH= /opt/symfoserver64/lib:$LD_LIBRARY_PATH

  If you do not set this path, the following error occurs:

  symbol lookup error: /opt/fsepv95client64/odbc/lib/psqlodbca.so: undefined symbol: PQconnectdbParams

• Cloud installations:
  • Common restrictions on privileges in cloud-hosted database services make it difficult for the setup utility to succeed in every possible type of scenario. To ensure that the database setup succeeds, only use the setup utility for the following purposes: To do a first-time database setup with a new SharePlex user, or, to modify an existing SharePlex user that either owns the database or has access to it.

  • You might need to grant usage/privileges on the target schema and tables to the SharePlex user, as shown in the following example:

    Log in as the schema owner, then issue the following commands:

    grant usage on schema schema_name to user_name;

    grant all privileges on all tables in schema schema_name to user_name;

  • For Google Cloud database, grant the following privileges if a user wants to change the owner of a table in replication:

    alter user splex_user createdb createrole login;

    create role role_name;

    grant role_name to splex_user;

Run PG setup for PostgreSQL

Follow the below instructions to run the PG setup for PostgreSQL:

1. Shut down any running SharePlex processes and sp_cop on the source system.

2. Run the pg_setup program from the bin subdirectory of the SharePlex product directory.

   Important! If you installed the SharePlex instance on any port other than the default of 2100, use the -p option to specify the port number. For example, in the following command the port number is 9400.

   $ /users/splex/bin> pg_setup -p9400

Table 14: Setup prompts and responses

A successful setup terminates with a message similar to the following:

Completed SharePlex for PostgreSQL database configuration

SharePlex User name: splexab

Database name: testdb

-- The datasource identifier in the SharePlex configuration is 'r.testdb' --

Support for hyphen, dot, and plus (-, ., and +) special characters

When the database name contains a hyphen, dot, or plus (-, ., or +) special characters, then remove these characters. Append the _<count> value to the identifier, where the count depends on the datasource identifier existing in the connections.yaml file.

Example:

• For the database name aaa.db, the datasource identifier will be r.aaadb_1.

• For the database name aaa+db, the datasource identifier will be r.aaadb_2, as r.aaadb_1 already points to the aaa.db database.

If the database does not contain any special characters but its entry exists in the connections.yaml file, append _<count> to its identifier.

Example:

If the database name is aaadb_1 and its entry exists in the connections.yaml file, and the database name is different, the identifier will become r.aaadb_1_1.

If the database name does not contain a hyphen, dot, or plus character, then the database name is the same as the datasource identifier.

Configure SharePlex to support PostgreSQL database on Azure platform

You can configure SharePlex to support the PostgreSQL database on the Azure platform using the below steps:

1. Add the DSN entry in the Odbc.ini file at /etc/odbc.ini and the …/var/odbc directory.
2. Copy the /etc/odbcinst.ini file to the …/var/odbc directory.
3. Run the PostgreSQL setup utility.

Note: While performing DML operations in bulk for PostgreSQL PaaS (Azure), a performance slowness issue is observed.

Table 15: Setup Prompts and Responses

A successful setup terminates with a message similar to the following:

Completed SharePlex for PostgreSQL database configuration

SharePlex User name: postgres@pslazpgdb02.postgres.database.azure.com

Database name: sp_mk

The data source identifier in the SharePlex configuration is 'r.sp_mk' –

Database Setup for PGDB as a Service

Database Setup for PostgreSQL Database as a Service

Pre-requisites to configure PostgreSQL database setup as a source:

1. Modify the cloud database parameters :

   AWS : rds.logical_replication 1

   Azure : wal_level LOGICAL

2. Add database connection details in the ODBC files in SharePlex var/odbc directory. See the sample screenshot.

3. For all the tables which are going to be part of replication, set the 'replica identity' to 'full' before activation.
   For example: alter table "DEMO_SRC" replica identity full;

Overview

Run the PG Setup utility for PostgreSQL (pg_setup) to establish a user account, schema, and tables for use by SharePlex.

Guidelines for Use

• Run the PG Setup utility on an independent Linux VM from where the PostgreSQL instance is accessible.
• For consolidated replication, run the PG Setup utility for each variable-data directory.

• For PostgreSQL Database as a Service, follow the connection handling best practices recommended by the respective vendor.

• You can provide a DSN (data source name). Note the following when deciding on the connection method on Linux:

  Connection Type on Linux

  What to do

  DSN

  If you have a DSN defined, and you want to use it for the SharePlex connection, copy or link the ODBC files in which that DSN is defined (odbc.ini and odbcinst.ini) to the odbc subdirectory of the SharePlex variable-data directory. This prevents connection errors when the SharePlex processes connect to the database. If you do not have a DSN defined but want to use one, you can create it in the template files provided in the odbc subdirectory.

Review the following requirements to ensure that the setup succeeds.

• The Database Setup utility must be run as a PostgreSQL Administrator in order to grant SharePlex the required privileges to operate on the database and to create the SharePlex database account.

• (Symfoware only) If the person who is running the setup utility is not a Fujitsu Enterprise PostgreSQL owner, set the environment variable LD_LIBRARY_PATH to include the path to the lib subdirectory in the Fujitsu Enterprise PostgreSQL installation directory. The LD_LIBRARY_PATH is set in the .bash_profile file of the Fujitsu Enterprise PostgreSQL owner.

  Example:

  export LD_LIBRARY_PATH= /opt/symfoserver64/lib:$LD_LIBRARY_PATH

  If you do not set this path, the following error occurs:

  symbol lookup error: /opt/fsepv95client64/odbc/lib/psqlodbca.so: undefined symbol: PQconnectdbParams

• Cloud installations:
  • Common restrictions on privileges in cloud-hosted database services make it difficult for the setup utility to succeed in every possible type of scenario. To ensure that the database setup succeeds, only use the setup utility for the following purposes: To do a first-time database setup with a new SharePlex user, or, to modify an existing SharePlex user that either owns the database or has access to it.

  • You might need to grant usage/privileges on the target schema and tables to the SharePlex user, as shown in the following example:

    Log in as the schema owner, then issue the following commands:

    grant usage on schema schema_name to user_name;

    grant all privileges on all tables in schema schema_name to user_name;

  • For Google Cloud database, grant the following privileges if a user wants to change the owner of a table in replication:

    alter user splex_user createdb createrole login;

    create role role_name;

    grant role_name to splex_user;

Run PG setup for PostgreSQL

Follow the below instructions to run the PG setup for PostgreSQL:

1. Shut down any running SharePlex processes and sp_cop on the source system.

2. Run the pg_setup program from the bin subdirectory of the SharePlex product directory.

   Important! If you installed the SharePlex instance on any port other than the default of 2100, use the -p option to specify the port number. For example, in the following command the port number is 9400.

   $ /users/splex/bin> pg_setup -p9400

Table 16: Setup prompts and responses

A successful setup terminates with a message similar to the following:

Completed SharePlex for PostgreSQL database configuration

SharePlex User name: splexab

Database name: testdb

-- The datasource identifier in the SharePlex configuration is 'r.testdb' --

Installation of pg_hint_plan extension

To install pg_hint_plan:

For On-Prem Databases:

1. Install the RPM according to the PostgreSQL version support matrix at https://github.com/ossc-db/pg_hint_plan.

2. Run the query with the user [Shareplex user]: CREATE EXTENSION IF NOT EXISTS pg_hint_plan;

3. Reboot the instance.

For Cloud PostgreSQL:

1. Set the static parameter in the parameter group: shared_preload_libraries=pg_hint_plan.

2. Reboot the instance.