pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum script
3+ messages / 3 participants
[nested] [flat]
* Autovacuum script
@ 2022-04-07 09:36 Avihai Shoham <avihai.shoham@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Avihai Shoham @ 2022-04-07 09:36 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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")
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Autovacuum script
@ 2022-04-07 10:27 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Avihai Shoham <avihai.shoham@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2022-04-07 10:27 UTC (permalink / raw)
To: Avihai Shoham <avihai.shoham@gmail.com>; pgsql-admin@lists.postgresql.org
On Thu, 2022-04-07 at 12:36 +0300, Avihai Shoham wrote:
> 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
> [...]
> query = 'VACUUM FULL %s;' % (row[0])
I think that is quite a bad idea, for several reasons:
1. VACUUM (FULL) blocks read access to the table and is *not* a routine maintenance job
It should be VACUUM instead.
2. It may not be your largest tables that require VACUUM
3. There already is an autovacuum daemon in PostgreSQL that takes care of that and
has many parameters to adapt it to your needs.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Autovacuum script
@ 2022-04-07 10:44 Ron <ronljohnsonjr@gmail.com>
parent: Avihai Shoham <avihai.shoham@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ron @ 2022-04-07 10:44 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-04-07 10:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-07 09:36 Autovacuum script Avihai Shoham <avihai.shoham@gmail.com>
2022-04-07 10:27 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-04-07 10:44 ` Ron <ronljohnsonjr@gmail.com>
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