diff options
Diffstat (limited to 'doc/src/sgml/html/oid2name.html')
| -rw-r--r-- | doc/src/sgml/html/oid2name.html | 192 |
1 files changed, 192 insertions, 0 deletions
diff --git a/doc/src/sgml/html/oid2name.html b/doc/src/sgml/html/oid2name.html new file mode 100644 index 0000000..0a38994 --- /dev/null +++ b/doc/src/sgml/html/oid2name.html @@ -0,0 +1,192 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>oid2name</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="contrib-prog-client.html" title="G.1. Client Applications" /><link rel="next" href="vacuumlo.html" title="vacuumlo" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">oid2name</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="contrib-prog-client.html" title="G.1. Client Applications">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib-prog-client.html" title="G.1. Client Applications">Up</a></td><th width="60%" align="center">G.1. Client Applications</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="vacuumlo.html" title="vacuumlo">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="OID2NAME"><div class="titlepage"></div><a id="id-1.11.8.4.3.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">oid2name</span></h2><p>oid2name — resolve OIDs and file nodes in a <span class="productname">PostgreSQL</span> data directory</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><div class="cmdsynopsis"><p id="id-1.11.8.4.3.4.1"><code class="command">oid2name</code> [<em class="replaceable"><code>option</code></em>...]</p></div></div><div class="refsect1" id="id-1.11.8.4.3.5"><h2>Description</h2><p> + <span class="application">oid2name</span> is a utility program that helps administrators to + examine the file structure used by PostgreSQL. To make use of it, you need + to be familiar with the database file structure, which is described in + <a class="xref" href="storage.html" title="Chapter 69. Database Physical Storage">Chapter 69</a>. + </p><div class="note"><h3 class="title">Note</h3><p> + The name <span class="quote">“<span class="quote">oid2name</span>”</span> is historical, and is actually rather + misleading, since most of the time when you use it, you will really + be concerned with tables' filenode numbers (which are the file names + visible in the database directories). Be sure you understand the + difference between table OIDs and table filenodes! + </p></div><p> + <span class="application">oid2name</span> connects to a target database and + extracts OID, filenode, and/or table name information. You can also have + it show database OIDs or tablespace OIDs. + </p></div><div class="refsect1" id="id-1.11.8.4.3.6"><h2>Options</h2><p> + <span class="application">oid2name</span> accepts the following command-line arguments: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">-f <em class="replaceable"><code>filenode</code></em></code><br /></span><span class="term"><code class="option">--filenode=<em class="replaceable"><code>filenode</code></em></code></span></dt><dd><p>show info for table with filenode <em class="replaceable"><code>filenode</code></em>.</p></dd><dt><span class="term"><code class="option">-i</code><br /></span><span class="term"><code class="option">--indexes</code></span></dt><dd><p>include indexes and sequences in the listing.</p></dd><dt><span class="term"><code class="option">-o <em class="replaceable"><code>oid</code></em></code><br /></span><span class="term"><code class="option">--oid=<em class="replaceable"><code>oid</code></em></code></span></dt><dd><p>show info for table with OID <em class="replaceable"><code>oid</code></em>.</p></dd><dt><span class="term"><code class="option">-q</code><br /></span><span class="term"><code class="option">--quiet</code></span></dt><dd><p>omit headers (useful for scripting).</p></dd><dt><span class="term"><code class="option">-s</code><br /></span><span class="term"><code class="option">--tablespaces</code></span></dt><dd><p>show tablespace OIDs.</p></dd><dt><span class="term"><code class="option">-S</code><br /></span><span class="term"><code class="option">--system-objects</code></span></dt><dd><p>include system objects (those in + <code class="option">information_schema</code>, <code class="option">pg_toast</code> + and <code class="option">pg_catalog</code> schemas). + </p></dd><dt><span class="term"><code class="option">-t <em class="replaceable"><code>tablename_pattern</code></em></code><br /></span><span class="term"><code class="option">--table=<em class="replaceable"><code>tablename_pattern</code></em></code></span></dt><dd><p>show info for table(s) matching <em class="replaceable"><code>tablename_pattern</code></em>.</p></dd><dt><span class="term"><code class="option">-V</code><br /></span><span class="term"><code class="option">--version</code></span></dt><dd><p> + Print the <span class="application">oid2name</span> version and exit. + </p></dd><dt><span class="term"><code class="option">-x</code><br /></span><span class="term"><code class="option">--extended</code></span></dt><dd><p>display more information about each object shown: tablespace name, + schema name, and OID. + </p></dd><dt><span class="term"><code class="option">-?</code><br /></span><span class="term"><code class="option">--help</code></span></dt><dd><p> + Show help about <span class="application">oid2name</span> command line + arguments, and exit. + </p></dd></dl></div><p> + </p><p> + <span class="application">oid2name</span> also accepts the following command-line + arguments for connection parameters: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">-d <em class="replaceable"><code>database</code></em></code><br /></span><span class="term"><code class="option">--dbname=<em class="replaceable"><code>database</code></em></code></span></dt><dd><p>database to connect to.</p></dd><dt><span class="term"><code class="option">-h <em class="replaceable"><code>host</code></em></code><br /></span><span class="term"><code class="option">--host=<em class="replaceable"><code>host</code></em></code></span></dt><dd><p>database server's host.</p></dd><dt><span class="term"><code class="option">-H <em class="replaceable"><code>host</code></em></code></span></dt><dd><p>database server's host. Use of this parameter is + <span class="emphasis"><em>deprecated</em></span> as of + <span class="productname">PostgreSQL</span> 12.</p></dd><dt><span class="term"><code class="option">-p <em class="replaceable"><code>port</code></em></code><br /></span><span class="term"><code class="option">--port=<em class="replaceable"><code>port</code></em></code></span></dt><dd><p>database server's port.</p></dd><dt><span class="term"><code class="option">-U <em class="replaceable"><code>username</code></em></code><br /></span><span class="term"><code class="option">--username=<em class="replaceable"><code>username</code></em></code></span></dt><dd><p>user name to connect as.</p></dd></dl></div><p> + </p><p> + To display specific tables, select which tables to show by + using <code class="option">-o</code>, <code class="option">-f</code> and/or <code class="option">-t</code>. + <code class="option">-o</code> takes an OID, + <code class="option">-f</code> takes a filenode, + and <code class="option">-t</code> takes a table name (actually, it's a <code class="literal">LIKE</code> + pattern, so you can use things like <code class="literal">foo%</code>). + You can use as many + of these options as you like, and the listing will include all objects + matched by any of the options. But note that these options can only + show objects in the database given by <code class="option">-d</code>. + </p><p> + If you don't give any of <code class="option">-o</code>, <code class="option">-f</code> or <code class="option">-t</code>, + but do give <code class="option">-d</code>, it will list all tables in the database + named by <code class="option">-d</code>. In this mode, the <code class="option">-S</code> and + <code class="option">-i</code> options control what gets listed. + </p><p> + If you don't give <code class="option">-d</code> either, it will show a listing of database + OIDs. Alternatively you can give <code class="option">-s</code> to get a tablespace + listing. + </p></div><div class="refsect1" id="id-1.11.8.4.3.7"><h2>Environment</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="envar">PGHOST</code><br /></span><span class="term"><code class="envar">PGPORT</code><br /></span><span class="term"><code class="envar">PGUSER</code></span></dt><dd><p> + Default connection parameters. + </p></dd></dl></div><p> + This utility, like most other <span class="productname">PostgreSQL</span> + utilities, also uses the environment variables supported by + <span class="application">libpq</span> (see <a class="xref" href="libpq-envars.html" title="33.14. Environment Variables">Section 33.14</a>). + </p><p> + The environment variable <code class="envar">PG_COLOR</code> specifies whether to use + color in diagnostic messages. Possible values are + <code class="literal">always</code>, <code class="literal">auto</code> and + <code class="literal">never</code>. + </p></div><div class="refsect1" id="id-1.11.8.4.3.8"><h2>Notes</h2><p> + <span class="application">oid2name</span> requires a running database server with + non-corrupt system catalogs. It is therefore of only limited use + for recovering from catastrophic database corruption situations. + </p></div><div class="refsect1" id="id-1.11.8.4.3.9"><h2>Examples</h2><pre class="screen"> +$ # what's in this database server, anyway? +$ 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 + +$ # OK, let's look into database alvherre +$ cd $PGDATA/base/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 + +$ # I wonder what file 155173 is ... +$ 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 mix the options, and get more details with -x +$ 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 +</pre></div><div class="refsect1" id="id-1.11.8.4.3.10"><h2>Author</h2><p> + B. Palmer <code class="email"><<a class="email" href="mailto:bpalmer@crimelabs.net">bpalmer@crimelabs.net</a>></code> + </p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="contrib-prog-client.html" title="G.1. Client Applications">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib-prog-client.html" title="G.1. Client Applications">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="vacuumlo.html" title="vacuumlo">Next</a></td></tr><tr><td width="40%" align="left" valign="top">G.1. Client Applications </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> <span xmlns="http://www.w3.org/1999/xhtml" class="application">vacuumlo</span></td></tr></table></div></body></html>
\ No newline at end of file |