Re: Autovacuum script

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

*Auto*vacuum 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.