PostgreSQL parameters control and tune various aspects of replication for PostgreSQL databases.

Contents

Descriptions of User-Configurable Parameters

This section describes the user-configurable SharePlex parameters for the PostgreSQL database. Parameters not documented in this chapter are internal parameters that should only be modified under guidance of a Quest developer or Technical Support representative.

Parameters are grouped as follows:

Capture Parameters

These parameters are used by the SharePlex Capture process.

SP_CAP_INCLUDE_UNCHANGED_COL

This parameter controls whether or not unchanged columns are included in the after image of an UPDATE operation. By default, SharePlex only includes the changed values in the after image.

Default: 0 (off)

Range of valid values: 0 or 1

Takes effect: immediately

SP_CAP_REDUCED_KEY

This parameter controls which parts of an operation in the WAL file are sent by Capture to the target for use by Post. Different SharePlex features may require more or less data to be made available to the Poster process.

  • If set to 0, this parameter directs Capture to send all of the data that PostgreSQL writes to the WAL files other than TEXT. This setting sends the most data across the network, but is required to support certain SharePlex features.
  • If set to 1 (the default), this parameter directs Capture to send the key values and, for UPDATEs, the before and after values of columns that were changed in the operation. This setting is the default. This setting provides a good balance between replication performance and target data integrity, because it enables Posterto perform a before-and-after comparison of the changed columns when constructing the WHERE clause. For more information about how SharePlex uses before and after values, see the SharePlex Administration Guide.
  • If set to 2, this parameter directs Capture to send the key values and, for UPDATEs, the after values of the columns that changed to the target. This setting sends the least amount of data across the network and also enables Posterto only use the key values when posting data, which improves performance.

Default: 1

Range of Valid Values: 0, 1, 2

Takes effect: Immediately

SP_CAP_ONELINE_DEBUG

This parameter controls the oneline debug feature for the Capture process.

The oneline debug feature enables you to perform debugging for out-of-sync errors without consuming a large amount of the system resources. The oneline debug logs just enough information in one line to detect where in the data stream an out-of-sync condition occurred. Once oneline debug identifies the process that is causing the problem, you can then enable regular debugging for that process.

To enable this parameter, specify the objects that you want to debug by their object ID, and separate each one by a comma. An example is:

sp_ctrl>set sp_cap_oneline_debug 230230, 351626

The following items are logged:

  • The transaction identifier used by SharePlex
  • The type of DML or DDL operation that was affected
  • The object ID of the affected source table
  • The time when the operation occurred (PostgreSQL)
  • The LSN value for the transaction
  • An internal SharePlex code that prevents redundant operations

 

Default: Disabled

Range of valid values: a string that forms a list of objects listed by object ID, separated by commas.

Takes effect: Immediately

SP_CAP_CHECKPOINT_FREQ

This parameter is one of the parameters that can be used to define the frequency of the checkpoints that are issued by the show capture command. This parameter works in conjunction with the SP_CAP_CHECKPOINT_TIME parameter.

Default: 40000 messages

Range of valid values: any positive integer

Takes effect: immediately

SP_CAP_CHECKPOINT_TIME

This parameter works in conjunction with the SP_CAP_CHECKPOINT_FREQ parameter. It defines the time delay, in seconds, before the Capture process checkpoints. If the value set for this parameter is reached before the value set for SP_CAP_CHECKPOINT_FREQ, it triggers the checkpoint. (Check pointing saves the state of the process in case it is needed for failure recovery.)

Default: 120 seconds

Range of valid values: any positive integer

Takes effect: immediately

SP_CAP_DENIED_SESSION_PG

This parameter is applicable for SharePlex PostgreSQL Capture only. It controls whether all transactions for a user session should be ignored or filtered by the Capture process.

If set to 0, Capture will not ignore any transactions, whether or not a user session is bound to the origin name.

If set to 1, Capture will ignore all transactions for a user session which are bound to the origin name in the format ‘sp_deny_anyString’. The sp_deny_ prefix must be used, and you can insert a string in place of 'anyString'. For example: sp_deny_1.

The user needs to create (if it does not exist) and bind the PostgreSQL user session to the origin name by calling the PostgreSQL functions ‘pg_replication_origin_create ()’ and ‘pg_replication_origin_session_setup ()’.

