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")
