On 4/7/22 04:36, Avihai Shoham wrote:
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 ')
You've duplicated the SELECT statements
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])
Autovacuum automatically vacuums tables. Your script
manually vacuums tables.
Also, VACUUM FULL (which locks and then duplicates the table before dropping the original) should only be done in
very rare circumstances.
Lastly, a bash script would be much simpler.
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")
--
Angular momentum makes the world go 'round.
