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 |
| Supported targets: |
PostgreSQL, Oracle, SQL Server, and Kafka |
| 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 command 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
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 command 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. |
Usage
| Supported sources: |
PostgreSQL |
| Supported targets: |
PostgreSQL, Oracle, SQL Server, and Kafka |
| 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 |
Use of Show Post for PostgreSQL
Use the show post command to view statistics for the Post process.
Basic Show Post command
The basic show post command shows global statistics for all sessions a Post process. It shows the status of the Post process and the number of messages posted since it started. To filter the output for a specific post queue or datasource (useful when you have multiple replicating data streams), use the queue queuename or for datasource-datadest option.
Detailed Show Post command
To view detailed statistics for the Post process, use the show post command with the detail option. That option shows the most recent SQL statement processed, as well as other statistics that can help you assess Post’s performance, decide whether tuning parameters need to be adjusted, and detect problems or bottlenecks.
The following explains the detailed statistics shown with show post. These statistics vary slightly depending on the type of source and target.
| Host |
The name of the local machine (target system). |
| Source |
The source of the data being processed by Post. |
| Queue |
The Post queue for this Post process. For a default Post queue, it is the name of the source system. For a named queue, it is the user-defined name. |
| Target |
The name of the target of this Post process, for example the name of an PostgreSQL instance or Open Target database. |
| Status |
The status of the Post process (running or stopped). Possible statuses are:
- Running
- Stopping
- Stopped by user
- Stopped due to error
|
|
Operations posted
Operations processed |
The number of transactional operations and SharePlex internal operations that this Post process processed since it was started. |
| Since |
The time that Post started. |
| Total |
The number of messages in the queue that have yet to be read-released. This number corresponds to the 'Number of messages' returned from running qstatus. (The TOTAL value goes down with time and shows same value as Number of messages in QSTATUS) |
| Backlog |
The number of messages that are waiting in the queue to be processed by Post. |
|
Last operation posted |
Identifying information for the most current operation that is being posted to the target if Post is active, or the last operation posted if it is inactive. This information is specific to the type of datastore that originated the data. An operation can be:
- INSERT
- UPDATE
- DELETE
- COMMIT
- INSERT_MULTIPLE or DELETE_MULTIPLE (array/bulk operations).
- SharePlex internal operation.
|
|
Last transaction posted |
Identifying information for the last transaction that was posted. This information is specific to the type of datastore that originated the data. |
| Post state |
The state of the Post process, in relation to the replication work it performs. It can be one of the following:
- Waiting: Post is waiting for messages to process.
- Active: Post is posting changes to the database.
- Committed: Post is committing the transaction.
- Idle: Post has no open transactions to process.
- Rollback: Post is processing a rollback.
- Recovery: Post is in a crash-recovery mode.
|
| Activation ID |
The activation ID of the current configuration. This value needs to be displayed immediately after activation even before DML replication starts. |
|
Operations processed |
The number of SQL operations that Post applied to the target, whether or not the COMMIT was received. |
|
Transactions processed |
The number of committed transactions that Post applied to the target since it was started. |
| Insert operations |
The number of INSERT operations processed by Post since it was started. |
| Update operations |
The number of UPDATE operations processed by Post since it was started. |
| Delete operations |
The number of DELETE operations processed by Post since it was started. |
| Latency |
The time taken to process the replication (excluding the time taken by database on source) |
Usage
| Supported sources: |
PostgreSQL |
| Supported targets: |
PostgreSQL, Oracle, SQL Server, and Kafka |
| Issued for: |
target system |
| Related commands: |
show capture |
Syntax
| show post |
[detail]
[queue queuename]
[fordatasource-datadest]
[sessions] |
Syntax description
| show post |
Shows the state of the Process process and a summary of the operations processed. |
| detail |
This option displays detailed statistics for the Post process.
Example:
sp_ctrl(sysB)> show post detail |
| queuequeuename |
This option filters the show post display for a specific post queue.
If you are unsure what the queue name is, issue the qstatus command. Queue names are case-sensitive on all platforms.
This option can appear in any order with other options.
Example:
sp_ctrl(sysB)> show post queue sysA |
|
fordatasource-datadest |
This option filters the show post display for a specific data stream.
This option can appear in any order with other options.
Example:
sp_ctrl(sysB)> show post for r.dbnameA-r.ssB |
| sessions |
For PostgreSQL targets, this option displays statistics for all the threads spawned by the Post process.
For Open Target, which is single-threaded, this option can be used to view details for that thread.
This option can appear in any order with other options.
Example:
sp_ctrl(sysB)> show post sessions queue queuename |
