Uploaded image for project: 'PuppetDB'
  1. PuppetDB
  2. PDB-2398

PuppetDB should enable emergency deletion of historical data

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Normal
    • Resolution: Fixed
    • Affects Version/s: PDB 3.2.2
    • Fix Version/s: PDB 6.10.0, PDB 5.2.14
    • Component/s: None
    • Labels:
    • Template:
    • Sprint:
      Hopper/Triage
    • CS Priority:
      Reviewed
    • Release Notes:
      New Feature
    • Release Notes Summary:
      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

            Activity

              People

              Assignee:
              austin.blatt Austin Blatt
              Reporter:
              chuck Charlie Sharpsteen
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Zendesk Support