Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title Morgan Rhodes updated an issue PuppetDB / PDB-2398 PuppetDB should enable emergency deletion of historical data Change By: Morgan Rhodes Sprint: Release Engineering Hopper Add Comment This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.115454.1454725161000.164224.1615491004227%40Atlassian.JIRA.
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title Zachary Kent updated an issue PuppetDB / PDB-2398 PuppetDB should enable emergency deletion of historical data Change By: Zachary Kent Sprint: Release Engineering Hopper Add Comment This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.115454.1454725161000.162690.1615408440931%40Atlassian.JIRA.
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title Zachary Kent updated an issue PuppetDB / PDB-2398 PuppetDB should enable emergency deletion of historical data Change By: Zachary Kent Sprint: Add Comment This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.115454.1454725161000.162641.1615408320120%40Atlassian.JIRA.
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title Austin Blatt assigned an issue to Austin Blatt PuppetDB / PDB-2398 PuppetDB should enable emergency deletion of historical data Change By: Austin Blatt 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. Assignee: Austin Blatt Resolution: Fixed Status: Accepted Resolved Add Comment This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935)
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title Austin Blatt updated an issue PuppetDB / PDB-2398 PuppetDB should enable emergency deletion of historical data Change By: Austin Blatt Fix Version/s: PDB 5.2.14 Fix Version/s: PDB 6.10.0 Add Comment This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.115454.1454725161000.35748.1586535300057%40Atlassian.JIRA.
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-2398
PuppetDB should enable emergency deletion of historical data
Change By:
Nick Walker
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. h2. Historical ContextIn 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:{noformat}# PE 3.8.x# sudo -u pe-postgres /opt/puppet/bin/psql -d pe-puppetdbcould 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 TABLEpe-puppetdb=# SELECT count(*) FROM catalog_resources WHERE exported = true; count--- 5(1 row){noformat}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:{noformat}# PE 2015.x# sudo -u pe-postgres /opt/puppetlabs/server/bin/psql -d pe-puppetdbcould not change directory to "/root": Permission deniedpsql (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 TABLEpe-puppetdb=# SELECT count(*) FROM catalog_resources WHERE exported = true; count--- 0(1 row){noformat}h2. Desired FunctionalityPuppetDB should allow for emergency removal of historical data without impacting catalog compilation or requiring changes to disk space allocation.h1. Workaround / Proposed Implementation {code}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;" >
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title Rob Browning commented on PDB-2398 Re: PuppetDB should enable emergency deletion of historical data It sounds like there's a chance this might be obviated by PDB-4464 if it pans out. Add Comment This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.115454.1454725161000.54220.1565724900503%40Atlassian.JIRA.
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-2398
PuppetDB should enable emergency deletion of historical data
Change By:
Nick Walker
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.h2. Historical ContextIn 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:{noformat}# PE 3.8.x# sudo -u pe-postgres /opt/puppet/bin/psql -d pe-puppetdbcould 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 TABLEpe-puppetdb=# SELECT count(*) FROM catalog_resources WHERE exported = true; count--- 5(1 row){noformat}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:{noformat}# PE 2015.x# sudo -u pe-postgres /opt/puppetlabs/server/bin/psql -d pe-puppetdbcould not change directory to "/root": Permission deniedpsql (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 TABLEpe-puppetdb=# SELECT count(*) FROM catalog_resources WHERE exported = true; count--- 0(1 row){noformat}h2. Desired FunctionalityPuppetDB should allow for emergency removal of historical data without impacting catalog compilation or requiring changes to disk space allocation.h1. Workaround / Proposed Implementation {code} # cat /tmp/emergency_delete.sql 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 {code}{code}# su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -f /tmp/emergency_delete.sql"BEGINALTER TABLEUPDATE 1psql:/tmp/emergency_delete.sql:7: NOTICE: truncate cascades to table "resource_events"TRUNCATE TABLEALTER TABLECOMMIT{code}
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-2398
PuppetDB should enable emergency deletion of historical data
Change By:
Nick Walker
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.h2. Historical ContextIn 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:{noformat}# PE 3.8.x# sudo -u pe-postgres /opt/puppet/bin/psql -d pe-puppetdbcould 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 TABLEpe-puppetdb=# SELECT count(*) FROM catalog_resources WHERE exported = true; count--- 5(1 row){noformat}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:{noformat}# PE 2015.x# sudo -u pe-postgres /opt/puppetlabs/server/bin/psql -d pe-puppetdbcould not change directory to "/root": Permission deniedpsql (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 TABLEpe-puppetdb=# SELECT count(*) FROM catalog_resources WHERE exported = true; count--- 0(1 row){noformat}h2. Desired FunctionalityPuppetDB should allow for emergency removal of historical data without impacting catalog compilation or requiring changes to disk space allocation. h1. Workaround / Proposed Implementation {code}# cat /tmp/emergency_delete.sqlBEGIN 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;{code}{code}# su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -f /tmp/emergency_delete.sql"BEGINALTER TABLEUPDATE 1psql:/tmp/emergency_delete.sql:7: NOTICE: truncate cascades to table "resource_events"TRUNCATE TABLEALTER TABLECOMMIT{code}
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title Russell Mull updated an issue PuppetDB / PDB-2398 PuppetDB should enable emergency deletion of historical data Change By: Russell Mull Sprint: Hopper Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title
Charlie Sharpsteen commented on PDB-2398
Re: PuppetDB should enable emergency deletion of historical data
Currently, in PDB 3.x, the certnames_reports_id_fkey constraint is what propagates TRUNCATE CASCADE beyond the reports and resource_events tables:
pe-puppetdb=# \d+ reports
Table "public.reports"
Column | Type | Modifiers | Storage | Stats target | Description
---+--+--+--+--+-
id| bigint | not null default nextval('reports_id_seq'::regclass) | plain| |
hash | bytea| not null | extended | |
transaction_uuid | uuid | | plain| |
certname | text | not null | extended | |
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title Charlie Sharpsteen updated an issue PuppetDB / PDB-2398 PuppetDB should enable emergency deletion of historical data Change By: Charlie Sharpsteen Scrum Team: PuppetDB Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title Charlie Sharpsteen created an issue PuppetDB / PDB-2398 PuppetDB should enable emergency deletion of historical data Issue Type: Improvement Assignee: Unassigned Created: 2016/02/05 6:19 PM Labels: support Priority: Normal Reporter: Charlie Sharpsteen 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. Historical Context In PuppetDB 2.x disk usage could be reduced 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
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title Charlie Sharpsteen updated an issue PuppetDB / PDB-2398 PuppetDB should enable emergency deletion of historical data Change By: Charlie Sharpsteen CS Priority: Needs Priority Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title
Charlie Sharpsteen updated an issue
PuppetDB / PDB-2398
PuppetDB should enable emergency deletion of historical data
Change By:
Charlie Sharpsteen
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.h2. Historical ContextIn 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:{noformat}# PE 3.8.x# sudo -u pe-postgres /opt/puppet/bin/psql -d pe-puppetdbcould 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 TABLEpe-puppetdb=# SELECT count(*) FROM catalog_resources WHERE exported = true; count--- 5(1 row){noformat}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:{noformat}# PE 2015.x# sudo -u pe-postgres /opt/puppetlabs/server/bin/psql -d pe-puppetdbcould not change directory to "/root": Permission deniedpsql (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 TABLEpe-puppetdb=# SELECT count(*) FROM catalog_resources WHERE exported = true; count--- 0(1 row){noformat}h2. Desired FunctionalityPuppetDB should allow for emergency removal of historical data without impacting catalog compilation or requiring changes to disk space allocation.
Add Comment
Jira (PDB-2398) PuppetDB should enable emergency deletion of historical data
Title: Message Title Charlie Sharpsteen updated an issue PuppetDB / PDB-2398 PuppetDB should enable emergency deletion of historical data Change By: Charlie Sharpsteen Affects Version/s: PDB 3.2.2 Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
