Skip site navigation (1) Skip section navigation (2)

Re: vacuum verbose relations reporting

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Carol Walter <walterc(at)indiana(dot)edu>, pgsql-admin(at)postgresql(dot)org
Subject: Re: vacuum verbose relations reporting
Date: 2008-08-27 06:02:31
Message-ID: 48B4EDF7.9080505@frostconsultingllc.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 916-647-6411	FAX: 916-405-4032

In response to

Responses

pgsql-admin by date

Next:From: Merlin MoncureDate: 2008-08-27 12:12:31
Subject: Re: [GENERAL] PITR - base backup question
Previous:From: Alvaro HerreraDate: 2008-08-27 01:36:12
Subject: Re: vacuum verbose relations reporting

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group