1OID2NAME(1) PostgreSQL 12.2 Documentation OID2NAME(1)
2
3
4
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 68.
16
17 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
24 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
31 -f filenode
32 --filenode=filenode
33 show info for table with filenode filenode.
34
35 -i
36 --indexes
37 include indexes and sequences in the listing.
38
39 -o oid
40 --oid=oid
41 show info for table with OID oid.
42
43 -q
44 --quiet
45 omit headers (useful for scripting).
46
47 -s
48 --tablespaces
49 show tablespace OIDs.
50
51 -S
52 --system-objects
53 include system objects (those in information_schema, pg_toast and
54 pg_catalog schemas).
55
56 -t tablename_pattern
57 --table=tablename_pattern
58 show info for table(s) matching tablename_pattern.
59
60 -V
61 --version
62 Print the oid2name version and exit.
63
64 -x
65 --extended
66 display more information about each object shown: tablespace name,
67 schema name, and OID.
68
69 -?
70 --help
71 Show help about oid2name command line arguments, and exit.
72
73 oid2name also accepts the following command-line arguments for
74 connection parameters:
75
76 -d database
77 --dbname=database
78 database to connect to.
79
80 -h host
81 --host=host
82 database server's host.
83
84 -H host
85 database server's host. Use of this parameter is deprecated as of
86 PostgreSQL 12.
87
88 -p port
89 --port=port
90 database server's port.
91
92 -U username
93 --username=username
94 user name to connect as.
95
96 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
103 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
107 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
116 This utility, like most other PostgreSQL utilities, also uses the
117 environment variables supported by libpq (see Section 33.14).
118
120 oid2name requires a running database server with non-corrupt system
121 catalogs. It is therefore of only limited use for recovering from
122 catastrophic database corruption situations.
123
125 $ # what's in this database server, anyway?
126 $ oid2name
127 All databases:
128 Oid Database Name Tablespace
129 ----------------------------------
130 17228 alvherre pg_default
131 17255 regression pg_default
132 17227 template0 pg_default
133 1 template1 pg_default
134
135 $ oid2name -s
136 All tablespaces:
137 Oid Tablespace Name
138 -------------------------
139 1663 pg_default
140 1664 pg_global
141 155151 fastdisk
142 155152 bigdisk
143
144 $ # OK, let's look into database alvherre
145 $ cd $PGDATA/base/17228
146
147 $ # get top 10 db objects in the default tablespace, ordered by size
148 $ ls -lS * | head -10
149 -rw------- 1 alvherre alvherre 136536064 sep 14 09:51 155173
150 -rw------- 1 alvherre alvherre 17965056 sep 14 09:51 1155291
151 -rw------- 1 alvherre alvherre 1204224 sep 14 09:51 16717
152 -rw------- 1 alvherre alvherre 581632 sep 6 17:51 1255
153 -rw------- 1 alvherre alvherre 237568 sep 14 09:50 16674
154 -rw------- 1 alvherre alvherre 212992 sep 14 09:51 1249
155 -rw------- 1 alvherre alvherre 204800 sep 14 09:51 16684
156 -rw------- 1 alvherre alvherre 196608 sep 14 09:50 16700
157 -rw------- 1 alvherre alvherre 163840 sep 14 09:50 16699
158 -rw------- 1 alvherre alvherre 122880 sep 6 17:51 16751
159
160 $ # I wonder what file 155173 is ...
161 $ oid2name -d alvherre -f 155173
162 From database "alvherre":
163 Filenode Table Name
164 ----------------------
165 155173 accounts
166
167 $ # you can ask for more than one object
168 $ oid2name -d alvherre -f 155173 -f 1155291
169 From database "alvherre":
170 Filenode Table Name
171 -------------------------
172 155173 accounts
173 1155291 accounts_pkey
174
175 $ # you can mix the options, and get more details with -x
176 $ oid2name -d alvherre -t accounts -f 1155291 -x
177 From database "alvherre":
178 Filenode Table Name Oid Schema Tablespace
179 ------------------------------------------------------
180 155173 accounts 155173 public pg_default
181 1155291 accounts_pkey 1155291 public pg_default
182
183 $ # show disk space for every db object
184 $ du [0-9]* |
185 > while read SIZE FILENODE
186 > do
187 > echo "$SIZE `oid2name -q -d alvherre -i -f $FILENODE`"
188 > done
189 16 1155287 branches_pkey
190 16 1155289 tellers_pkey
191 17561 1155291 accounts_pkey
192 ...
193
194 $ # same, but sort by size
195 $ du [0-9]* | sort -rn | while read SIZE FN
196 > do
197 > echo "$SIZE `oid2name -q -d alvherre -f $FN`"
198 > done
199 133466 155173 accounts
200 17561 1155291 accounts_pkey
201 1177 16717 pg_proc_proname_args_nsp_index
202 ...
203
204 $ # If you want to see what's in tablespaces, use the pg_tblspc directory
205 $ cd $PGDATA/pg_tblspc
206 $ oid2name -s
207 All tablespaces:
208 Oid Tablespace Name
209 -------------------------
210 1663 pg_default
211 1664 pg_global
212 155151 fastdisk
213 155152 bigdisk
214
215 $ # what databases have objects in tablespace "fastdisk"?
216 $ ls -d 155151/*
217 155151/17228/ 155151/PG_VERSION
218
219 $ # Oh, what was database 17228 again?
220 $ oid2name
221 All databases:
222 Oid Database Name Tablespace
223 ----------------------------------
224 17228 alvherre pg_default
225 17255 regression pg_default
226 17227 template0 pg_default
227 1 template1 pg_default
228
229 $ # Let's see what objects does this database have in the tablespace.
230 $ cd 155151/17228
231 $ ls -l
232 total 0
233 -rw------- 1 postgres postgres 0 sep 13 23:20 155156
234
235 $ # OK, this is a pretty small table ... but which one is it?
236 $ oid2name -d alvherre -f 155156
237 From database "alvherre":
238 Filenode Table Name
239 ----------------------
240 155156 foo
241
243 B. Palmer <bpalmer@crimelabs.net>
244
245
246
247PostgreSQL 12.2 2020 OID2NAME(1)