Re: Subject: bool / vacuum full bug followup part 2 - Mailing list pgsql-general

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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026