1OID2NAME(1) PostgreSQL 14.3 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 70.
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 --filenode=filenode
33 show info for table with filenode filenode.
34 -i
36 --indexes
37 include indexes and sequences in the listing.
38 -o oid
40 --oid=oid
41 show info for table with OID oid.
42 -q
44 --quiet
45 omit headers (useful for scripting).
46 -s
48 --tablespaces
49 show tablespace OIDs.
50 -S
52 --system-objects
53 include system objects (those in information_schema, pg_toast and
54 pg_catalog schemas).
55 -t tablename_pattern
57 --table=tablename_pattern
58 show info for table(s) matching tablename_pattern.
59 -V
61 --version
62 Print the oid2name version and exit.
63 -x
65 --extended
66 display more information about each object shown: tablespace name,
67 schema name, and OID.
68 -?
70 --help
71 Show help about oid2name command line arguments, and exit.
72 oid2name also accepts the following command-line arguments for
74 connection parameters:
75 -d database
77 --dbname=database
78 database to connect to.
79 -h host
81 --host=host
82 database server's host.
83 -H host
85 database server's host. Use of this parameter is deprecated as of
86 PostgreSQL 12.
87 -p port
89 --port=port
90 database server's port.
91 -U username
93 --username=username
94 user name to connect as.
95 To display specific tables, select which tables to show by using -o, -f
97 and/or -t. -o takes an OID, -f takes a filenode, and -t takes a table
98 name (actually, it's a LIKE pattern, so you can use things like foo%).
99 You can use as many of these options as you like, and the listing will
100 include all objects matched by any of the options. But note that these
101 options can only show objects in the database given by -d.
102 If you don't give any of -o, -f or -t, but do give -d, it will list all
104 tables in the database named by -d. In this mode, the -S and -i options
105 control what gets listed.
106 If you don't give -d either, it will show a listing of database OIDs.
108 Alternatively you can give -s to get a tablespace listing.
109
111 PGHOST
112 PGPORT
113 PGUSER
114 Default connection parameters.
115 This utility, like most other PostgreSQL utilities, also uses the
117 environment variables supported by libpq (see Section 34.15).
118 The environment variable PG_COLOR specifies whether to use color in
120 diagnostic messages. Possible values are always, auto and never.
121
123 oid2name requires a running database server with non-corrupt system
124 catalogs. It is therefore of only limited use for recovering from
125 catastrophic database corruption situations.
126
128 $ # what's in this database server, anyway?
129 $ oid2name
130 All databases:
131 Oid Database Name Tablespace
132 ----------------------------------
133 17228 alvherre pg_default
134 17255 regression pg_default
135 17227 template0 pg_default
136 1 template1 pg_default
137 $ oid2name -s
139 All tablespaces:
140 Oid Tablespace Name
141 -------------------------
142 1663 pg_default
143 1664 pg_global
144 155151 fastdisk
145 155152 bigdisk
146 $ # OK, let's look into database alvherre
148 $ cd $PGDATA/base/17228
149 $ # get top 10 db objects in the default tablespace, ordered by size
151 $ ls -lS * | head -10
152 -rw------- 1 alvherre alvherre 136536064 sep 14 09:51 155173
153 -rw------- 1 alvherre alvherre 17965056 sep 14 09:51 1155291
154 -rw------- 1 alvherre alvherre 1204224 sep 14 09:51 16717
155 -rw------- 1 alvherre alvherre 581632 sep 6 17:51 1255
156 -rw------- 1 alvherre alvherre 237568 sep 14 09:50 16674
157 -rw------- 1 alvherre alvherre 212992 sep 14 09:51 1249
158 -rw------- 1 alvherre alvherre 204800 sep 14 09:51 16684
159 -rw------- 1 alvherre alvherre 196608 sep 14 09:50 16700
160 -rw------- 1 alvherre alvherre 163840 sep 14 09:50 16699
161 -rw------- 1 alvherre alvherre 122880 sep 6 17:51 16751
162 $ # I wonder what file 155173 is ...
164 $ oid2name -d alvherre -f 155173
165 From database "alvherre":
166 Filenode Table Name
167 ----------------------
168 155173 accounts
169 $ # you can ask for more than one object
171 $ oid2name -d alvherre -f 155173 -f 1155291
172 From database "alvherre":
173 Filenode Table Name
174 -------------------------
175 155173 accounts
176 1155291 accounts_pkey
177 $ # you can mix the options, and get more details with -x
179 $ oid2name -d alvherre -t accounts -f 1155291 -x
180 From database "alvherre":
181 Filenode Table Name Oid Schema Tablespace
182 ------------------------------------------------------
183 155173 accounts 155173 public pg_default
184 1155291 accounts_pkey 1155291 public pg_default
185 $ # show disk space for every db object
187 $ du [0-9]* |
188 > while read SIZE FILENODE
189 > do
190 > echo "$SIZE `oid2name -q -d alvherre -i -f $FILENODE`"
191 > done
192 16 1155287 branches_pkey
193 16 1155289 tellers_pkey
194 17561 1155291 accounts_pkey
195 ...
196 $ # same, but sort by size
198 $ du [0-9]* | sort -rn | while read SIZE FN
199 > do
200 > echo "$SIZE `oid2name -q -d alvherre -f $FN`"
201 > done
202 133466 155173 accounts
203 17561 1155291 accounts_pkey
204 1177 16717 pg_proc_proname_args_nsp_index
205 ...
206 $ # If you want to see what's in tablespaces, use the pg_tblspc directory
208 $ cd $PGDATA/pg_tblspc
209 $ oid2name -s
210 All tablespaces:
211 Oid Tablespace Name
212 -------------------------
213 1663 pg_default
214 1664 pg_global
215 155151 fastdisk
216 155152 bigdisk
217 $ # what databases have objects in tablespace "fastdisk"?
219 $ ls -d 155151/*
220 155151/17228/ 155151/PG_VERSION
221 $ # Oh, what was database 17228 again?
223 $ oid2name
224 All databases:
225 Oid Database Name Tablespace
226 ----------------------------------
227 17228 alvherre pg_default
228 17255 regression pg_default
229 17227 template0 pg_default
230 1 template1 pg_default
231 $ # Let's see what objects does this database have in the tablespace.
233 $ cd 155151/17228
234 $ ls -l
235 total 0
236 -rw------- 1 postgres postgres 0 sep 13 23:20 155156
237 $ # OK, this is a pretty small table ... but which one is it?
239 $ oid2name -d alvherre -f 155156
240 From database "alvherre":
241 Filenode Table Name
242 ----------------------
243 155156 foo
244
246 B. Palmer <bpalmer@crimelabs.net>
247PostgreSQL 14.3 2022 OID2NAME(1)