doc/src/sgml/oid2name.sgml

   1 <!-- doc/src/sgml/oid2name.sgml -->
   2
   3 <refentry id="oid2name">
   4  <indexterm zone="oid2name">
   5   <primary>oid2name</primary>
   6  </indexterm>
   7
   8  <refmeta>
   9   <refentrytitle>oid2name</refentrytitle>
  10   <manvolnum>1</manvolnum>
  11   <refmiscinfo>Application</refmiscinfo>
  12  </refmeta>
  13
  14  <refnamediv>
  15   <refname>oid2name</refname>
  16   <refpurpose>resolve OIDs and file nodes in a <productname>PostgreSQL</productname> data directory</refpurpose>
  17  </refnamediv>
  18
  19  <refsynopsisdiv>
  20   <cmdsynopsis>
  21    <command>oid2name</command>
  22    <arg rep="repeat"><replaceable>option</replaceable></arg>
  23   </cmdsynopsis>
  24  </refsynopsisdiv>
  25
  26  <refsect1>
  27   <title>Description</title>
  28
  29  <para>
  30   <application>oid2name</application> is a utility program that helps administrators to
  31   examine the file structure used by PostgreSQL.  To make use of it, you need
  32   to be familiar with the database file structure, which is described in
  33   <xref linkend="storage"/>.
  34  </para>
  35
  36  <note>
  37   <para>
  38    The name <quote>oid2name</quote> is historical, and is actually rather
  39    misleading, since most of the time when you use it, you will really
  40    be concerned with tables' filenode numbers (which are the file names
  41    visible in the database directories).  Be sure you understand the
  42    difference between table OIDs and table filenodes!
  43   </para>
  44  </note>
  45
  46   <para>
  47    <application>oid2name</application> connects to a target database and
  48    extracts OID, filenode, and/or table name information.  You can also have
  49    it show database OIDs or tablespace OIDs.
  50   </para>
  51
  52  </refsect1>
  53
  54  <refsect1>
  55   <title>Options</title>
  56
  57   <para>
  58    <application>oid2name</application> accepts the following command-line arguments:
  59
  60    <variablelist>
  61
  62     <varlistentry>
  63      <term><option>-f <replaceable class="parameter">filenode</replaceable></option></term>
  64      <term><option>--filenode=<replaceable class="parameter">filenode</replaceable></option></term>
  65      <listitem><para>show info for table with filenode <replaceable>filenode</replaceable>.</para></listitem>
  66     </varlistentry>
  67
  68     <varlistentry>
  69      <term><option>-i</option></term>
  70      <term><option>--indexes</option></term>
  71      <listitem><para>include indexes and sequences in the listing.</para></listitem>
  72     </varlistentry>
  73
  74     <varlistentry>
  75      <term><option>-o <replaceable class="parameter">oid</replaceable></option></term>
  76      <term><option>--oid=<replaceable class="parameter">oid</replaceable></option></term>
  77      <listitem><para>show info for table with OID <replaceable>oid</replaceable>.</para></listitem>
  78     </varlistentry>
  79
  80     <varlistentry>
  81      <term><option>-q</option></term>
  82      <term><option>--quiet</option></term>
  83      <listitem><para>omit headers (useful for scripting).</para></listitem>
  84     </varlistentry>
  85
  86     <varlistentry>
  87      <term><option>-s</option></term>
  88      <term><option>--tablespaces</option></term>
  89      <listitem><para>show tablespace OIDs.</para></listitem>
  90     </varlistentry>
  91
  92     <varlistentry>
  93      <term><option>-S</option></term>
  94      <term><option>--system-objects</option></term>
  95      <listitem><para>include system objects (those in
  96       <option>information_schema</option>, <option>pg_toast</option>
  97       and <option>pg_catalog</option> schemas).
  98      </para></listitem>
  99     </varlistentry>
 100
 101     <varlistentry>
 102      <term><option>-t <replaceable class="parameter">tablename_pattern</replaceable></option></term>
 103      <term><option>--table=<replaceable class="parameter">tablename_pattern</replaceable></option></term>
 104      <listitem><para>show info for table(s) matching <replaceable class="parameter">tablename_pattern</replaceable>.</para></listitem>
 105     </varlistentry>
 106
 107     <varlistentry>
 108      <term><option>-V</option></term>
 109      <term><option>--version</option></term>
 110      <listitem>
 111       <para>
 112        Print the <application>oid2name</application> version and exit.
 113       </para>
 114      </listitem>
 115     </varlistentry>
 116
 117     <varlistentry>
 118      <term><option>-x</option></term>
 119      <term><option>--extended</option></term>
 120      <listitem><para>display more information about each object shown: tablespace name,
 121       schema name, and OID.
 122      </para></listitem>
 123     </varlistentry>
 124
 125     <varlistentry>
 126      <term><option>-?</option></term>
 127      <term><option>--help</option></term>
 128      <listitem>
 129       <para>
 130        Show help about <application>oid2name</application> command line
 131        arguments, and exit.
 132       </para>
 133      </listitem>
 134     </varlistentry>
 135    </variablelist>
 136   </para>
 137
 138   <para>
 139    <application>oid2name</application> also accepts the following command-line
 140    arguments for connection parameters:
 141
 142    <variablelist>
 143     <varlistentry>
 144      <term><option>-d <replaceable class="parameter">database</replaceable></option></term>
 145      <term><option>--dbname=<replaceable class="parameter">database</replaceable></option></term>
 146      <listitem><para>database to connect to.</para></listitem>
 147     </varlistentry>
 148
 149     <varlistentry>
 150      <term><option>-h <replaceable class="parameter">host</replaceable></option></term>
 151      <term><option>--host=<replaceable class="parameter">host</replaceable></option></term>
 152      <listitem><para>database server's host.</para></listitem>
 153     </varlistentry>
 154
 155     <varlistentry>
 156      <term><option>-H <replaceable class="parameter">host</replaceable></option></term>
 157      <listitem><para>database server's host.  Use of this parameter is
 158      <emphasis>deprecated</emphasis> as of
 159      <productname>PostgreSQL</productname> 12.</para></listitem>
 160     </varlistentry>
 161
 162     <varlistentry>
 163      <term><option>-p <replaceable class="parameter">port</replaceable></option></term>
 164      <term><option>--port=<replaceable class="parameter">port</replaceable></option></term>
 165      <listitem><para>database server's port.</para></listitem>
 166     </varlistentry>
 167
 168     <varlistentry>
 169      <term><option>-U <replaceable class="parameter">username</replaceable></option></term>
 170      <term><option>--username=<replaceable class="parameter">username</replaceable></option></term>
 171      <listitem><para>user name to connect as.</para></listitem>
 172     </varlistentry>
 173
 174    </variablelist>
 175   </para>
 176
 177   <para>
 178    To display specific tables, select which tables to show by
 179    using <option>-o</option>, <option>-f</option> and/or <option>-t</option>.
 180    <option>-o</option> takes an OID,
 181    <option>-f</option> takes a filenode,
 182    and <option>-t</option> takes a table name (actually, it's a <literal>LIKE</literal>
 183    pattern, so you can use things like <literal>foo%</literal>).
 184    You can use as many
 185    of these options as you like, and the listing will include all objects
 186    matched by any of the options.  But note that these options can only
 187    show objects in the database given by <option>-d</option>.
 188   </para>
 189
 190   <para>
 191    If you don't give any of <option>-o</option>, <option>-f</option> or <option>-t</option>,
 192    but do give <option>-d</option>, it will list all tables in the database
 193    named by <option>-d</option>.  In this mode, the <option>-S</option> and
 194    <option>-i</option> options control what gets listed.
 195   </para>
 196
 197   <para>
 198    If you don't give <option>-d</option> either, it will show a listing of database
 199    OIDs.  Alternatively you can give <option>-s</option> to get a tablespace
 200    listing.
 201   </para>
 202  </refsect1>
 203
 204  <refsect1>
 205   <title>Environment</title>
 206
 207   <variablelist>
 208    <varlistentry>
 209     <term><envar>PGHOST</envar></term>
 210     <term><envar>PGPORT</envar></term>
 211     <term><envar>PGUSER</envar></term>
 212
 213     <listitem>
 214      <para>
 215       Default connection parameters.
 216      </para>
 217     </listitem>
 218    </varlistentry>
 219   </variablelist>
 220
 221   <para>
 222    This utility, like most other <productname>PostgreSQL</productname>
 223    utilities, also uses the environment variables supported by
 224    <application>libpq</application> (see <xref linkend="libpq-envars"/>).
 225   </para>
 226
 227   <para>
 228    The environment variable <envar>PG_COLOR</envar> specifies whether to use
 229    color in diagnostic messages. Possible values are
 230    <literal>always</literal>, <literal>auto</literal> and
 231    <literal>never</literal>.
 232   </para>
 233  </refsect1>
 234
 235  <refsect1>
 236   <title>Notes</title>
 237
 238   <para>
 239    <application>oid2name</application> requires a running database server with
 240    non-corrupt system catalogs.  It is therefore of only limited use
 241    for recovering from catastrophic database corruption situations.
 242   </para>
 243  </refsect1>
 244
 245  <refsect1>
 246   <title>Examples</title>
 247
 248 <screen>
 249 $ # what's in this database server, anyway?
 250 $ oid2name
 251 All databases:
 252     Oid  Database Name  Tablespace
 253 ----------------------------------
 254   17228       alvherre  pg_default
 255   17255     regression  pg_default
 256   17227      template0  pg_default
 257       1      template1  pg_default
 258
 259 $ oid2name -s
 260 All tablespaces:
 261      Oid  Tablespace Name
 262 -------------------------
 263     1663       pg_default
 264     1664        pg_global
 265   155151         fastdisk
 266   155152          bigdisk
 267
 268 $ # OK, let's look into database alvherre
 269 $ cd $PGDATA/base/17228
 270
 271 $ # get top 10 db objects in the default tablespace, ordered by size
 272 $ ls -lS * | head -10
 273 -rw-------  1 alvherre alvherre 136536064 sep 14 09:51 155173
 274 -rw-------  1 alvherre alvherre  17965056 sep 14 09:51 1155291
 275 -rw-------  1 alvherre alvherre   1204224 sep 14 09:51 16717
 276 -rw-------  1 alvherre alvherre    581632 sep  6 17:51 1255
 277 -rw-------  1 alvherre alvherre    237568 sep 14 09:50 16674
 278 -rw-------  1 alvherre alvherre    212992 sep 14 09:51 1249
 279 -rw-------  1 alvherre alvherre    204800 sep 14 09:51 16684
 280 -rw-------  1 alvherre alvherre    196608 sep 14 09:50 16700
 281 -rw-------  1 alvherre alvherre    163840 sep 14 09:50 16699
 282 -rw-------  1 alvherre alvherre    122880 sep  6 17:51 16751
 283
 284 $ # What file is 155173?
 285 $ oid2name -d alvherre -f 155173
 286 From database "alvherre":
 287   Filenode  Table Name
 288 ----------------------
 289     155173    accounts
 290
 291 $ # you can ask for more than one object
 292 $ oid2name -d alvherre -f 155173 -f 1155291
 293 From database "alvherre":
 294   Filenode     Table Name
 295 -------------------------
 296     155173       accounts
 297    1155291  accounts_pkey
 298
 299 $ # you can mix the options, and get more details with -x
 300 $ oid2name -d alvherre -t accounts -f 1155291 -x
 301 From database "alvherre":
 302   Filenode     Table Name      Oid  Schema  Tablespace
 303 ------------------------------------------------------
 304     155173       accounts   155173  public  pg_default
 305    1155291  accounts_pkey  1155291  public  pg_default
 306
 307 $ # show disk space for every db object
 308 $ du [0-9]* |
 309 > while read SIZE FILENODE
 310 > do
 311 >   echo "$SIZE       `oid2name -q -d alvherre -i -f $FILENODE`"
 312 > done
 313 16            1155287  branches_pkey
 314 16            1155289  tellers_pkey
 315 17561            1155291  accounts_pkey
 316 ...
 317
 318 $ # same, but sort by size
 319 $ du [0-9]* | sort -rn | while read SIZE FN
 320 > do
 321 >   echo "$SIZE   `oid2name -q -d alvherre -f $FN`"
 322 > done
 323 133466             155173    accounts
 324 17561            1155291  accounts_pkey
 325 1177              16717  pg_proc_proname_args_nsp_index
 326 ...
 327
 328 $ # If you want to see what's in tablespaces, use the pg_tblspc directory
 329 $ cd $PGDATA/pg_tblspc
 330 $ oid2name -s
 331 All tablespaces:
 332      Oid  Tablespace Name
 333 -------------------------
 334     1663       pg_default
 335     1664        pg_global
 336   155151         fastdisk
 337   155152          bigdisk
 338
 339 $ # what databases have objects in tablespace "fastdisk"?
 340 $ ls -d 155151/*
 341 155151/17228/  155151/PG_VERSION
 342
 343 $ # Oh, what was database 17228 again?
 344 $ oid2name
 345 All databases:
 346     Oid  Database Name  Tablespace
 347 ----------------------------------
 348   17228       alvherre  pg_default
 349   17255     regression  pg_default
 350   17227      template0  pg_default
 351       1      template1  pg_default
 352
 353 $ # Let's see what objects does this database have in the tablespace.
 354 $ cd 155151/17228
 355 $ ls -l
 356 total 0
 357 -rw-------  1 postgres postgres 0 sep 13 23:20 155156
 358
 359 $ # OK, this is a pretty small table ... but which one is it?
 360 $ oid2name -d alvherre -f 155156
 361 From database "alvherre":
 362   Filenode  Table Name
 363 ----------------------
 364     155156         foo
 365 </screen>
 366  </refsect1>
 367
 368  <refsect1>
 369   <title>Author</title>
 370
 371   <para>
 372    B. Palmer <email>bpalmer@crimelabs.net</email>
 373   </para>
 374  </refsect1>
 375
 376 </refentry>