When decommissioning PostgreSQL servers, the proactive method for immediately removing their data from pgwatch is to manually drop the corresponding partitions (subpartitions). This manual process can be cumbersome, especially when dealing with a large number of decommissioned sources.
To improve the user experience and provide a straightforward way to clean up monitoring data, we should implement a dedicated, idempotent function (e.g., a stored procedure or function in PostgreSQL) that automates this proactive cleanup.
Proposed Functionality:
The new function should accept a parameter (e.g., a source name or an array of source names) and perform the necessary steps to drop the associated partitions/subpartitions, effectively removing all historical data for that decommissioned source from the pgwatch measurements database.
This will offer a cleaner and more efficient alternative to the current manual approach.
Notes:
The existing "lazy" approach via the --retention parameter remains a viable option for users who prefer to let the data expire automatically.
The function should be built to handle potential partition name variations or other edge cases gracefully.
When decommissioning PostgreSQL servers, the proactive method for immediately removing their data from pgwatch is to manually drop the corresponding partitions (subpartitions). This manual process can be cumbersome, especially when dealing with a large number of decommissioned sources.
To improve the user experience and provide a straightforward way to clean up monitoring data, we should implement a dedicated, idempotent function (e.g., a stored procedure or function in PostgreSQL) that automates this proactive cleanup.
Proposed Functionality:
The new function should accept a parameter (e.g., a source name or an array of source names) and perform the necessary steps to drop the associated partitions/subpartitions, effectively removing all historical data for that decommissioned source from the pgwatch measurements database.
This will offer a cleaner and more efficient alternative to the current manual approach.
Notes:
The existing "lazy" approach via the --retention parameter remains a viable option for users who prefer to let the data expire automatically.
The function should be built to handle potential partition name variations or other edge cases gracefully.