Detach Old Partitions Script and Scheduler Usage
Detaching existing partitions can help manage large value_stream_tables more efficiently. This process can be done through command line operations and the Scheduler.
A value_stream table with a large amount of data can cause purge operations to take significant time due to the resulting table-scans, increasing overall server load. Using drop-partition operations can streamline this issue.
After partitioning the value_stream table with partition_value_stream_by_range_setting command line (Partitioning value_stream table in PostgreSQL), you can use the detach_partitions_from_value_stream command to drop or archive the created partitions more easily and quickly.
This process can be done through command line operations and the Scheduler to maintain partitions archive or maintenance process.
The script named detach_partitions_from_value_stream has the following command line parameters:
|
Parameter name
|
Description
|
Default value
|
Comment
|
|---|---|---|---|
|
host
|
database host
|
Mandatory
|
|
|
port
|
database port
|
Mandatory
|
|
|
database
|
database name where the value_stream table exists
|
Mandatory
|
|
|
username
|
database user
|
Mandatory
|
|
|
password
|
database password
|
Mandatory
|
|
|
interval_to_retain
|
Calculated interval to retain = current time minus 26 hours (maximum time zone difference) minus the given interval_to_retain.
|
Mandatory
Possible formats:
• 1h or hours
• 1d or 1day
• 1 month
• 1y or 1year
|
|
|
action
|
Drop or Archive partitions action
|
Mandatory
Possible values:
• ‘drop’
• ‘archive’
|
|
|
value_stream_archive_name
|
Archived value stream table name
|
'value_stream_archive'
|
If you change the default name, then do not use the names:
• value_stream_part_0,
• value_stream_old,
• value_stream_tmp,
• or any existing table or partition.
|
|
future_data_exists
|
Optional
|
no
|
Possible values:
• yes or no
• if = yes (optional), the script will support future data partitioning
|
If you choose the action drop, full partitions older than interval_to_retain will be detached from the value_stream table and dropped.
If you choose the action archive, full partitions older than interval_to_retain will be detached from the value_stream table and attached to the value_stream_archive table (or another table set in the value_stream_archive_name input parameter).
For example, if you choose interval_to_retain as 1h, full partitions older than 1 hour will be detached and dropped or archived according to the action parameter.
 |
For recovery purposes, clone or back up the database before initiating the partitioning process.
|
To use the detach_partitions_from_value_stream command line, do the following:
1. Download the detach_partitions_from_value_stream.sh script. Request the download location from PTC support.
2. Copy the script to a server connected to the ThingWorx database.
3. Open a command line and run the command as desired using the above parameters.
For example, ./detach_partitions_from_value_stream.sh -host <localhost> -port <port> -database <database> -username <username> -password <password> -interval_to_retain '1h' -action 'archive'.
Scheduler Usage
You can use the Windows Task Scheduler or other OS schedulers to run the detach_partitions_from_value_stream sh script, such as for a monthly run.
You can also write another script that the scheduler will execute. This additional script will run detach_partitions_from_value_stream with parameters mentioned in the usage example.