Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 01D2D65029E for ; Wed, 27 Aug 2008 03:13:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 55647-02 for ; Wed, 27 Aug 2008 03:13:31 -0300 (ADT) Received: from mx1.hub.org (mx1.hub.org [200.46.208.106]) by postgresql.org (Postfix) with ESMTP id 1860A64FCD2 for ; Wed, 27 Aug 2008 03:05:43 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.01.com (lb-2.01.com [38.98.186.147]) by mx1.hub.org (Postfix) with ESMTP id 50B38B07630 for ; Wed, 27 Aug 2008 03:05:25 -0300 (ADT) Received: from asav-2.01.com (localhost.localdomain [127.0.0.1]) by asav-2.01.com (8.13.1/8.13.1) with SMTP id m7R62XaV001986; Wed, 27 Aug 2008 01:02:33 -0500 Received: from smtp-1.01.com (smtp-1.01.com [38.98.186.157]) by asav-2.01.com (asav-2.01.com [10.25.1.12]) id k7Q12X2278844549QQ ret-id none; Wed, 27 Aug 2008 01:02:33 -0500 Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp-1.01.com (Postfix) with ESMTP id 2742BE02EC; Wed, 27 Aug 2008 01:02:33 -0500 (CDT) Received: from smtp-1.01.com ([127.0.0.1]) by localhost (smtp-1.01.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id I6orW8QtbrWh; Wed, 27 Aug 2008 01:02:33 -0500 (CDT) Received: from [10.10.10.18] (8.202-62-69.ftth.swbr.surewest.net [69.62.202.8]) by smtp-1.01.com (Postfix) with ESMTP id AFC00E00F5; Wed, 27 Aug 2008 01:02:32 -0500 (CDT) Message-ID: <48B4EDF7.9080505@frostconsultingllc.com> Date: Tue, 26 Aug 2008 23:02:31 -0700 From: Jeff Frost Organization: Frost Consulting, LLC User-Agent: Thunderbird 2.0.0.16 (Windows/20080708) MIME-Version: 1.0 To: Alvaro Herrera CC: Tom Lane , Carol Walter , pgsql-admin@postgresql.org Subject: Re: vacuum verbose relations reporting References: <11312.1219794535@sss.pgh.pa.us> <20080827013612.GT4920@alvh.no-ip.org> In-Reply-To: <20080827013612.GT4920@alvh.no-ip.org> Content-Type: multipart/alternative; boundary="------------060607050802020206050509" X-FSL-MailScanner-Information: Please contact postmaster@01.com for more information X-FSL-MailScanner: Found to be clean X-FSL-MailScanner-SpamCheck: X-FSL-MailScanner-From: jeff@frostconsultingllc.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/190 X-Sequence-Number: 30035 This is a multi-part message in MIME format. --------------060607050802020206050509 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Alvaro Herrera wrote: > Jeff Frost wrote: > > >> Tom, is there an easy (or hard) way to count relations from all DBs by >> using the system catalogs? >> > > Just do a count(*) from pg_class where relkind in ('r', 't', 'i'), and > sum across all databases (you need to connect to each one). > > (Actually you only need to count indexes that are btrees, if you need > such a distinction. Other indexes do not use the FSM as far as I know). > Perfect, so here's a little script that does the trick then: #!/bin/sh PSQL=/usr/bin/psql DATABASES=$($PSQL -lt | awk {'print $1'} | grep -v template0 ) RELATIONS=0 for DB in $DATABASES; do RELATIONS=$(($RELATIONS + $($PSQL --tuples-only --command "select count(*) from pg_class where relkind IN ('r', 't', 'i');" $DB) )) done echo $RELATIONS Thanks folks! -- Jeff Frost, Owner Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032 --------------060607050802020206050509 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Alvaro Herrera wrote: 
Jeff Frost wrote:

  
Tom, is there an easy (or hard) way to count relations from all DBs by 
using the system catalogs?
    

Just do a count(*) from pg_class where relkind in ('r', 't', 'i'), and
sum across all databases (you need to connect to each one).

(Actually you only need to count indexes that are btrees, if you need
such a distinction.  Other indexes do not use the FSM as far as I know).
Perfect, so here's a little script that does the trick then:

#!/bin/sh

PSQL=/usr/bin/psql
DATABASES=$($PSQL -lt |  awk {'print $1'} | grep -v template0 )
RELATIONS=0

for DB in $DATABASES; do
    RELATIONS=$(($RELATIONS + $($PSQL --tuples-only --command "select count(*) from pg_class where relkind IN ('r', 't', 'i');" $DB) ))
done

echo $RELATIONS

Thanks folks!

-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 916-647-6411	FAX: 916-405-4032
--------------060607050802020206050509--