Details
-
Improvement
-
Status: Resolved
-
Normal
-
Resolution: Fixed
-
PDB 3.2.2
-
PuppetDB
-
Reviewed
-
New Feature
-
Adds a puppetdb subcommand, `delete-reports`, that will stop PuppetDB and delete all reports and their resource events.
Description
When a disk fills up on a database server, it is useful to have an option for reducing the disk space used without deleting data used by catalog compilation or requiring an increase in disk space. Disk increases can be infeasible or may require a long change cycle during which the Puppet infrastructure is inoperative.
It's also useful to TRUNCATE the reports table before a major upgrade that will need to migrate the reports table or if the upgrade contains a PostgreSQL upgrade that will copy the entire database.
Historical Context
In PuppetDB 2.x disk usage could be reduced in an emergency by truncating the reports table, which is often the largest table in the database by several orders of magnitude. This would return large amounts of space to the operating system, enabling further maintenance operations, while keeping exported resources intact for catalog compilation:
# PE 3.8.x
|
|
|
# sudo -u pe-postgres /opt/puppet/bin/psql -d pe-puppetdb
|
could not change directory to "/root"
|
psql (9.2.14)
|
Type "help" for help.
|
|
|
pe-puppetdb=# SELECT count(*) FROM catalog_resources WHERE exported = true;
|
count
|
-------
|
5
|
(1 row)
|
|
|
pe-puppetdb=# TRUNCATE TABLE reports CASCADE;
|
NOTICE: truncate cascades to table "resource_events"
|
NOTICE: truncate cascades to table "latest_reports"
|
TRUNCATE TABLE
|
pe-puppetdb=# SELECT count(*) FROM catalog_resources WHERE exported = true;
|
count
|
-------
|
5
|
(1 row)
|
In 2015.3.x, the TRUNCATE operation now cascades beyond the tables related to reporting and wipes out exported resources, which negatively impacts catalog compilation:
# PE 2015.x
|
|
|
# sudo -u pe-postgres /opt/puppetlabs/server/bin/psql -d pe-puppetdb
|
could not change directory to "/root": Permission denied
|
psql (9.4.5)
|
Type "help" for help.
|
|
|
pe-puppetdb=# SELECT count(*) FROM catalog_resources WHERE exported = true;
|
count
|
-------
|
6
|
(1 row)
|
|
|
pe-puppetdb=# TRUNCATE TABLE reports CASCADE;
|
NOTICE: truncate cascades to table "certnames"
|
NOTICE: truncate cascades to table "resource_events"
|
NOTICE: truncate cascades to table "factsets"
|
NOTICE: truncate cascades to table "catalogs"
|
NOTICE: truncate cascades to table "facts"
|
NOTICE: truncate cascades to table "catalog_resources"
|
TRUNCATE TABLE
|
pe-puppetdb=# SELECT count(*) FROM catalog_resources WHERE exported = true;
|
count
|
-------
|
0
|
(1 row)
|
Desired Functionality
PuppetDB should allow for emergency removal of historical data without impacting catalog compilation or requiring changes to disk space allocation.
Workaround / Proposed Implementation
echo "BEGIN TRANSACTION;
|
|
|
ALTER TABLE certnames DROP CONSTRAINT IF EXISTS certnames_reports_id_fkey;
|
|
|
UPDATE certnames SET latest_report_id = NULL;
|
|
|
TRUNCATE TABLE reports CASCADE;
|
|
|
ALTER TABLE certnames ADD CONSTRAINT "certnames_reports_id_fkey" FOREIGN KEY (latest_report_id) REFERENCES reports(id) ON DELETE SET NULL;
|
|
|
COMMIT TRANSACTION;" > /tmp/emergency_delete.sql
|
# su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -f /tmp/emergency_delete.sql"
|
BEGIN
|
ALTER TABLE
|
UPDATE 1
|
psql:/tmp/emergency_delete.sql:7: NOTICE: truncate cascades to table "resource_events"
|
TRUNCATE TABLE
|
ALTER TABLE
|
COMMIT
|
Attachments
Issue Links
- relates to
-
PDB-231 Add Postgres disk space full warnings
-
- Closed
-
