Chat now with support
Chat mit Support

SharePlex 11.3 - Reference Guide

About this guide Conventions used in this guide Revision History SharePlex Commands for Oracle SharePlex Commands for PostgreSQL SharePlex parameters General SharePlex utilities Oracle Cloud Infrastructure SharePlex environment variables

Alphabetical Reference for SharePlex Commands for PostgreSQL

This starts the detailed reference documentation for SharePlex commands in alphabetical order.

Abort Config for PostgreSQL

Use the abort config command to deactivate a configuration instantly and delete all associated queues and replication processes at the same time. This command stops all replication activity for the configured datasource on the source and target systems, whether or not data in the queues has been posted. It is a forceful deactivation.

The abort config command is effective when there has been a system, configuration, or data problem and you need to prevent posting to the target system(s). Because you are deleting whatever data remains in the pipeline, the source and target instances will go out of synchronization after this command takes effect.

If any SharePlex replication process stops before or during the abort config command, the command also stops. When the process starts again, the command resumes working. This allows abort config to work even if the network is temporarily down — it remains in the queues until the connection is restored.

Usage

Supported source:

PostgreSQL (on-prem), Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL

Supported targets: PostgreSQL, Oracle, SQL Server, Kafka, Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL
Issues for: source and target systems
Related commands: deactivate config, purge config

Syntax

Basic command
abort config filename
Syntax description
Component Description
filename 

The name of the configuration that you want to abort. Configuration names are case-sensitive.

Example: sp_ctrl(sysA)>abort config sales

Activate Config for PostgreSQL

Use the activate config command to activate a configuration. Replication begins immediately as soon as the activation process is complete.

The activation process reads the configuration file, from which it gets all of the information needed for SharePlex to:

  • Identify the objects that are in replication
  • Route the replicated changes to the appropriate source and target database
  • Generate the SQL that Post uses to apply the changes to the target
  • Activate all of the tables that have been added to replication

The process that sp_cop calls to activate a configuration is sp_tconf.

Notes:

  • While activating the configuration using logical replication, if any uncommitted transactions are being executed on the PostgreSQL instance, you may experience lag in the config activation.

  • For partitioned tables, users must set the replica identity to full for all its sub-partitions. If not set, verify config will display a message as "Object may not be replicated because replica identity is not full for its partitions." Activation will not add the partitioned table in replication. It will log all the sub-partition table names that do not have replica identity set to full in the activation log.

Guidelines for activation

  • To activate a configuration, the database containing the objects to be replicated must be mounted and open. The length of time that activation takes varies, depending on the size, number and structure of the configured objects.
  • You can activate one configuration per PostgreSQL server on each system. For example, if there are ConfigA, ConfigB and ConfigC for instance dbname1, you can activate only one of them at a time. Activating another configuration for the same datasource automatically deactivates the first one.

  • Before you activate a configuration, use the verify config command to confirm that basic requirements for successful activation and replication have been satisfied. The command alerts you to potential problems that can cause the activation to fail.

View activation status and results

SharePlex activates objects according to their object ID, not their order in the configuration file, so there is no way to predict the order of activation.

To view the results of activation, issue the show config command

What to do if activation fails

Many things can cause the activation of a table or the entire configuration to fail. For example, if one or more components in the configuration file were entered incorrectly, activation of the affected objects fails.

If you did not issue the verify config command before you activated, run it now, and correct any problems that it finds. Then, try activating again.

Usage

Supported sources: PostgreSQL (pglsn option only applicable for physical replication.) , Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL
Supported targets: PostgreSQL, Oracle, SQL Server, Kafka, Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL
Authorization level: Administrator (1)
Issues for: source system
Related commands: abort config, copy config, create config, deactivate config, edit config, list config, purge config, remove config, rename config, show config, verify config , view config

Syntax

Basic command Command option
activate config filename pglsn=lsn_value
Syntax description
Component Description
filename 

Required. The name of the configuration that you want to activate. Configuration names are case-sensitive.

Example: sp_ctrl(sysA)>activate config sales

pglsn=lsn_number

Use this option to activate the configuration to start replication at a specific LSN in the WAL files. Before activating the configuration, do the following:

If there was a previously active configuration, run the pg_cleansp utility on the source and all targets to restore the environment to a clean state. For more information, see pg_cleansp.

Use the show last_posted command to get the LSNs of the last transactions that were posted from all the Post processes (if using named queues). Use the lowest of those LSN values for activate config.

Example: sp_ctrl> activate config myconfig pglsn=6/555FAE0

Note: Activate with LSN is not supported for logical replication. If database goes out of sync, user need to sync the data manually using external utilities.

Note: After successful activation physical and logical slots will get created in the database.

 

Important!

Steps to follow when performing the alter table add/drop column operation which is part of ongoing replication.

Cautions:

  • Do not stop the Capture process while altering the table.

  • No DML operations should be performed on the table for which column is being added or dropped.

Follow the below steps:

  1. Execute the query for alter table add/drop column without performing any DML on the table.

  2. Reactivate the config file again so that the latest details are fetched and stored in the object cache of all the processes.

  3. Once reactivation is done, then DML operations can be performed on the table.

Steps to follow when performing the alter table add/drop partition operation which is part of ongoing replication.

Cautions:

  • Do not stop the Capture process while altering the table. (applicable only if reactivation is being done)

  • No DML operations should be performed on the table for which partition is being added or dropped.

Follow the below steps:

  1. Execute the query for alter table add/drop partition without performing any DML on the table.

  2. Reactivate the config file again or restart the Capture process so that the latest partition details are fetched and used by the Capture process.

  3. Once reactivation or restart Capture process is done, then DML operations can be performed on the table.

Add Partition for PostgreSQL

Use the add partition command to add a row partition to a partition scheme when configuring horizontally partitioned replication. Issue an add partition command for each row partition that you want to create.

This command captures all of the information required to create the partition, including the following required components:

  • The partition scheme name. To create a new partition scheme, specify the name in the add partition command that creates the first row partition for that scheme. SharePlex automatically creates the partition scheme. Then, specify that name when adding additional row partitions to that partition scheme.
  • The routing for the rows that are specified in the row partition.

Reactivate the configuration file if the command affects a table that is already being replicated. SharePlex will only lock tables for which there are configuration changes.

For more information about how to configure horizontally partitioned replication, see the SharePlex Administration Guide.

Usage

Supported source:

PostgreSQL (on-prem), Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL

Supported targets: PostgreSQL, Oracle, SQL Server, Kafka, Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL
Issues on: source system
Related commands: modify partition, drop partition, drop partition scheme, view partitions

Syntax

Basic command Command options

add partition toscheme_name

set

{condition = column_condition |

route = routing_map

[and name =partition_name]

[and tablename =target_table]

[and description =description]

Syntax description
Component Description
to scheme_name

to is a required keyword indicating the row partition is being added to scheme_name.

scheme_name is the name of the partition scheme. The partition scheme is created by the first add partition command that you issue, which will also specify the first set of rows to partition.

If you are making heavy use of horizontal partitioning, it may help to establish naming conventions for your partition schemes.

set

Required keyword that starts the definition of the row partition.

condition = column_condition

Creates a row partition based on a column condition. The condition must be in quotes. Use standard WHERE conditional syntax such as ((region_id = West) and region_id is not null).

route = routing_map

The route for this partition. This can be one of the following:

Partition based on a column condition:

Specify any standard SharePlex routing map, for example: sysB@r.dbname or sysB:q1@r.dbname or sysB@r.testdb+sysC@r.testdb (compound routing map).

To route a partition to multiple target tables that have different names, do the following:

  • Issue a separate add partition command for each different target name. Use the tablename option to specify the name.
  • In the configuration file, specify any of these target tables as the target table in the entry that uses this partition scheme. SharePlex will detect the other names when the configuration is activated.
  • Set the SP_ORD_FIRST_FIND parameter to 0 so that SharePlex checks all of the column conditions in the partition scheme. By default SharePlex assumes that any given row change will satisfy only one column condition in the partition scheme.
name = name

(Recommended) A short name for this partition. This option is only useful for partitions based on column conditions. A name eliminates the need to type out long column conditions in the event that you need to modify or drop the partition in the future.

tablename = owner.table

(Optional) Use this option when there are multiple target tables and one or more have different names. Issue a separate add partition command for each name.

The table name must be fully qualified. If case-sensitive, the name must be specified in quotes.

Example:

add partition to scheme1 set name = p1 and condition = "C1 > 200" and route = sysb:p1@r.dbname and tablename = myschema.mytable

description = description (Optional) Description of this partition.
Examples
Row partitions based on column conditions

Route different sets of rows through different post queues:

sp_ctrl> add partition to scheme1 set name = q1 and condition = "C1 >= 200" and route = sysb:q1@r.dbname

sp_ctrl> add partition to scheme1 set name = q2 and condition = "C1 < 200" and route = sysb:q2@r.dbname

Route different sets of rows to different target systems and different table names from the source:

sp_ctrl> add partition to scheme1 set name = east and condition = "area = east" and route = sys1e@r.dbname and tablename = schema1.targ

sp_ctrl> add partition to scheme1 set name = west and condition = "area = west" and route = sys2w@r.dbname and tablename = schema2.targ

Verwandte Dokumente

The document was helpful.

Bewertung auswählen

I easily found the information I needed.

Bewertung auswählen