From: Jeff Frost <jeff@frostconsultingllc.com>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Carol Walter <walterc@indiana.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: vacuum verbose relations reporting
Date: Tue, 26 Aug 2008 23:02:31 -0700
Message-ID: <48B4EDF7.9080505@frostconsultingllc.com> (raw)
In-Reply-To: <20080827013612.GT4920@alvh.no-ip.org>
References: <Pine.LNX.4.64.0808261207140.5906@discord.home.frostconsultingllc.com>
<E1DC2F12-02E8-4D82-9821-B9C7D01657D3@indiana.edu>
<Pine.LNX.4.64.0808261230460.3638@discord.home.frostconsultingllc.com>
<11312.1219794535@sss.pgh.pa.us>
<Pine.LNX.4.64.0808261820360.3638@discord.home.frostconsultingllc.com>
<20080827013612.GT4920@alvh.no-ip.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jeff@frostconsultingllc.com, alvherre@commandprompt.com, tgl@sss.pgh.pa.us, walterc@indiana.edu
Subject: Re: vacuum verbose relations reporting
In-Reply-To: <48B4EDF7.9080505@frostconsultingllc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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