PostgreSQL parameters

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

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 Post 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 Post to 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 Post to 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_OPX_REDUCED_KEY

This parameter controls the content of the Post WHERE clause. Post uses 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 Post WHERE clause.

  • If set to 0, this parameter directs Post 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_CAP_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 Post 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 Post 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_CAP_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 Post 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 Post 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 Post only when the target is PostgreSQL. It is used to create a replication origin in the PostgreSQL database and bind the Post user session with the created origin. It is useful in bi-directional replication(peer to peer relication) to prevent operations posted by the Post process from looping back. If enabled, the Capture process ignores such operations posted by the Post process. By default, it is disabled.

Note: If this parameter is enabled, each Post 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 Post is restarted

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 Post is doing full table scans on tables where there are defined indexes. Using a large number of hints can reduce the performance of the Post process.

 

Default: 100 hints

Range of valid values: any positive integer

Takes effect: when Post is restarted

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 Post 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 Post 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 Post 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