Repair status command for PostgreSQL
Use the repair status command to view the status of the last compare or repair job run. The repair status command can be used to view detailed status on a compare or repair job or a portion of a compare or repair job, or to view status on all compare and repair jobs for which SharePlex has history.
For details and examples about using the repair status command, see the job status command
Usage
| Supported sources: |
PostgreSQL |
| Supported targets: |
PostgreSQL |
| Authorization level: |
Operator (2) |
| Issued for: |
source system |
| Related commands: |
compare status, job status |
Syntax
| repair status |
[job_id]
[job_id.table_id]
[all]
[full]
[detail]
[status] |
[ on host |
on host:portnumber |
on login/password@host |
on login/password@host:portnumber ] |
Syntax description
| job_id |
Displays status history for the job with the specified SharePlex-assigned job ID.
Example: sp_ctrl(sysA)> repair status 2828.2 |
| job_id.table_id |
Displays status history for the job with the specified SharePlex-assigned job ID and table.
Example: sp_ctrl(sysA)> repair status 2828.HR.SRC_TEST3 |
| all |
Displays a summary line for every job with history in the database.
Example: sp_ctrl(sysA)> repair status all |
| full |
Displays the status of every object in the job. By default, the job status command displays the status of those objects not completed, or completed with an exceptional status.
Example: sp_ctrl(sysA)> repair status2828 full |
| detail |
Displays detail information for every object reported upon. By default, the job status command displays a summary line for every object reported upon. Note that the detail information is the same as is displayed for the job_id.table_id option.
Example: sp_ctrl(sysA)> repair status detail |
| status |
Displays status history for previous jobs with the specified status.
Example: sp_ctrl(SysA)> repair status “Error” |
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 |
Verify config for PostgreSQL
The verify config command is intended for use as a preventive measure to avoid certain activation and replication problems. It is intended to be used to test activation to ensure that it will complete successfully.
The verify config command verifies tables only.
This command can be used to:
- Verify the syntax of the entries in the configuration file.
- Report an error if the source object is not supported for replication by SharePlex.
- Report if a host name specified in a route is unreachable.
- Report if there are duplicate specifications for a single object.
- Report if an object specification will be skipped and the reason why.
What the verify config command does not support
The verify config command does not:
- Verify activation time.
- Verify target objects or the target database name.
Verifying added or changed objects in an active configuration
To verify objects that you want to add to an active configuration or objects that you want to change (such as routing changes), it is suggested that you copy and modify the active configuration and then run the verify command against that copy.
Viewing the results of the verification
The verify config command retains control of the sp_ctrl interface until the verification is completed.
The command will read the entire config file and logging errors.
The results of the verify are displayed to the screen within sp_ctrl. If you would like to view detailed results you may:
- Issue the verify config command in sp_ctrl using the detail option
- Navigate to the results file directly using the path displayed to the screen after issuing the verify config command.
Usage
| Supported sources: |
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 |
| Authorization level: |
Viewer (3) |
| Issued for: |
source system |
| Related commands: |
activate config |
Syntax
| verify config filename |
detail |
Supported wildcard syntax
SharePlex supports the following SQL wildcards for tablename
- Percent (%) wildcard to specify a string.
- Underscore (_) wildcard to specify a single-character.
For more information on supported wildcard syntax, see Use Wildcards to Specify Multiple Tables.
Syntax description
| filename |
filename is the name of the configuration to be verified. |
| detail |
This option will display a greater level of detail to the screen.
Example:
sp_ctrl(sysA)> verify config myconfig detail
In this example, the myconfig file will be verified and the results will be displayed with a higher level of detail. |
View Partitions for PostgreSQL
Use the view partitions command to view the row partitions in one partition scheme or all partition schemes in a horizontally partitioned replication configuration.
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: |
add partition, drop partition, drop partition scheme, modify partition |
Syntax
|
view partitions for {scheme_name | all} |
Syntax description
| scheme_name |
Show the row partitions for the specified partition scheme. |
| all |
Show all row partitions, grouped according to the names of their partition schemes. |
Examples
sp_ctrl> view partitions for scheme1
sp_ctrl> view partitions all
Show Capture for PostgreSQL
Use the show capture command to view statistics for the Capture process.
Basic command
The basic show capture command shows an overview of the process, such as the datasource, whether the process is running or stopped, and other basic information.
Detailed statistics
To view detailed statistics for the Capture process, use the show capture command with the [detail] option. That option shows detailed statistics that can help you assess the performance of the process, decide whether tuning parameters need to be adjusted, and detect problems or bottlenecks.
Detailed statistics for PostgreSQL Capture
| Host |
The name of the local machine (source system). |
| System time |
The current time according to the system clock. |
| Source |
The name of the source PostgreSQL database. |
| Status |
The status of the Capture process (running or stopped). |
| Since |
The time that Capture started. |
| PostgreSQL current WAL LSN |
The LSN (Log Sequence Number) number of the WAL file log to which PostgreSQL is writing. |
| Capture current WAL LSN |
The LSN (Log Sequence Number) number of the WAL file log that Capture is reading.
This value needs to show the latest LSN value read by Capture, independent of whether data is coming from a replicated table or not. In idle condition, it should match PostgreSQL's current WAL LSN. |
| Last WAL file record processed |
The record being processed by Capture or the last one processed if Capture is not currently replicating data. |
| Capture state |
The state of the process, in relation to the replication work it performs: It can be one of the following:
- WAITING: Capture is waiting for records from WAL sender
- PROCESSING: Capture is processing a WAL file log record for replication.
- STOPPED BY ERROR: Capture is stopped by error and error is mentioned in the EVENT logs
|
| Activation ID |
The internal identifying number of the configuration activation, which identifies the associated processes and queues. This value needs to be displayed immediately after activation even before DML replication starts. |
| Error count |
The number of records that were skipped due to PostgreSQL errors since Capture started. Data from skipped records is not reflected in the target database. |
| Operations captured |
The number of DML operations that Capture successfully processed for replication since it started. |
| Transactions captured |
The number of committed PostgreSQL transactions whose operations Capture successfully replicated since it started. |
| Concurrent sessions |
The number of PostgreSQL sessions being processed at the same time. |
| HWM concurrent sessions |
The largest number of concurrent PostgreSQL sessions since Capture started. |
| Checkpoints performed |
The number of checkpoints to save the state of Capture since Capture started. Frequent checkpointing generates additional overhead on the system, but infrequent checkpoints cause SharePlex to recover less quickly from a system or instance failure. By default, Capture checkpoints every 40,000 messages or 120 seconds, but it can be adjusted with the SP_OCT_CHECKPOINT_FREQ and SP_OCT_CHECKPOINT_TIME parameters. |
| Total operations processed |
The number of all PostgreSQL operations and SharePlex internal operations processed by Capture since it started, including records captured for replication and records for objects not in the configuration (both replicated and not-to-be replicated records) |
| Total transactions completed |
The number of committed PostgreSQL transactions processed by Capture since it started, including transactions captured for replication and transactions for objects not in the replication configuration (both replicated and not-to-be replicated transactions) |
| Total Kbytes read |
The size in kilobytes of the data that was processed by Capture since it started. |
| XLOG records in progress |
The number of records that Capture is processing. |
| XLOG records processed |
The total number of XLOG records processed. |
| XLOG records ignored |
The number of records that Capture ignored because they are not associated with objects in the configuration. |
| Replication |
Type of replication (Physical or Logical) |
| Capture current TIMELINE_ID |
Displays the current Timeline ID (applicable only for Physical replication) |
For an example of the sample statistics for PostgreSQL Capture, see the example below:
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 |
| Issued for: |
source and target systems |
| Related commands: |
show post |
Syntax
| show capture |
[detail] [fordatasource] |
Syntax description
| show capture |
Shows the state of the Capture process and a summary of the operations captured. |
| detail |
Shows detailed statistics that can help you tune Capture’s performance and diagnose problems.
Example: sp_ctrl(sysA)> show capture detail |
| for datasource |
This option shows Capture statistics only for a specific datasource.
datasource is expressed as r.database where database is a dbname.
Example: sp_ctrl(sysA)> show capture for r.dbname |
