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
Syntax 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
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
| activate config filename |
pglsn=lsn_value |
Syntax 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:
Follow the below steps:
-
Execute the query for alter table add/drop column without performing any DML on the table.
-
Reactivate the config file again so that the latest details are fetched and stored in the object cache of all the processes.
-
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:
-
Execute the query for alter table add/drop partition without performing any DML on the table.
-
Reactivate the config file again or restart the Capture process so that the latest partition details are fetched and used by the Capture process.
-
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
|
add partition toscheme_name
set
{condition = column_condition |
route = routing_map |
[and name =partition_name]
[and tablename =target_table]
[and description =description] |
Syntax 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
Cancel command for PostgreSQL
Use the cancel command to cancel a running compare and repair command job.
To cancel a job, you must supply its job ID. The job ID is reported back from the compare and repair job when you issue the command that starts it:
sp_ctrl>repair using 1elliot
repairing 7 of 7 objects
repair started; job id 408
Alternatively, you can get the job ID of the most recently streamed job by issuing the job status command:
sp_ctrl>job status
Job ID: 408
PID: 11527
Host: prodsys
Started: 22-FEB-15 18:08:09
Job Type: Repair
Status: Processing - 0 objects completed
Usage
| Supported sources: |
PostgreSQL |
| Supported targets: |
PostgreSQL |
| Authorization level: |
Operator (2) |
| Issues on: |
source system |
| Related commands: |
compare, repair |
Syntax
| cancel job_id |
[ on host |
on host:portnumber |
on login/password@host |
on login/password@host:portnumber ] |
Syntax description
| jobID |
The ID of the job to be canceled.
Example:
sp_ctrl>cancel 407 |
Remote options
These options enable you to issue the command on a remote machine and to script commands that include a login name, password, port number, or combination of those items.
| on host |
Execute the command on a remote system (one other than the one where the current sp_ctrl session is running). You are prompted for login credentials for the remote system. If used, must be the last component of the command syntax.
Example: sp_ctrl(sysB)>status on SysA |
| on host:portnumber |
Execute the command on a remote system when a remote login and port number must be provided. If used, must be the last component of the command syntax.
Example: sp_ctrl(sysB)>status on SysA:8304 |
| on login/password@host |
Execute the command on a remote system when a remote login, password, and host name must be provided. If used, must be the last component of the command syntax.
Example:sp_ctrl(sysB)>status on john/spot5489@SysA |
| on login/password@host:portnumber |
Execute the command on a remote system when a remote login, password, host name, and port number must be provided. If used, must be the last component of the command syntax.
Example: sp_ctrl(sysB)>status on john/spot5489@SysA:8304 |