Examples:

  • select pg_replication_origin_create('sp_deny_1’);

  • select pg_replication_origin_session_setup('sp_deny_1’);

IMPORTANT: Ignoring transactions on the source machine may lead to an out-of-sync condition.

 

Default: 0

Range of valid values: 0 or 1

Takes effect: immediately

How to use this parameter:

Before the designated user performs the transaction in the source database, issue the following command on source sp ctrl: (For PostgreSQL as a source and target).

sp_ctrl>set param SP_CAP_DENIED_SESSION_PG 1

NOTE: The parameter is live. After setting this parameter, users need to bind the PostgreSQL session to the origin by calling the pg_replication_origin_session_setup() function. All operations executed on source database after this will be ignored until the parameter is unset. The SP_CAP_DENIED_SESSION_PG is a live parameter and can be changed any time.

To unset the parameter, run the below command:

sp_ctrl>reset param SP_CAP_DENIED_SESSION_PG

Perform the following steps to bind more than 10 PostgreSQL sessions simultaneously to the different origins:

  1. Navigate to PostgreSQL data directory (cd /var/lib/pgsql/13/data/).

  2. Edit the postgresql.conf file by uncommenting the max_replication_slots Parameter (It is by default commented, user need to uncomment it & set it to more than maximum number of simultaneous sessions user want to bind to different origins).

  3. Restart the database.

Notes:

  • The syntax for adding a valid origin is sp_deny_name, the sp_deny_ prefix is required before the user-defined name of origin.

  • The max_replication_slots value should be more than simultaneous user sessions who are going to do session setup. [For eg, if user is going to do origin setup for 100 sessions, set the max_replication_slots value greater than 100 ]

SP_CAP_DENIED_PORT

This parameter is applicable only for PostgreSQL Capture. It can be used to ignore or filter operations performed on specific SharePlex ports by the Capture process when multiple SharePlex instances are running on the same machine. It is useful in a cascading replication setup of SharePlex running on different ports.

The SP_OPX_CREATE_ORIGIN_PG parameter must be enabled on the specified ports for this parameter to work.

IMPORTANT: Ignoring transactions on the source machine may lead to an out-of-sync condition.

Default: (empty)

Range of valid values: list of valid SharePlex ports separated by commas

Takes effect: when Capture is restarted

Open Target Poster parameters

These parameters are used by the SharePlex Poster process when applying data to an Open Target (non-Oracle) target.

SP_OPX_REDUCED_KEY

This parameter controls the content of the Poster WHERE clause. Posteruses a WHERE clause to find the row in the target that needs to be changed by a replicated UPDATE from the source. Different SharePlex features may require more or less data to be used in the Poster WHERE clause.

  • If set to 0, this parameter directs Poster to construct a WHERE clause of all of the data that is sent by Capture. The data that Capture sends depends on the setting of the SP_OCT_REDUCED_KEY parameter. If you want the WHERE clause to include the values of the keys and all of the columns other than TEXT, set both parameters to 0.
  • If set to 1, this parameter directs Poster to build a WHERE clause with the key values and the before values of the columns that changed. This setting provides a good balance between replication performance and target data integrity, because it enables Poster to perform a before-and-after comparison of the changed columns when constructing the WHERE clause. To be able to use this setting, the SP_OCT_REDUCED_KEY parameter must be set to 0 or 1. For more information about how SharePlex uses before and after values, see the SharePlex Administration Guide.
  • If set to 2, this parameter directs Poster to build a WHERE clause of only the key columns. This setting can be used to maximize posting performance. Because this setting omits the before-and-after comparison of the changed columns, you should ensure that no process or user can make changes to the target data except SharePlex.

Default: 0

Range of Valid Values: 0, 1, 2

Takes effect: When Poster is restarted

SP_OPX_XML_MARK_NULL

This parameter controls how Null values are displayed when the user selects XML as the target output format for Kafka. JSON format should always be NULL. Set the SP_OPX_XML_MARK_NULL parameter to 1 on the Kafka target to display the null value as xsi:nil="true".

Default: 1

Range of Valid Values: 0 or 1

Takes effect: Process restart

SP_OPX_CREATE_ORIGIN_PG

This parameter is applicable for Open Poster only when the target is PostgreSQL. It is used to create a replication origin in the PostgreSQL database and bind the Poster user session with the created origin. It is useful in bi-directional replication (peer to peer replication) to prevent operations posted by the Poster process from looping back. It is also useful in a cascading replication setup of SharePlex running on different ports to ignore operations performed on ports set in the SP_CAP_DENIED_PORT parameter. If enabled, the Capture process of the same port ignores such operations posted by the Poster process. By default, it is disabled.

Note: If this parameter is enabled, each Poster process creates an origin, and the number of origins created depends upon the max_replication_slots parameter of the PostgreSQL database. As a result, if the number of post-processes exceeds this parameter value, increase it and restart the database.

Default: 0 (Disabled)

Range of valid values: 0 or 1

Takes effect: When Poster is restarted

SP_OPX_LOG_CONFLICT

This parameter enables the logging of information about successful conflict resolution procedures to the shareplex_conf_log table. This applies only to the prepared routines that are provided by SharePlex.

  • A setting of 1 enables the logging of conflict resolution to the shareplex_conf_log table.

    Note: A setting of 1 will not update the column existing_timestamp (when existing data is not replaced) in the shareplex_conf_log table.

  • A setting of 2 enables the logging of conflict resolution to the shareplex_conf_log table with Poster query for additional meta data.

    Using LeastRecentRecord or MostRecentRecord prepared routines Poster will query the target database for the timestamp column of the existing record. The query result is logged into the existing_timestamp column of the shareplex_conf_log table.

    Note: A setting of 2 may affect the performance of Poster as a result of making the query.

 

Default: 0 (Disabled)

Range of valid values: 0, 1, or 2

Takes effect: when Poster is restarted

List of additional supported parameters for PostgreSQL Open Target Poster

Oracle Poster parameters

These parameters are used by the SharePlex Poster process when applying data to an Oracle target.

SP_OPO_HINTS_LIMIT

This parameter controls the maximum number of hints (table/index combinations) that can be listed in the hints file. Use hints only if you see that Poster is doing full table scans on tables where there are defined indexes. Using a large number of hints can reduce the performance of the Poster process.

 

Default: 100 hints

Range of valid values: any positive integer

Takes effect: when Poster is restarted

List of additional supported parameters for Oracle Poster process

Read parameters

These parameters are used by the SharePlex Reader process.

SP_ORD_FIRST_FIND

This parameter controls how the Read process checks column conditions to determine whether or not a replicated row change satisfies them.

  • At the default of 1, when a row change satisfies a column condition, SharePlex does not check any other column conditions to see if that row change also satisfies any of them.
  • At a value of 0, SharePlex sends the data to all target systems where the column conditions are satisfied.

For more information about horizontal partitioning, see the SharePlex Administration Guide.

 

Default: 1 (on)

Range of valid values: 0 or 1 (flag)

Takes effect: when Read is restarted

SP_ORD_HP_IN_SYNC

This parameter is used for horizontally partitioned replication to ensure that data is replicated properly when a value for a column in a column condition changes so that the row no longer satisfies the condition. It enables SharePlex to automatically correct the following:

  • UPDATEs that cause a row to meet a different column condition than the one created for that row, sending the changes to a different location. An example would be an UPDATE to a row for which the column condition is region=East that changes the value of the region column to WEST. Such operations will fail because the original INSERT statement for that row was replicated to the original location (the Eastern region), so the row does not exist in the new location (the Western region) when Poster attempts the update there.

  • UPDATEs that cause a row to meet a column condition (and be replicated) when the row was not supposed to be replicated. An example would be when the region column is updated from the value of HEADQUARTERS (for which a row is not replicated) to the value of WEST. Such operations will fail because the original INSERT statement for that row (into the headquarters system) was not replicated to the Western region’s system, so Poster cannot perform the update there.

  • UPDATEs that cause a row to no longer meet any column condition. An example would be when the region column is updated from the value of WEST to the value of HEADQUARTERS. The original INSERT statement was replicated to the Western region’s system, but the update to the new value is not replicated, because the new value does not meet a column condition (headquarters data is not shared). The rows are now out of synchronization, but there are no errors.

When this parameter is enabled, SharePlex automatically corrects rows for which UPDATEs cause the preceding conditions. SharePlex converts the UPDATE to a DELETE and, if needed, an INSERT.

To convert an UPDATE statement (which normally only uses the changed columns and the key) to an INSERT statement, SharePlex needs values for all of the columns. Enabling SP_ORD_HP_IN_SYNC directs SharePlex to send all of the columns in a row to the Poster process when there is an UPDATE to a table using horizontally partitioned replication, so that an INSERT can be constructed.

Set this parameter on the source system before you activate the configuration. If replication is active, set the parameter and then reactivate the configuration so that SharePlex can rebuild its object cache.

If you know that the columns in column conditions for tables using horizontally partitioned replication will never change, leave this parameter set to 0, because using it incurs processing overhead.

This parameter is not compatible with SP_OCT_REDUCED_KEY and SP_OPO_REDUCED_KEY [any value: 1 or 2] as it overrides the behavior of both the parameters.

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: when Read is restarted

List of additional supported parameters for PostgreSQL Reader process

Compare/Repair Parameters

These parameters control properties of the SharePlex Compare and Repair commands.

SP_DEQ_PG_FORCE_PARALLEL

This parameter enables the use of parallel queries in a running session for the PostgreSQL database.

Based on this parameter value, the following value is assigned to PostgreSQL database parameter force_parallel_mode (DB version < 16.0)/ debug_parallel_query (DB version >= 16.x)

0 - off

1 - on

2 - regress

Default value: 0

Range of valid values: 0 to 2

Takes effect: Immediately available for the next comparison.

Notes:

  • SharePlex cannot control the number of worker threads assigned for parallelism in a SELECT query by the PostgreSQL database optimizer.

  • This parameter is not supported with the pg_hint_plan extension.

SP_DEQ_PG_PARALLEL_MAX

This parameter manages the maximum number of parallel worker threads allowed for a query in a running session for the PostgreSQL database.

Value of this parameter is assigned to PostgreSQL database parameter - max_parallel_workers_per_gather

Default value: 6

Range of valid values: 0 to 1024

Takes effect: Immediately available for the next comparison.

 

Notes:

  • SharePlex cannot control the number of worker threads assigned for parallelism in a SELECT query by the PostgreSQL database optimizer.

  • This parameter is not supported with the pg_hint_plan extension.

SP_DEQ_PG_PARALLEL_SETUP_COST

This parameter is utilized to configure the parallel_setup_cost parameter for the PostgreSQL database in a running session.

Default value: 10

Range of valid values: 0 to 2147483647

Takes effect: Immediately available for the next comparison.

Notes:

  • SharePlex cannot control the number of worker threads assigned for parallelism in a SELECT query by the PostgreSQL database optimizer.

  • This parameter is not supported with the pg_hint_plan extension.

SP_DEQ_PG_PARALLEL_TUPLE_COST

This parameter is used to configure the parallel_tuple_cost parameter for the PostgreSQL database in a running session.

Default value: 0.001

Range of valid values: 0.0001 to 3.4E+38

Takes effect: Immediately available for the next comparison.

Notes:

  • SharePlex cannot control the number of worker threads assigned for parallelism in a SELECT query by the PostgreSQL database optimizer.

  • This parameter is not supported with the pg_hint_plan extension.

SP_DEQ_BATCH_ENABLED

When set to 1, this parameter enables batch DML operations during the repair process. If set to 0, out-of-sync (OOS) records will be corrected by firing one transaction at a time during the repair.

Default value: 1

Range of valid values: 0 or 1

Takes effect: Immediately available for the next comparison.

SP_DEQ_MAX_BATCH_MBYTES

This parameter controls the maximum size of a batched DML operation to be executed at once during the repair process. The repair process continues to add operations (I/U/D) until the batch size specified by this parameter value is reached. This parameter is applicable on the target side where the client process runs.

Default: 1 megabyte

Range of valid values: 1 to any positive integer

Takes effect: Immediately available for the next comparison.

SP_DEQ_COMMIT_FREQ

This parameter is applicable for repair when batch mode is off. It determines the number of single transactions that should be committed.

Default value: 1000

Range of valid values: 0 to 10000

Takes effect: Immediately available for the next comparison.

SP_DEQ_USE_SP_CKSUM

This parameter determines whether SHA256 hash is used in the comparison. When set to the default value of 0, SHA256 hash is utilized. When set to 1, SharePlex selects the exact table data as it is, using an internal checksum method.

Notes:

  • Setting this parameter to 1 for larger tables may affect performance, as hash_values take significantly less time than processing exact table data.

  • If the table has no key columns and has NULL values in any of its columns, the Compare and Repair operation should be run by setting the parameter SP_DEQ_USE_SP_CKSUM to 1.

Default value: 0

Range of valid values: 0 or 1

Takes effect: Immediately available for the next comparison.

SP_DEQ_PG_DECLARE_FETCH_SIZE

This parameter determines whether the driver attempts to return a result set in a single fetch or across multiple fetches.

If this parameter is set to 0, the driver will perform a single fetch that can improve overall response times but at the cost of additional memory.

If this parameter is set to a non-zero number, the driver may perform either a single fetch or multiple fetches, depending on the size set for this parameter. The driver will perform a single fetch when the size set by the user for this parameter is greater than the actual size of the result set returned by a select query. Otherwise, it will perform multiple fetches. If multiple fetches are used, the size of the fetch is determined by the value of this parameter. For large result sets, retrieving rows in multiple fetches can improve response time and thereby prevent possible timeouts.

Additionally, this parameter can be adjusted to trade-off between throughput and response time.

Default value: 100

Range of valid values: Between 0 to 2147483647, applicable to both Server and Client processes.

Takes effect: Immediately after running the Compare or Repair command.

SP_DEQ_READ_BUFFER_SIZE for PostgreSQL

This parameter applies only to advanced data type columns during a compare/repair operation. It adjusts the size of the buffer that holds the fetched data related to advanced data types when those columns are being compared and repaired. The value of the parameter should be adjusted based on the available system memory and data size.

Default: 1 MB

Range of valid values: any integer between 1 and 100 (values in MB)

Takes effect: Immediately available for the next comparison

Export parameters

These parameters are used by the SharePlex Export process.

Import parameters

These parameters are used by the SharePlex Import process.

Queue parameters

These parameters control properties of the SharePlex queues.

sp_cop parameters

These parameters are used by the SharePlex sp_cop program.

System parameters

These parameters control system-related SharePlex properties.