Re: Subject: bool / vacuum full bug followup part 2

I added a -q/quiet option to oid2name, and have added a section to the
oid2name README showing how to use the utility, with an example using
du:

$ du * | while read SIZE OID
> do
> echo "$SIZE `oid2name -q -d test -o $OID`"
> done |
> sort -rn
2048 19324 = bigtable
1950 23903 = customers

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

Scott Marlowe wrote:
> On Mon, 6 May 2002, Steve Lane wrote:
>
> > I'd like to interject a short novice question, because I'd like to check
> > this:
> >
> > What's the best way to see how much space my indexes take up?
>
> You'll need a program called oid2name, found in the contrib directory of
> the postgresql source distribution. Many Linux distros include it
> automagically, but if not, it's a simple install.
>
> On my machine, I build postgresql from source, and I build it in the
> /usr/local/src/postgresql-x.y.z directory, where x.y.z is the version
> number. so, for 7.2.1, I would do this:
>
> su -
> (enter root password)
> cd /usr/local/src/postgresql-7.2.1/contrib/oid2name
> make
> make install
> exit (back to being joe regular user)
>
> then try entering oid2name. Assuming you have /usr/local/pgsql/bin in
> your path, it should run and tell you the name of all your databases.
>
> On my local test box, I get this:
> All databases:
> ---------------------------------
> 16556 = scott.marlowe
> 1126697 = test
> 1 = template1
> 16555 = template0
>
> Using the -d switch, you can get a list of all the oids used by a given
> database, like so:
>
> oid2name -d scott.marlowe
> All tables from database "scott.marlowe":
> ---------------------------------
> 126572 = accounts
> 1126708 = accounts_pkey
> 126566 = branches
> 1126706 = branches_pkey
> 126575 = history
> 126569 = tellers
> 1126707 = tellers_pkey
> 16557 = test
> 1126709 = test_id_dx
>
> Now, lastly, you need to be the postgres super user to do this, replacing
> db with the name of the database you wanna see, and dx with the name of
> the index you want to know about: (note this should be on one line, but it
> ran pretty long, so I have a \ continuation character in there)
>
> du -s $PGDATA/base/`oid2name |grep db|cut -d ' ' -f 1`/`oid2name -d \
> db|grep dx|cut -d ' ' -f 1`
>
> or scriptify it by putting that command into a file called
> /usr/local/pgsql/bin/tsize that looks like this:
>
> #!/bin/bash
> du -s $PGDATA/base/`oid2name |grep $1|cut -d ' ' -f 1`/`oid2name -d \
> $1|grep $2|cut -d ' ' -f 1`
>
> (don't forget to chmod 755 it so it's executable)
> and call it like so:
>
> tsize dbname tablename
>
> Hope that helps!
>
> explanation:
>
> What the above script does is kind like this:
>
> cd $PGDATA/base
> oid2name
> (find oid of your database in list)
> cd oid_of_your_database
> oid2name -d your_database
> (find oid of your index)
> du -s oid_of_your_index
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026