doc/src/sgml/oid2name.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="oid2name">
   4  <title>oid2name</title>
   5
   6  <indexterm zone="oid2name">
   7   <primary>oid2name</primary>
   8  </indexterm>
   9
  10  <para>
  11   <application>oid2name</> is a utility program that helps administrators to
  12   examine the file structure used by PostgreSQL.  To make use of it, you need
  13   to be familiar with the database file structure, which is described in
  14   <xref linkend="storage">.
  15  </para>
  16
  17  <note>
  18   <para>
  19    The name <quote>oid2name</> is historical, and is actually rather
  20    misleading, since most of the time when you use it, you will really
  21    be concerned with tables' filenode numbers (which are the file names
  22    visible in the database directories).  Be sure you understand the
  23    difference between table OIDs and table filenodes!
  24   </para>
  25  </note>
  26
  27  <sect2>
  28   <title>Overview</title>
  29
  30   <para>
  31    <application>oid2name</application> connects to a target database and
  32    extracts OID, filenode, and/or table name information.  You can also have
  33    it show database OIDs or tablespace OIDs.  The program is controlled by
  34    a large number of command-line switches, as shown in
  35    <xref linkend="oid2name-switches">.
  36   </para>
  37
  38   <table id="oid2name-switches">
  39    <title><application>oid2name</> switches</title>
  40    <tgroup cols="2">
  41     <thead>
  42      <row>
  43       <entry>Switch</entry>
  44       <entry>Description</entry>
  45      </row>
  46     </thead>
  47
  48     <tbody>
  49      <row>
  50       <entry><literal>-o</literal> <replaceable>oid</></entry>
  51       <entry>show info for table with OID <replaceable>oid</></entry>
  52      </row>
  53
  54      <row>
  55       <entry><literal>-f</literal> <replaceable>filenode</></entry>
  56       <entry>show info for table with filenode <replaceable>filenode</></entry>
  57      </row>
  58
  59      <row>
  60       <entry><literal>-t</literal> <replaceable>tablename_pattern</></entry>
  61       <entry>show info for table(s) matching <replaceable>tablename_pattern</></entry>
  62      </row>
  63
  64      <row>
  65       <entry><literal>-s</literal></entry>
  66       <entry>show tablespace OIDs</entry>
  67      </row>
  68
  69      <row>
  70       <entry><literal>-S</literal></entry>
  71       <entry>include system objects (those in
  72        <literal>information_schema</literal>, <literal>pg_toast</literal>
  73        and <literal>pg_catalog</literal> schemas)
  74       </entry>
  75      </row>
  76
  77      <row>
  78       <entry><literal>-i</literal></entry>
  79       <entry>include indexes and sequences in the listing</entry>
  80      </row>
  81
  82      <row>
  83       <entry><literal>-x</literal></entry>
  84       <entry>display more information about each object shown: tablespace name,
  85        schema name, and OID
  86       </entry>
  87      </row>
  88
  89      <row>
  90       <entry><literal>-q</literal></entry>
  91       <entry>omit headers (useful for scripting)</entry>
  92      </row>
  93
  94      <row>
  95       <entry><literal>-d</literal> <replaceable>database</></entry>
  96       <entry>database to connect to</entry>
  97      </row>
  98
  99      <row>
 100       <entry><literal>-H</literal> <replaceable>host</></entry>
 101       <entry>database server's host</entry>
 102      </row>
 103
 104      <row>
 105       <entry><literal>-p</literal> <replaceable>port</></entry>
 106       <entry>database server's port</entry>
 107      </row>
 108
 109      <row>
 110       <entry><literal>-U</literal> <replaceable>username</></entry>
 111       <entry>username to connect as</entry>
 112      </row>
 113     </tbody>
 114    </tgroup>
 115   </table>
 116
 117   <para>
 118    To display specific tables, select which tables to show by
 119    using <literal>-o</>, <literal>-f</> and/or <literal>-t</>.
 120    <literal>-o</> takes an OID,
 121    <literal>-f</> takes a filenode,
 122    and <literal>-t</> takes a tablename (actually, it's a LIKE
 123    pattern, so you can use things like <literal>foo%</>).
 124    You can use as many
 125    of these switches as you like, and the listing will include all objects
 126    matched by any of the switches.  But note that these switches can only
 127    show objects in the database given by <literal>-d</>.
 128   </para>
 129
 130   <para>
 131    If you don't give any of <literal>-o</>, <literal>-f</> or <literal>-t</>,
 132    but do give <literal>-d</>, it will list all tables in the database
 133    named by <literal>-d</>.  In this mode, the <literal>-S</> and
 134    <literal>-i</> switches control what gets listed.
 135   </para>
 136
 137   <para>
 138    If you don't give <literal>-d</> either, it will show a listing of database
 139    OIDs.  Alternatively you can give <literal>-s</> to get a tablespace
 140    listing.
 141   </para>
 142  </sect2>
 143
 144  <sect2>
 145   <title>Examples</title>
 146
 147   <programlisting>
 148 $ # what's in this database server, anyway?
 149 $ oid2name
 150 All databases:
 151     Oid  Database Name  Tablespace
 152 ----------------------------------
 153   17228       alvherre  pg_default
 154   17255     regression  pg_default
 155   17227      template0  pg_default
 156       1      template1  pg_default
 157
 158 $ oid2name -s
 159 All tablespaces:
 160      Oid  Tablespace Name
 161 -------------------------
 162     1663       pg_default
 163     1664        pg_global
 164   155151         fastdisk
 165   155152          bigdisk
 166
 167 $ # OK, let's look into database alvherre
 168 $ cd $PGDATA/base/17228
 169
 170 $ # get top 10 db objects in the default tablespace, ordered by size
 171 $ ls -lS * | head -10
 172 -rw-------  1 alvherre alvherre 136536064 sep 14 09:51 155173
 173 -rw-------  1 alvherre alvherre  17965056 sep 14 09:51 1155291
 174 -rw-------  1 alvherre alvherre   1204224 sep 14 09:51 16717
 175 -rw-------  1 alvherre alvherre    581632 sep  6 17:51 1255
 176 -rw-------  1 alvherre alvherre    237568 sep 14 09:50 16674
 177 -rw-------  1 alvherre alvherre    212992 sep 14 09:51 1249
 178 -rw-------  1 alvherre alvherre    204800 sep 14 09:51 16684
 179 -rw-------  1 alvherre alvherre    196608 sep 14 09:50 16700
 180 -rw-------  1 alvherre alvherre    163840 sep 14 09:50 16699
 181 -rw-------  1 alvherre alvherre    122880 sep  6 17:51 16751
 182
 183 $ # I wonder what file 155173 is ...
 184 $ oid2name -d alvherre -f 155173
 185 From database "alvherre":
 186   Filenode  Table Name
 187 ----------------------
 188     155173    accounts
 189
 190 $ # you can ask for more than one object
 191 $ oid2name -d alvherre -f 155173 -f 1155291
 192 From database "alvherre":
 193   Filenode     Table Name
 194 -------------------------
 195     155173       accounts
 196    1155291  accounts_pkey
 197
 198 $ # you can mix the options, and get more details with -x
 199 $ oid2name -d alvherre -t accounts -f 1155291 -x
 200 From database "alvherre":
 201   Filenode     Table Name      Oid  Schema  Tablespace
 202 ------------------------------------------------------
 203     155173       accounts   155173  public  pg_default
 204    1155291  accounts_pkey  1155291  public  pg_default
 205
 206 $ # show disk space for every db object
 207 $ du [0-9]* |
 208 > while read SIZE FILENODE
 209 > do
 210 >   echo "$SIZE       `oid2name -q -d alvherre -i -f $FILENODE`"
 211 > done
 212 16            1155287  branches_pkey
 213 16            1155289  tellers_pkey
 214 17561            1155291  accounts_pkey
 215 ...
 216
 217 $ # same, but sort by size
 218 $ du [0-9]* | sort -rn | while read SIZE FN
 219 > do
 220 >   echo "$SIZE   `oid2name -q -d alvherre -f $FN`"
 221 > done
 222 133466             155173    accounts
 223 17561            1155291  accounts_pkey
 224 1177              16717  pg_proc_proname_args_nsp_index
 225 ...
 226
 227 $ # If you want to see what's in tablespaces, use the pg_tblspc directory
 228 $ cd $PGDATA/pg_tblspc
 229 $ oid2name -s
 230 All tablespaces:
 231      Oid  Tablespace Name
 232 -------------------------
 233     1663       pg_default
 234     1664        pg_global
 235   155151         fastdisk
 236   155152          bigdisk
 237
 238 $ # what databases have objects in tablespace "fastdisk"?
 239 $ ls -d 155151/*
 240 155151/17228/  155151/PG_VERSION
 241
 242 $ # Oh, what was database 17228 again?
 243 $ oid2name
 244 All databases:
 245     Oid  Database Name  Tablespace
 246 ----------------------------------
 247   17228       alvherre  pg_default
 248   17255     regression  pg_default
 249   17227      template0  pg_default
 250       1      template1  pg_default
 251
 252 $ # Let's see what objects does this database have in the tablespace.
 253 $ cd 155151/17228
 254 $ ls -l
 255 total 0
 256 -rw-------  1 postgres postgres 0 sep 13 23:20 155156
 257
 258 $ # OK, this is a pretty small table ... but which one is it?
 259 $ oid2name -d alvherre -f 155156
 260 From database "alvherre":
 261   Filenode  Table Name
 262 ----------------------
 263     155156         foo
 264   </programlisting>
 265  </sect2>
 266
 267  <sect2>
 268   <title>Limitations</title>
 269
 270   <para>
 271    <application>oid2name</> requires a running database server with
 272    non-corrupt system catalogs.  It is therefore of only limited use
 273    for recovering from catastrophic database corruption situations.
 274   </para>
 275  </sect2>
 276
 277  <sect2>
 278   <title>Author</title>
 279
 280   <para>
 281    B. Palmer <email>bpalmer@crimelabs.net</email>
 282   </para>
 283  </sect2>
 284
 285 </sect1>