Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ncPdH-0006R2-TY for pgsql-admin@arkaria.postgresql.org; Thu, 07 Apr 2022 10:44:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ncPdG-0006gZ-4Y for pgsql-admin@arkaria.postgresql.org; Thu, 07 Apr 2022 10:44:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ncPdF-0006aP-J8 for pgsql-admin@lists.postgresql.org; Thu, 07 Apr 2022 10:44:53 +0000 Received: from mail-ot1-x331.google.com ([2607:f8b0:4864:20::331]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ncPd8-0005to-Uk for pgsql-admin@lists.postgresql.org; Thu, 07 Apr 2022 10:44:52 +0000 Received: by mail-ot1-x331.google.com with SMTP id b17-20020a0568301df100b005ce0456a9efso3600483otj.9 for ; Thu, 07 Apr 2022 03:44:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to; bh=DpMiFA4n8Csh84idq/WTZ8PFUz0NuWOsBShDicKL18g=; b=Nctw1ZxV4cIa0hBt2+UvUfLtzWiuxUPUz4ECRrzDhrll8QM5q6rRKXF8ljeCug6CQg 0Em+goTYSC+zew4Uef6wHYFPjM6DMiwD4lb07hF3ThTpMiVL8tPBd0OWey8BGLtyvOk0 QaUaljcNHL/tZlFs0QCUlcqWJ1woc8C36KDnx4KTcSvuT+BH/8rdEgn8OIQT9PkpGmSE fcl15b+S2GLbeAcyKLKlVFaL5dQ957bigcOLPP2Mjy3N7Mrpus4pi+ETC+5D1Gx4NBxn e0Z8QD06Crb2sgNjQ20OGA8LB1MAye25dsuX8v5PZXb4/xj9x2BsdZFGtnSqyCOvSSA9 fsiA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=DpMiFA4n8Csh84idq/WTZ8PFUz0NuWOsBShDicKL18g=; b=Syv5GD1cq+LIz8CmxGwfbw/g107rmv7fNJCpSdrynpL//Q9vzY5mRRe5ADOYdlmADP iZu9uB28L4IMrArCQsQK3M6/sJv5/MI9gNv/YD0leP8/GEME02cfofkYFHgz/vbUqSg9 YTF9Z7W2zLHkjvWN6xq/C1jeVHj47vRsoEy74F0eve+qBvZKmNWGYGg+UoVxF/F+1qS8 ThByZrtXSbCuXRwp8Cd3XutOSa/PI3XhZ5wa8UQmtwTKZFbUkT7ocRImeQZju6jT4Pn9 IxN197S7e16u2JQfjEkG07av+oHkIsjbw3016Yrf8CtgXmz9RGVoCNP5w+oD7TxRVcbo zb9w== X-Gm-Message-State: AOAM532vai1XRL9TaKRBRUwmV1TJngMcbfl5tRFI9U8H4DYnWKrKvRik MU/cECG/plfGVxbLHriOpv0OQ+d6kK0= X-Google-Smtp-Source: ABdhPJyWWDQLKmc2K1csDr/BccoxCs+EW8ndym5Q6xrH6IhmsHVmkJrbwODfIMblX/fBSgfWZmoc0w== X-Received: by 2002:a05:6830:34a0:b0:5b2:71c2:16b5 with SMTP id c32-20020a05683034a000b005b271c216b5mr4819392otu.181.1649328285714; Thu, 07 Apr 2022 03:44:45 -0700 (PDT) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id i21-20020a056830011500b005cdc3cdacb5sm7903487otp.57.2022.04.07.03.44.44 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 07 Apr 2022 03:44:45 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------R2KS4N1Qlc2mnfGRMpioTsfA" Message-ID: <915b49c9-fe2d-745f-49f3-3fa955445adf@gmail.com> Date: Thu, 7 Apr 2022 05:44:44 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.5.0 Subject: Re: Autovacuum script Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------R2KS4N1Qlc2mnfGRMpioTsfA Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------R2KS4N1Qlc2mnfGRMpioTsfA Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------R2KS4N1Qlc2mnfGRMpioTsfA--