SharePlex 11.3 - Installation and Setup Guide

Create and activate a configuration for PostgreSQL

SharePlex gets its replication instructions from a configuration file, which defines the objects that are to be replicated. The file specifies following:

• The datasource (source database) — the identifier of the source database.
• The source objects — the names of the objects that contain the data to be replicated.
• The target objects — the names of the target objects that will receive the replicated data.
• The routing map — the name of the target system and, if the target is a database, its identifier.

Note: This demonstration demonstrates replication from one database to another. It does not cover replication to a file or a messaging container.

Create a configuration file

Perform these steps on the source system. The demonstration objects are assumed to be in the schema named splex.

Perform the following steps to create a configuration file:

1. Run sp_ctrl.

2. Issue the following command to create a configuration file named sample_config in the default text editor.

   sp_ctrl(source)>create config sample_config

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

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

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

   sp_ctrl(source)>verify config sample_config

Configuration templates

PostgreSQL source to PostgreSQL target

where:

• source_DB is the database name of the source database
• "qarun" is the database user of the source database
• "splex" is the database user of the target database
• "sp_ad" is the database name of the source database

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.

Perform the following steps to activate a configuration file:

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 sample_config should appear under File Name, and the word Active should appear under State.

   sp_ctrl(source)>list config

Troubleshooting tips

If your configuration activation fails, isue the view config sample_config command to view the file. Compare it to the template and make sure all of the information you entered is correct. Make certain you specified the correct database identifier. Check your syntax for extra spaces or missing components.

Because the configuration file is not active, you can edit it directly with the following command:

sp_ctrl(source)>edit config sample_config

Save the changes, then re-try the activation.

Note: To change an active configuration, you must copy it to a new file first, and then edit and activate the copy. For more information, see Add or Change Table Specifications in an Active Configuration in the SharePlex Installation and Setup Guide.

Demonstration of replication for PostgreSQL

This section demonstrates the speed and accuracy of SharePlex replication. It can also be used to verify that SharePlex was installed and configured correctly. For detailed information, see Demonstration of replication.

Demonstration of named post queues for PostgreSQL

This demonstration shows you how to use named post queues to process different tables through different, parallel Post processes to improve performance.

Clean up the replication environment

NOTE: The demonstration objects are assumed to be in the schema named splex.

Perform the following steps to clean up the replication environment:

1. If you ran previous demonstrations, do the following:

   1. On the source and target, run sp_ctrl and issue the following command to shut down sp_cop.

      sp_ctrl(source)shutdown

      sp_ctrl(target)shutdown

   2. On the source and target, run the pg_cleansp utility according to the instructions in SharePlex utilities. This removes the queues from the previous demonstrations and deactivates the previous configuration.

2. On the source, TRUNCATE the splex.demo_src. SharePlex replicates the TRUNCATE to the target.

   truncate table splex.demo_src;

Create a configuration file

Perform these steps on the source system. The demonstration objects are assumed to be in the schema named splex.

You will configure the following:

• demo_src on the source replicates through a post queue named q1 to target demo_dest.
• demo_dest on the source replicates through a post queue named q2 to target demo_src.

1. Run sp_ctrl.

2. Issue the following command to create a configuration file named postq_config in the default text editor.

   sp_ctrl(source)>create config postq_config

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

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

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

   sp_ctrl(source)>verify config postq_config

Configuration templates

PostgreSQL to PostgreSQL

where:

• dbname is the database name of the source database

• splex is the database user of the target database

• database_name is the name of the open target database.

Activate the configuration

Important!Make certain you created the scripts described in Prework for the demonstrations.

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.

Perform the following steps to activate the configuration:

1. Activate the configuration.

   sp_ctrl(source)>activate config postq_config

   Note: Configuration names are case-sensitive.

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

   sp_ctrl(source)>list config

Generate data

On the source, run the following scripts to insert data into the two local demonstration tables (in this demonstration, the demo_dest table is used as a source):

• Run the insert_demo_src script to insert 500 rows into the splex.demo_src table.
• Run the insert_demo_dest script to insert 500 rows into the splex.demo_dest table.

View the post queues

Perform the following steps to view the post queues:

1. On the target, run sp_ctrl.

2. On the target, issue the show post command with the detail option. This command shows statistics about the number of messages processed and the queues that were used.

   sp_ctrl(target)>show post detail

   In the output, the Queue field shows the name of each post queue, in this case q1 and q2, and the Operations Posted field shows the number of operations that were posted. Each queue should show 500 operations posted.

Advanced SharePlex Demonstrations for PostgreSQL

Contents

This chapter demonstrates selected features of SharePlex. These exercises can be run on Unix and Linux systems to demonstrate:

• How to build and verify a replication configuration
• How to use generic conflict resolution in peer-to-peer replication