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.
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:
Run sp_ctrl .
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
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.
# sample_config configuration file | ||
datasource:r.source_databasename | ||
"demo"."sample_department" |
"demo"."sample_department" |
target_system@r.target_databasename |
"demo"."sample_salary" | "demo"."sample_salary" |
target_system@r.target_databasename |
"demo"."sample_timesheet" | "demo"."sample_timesheet" | target_system@r.target_databasename |
"demo"."sample_employee" | "demo"."sample_employee" | target_system@r.target_databasename |
where:
In sp_ctrl, verify that the configuration file will activate successfully.
sp_ctrl(source)>verify config sample_config
An example of a config file for PostgreSQL to PostgreSQL replication:
# data2k configuration file | ||
datasource:r.source_databasename | ||
"demo"."data2k" | "demo"."data2k" | target_system@r.target_databasename |
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 |
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.
Activate the configuration.
sp_ctrl(source)>activate config sample_config
Note: Configuration names are case-sensitive.
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
Set the value of logical_decoding_work_mem = 4096MB (recommended). This specifies the maximum amount of memory to be used by logical decoding.
Notes:
|
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
Run the PG Setup utility for PostgreSQL (pg_setup) to establish a user account, schema, and tables for use by SharePlex.
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. |
For an example of the sample odbc.ini and odbcinst.ini files, see the example below:
Review the following requirements to ensure that the setup succeeds.
(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
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;
Follow the below instructions to run the PG setup for PostgreSQL:
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
Prompt | Response |
---|---|
(Linux) Enter the PostgreSQL DSN name: |
Enter a DSN. |
Is DB hosted over "Azure Single Server"? |
Enter "n" for a database hosted on the platform other than "Azure single server". NOTE: Ensure Admin users have the below mentioned roles before initiating setup- On-Prem DB: superuser AWS hosted DB: rds_superuser Azure hosted DB: azure_pg_admin |
Enter the PostgreSQL Administrator name : |
Enter the name of the PostgreSQL Administrator. This user will perform the work on the SharePlex account. |
Enter the password for the Administrator account : |
Enter the password of the Administrator. |
Enter the database name: |
Enter the name of the database that you want to contain the SharePlex tables and other objects for use by SharePlex. You can enter the name of a new or existing database. |
Database name database does not exist. Would you like to create it? [y] : |
If this prompt is displayed, the specified database does not exist. Press Enter to have the setup utility create it for you. |
Would you like to create a new SharePlex user [y]: |
Press Enter to accept the default to create a new SharePlex database user account and schema of the same name in the specified database, or enter n to use an existing SharePlex account. |
Enter the name of the new SharePlex user: Enter the name of the existing SharePlex user: |
One of these prompts is displayed depending on whether you elected to create a new user or use an existing user. Enter the name of the SharePlex user. NOTE: Enter the user name in lowercase letters only. |
Enter the password of the SharePlex user : |
Enter the password of the SharePlex user account. |
Re-enter the password for the SharePlex user : |
This prompt is only shown if you created a new user. Enter the SharePlex password again. |
Will this database be used as a source? [n] : |
Enter Y to use the DB connection for a source. You will see the below prompts only for the DB connection for the source. Enter N to use the DB connection for a target. |
Enter the replication slot name? [test_slot] : |
Enter the replication slot name. NOTE: Replication slot test_slot will be created in database after successful config activation. |
Enter the default tablespace for use by SharePlex [pg_default] : | Enter the default tablespace by SharePlex |
Enter the index tablespace for use by SharePlex [pg_default] : | Enter the index tablespace by SharePlex |
Do you want Logical replication? [n] : |
This prompt is displayed only for on-premise hosted PostgreSQL database. Enter "y" to perform logical replication. Enter "n" to perform physical replication. Setup will now install SharePlex objects. |
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' --
Important! Applicable only for source configuration: SharePlex holds the WAL logs when configuration is not active. In SharePlex 11.0, when pg_setup is executed, it creates a replication slot. From SharePlex 11.1 onwards, slot is created while activating configuration. This replication slot maintains the latest LSN value of the WAL records that Capture has received and processed. Also, it holds all the WAL files from getting removed or archived, starting from the stored LSN in this slot. In SharePlex 11.1 onwards, the replication slots are dropped while deactivating the configuration. If SharePlex is shut down without deactivating the config file, then run the pg_cleansp utility to drop the slot. This utility also deletes slot names present in connections.yaml from the installation of version 11.0. The following steps need to be performed if you do not want to run pg_cleansp:
|
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.
You can configure SharePlex to support the PostgreSQL database on the Azure platform using the below steps:
Note: While performing DML operations in bulk for PostgreSQL PaaS (Azure), a performance slowness issue is observed.
Table 15: Setup Prompts and Responses
Prompt | Response |
---|---|
Enter the PostgreSQL DSN name or connection string [] : |
Enter the PostgreSQL DSN name or a connection string that connects to the PostgreSQL database system. Example: pslazpgdb02 |
Is DB hosted over "Azure Single Server"? |
Enter "Y" only when PostgreSQL DB is hosted over 'Azure Single Server'. |
Enter the PostgreSQL Administrator name : |
Enter the name of the PostgreSQL Administrator. This user will perform the setup work on the SharePlex account and schema. Example: PostgreSQL |
Enter the password for the Administrator account: |
Enter the password of the Administrator. |
Enter the replication target database name : |
Enter the target database name on which replication needs to be performed. Example: sp_mk |
Would you like to create a new SharePlex user? Enter the name of the existing SharePlex user: |
N (If you want to create a user, enter “y”)
Enter the name of the SharePlex user. |
Enter the password of the SharePlex user: |
Enter the password of the SharePlex user account. |
Re-enter the password for the SharePlex user: |
This prompt is only shown if you created a new user. Enter the SharePlex password again. |
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' –
Modify the cloud database parameters :
AWS : rds.logical_replication 1
Azure : wal_level LOGICAL
Add database connection details in the ODBC files in SharePlex var/odbc directory. See the sample screenshot.
Notes:
|
Run the PG Setup utility for PostgreSQL (pg_setup) to establish a user account, schema, and tables for use by SharePlex.
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. |
For an example of the sample odbc.ini and odbcinst.ini files, see the example below:
Review the following requirements to ensure that the setup succeeds.
(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
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;
Follow the below instructions to run the PG setup for PostgreSQL:
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
Prompt | Response |
---|---|
Enter the PostgreSQL DSN name: |
Enter a DSN. |
Is DB hosted over "Azure Single Server"? |
Enter "n" for a database hosted on the platform other than "Azure single server". NOTE: Ensure Admin users have the below mentioned roles before initiating setup- On-Prem DB:superuser AWS hosted DB:rds_superuser Azure hosted DB:azure_pg_admin |
Enter the PostgreSQL Administrator name : |
Enter the name of the PostgreSQL Administrator. This user will perform the work on the SharePlex account. |
Enter the password for the Administrator account : |
Enter the password of the Administrator. |
Enter the database name: |
Enter the name of the database that you want to contain the SharePlex tables and other objects for use by SharePlex. You can enter the name of a new or existing database. |
Database name database does not exist. Would you like to create it? [y] : |
If this prompt is displayed, the specified database does not exist. Press Enter to have the setup utility create it for you. |
Would you like to create a new SharePlex user [y]: |
Press Enter to accept the default to create a new SharePlex database user account and schema of the same name in the specified database, or enter n to use an existing SharePlex account. |
Enter the name of the new SharePlex user: Enter the name of the existing SharePlex user: |
One of these prompts is displayed depending on whether you elected to create a new user or use an existing user. Enter the name of the SharePlex user. NOTE: Enter the user name in lowercase letters only. |
Enter the password of the SharePlex user : |
Enter the password of the SharePlex user account. |
Re-enter the password for the SharePlex user : |
This prompt is only shown if you created a new user. Enter the SharePlex password again. |
Will this database be used as a source? [n] : |
Enter Y to use the DB connection for a source. You will see the below prompts only for the DB connection for the source. Enter N to use the DB connection for a target. |
Enter the replication slot name? [test_slot] : |
Enter the replication slot name. NOTE: Replication slot test_slot will be created in database after successful config activation. |
Enter the default tablespace for use by SharePlex [pg_default] : | Enter the default tablespace by SharePlex |
Enter the index tablespace for use by SharePlex [pg_default] : | Enter the index tablespace by SharePlex |
Do you want Logical replication? [n] : |
This prompt is displayed only for on-premise hosted PostgreSQL database. Enter "y" to perform logical replication. Enter "n" to perform physical replication. Setup will now install SharePlex objects. |
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' --
To install pg_hint_plan:
For On-Prem Databases:
Install the RPM according to the PostgreSQL version support matrix at https://github.com/ossc-db/pg_hint_plan.
Run the query with the user [Shareplex user]: CREATE EXTENSION IF NOT EXISTS pg_hint_plan;
Reboot the instance.
For Cloud PostgreSQL:
Set the static parameter in the parameter group: shared_preload_libraries=pg_hint_plan.
Reboot the instance.
NOTE: The total number of background workers that can exist at any one time is limited by both max_worker_processes and max_parallel_workers:
|
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center