Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
tag: REL7_4_8

Fetching latest commit…

Cannot retrieve the latest commit at this time

..
Failed to load latest commit information.
Makefile
README.oid2name
oid2name.c

README.oid2name

This utility allows administrators to view the file structure used by
PostgreSQL.  Databases are placed in directories based on their OIDs in
pg_database, and the tables in that directory are named by original
OIDs, stored in pg_class.relfilenode.  Oid2name connects to the database
and extracts the OID and table name information.

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

It can be used in four ways:


oid2name

  This will connect to the template1 database and display all databases
  in the system:

  $ oid2name
  All databases:
  ---------------------------------
  18720  = test1
  1      = template1
  18719  = template0
  18721  = test
  18735  = postgres
  18736  = cssi


oid2name -d test [-x]

  This connects to the database test and shows all tables and their OIDs:

  $ oid2name -d test
  All tables from database "test":
  ---------------------------------
  18766  = dns
  18737  = ips
  18722  = testdate


oid2name -d test -o 18737
oid2name -d test -t testdate

  This will connect to the database test and display the table name for oid
  18737 and the oid for table name testdate respectively:

  $ oid2name -d test -o 18737
  Tablename of oid 18737 from database "test":
  ---------------------------------
  18737  = ips


  $ oid2name -d test -t testdate 
  Oid of table testdate from database "test":
  ---------------------------------
  18722  = testdate

Keep in mind tables over one gigabyte will be split into separate files
with numeric file extensions.

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

Sample session:

$ cd /u/pg/data/base
$ oid2name
All databases:
---------------------------------
16817  = test2
16578  = x
16756  = test
1      = template1
16569  = template0
16818  = test3
16811  = floattest

$ cd 16756
$ ls 1873*
18730   18731   18732   18735   18736   18737   18738   18739

$ oid2name -d test -o 18737
Tablename of oid 18737 from database "test":
---------------------------------
18737  = ips

$ oid2name -d test -t ips
Oid of table ips from database "test":
---------------------------------
18737  = ips

$ # show disk space for every db object
$ du * | while read SIZE OID
> do
>	echo "$SIZE      `oid2name -q -d test -o $OID`"
> done
24      18737  = ips
36      18722  = cities
...

$ # same as above, but sort by largest first
$ du * | while read SIZE OID
> do
>	echo "$SIZE      `oid2name -q -d test -o $OID`"
> done |
> sort -rn
2048    19324  = bigtable
1950    23903  = customers
...

$ # show disk usage per database
$ cd /u/pg/data/base
$ du -s * |
> while read SIZE OID
> do
>	echo "$SIZE      `aspg oid2name -q | grep ^$OID' '`"
> done |
> sort -rn
2256        18721  = test
2135        18735  = postgres
..

This can be done in psql with:  

test=> SELECT relpages, relfilenode, relname FROM pg_class ORDER BY relpages DESC;

Each page is typically 8k.  Relpages is updated by VACUUM.

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

Mail me with any problems or additions you would like to see.  Clearing 
house for the code will be at:  http://www.crimelabs.net

b. palmer, bpalmer@crimelabs.net
Something went wrong with that request. Please try again.