From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Autovacuum script
Date: Thu, 7 Apr 2022 05:44:44 -0500
Message-ID: <915b49c9-fe2d-745f-49f3-3fa955445adf@gmail.com> (raw)
In-Reply-To: <CA+=0ERct5Orv+GeERiROr4n+EXVi1=V+iQTnozb5qY+e_MaCFQ@mail.gmail.com>
References: <CA+=0ERcG7nz7N-sEPDsKzNn+aMC+Rt2oy=sXe94ieoG61x-W2Q@mail.gmail.com>
<CA+=0ERct5Orv+GeERiROr4n+EXVi1=V+iQTnozb5qY+e_MaCFQ@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Autovacuum script
In-Reply-To: <915b49c9-fe2d-745f-49f3-3fa955445adf@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox