a script to remove old partitions from a zabbix database

This script searches the database (postgres) for the most old tables and removes if they are old enough. Only for %Y_%m_%d format.

#!/usr/bin/python



import sys;
#import datetime;
from datetime import datetime, date, time, timedelta, tzinfo;
import time;
import psycopg2;

###### User params ########

TABLES = ( "history", "history_log", "history_uint", "history_str" );
MONTH = 3;
LIMIT = 3;

db_params = "host='localhost' dbname='zabbix' user='zabbix' password='xxx'";

##### End of User params ##




th_mth_ago = (datetime.today() - timedelta(MONTH*365/12)).strftime("%Y_%m_%d");
log_ft = "%Y-%m-%d %H:%M:%S";


def pg(curs,table,q):
    cnds = [];

    curs.execute(q);
    for row in curs:
        cnds.append(row[0]);

    return cnds;


def main():


    conn = psycopg2.connect(db_params);
    curs = conn.cursor();

    print "%s" %(datetime.now().strftime(log_ft));

    for table in TABLES:
 table_long = "partitions." + table + "_" + th_mth_ago;
        query = "SELECT  tablename FROM pg_tables WHERE schemaname='partitions' AND tablename like '" + table + "%' order by tablename limit " + str(LIMIT);
 candidates = pg(curs,table,query);
 if candidates:
  print "\n %s oldest tables:" % (LIMIT);
 for c_table in candidates:
     sys.stdout.write( '\n\t' + c_table + '');
     ct = str.rsplit(c_table,table+"_");
            candidate_time = ct[1];
     dt = datetime.strptime(candidate_time,"%Y_%m_%d");
#         print dt.strftime("%Y_%m_%d");
            if (dt.strftime("%Y_%m_%d") < th_mth_ago):


  sys.stdout.write(' is older then tree months.\n');

  try:
      q1 = "ALTER TABLE partitions." + table + "_" + dt.strftime("%Y_%m_%d") + " NO INHERIT " + table;
      print "%s: %s;" %(datetime.now().strftime(log_ft),q1);
             curs.execute(q1);
  except:
      print "%s: No inheritance!"%(datetime.now().strftime(log_ft));
      conn.rollback();
      pass;

  sys.exc_clear();

  try:
          q2 = "DROP RULE route_" + dt.strftime("%Y_%m_%d") + " ON " + table;
      print "%s: %s;" %(datetime.now().strftime(log_ft),q2);
          curs.execute(q2);
  except:
      print "%s: No INSERT rule!" %(datetime.now().strftime(log_ft));
      conn.rollback();
      pass;

  sys.exc_clear();

  conn.commit();
  
  q3 = "DROP TABLE partitions." + table + "_" + dt.strftime("%Y_%m_%d") + " CASCADE";
  print "%s: %s;" %(datetime.now().strftime(log_ft),q3);
         curs.execute(q3);
  conn.commit();
  
                print "%s: OK" %(datetime.now().strftime(log_ft));

    print "\n%s: Finished" % (datetime.now().strftime(log_ft));
    curs.close()
    conn.close()
    sys.stdout.flush()
    sys.stderr.flush()


if __name__ == "__main__":
    sys.exit(main())

# author: crypt
# url: http://crypt47.blogspot.com
# EOF