1OID2NAME(1) PostgreSQL 9.2.24 Documentation OID2NAME(1)
2
6 oid2name - resolve OIDs and file nodes in a PostgreSQL data directory
7
9 oid2name [option...]
10
12 oid2name is a utility program that helps administrators to examine the
13 file structure used by PostgreSQL. To make use of it, you need to be
14 familiar with the database file structure, which is described in
15 Chapter 56, Database Physical Storage, in the documentation.
16 Note
18 The name “oid2name” is historical, and is actually rather
19 misleading, since most of the time when you use it, you will really
20 be concerned with tables' filenode numbers (which are the file
21 names visible in the database directories). Be sure you understand
22 the difference between table OIDs and table filenodes!
23 oid2name connects to a target database and extracts OID, filenode,
25 and/or table name information. You can also have it show database OIDs
26 or tablespace OIDs.
27
29 oid2name accepts the following command-line arguments:
30 -f filenode
32 show info for table with filenode filenode
33 -i
35 include indexes and sequences in the listing
36 -o oid
38 show info for table with OID oid
39 -q
41 omit headers (useful for scripting)
42 -s
44 show tablespace OIDs
45 -S
47 include system objects (those in information_schema, pg_toast and
48 pg_catalog schemas)
49 -t tablename_pattern
51 show info for table(s) matching tablename_pattern
52 -V, --version
54 Print the oid2name version and exit.
55 -x
57 display more information about each object shown: tablespace name,
58 schema name, and OID
59 -?, --help
61 Show help about oid2name command line arguments, and exit.
62 oid2name also accepts the following command-line arguments for
64 connection parameters:
65 -d database
67 database to connect to
68 -H host
70 database server's host
71 -p port
73 database server's port
74 -U username
76 user name to connect as
77 -P password
79 password (deprecated — putting this on the command line is a
80 security hazard)
81 To display specific tables, select which tables to show by using -o, -f
83 and/or -t. -o takes an OID, -f takes a filenode, and -t takes a table
84 name (actually, it's a LIKE pattern, so you can use things like foo%).
85 You can use as many of these options as you like, and the listing will
86 include all objects matched by any of the options. But note that these
87 options can only show objects in the database given by -d.
88 If you don't give any of -o, -f or -t, but do give -d, it will list all
90 tables in the database named by -d. In this mode, the -S and -i options
91 control what gets listed.
92 If you don't give -d either, it will show a listing of database OIDs.
94 Alternatively you can give -s to get a tablespace listing.
95
97 oid2name requires a running database server with non-corrupt system
98 catalogs. It is therefore of only limited use for recovering from
99 catastrophic database corruption situations.
100
102 $ # what's in this database server, anyway?
103 $ oid2name
104 All databases:
105 Oid Database Name Tablespace
106 ----------------------------------
107 17228 alvherre pg_default
108 17255 regression pg_default
109 17227 template0 pg_default
110 1 template1 pg_default
111 $ oid2name -s
113 All tablespaces:
114 Oid Tablespace Name
115 -------------------------
116 1663 pg_default
117 1664 pg_global
118 155151 fastdisk
119 155152 bigdisk
120 $ # OK, let's look into database alvherre
122 $ cd $PGDATA/base/17228
123 $ # get top 10 db objects in the default tablespace, ordered by size
125 $ ls -lS * | head -10
126 -rw------- 1 alvherre alvherre 136536064 sep 14 09:51 155173
127 -rw------- 1 alvherre alvherre 17965056 sep 14 09:51 1155291
128 -rw------- 1 alvherre alvherre 1204224 sep 14 09:51 16717
129 -rw------- 1 alvherre alvherre 581632 sep 6 17:51 1255
130 -rw------- 1 alvherre alvherre 237568 sep 14 09:50 16674
131 -rw------- 1 alvherre alvherre 212992 sep 14 09:51 1249
132 -rw------- 1 alvherre alvherre 204800 sep 14 09:51 16684
133 -rw------- 1 alvherre alvherre 196608 sep 14 09:50 16700
134 -rw------- 1 alvherre alvherre 163840 sep 14 09:50 16699
135 -rw------- 1 alvherre alvherre 122880 sep 6 17:51 16751
136 $ # I wonder what file 155173 is ...
138 $ oid2name -d alvherre -f 155173
139 From database "alvherre":
140 Filenode Table Name
141 ----------------------
142 155173 accounts
143 $ # you can ask for more than one object
145 $ oid2name -d alvherre -f 155173 -f 1155291
146 From database "alvherre":
147 Filenode Table Name
148 -------------------------
149 155173 accounts
150 1155291 accounts_pkey
151 $ # you can mix the options, and get more details with -x
153 $ oid2name -d alvherre -t accounts -f 1155291 -x
154 From database "alvherre":
155 Filenode Table Name Oid Schema Tablespace
156 ------------------------------------------------------
157 155173 accounts 155173 public pg_default
158 1155291 accounts_pkey 1155291 public pg_default
159 $ # show disk space for every db object
161 $ du [0-9]* |
162 > while read SIZE FILENODE
163 > do
164 > echo "$SIZE `oid2name -q -d alvherre -i -f $FILENODE`"
165 > done
166 16 1155287 branches_pkey
167 16 1155289 tellers_pkey
168 17561 1155291 accounts_pkey
169 ...
170 $ # same, but sort by size
172 $ du [0-9]* | sort -rn | while read SIZE FN
173 > do
174 > echo "$SIZE `oid2name -q -d alvherre -f $FN`"
175 > done
176 133466 155173 accounts
177 17561 1155291 accounts_pkey
178 1177 16717 pg_proc_proname_args_nsp_index
179 ...
180 $ # If you want to see what's in tablespaces, use the pg_tblspc directory
182 $ cd $PGDATA/pg_tblspc
183 $ oid2name -s
184 All tablespaces:
185 Oid Tablespace Name
186 -------------------------
187 1663 pg_default
188 1664 pg_global
189 155151 fastdisk
190 155152 bigdisk
191 $ # what databases have objects in tablespace "fastdisk"?
193 $ ls -d 155151/*
194 155151/17228/ 155151/PG_VERSION
195 $ # Oh, what was database 17228 again?
197 $ oid2name
198 All databases:
199 Oid Database Name Tablespace
200 ----------------------------------
201 17228 alvherre pg_default
202 17255 regression pg_default
203 17227 template0 pg_default
204 1 template1 pg_default
205 $ # Let's see what objects does this database have in the tablespace.
207 $ cd 155151/17228
208 $ ls -l
209 total 0
210 -rw------- 1 postgres postgres 0 sep 13 23:20 155156
211 $ # OK, this is a pretty small table ... but which one is it?
213 $ oid2name -d alvherre -f 155156
214 From database "alvherre":
215 Filenode Table Name
216 ----------------------
217 155156 foo
218
220 B. Palmer <bpalmer@crimelabs.net>
221PostgreSQL 9.2.24 2017-11-06 OID2NAME(1)