Edit config for PostgreSQL
Use the edit config command to modify an inactive configuration file. This command opens the configuration in SharePlex’s default text editor. Modify and save the configuration using the tools provided by the editor.
To modify an active configuration, it is recommended that you copy the active configuration with the copy config command, and then edit the copy with the edit config command. When you activate the copy, the original configuration deactivates.
To view a list of configurations on a system, use the list config command. To view the contents of a configuration, use the view config command.
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 system |
| Related commands: |
activate config, copy config, create config, list config, rename config, verify config, view config |
Syntax
Syntax description
| filename |
The name of the configuration that you want to edit. Configuration names are case-sensitive.
Example:
sp_ctrl(sysA)>edit config sales |
Flush for PostgreSQL
Use the flush command to stop the Post process at a certain point. It puts a marker in the data stream and automatically stops the Post process at the marker point. You can issue this command at a certain time or date – when month-end reports need to be generated, for example – and the data on the target system will reflect what was on the source system at the time the command was issued.
When you are performing backups, or when following certain documented SharePlex procedures, you issue the flush command after you stop user access on the source system to ensure that the last transaction gets posted to the target database before Post is stopped.
Starting Post again
Changes generated after the flush command accumulate in the queues and are applied to the target instance after you start Post again. Post remains stopped until the start post command is issued. Post keeps a record of where it stopped and resumes posting from that point to maintain synchronization.
Important: If users continue making changes on the source system while Post is stopped, data will accumulate in the post queue and possibly consume all available disk space. Remember to start Post as soon as permissible.
Note: The Status command for post will show "stopped due to flush"
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 system |
Syntax
| flushdatasource |
[tohost]
[queuequeuename]
[tohostqueuequeuename]
[tohost@target_SID]
[tohost@target_SIDqueuequeuename] |
Syntax description
| datasource |
This argument specifies the source data that the command will affect. Without additional options, it flushes the data through all queues on all target systems and stops Post.
datasource is expressed as r.dbname, where dbname is the database name of the source PostgreSQL instance.
Example: sp_ctrl(sysA)>flush r.dbname1
In the above example, the data for instance r.dbname1 is flushed to all target systems. |
| tohost |
This option flushes all of the data replicating to a designated target system. Replication to other target systems is unaffected.
Example: sp_ctrl(sysA)> flush r.dbname1 to sysB
In the above example, the data is flushed from dbname1 to sysB. |
| queuequeuename |
This option flushes data for a designated post queue. It flushes data flowing through all queues of that name on all target systems. This option is most useful when there are named post queues.
Example: sp_ctrl(sysA)> flush r.dbname1 queue sales
In the above example, the data is flushed for post queue sales on all target systems that have a post queue of that name. |
| tohostqueue queuename |
This option flushes data for a designated post queue on a designated target system. Other post queues on that and other target systems are unaffected.
Example: sp_ctrl(sysA)> flush r.dbname1 to sysB queue sales
In the above example, the data is flushed for post queue sales on sysB. |
| tohost@datadest |
This option flushes data to a designated target instance on a designated target system. It does not affect:
- Other target instances on that system.
- Other target instances with the same ORACLE_SID / database name on other target systems
In the syntax:
Examples:
sp_ctrl(sysA)> flush r.dbname1 to sysB@r.dbname2 (PostgreSQL to PostgreSQL implementation)
In the above example, the data is flushed to database dbname2 on sysB.
sp_ctrl(sysA)> flush r.dbname1 to sysB@o.oraB (PostgreSQL to Oracle implementation)
In the above example, the data is flushed to Oracle instance oraB on sysB. |
| tohost@datadestqueuequeuename |
This option flushes data for a designated post queue and target instance on a designated target system. It does not affect:
- Other post queues for that instance or any other target instance on that system.
- Other post queues on any other target system.
In the syntax:
- host is the target system’s name.
- datadest is expressed as o.SID, where SID is the ORACLE_SID of the target instance.
- queuename is the name of the post queue.
Examples:
sp_ctrl(sysA)> flush r.dbname1 to sysB@r.dbname2 queue sales (PostgreSQL to PostgreSQL implementation)
In the above example, the data is flushed for post queue sales for target PostgreSQL database dbname2 on sysB.
sp_ctrl(sysA)> flush r.dbname1 to sysB@o.oraB queue sales (PostgreSQL to Oracle implementation)
In the above example, the data is flushed for post queue sales for target instance oraB on sysB. |
Job status command for PostgreSQL
Use the job status command to view the status of the last compare, compare using, repair or repair using job, and for any other jobs that are still running. The job status command can be used to view detailed status on a job or a portion of a job, or to view status on all jobs for which SharePlex has history. SharePlex retains the history for all jobs as specified by the SP_SYS_JOB_HISTORY_RETENTION parameter. See the clear history and remove log commands for information on actively removing history and/or job process logs.
sp_ctrl (sysA) > job status
| Job ID |
: 861 |
| PID |
: 20571 |
| Host |
: sysa.domm.com |
| Started |
: 06-NOV-12 11:07:05 |
| Job Type |
: Compare |
| Status |
: Processing - 4 objects completed |
| ID |
Tablename |
Status |
Time |
Total rows |
%Comp |
Total time |
| --- |
---------------------- |
-------- |
--------- |
----------------- |
------ |
--------- |
| 1 |
"SCOTT"."SRC_TEST1" |
Out Sync |
N/A |
19 |
100 |
0:05 |
| 4 |
"SCOTT"."SRC_TEST4" |
WaitMarker |
0:02 |
27392 |
|
0:04 |
| 5 |
"SCOTT"."SRC_TEST5" |
Init |
0:01 |
27498 |
|
0:01 |
To view a summary of all jobs for which SharePlex has history:
sp_ctrl (alvspxl11:8567)> job status all
| Job ID |
Type |
filename/tablename |
Status |
Started |
Completed |
| ------ |
------ |
------------------ |
-------- |
-------------- |
------------- |
| 3441 |
Compare |
prod.conf |
Done - errors |
16-DEC-11 15:39 |
16-DEC-11 15:40 |
| 3442 |
Repair |
scott.src_test4 |
Done |
16-DEC-11 15:50 |
16-DEC-11 15:50 |
| 3443 |
Repair |
prod.conf |
Done |
19-DEC-11 10:42 |
19-DEC-11 10:42 |
| 3445 |
Compare |
scott.src_test33 |
Done - errors |
20-DEC-11 12:02 |
20-DEC-11 12:03 |
To view the status of a job with detail:
sp_ctrl (alvspxl11:8567)> compare status detail
| Job ID |
: 3448 |
| PID |
: 763 |
| Host |
: sysa.domm.com |
| Started |
: 20-DEC-11 12:40:46 |
| Job Type |
: Compare |
| Status |
: Done - 6 objects completed |
| Table ID |
: 3448.3 |
| Table |
: "SCOTT"."SRC_TEST3" |
| From |
: sysa.domm.com@r.w111a64f |
| To |
: "SYSPROD"."SRC_TEST3" sysb.domm.com@r.w111a64f |
| Started |
: 20-DEC-11 12:40:55 |
| Percent complete |
: 100% |
| Total Rows |
: 234452 |
| Rows processed |
: 234452 |
| Rows out-of-sync |
: 2 |
| Status |
: Out Sync |
| Status Elapsed |
: N/A |
| Total Elapsed |
: 0:07 |
| Inserts |
: 2 |
| Updates |
: 0 |
| Deletes |
: 0 |
Usage
| Supported sources: |
PostgreSQL |
| Supported targets: |
PostgreSQL |
| Authorization level: |
Operator (2) |
| Issued for: |
source system |
| Related commands: |
compare status, repair status |
Syntax
| job 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)>job 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)>job status 2828.HR.SRC_TEST3 |
| all |
Displays a summary line for every job with history in the database.
Example:
sp_ctrl(sysA)>job 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)>job 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)>job status detail |
| status |
Displays status history for previous jobs with the specified status.
sp_ctrl(SysA)>job 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 |
List config for PostgreSQL
Use the list config command to view a list of all active and inactive configurations on a source system.
The command displays the following information:
- File Name: The name of the configuration, the time and date that it was modified, and the size of the file.
- State: Whether the configuration is active or inactive. Active means the configuration file is currently involved in replication. Inactive means the configuration is not currently involved in replication.
- Datasource: The PostgreSQL instance containing the objects being replicated by the configuration.
- Internal Name: The name of the internal copy of the active configuration. This is the file that SharePlex actually replicates from. Its name is displayed under the State heading wherever there is an active configuration.
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: |
view config, show config |
Syntax
