some random utils for disk space analysis and eliminating old history records, RT#6914

2 files changed, 66 insertions, 0 deletions

diff --git a/bin/del-old-history b/bin/del-old-history
new file mode 100755
index 000000000..5c9412acf
--- /dev/null
+++ b/bin/del-old-history
@@ -0,0 +1,30 @@
+#!/usr/bin/perl -w
+
+use strict;
+use FS::UID qw(adminsuidsetup dbh);
+use FS::Record; #why is this necessary
+
+#WARNING: not all tables are safe to remove history!
+# these are, and seem to take the most space in a typical install with queued
+# exports
+my @tables = qw( h_queue h_queue_arg );
+
+my $years = 2;
+my $seconds = $years * 31556926; #60*60*24*365.2422 is close enough
+my $before = int( time - $seconds );
+
+adminsuidsetup shift or die "usage: del-old-history user\n";
+
+foreach my $table ( @tables ) {
+
+  unless ( $table =~ /^h_/ ) {
+    warn "$table is not a history table, skipping\n";
+    next;
+  }
+
+  my $sql = "DELETE FROM $table WHERE history_date < $before";
+  warn "$sql\n";
+  my $sth = dbh->prepare($sql) or die dbh->errstr;
+  $sth->execute or die $sth->errstr;
+
+}
diff --git a/bin/pg-sizer b/bin/pg-sizer
new file mode 100755
index 000000000..3af028633
--- /dev/null
+++ b/bin/pg-sizer
@@ -0,0 +1,36 @@
+#!/usr/bin/perl -w
+
+use strict;
+use FS::UID qw(adminsuidsetup dbh);
+use FS::Schema qw(dbdef);
+use FS::Record; #why is this necessary
+
+adminsuidsetup shift or die "usage: pg-sizer user";
+
+my $verbose = 1;
+
+my %size = ();
+my %prettysize = ();
+
+foreach my $table ( dbdef->tables ) {
+  warn "sizing $table...\n" if $verbose;
+  my $sth = dbh->prepare("SELECT pg_total_relation_size('$table')")
+    or die dbh->errstr;
+  $sth->execute or die $sth->errstr;
+  my $size = $sth->fetchrow_arrayref->[0];
+  $size{$table} = $size;
+
+  my $psth = dbh->prepare("SELECT pg_size_pretty( $size )")
+    or die dbh->errstr;
+  $psth->execute or die $psth->errstr;
+  my $prettysize = $psth->fetchrow_arrayref->[0];
+  $prettysize{$table} = $prettysize;
+
+  warn "$table: $prettysize{$table}\n" if $verbose;
+}
+
+foreach my $table ( reverse sort { $size{$a} <=> $size{$b} } keys %size ) {
+  #print "$table: $size{$table}\n";
+  print "$table: $prettysize{$table}\n";
+}
+