Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cJlRO-0005ux-2O for pgsql-performance@arkaria.postgresql.org; Wed, 21 Dec 2016 18:16:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cJlRN-0000jH-2p for pgsql-performance@arkaria.postgresql.org; Wed, 21 Dec 2016 18:16:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cJlRM-0000j4-BX for pgsql-performance@postgresql.org; Wed, 21 Dec 2016 18:16:36 +0000 Received: from mail-it0-x22e.google.com ([2607:f8b0:4001:c0b::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cJlRI-0004iY-Ix for pgsql-performance@postgresql.org; Wed, 21 Dec 2016 18:16:35 +0000 Received: by mail-it0-x22e.google.com with SMTP id o141so37604439itc.0 for ; Wed, 21 Dec 2016 10:16:32 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=propaas-com.20150623.gappssmtp.com; s=20150623; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to; bh=tHGivGbBK2yZC4JnscK0LFYLr65KGyEGPBKlWxpJy0E=; b=lhMYIrTygQ/CC9mwfMWeoy5Bsy1NcCnRcOY6He383IlU9ccRTbvmeBBV2fHDHggsb9 ONrbsR7aPlziDwVQgIObuHEp5o+JDA9dUK8wa3Do0dmNjux3vZOhAXk+BgsbUNW4Ji2z ZbJvClRJK2rG3HHADhk5kXtjrEEjycu3pJfpeM/5fWaxehRwJ2PIntEa9shounB5RFSx gdeRjo7sNFUO/+15b5GINwRvTGeJg8VQ9eRIPLtBS+HD0lBx1bnod2sjXVArpbpTb7Vn I+I9WI9Vd2GVjo4ghNY8/ig5oglUVxeovP9LvPrnQWcz+GCondTOxLPMlE8J3juJwmo9 VBEQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to; bh=tHGivGbBK2yZC4JnscK0LFYLr65KGyEGPBKlWxpJy0E=; b=f+WfzgxALs9Ykx5QEnZKPRlAruOjvdDeLlHptA9g4LuLC+XTRhf0KkP0n+fQS318vQ gL23+whi1TQG62G+QvafWgDlwSWzWCTo3J6JVgaWwmgogz0FuzquR4hP0BASoHtUM+Nx EFaVtUe9Hx7KcyPH+uZTu34ajdzkSBAiCbHKcKZZuox2yBZdNm8H4GHxGzoWG8sZ21Au ZzQixr7ozOvxR6dhqdqtigZVlnMBv/VmdZ0xoOEoArUxGXdt+BHs8S+icxWZWsc338UQ sgBPwecIss269lPRnIC4Uw5BcmxmSNSfM/ytT5eAxX11oTUfhU2wDVhBg2zJNCbToYjM /vRQ== X-Gm-Message-State: AIkVDXJEipe9N8FknwLmj++n7hp3NxGEdJIexSFJh7n15HVsCkFnKZmxugpxwxDRrY6dDg== X-Received: by 10.36.122.135 with SMTP id a129mr8722997itc.103.1482344190550; Wed, 21 Dec 2016 10:16:30 -0800 (PST) Received: from [192.168.1.17] (c-24-9-41-137.hsd1.co.comcast.net. [24.9.41.137]) by smtp.gmail.com with ESMTPSA id n102sm12499622ioi.38.2016.12.21.10.16.29 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 21 Dec 2016 10:16:29 -0800 (PST) Subject: Re: How to vacuum entire database excluding some tables in PostgreSQL9.1. To: pgsql-performance@postgresql.org References: From: ProPAAS DBA Message-ID: <91979dcd-9168-734d-e7e0-25f35d2ebd2f@propaas.com> Date: Wed, 21 Dec 2016 11:16:28 -0700 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.5.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------4046105A694928F86054AE60" X-Pg-Spam-Score: -0.1 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------4046105A694928F86054AE60 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit Something like this in a bash script? #!/bin/bash echo "select schemaname, tablename from pg_tables where tablename not in (your list of excluded tables) and schemaname not in ('information_schema', 'pg_catalog')" | psql -t > /tmp/tablist exec < /tmp/tablist while read line do set - $line echo "Vacuuming [$1] [$3]" echo "VACUUM VERBOSE ${1}.${3}" | psql done On 12/21/2016 10:49 AM, Dinesh Chandra 12108 wrote: > > Dear Expert, > > Could you please suggest me for the below query? > > *I want to vacuum the entire database with the exception of several* > > *tables.* > > Because there are some tables which are very big in size, so I just > want to exclude them. > > Thanks in advance. > > *Regards,* > > *Dinesh Chandra* > > *|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.* > > *------------------------------------------------------------------* > > Mobile: +91-9953975849 | Ext 1078 |dinesh.chandra@cyient.com > > > Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India. > > > ------------------------------------------------------------------------ > > DISCLAIMER: > > This email message is for the sole use of the intended recipient(s) > and may contain confidential and privileged information. Any > unauthorized review, use, disclosure or distribution is prohibited. If > you are not the intended recipient, please contact the sender by reply > email and destroy all copies of the original message. Check all > attachments for viruses before opening them. All views or opinions > presented in this e-mail are those of the author and may not reflect > the opinion of Cyient or those of our affiliates. --------------4046105A694928F86054AE60 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit

Something like this in a bash script?


#!/bin/bash

echo "select schemaname, tablename from pg_tables where tablename not in (your list of excluded tables) and schemaname not in ('information_schema', 'pg_catalog')" | psql -t > /tmp/tablist

exec < /tmp/tablist
while read line
do
     set - $line
     echo "Vacuuming [$1] [$3]"
     echo "VACUUM VERBOSE ${1}.${3}" | psql

done




On 12/21/2016 10:49 AM, Dinesh Chandra 12108 wrote:

Dear Expert,

 

Could you please suggest me for the below query?

 

I want to vacuum the entire database with the exception of several

tables.

 

Because there are some tables which are very big in size, so I just want to exclude them.

 

Thanks in advance.

 

Regards,

Dinesh Chandra

|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.

------------------------------------------------------------------

Mobile: +91-9953975849 | Ext 1078 |dinesh.chandra@cyient.com

Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.

 



DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. Check all attachments for viruses before opening them. All views or opinions presented in this e-mail are those of the author and may not reflect the opinion of Cyient or those of our affiliates.

--------------4046105A694928F86054AE60--