diff options
Diffstat (limited to 'doc/src/sgml/oid2name.sgml')
| -rw-r--r-- | doc/src/sgml/oid2name.sgml | 205 |
1 files changed, 205 insertions, 0 deletions
diff --git a/doc/src/sgml/oid2name.sgml b/doc/src/sgml/oid2name.sgml new file mode 100644 index 0000000000..2c5c396522 --- /dev/null +++ b/doc/src/sgml/oid2name.sgml @@ -0,0 +1,205 @@ +<sect1 id="oid2name"> + <title>oid2name</title> + + <indexterm zone="oid2name"> + <primary>oid2name</primary> + </indexterm> + + <para> + This utility allows administrators to examine the file structure used by + PostgreSQL. To make use of it, you need to be familiar with the file + structure, which is described in <xref linkend="storage">. + </para> + + <sect2> + <title>Overview</title> + <para> + <literal>oid2name</literal> connects to the database and extracts OID, + filenode, and table name information. You can also have it show database + OIDs and tablespace OIDs. + </para> + <para> + When displaying specific tables, you can select which tables to show by + using -o, -f and -t. The first switch takes an OID, the second takes + a filenode, and the third takes a tablename (actually, it's a LIKE + pattern, so you can use things like "foo%"). Note that you can use as many + of these switches as you like, and the listing will include all objects + matched by any of the switches. Also note that these switches can only + show objects in the database given in -d. + </para> + <para> + If you don't give any of -o, -f or -t it will dump all the tables in the + database given in -d. If you don't give -d, it will show a database + listing. Alternatively you can give -s to get a tablespace listing. + </para> + <table> + <title>Additional switches</title> + <tgroup cols="2"> + <tbody> + <row> + <entry><literal>-i</literal></entry> + <entry>include indexes and sequences in the database listing.</entry> + </row> + <row> + <entry><literal>-x</literal></entry> + <entry>display more information about each object shown: tablespace name, + schema name, OID. + </entry> + </row> + <row> + <entry><literal>-S</literal></entry> + <entry>also show system objects (those in information_schema, pg_toast + and pg_catalog schemas) + </entry> + </row> + <row> + <entry><literal>-q</literal></entry> + <entry>don't display headers(useful for scripting)</entry> + </row> + </tbody> + </tgroup> + </table> + </sect2> + + <sect2> + <title>Examples</title> + + <programlisting> +$ oid2name +All databases: + Oid Database Name Tablespace +---------------------------------- + 17228 alvherre pg_default + 17255 regression pg_default + 17227 template0 pg_default + 1 template1 pg_default + +$ oid2name -s +All tablespaces: + Oid Tablespace Name +------------------------- + 1663 pg_default + 1664 pg_global + 155151 fastdisk + 155152 bigdisk + +$ cd $PGDATA/17228 + +$ # get top 10 db objects in the default tablespace, ordered by size +$ ls -lS * | head -10 +-rw------- 1 alvherre alvherre 136536064 sep 14 09:51 155173 +-rw------- 1 alvherre alvherre 17965056 sep 14 09:51 1155291 +-rw------- 1 alvherre alvherre 1204224 sep 14 09:51 16717 +-rw------- 1 alvherre alvherre 581632 sep 6 17:51 1255 +-rw------- 1 alvherre alvherre 237568 sep 14 09:50 16674 +-rw------- 1 alvherre alvherre 212992 sep 14 09:51 1249 +-rw------- 1 alvherre alvherre 204800 sep 14 09:51 16684 +-rw------- 1 alvherre alvherre 196608 sep 14 09:50 16700 +-rw------- 1 alvherre alvherre 163840 sep 14 09:50 16699 +-rw------- 1 alvherre alvherre 122880 sep 6 17:51 16751 + +$ oid2name -d alvherre -f 155173 +From database "alvherre": + Filenode Table Name +---------------------- + 155173 accounts + +$ # you can ask for more than one object +$ oid2name -d alvherre -f 155173 -f 1155291 +From database "alvherre": + Filenode Table Name +------------------------- + 155173 accounts + 1155291 accounts_pkey + +$ # you can also mix the options, and have more details +$ oid2name -d alvherre -t accounts -f 1155291 -x +From database "alvherre": + Filenode Table Name Oid Schema Tablespace +------------------------------------------------------ + 155173 accounts 155173 public pg_default + 1155291 accounts_pkey 1155291 public pg_default + +$ # show disk space for every db object +$ du [0-9]* | +> while read SIZE FILENODE +> do +> echo "$SIZE `oid2name -q -d alvherre -i -f $FILENODE`" +> done +16 1155287 branches_pkey +16 1155289 tellers_pkey +17561 1155291 accounts_pkey +... + +$ # same, but sort by size +$ du [0-9]* | sort -rn | while read SIZE FN +> do +> echo "$SIZE `oid2name -q -d alvherre -f $FN`" +> done +133466 155173 accounts +17561 1155291 accounts_pkey +1177 16717 pg_proc_proname_args_nsp_index +... + +$ # If you want to see what's in tablespaces, use the pg_tblspc directory +$ cd $PGDATA/pg_tblspc +$ oid2name -s +All tablespaces: + Oid Tablespace Name +------------------------- + 1663 pg_default + 1664 pg_global + 155151 fastdisk + 155152 bigdisk + +$ # what databases have objects in tablespace "fastdisk"? +$ ls -d 155151/* +155151/17228/ 155151/PG_VERSION + +$ # Oh, what was database 17228 again? +$ oid2name +All databases: + Oid Database Name Tablespace +---------------------------------- + 17228 alvherre pg_default + 17255 regression pg_default + 17227 template0 pg_default + 1 template1 pg_default + +$ # Let's see what objects does this database have in the tablespace. +$ cd 155151/17228 +$ ls -l +total 0 +-rw------- 1 postgres postgres 0 sep 13 23:20 155156 + +$ # OK, this is a pretty small table ... but which one is it? +$ oid2name -d alvherre -f 155156 +From database "alvherre": + Filenode Table Name +---------------------- + 155156 foo + +$ # end of sample session. + </programlisting> + + <para> + You can also get approximate size data for each object using psql. For + example, + </para> + <programlisting> + SELECT relpages, relfilenode, relname FROM pg_class ORDER BY relpages DESC; + </programlisting> + <para> + Each page is typically 8k. Relpages is updated by VACUUM. + </para> + </sect2> + + <sect2> + <title>Author</title> + <para> + b. palmer, <email>bpalmer@crimelabs.net</email> + </para> + </sect2> + +</sect1> + |