Autovacuum script

[Avihai Shoham <avihai.shoham@xxxxxxxxx>]

Hi All , 

 

I would like for you advice/sharing  about a python script that will run overnight and do autovacuum.

 

I'm sharing mine below and will happy if you have suggestion to improve it , or sharing yours in case you have.

I'm not an expert of python or postgrsql so please understand :)

The script below do autovacuum for the biggest 10 tables

-----------------------------------------------------------------------------------------------------------------------------------

#!/usr/bin/python
import psycopg2
import sys,traceback

con = None
query = ''

try:
        con = psycopg2.connect(host="<>", database="manager", user="<>", password="<>")
cur = con.cursor()
print("Execute query cur.execute")

cur.execute('select relname as "Table",pg_size_pretty(pg_total_relation_size(relid)) As "Size",pg_size_pretty(pg_total_relation_size(relid) -   pg_relation_size(relid)) as "External Size" FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;select relname as "Table",pg_size_pretty(pg_total_relation_size(relid)) As "Size",pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size" FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC ')
        rows = cur.fetchmany(10)
  print("Selecting rows from table using cursor.fetchall")

        con.set_isolation_level(0)
        for  row in rows:
       print (row[0], row[1], row[2])
          query = 'VACUUM FULL %s;' % (row[0])
          cur.execute(query)
           # need to add a print after execution to know the size zfter autovacuum print (row[0], row[1], row[2])


except psycopg2.Error as e:
    print "I am unable to connect to the database"
    print e
    print e.pgcode
    print e.pgerror
    print traceback.format_exc()


finally:
    # closing database connection.
    if con:
        cur.close()
        con.close()
        print("PostgreSQL connection is closed")