1OID2NAME(1) PostgreSQL 11.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 68.
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
54 --version
55 Print the oid2name version and exit.
56 -x
58 display more information about each object shown: tablespace name,
59 schema name, and OID
60 -?
62 --help
63 Show help about oid2name command line arguments, and exit.
64 oid2name also accepts the following command-line arguments for
66 connection parameters:
67 -d database
69 database to connect to
70 -H host
72 database server's host
73 -p port
75 database server's port
76 -U username
78 user name to connect as
79 -P password
81 password (deprecated — putting this on the command line is a
82 security hazard)
83 To display specific tables, select which tables to show by using -o, -f
85 and/or -t. -o takes an OID, -f takes a filenode, and -t takes a table
86 name (actually, it's a LIKE pattern, so you can use things like foo%).
87 You can use as many of these options as you like, and the listing will
88 include all objects matched by any of the options. But note that these
89 options can only show objects in the database given by -d.
90 If you don't give any of -o, -f or -t, but do give -d, it will list all
92 tables in the database named by -d. In this mode, the -S and -i options
93 control what gets listed.
94 If you don't give -d either, it will show a listing of database OIDs.
96 Alternatively you can give -s to get a tablespace listing.
97
99 oid2name requires a running database server with non-corrupt system
100 catalogs. It is therefore of only limited use for recovering from
101 catastrophic database corruption situations.
102
104 $ # what's in this database server, anyway?
105 $ oid2name
106 All databases:
107 Oid Database Name Tablespace
108 ----------------------------------
109 17228 alvherre pg_default
110 17255 regression pg_default
111 17227 template0 pg_default
112 1 template1 pg_default
113 $ oid2name -s
115 All tablespaces:
116 Oid Tablespace Name
117 -------------------------
118 1663 pg_default
119 1664 pg_global
120 155151 fastdisk
121 155152 bigdisk
122 $ # OK, let's look into database alvherre
124 $ cd $PGDATA/base/17228
125 $ # get top 10 db objects in the default tablespace, ordered by size
127 $ ls -lS * | head -10
128 -rw------- 1 alvherre alvherre 136536064 sep 14 09:51 155173
129 -rw------- 1 alvherre alvherre 17965056 sep 14 09:51 1155291
130 -rw------- 1 alvherre alvherre 1204224 sep 14 09:51 16717
131 -rw------- 1 alvherre alvherre 581632 sep 6 17:51 1255
132 -rw------- 1 alvherre alvherre 237568 sep 14 09:50 16674
133 -rw------- 1 alvherre alvherre 212992 sep 14 09:51 1249
134 -rw------- 1 alvherre alvherre 204800 sep 14 09:51 16684
135 -rw------- 1 alvherre alvherre 196608 sep 14 09:50 16700
136 -rw------- 1 alvherre alvherre 163840 sep 14 09:50 16699
137 -rw------- 1 alvherre alvherre 122880 sep 6 17:51 16751
138 $ # I wonder what file 155173 is ...
140 $ oid2name -d alvherre -f 155173
141 From database "alvherre":
142 Filenode Table Name
143 ----------------------
144 155173 accounts
145 $ # you can ask for more than one object
147 $ oid2name -d alvherre -f 155173 -f 1155291
148 From database "alvherre":
149 Filenode Table Name
150 -------------------------
151 155173 accounts
152 1155291 accounts_pkey
153 $ # you can mix the options, and get more details with -x
155 $ oid2name -d alvherre -t accounts -f 1155291 -x
156 From database "alvherre":
157 Filenode Table Name Oid Schema Tablespace
158 ------------------------------------------------------
159 155173 accounts 155173 public pg_default
160 1155291 accounts_pkey 1155291 public pg_default
161 $ # show disk space for every db object
163 $ du [0-9]* |
164 > while read SIZE FILENODE
165 > do
166 > echo "$SIZE `oid2name -q -d alvherre -i -f $FILENODE`"
167 > done
168 16 1155287 branches_pkey
169 16 1155289 tellers_pkey
170 17561 1155291 accounts_pkey
171 ...
172 $ # same, but sort by size
174 $ du [0-9]* | sort -rn | while read SIZE FN
175 > do
176 > echo "$SIZE `oid2name -q -d alvherre -f $FN`"
177 > done
178 133466 155173 accounts
179 17561 1155291 accounts_pkey
180 1177 16717 pg_proc_proname_args_nsp_index
181 ...
182 $ # If you want to see what's in tablespaces, use the pg_tblspc directory
184 $ cd $PGDATA/pg_tblspc
185 $ oid2name -s
186 All tablespaces:
187 Oid Tablespace Name
188 -------------------------
189 1663 pg_default
190 1664 pg_global
191 155151 fastdisk
192 155152 bigdisk
193 $ # what databases have objects in tablespace "fastdisk"?
195 $ ls -d 155151/*
196 155151/17228/ 155151/PG_VERSION
197 $ # Oh, what was database 17228 again?
199 $ oid2name
200 All databases:
201 Oid Database Name Tablespace
202 ----------------------------------
203 17228 alvherre pg_default
204 17255 regression pg_default
205 17227 template0 pg_default
206 1 template1 pg_default
207 $ # Let's see what objects does this database have in the tablespace.
209 $ cd 155151/17228
210 $ ls -l
211 total 0
212 -rw------- 1 postgres postgres 0 sep 13 23:20 155156
213 $ # OK, this is a pretty small table ... but which one is it?
215 $ oid2name -d alvherre -f 155156
216 From database "alvherre":
217 Filenode Table Name
218 ----------------------
219 155156 foo
220
222 B. Palmer <bpalmer@crimelabs.net>
223PostgreSQL 11.3 2019 OID2NAME(1)