-
Notifications
You must be signed in to change notification settings - Fork 4.5k
/
README.oid2name
132 lines (100 loc) · 3.09 KB
/
README.oid2name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
This utility allows administrators to view the file structure used by
PostgreSQL. Databases are placed in directories based on their OIDs in
pg_database, and the tables in that directory are named by original
OIDs, stored in pg_class.relfilenode. Oid2name connects to the database
and extracts the OID and table name information.
---------------------------------------------------------------------------
It can be used in four ways:
oid2name
This will connect to the template1 database and display all databases
in the system:
$ oid2name
All databases:
---------------------------------
18720 = test1
1 = template1
18719 = template0
18721 = test
18735 = postgres
18736 = cssi
oid2name -d test [-x]
This connects to the database test and shows all tables and their OIDs:
$ oid2name -d test
All tables from database "test":
---------------------------------
18766 = dns
18737 = ips
18722 = testdate
oid2name -d test -o 18737
oid2name -d test -t testdate
This will connect to the database test and display the table name for oid
18737 and the oid for table name testdate respectively:
$ oid2name -d test -o 18737
Tablename of oid 18737 from database "test":
---------------------------------
18737 = ips
$ oid2name -d test -t testdate
Oid of table testdate from database "test":
---------------------------------
18722 = testdate
Keep in mind tables over one gigabyte will be split into separate files
with numeric file extensions.
---------------------------------------------------------------------------
Sample session:
$ cd /u/pg/data/base
$ oid2name
All databases:
---------------------------------
16817 = test2
16578 = x
16756 = test
1 = template1
16569 = template0
16818 = test3
16811 = floattest
$ cd 16756
$ ls 1873*
18730 18731 18732 18735 18736 18737 18738 18739
$ oid2name -d test -o 18737
Tablename of oid 18737 from database "test":
---------------------------------
18737 = ips
$ oid2name -d test -t ips
Oid of table ips from database "test":
---------------------------------
18737 = ips
$ # show disk space for every db object
$ du * | while read SIZE OID
> do
> echo "$SIZE `oid2name -q -d test -o $OID`"
> done
24 18737 = ips
36 18722 = cities
...
$ # same as above, but sort by largest first
$ du * | while read SIZE OID
> do
> echo "$SIZE `oid2name -q -d test -o $OID`"
> done |
> sort -rn
2048 19324 = bigtable
1950 23903 = customers
...
$ # show disk usage per database
$ cd /u/pg/data/base
$ du -s * |
> while read SIZE OID
> do
> echo "$SIZE `aspg oid2name -q | grep ^$OID' '`"
> done |
> sort -rn
2256 18721 = test
2135 18735 = postgres
..
This can be done in psql with:
test=> SELECT relpages, relfilenode, relname FROM pg_class ORDER BY relpages DESC;
Each page is typically 8k. Relpages is updated by VACUUM.
---------------------------------------------------------------------------
Mail me with any problems or additions you would like to see. Clearing
house for the code will be at: http://www.crimelabs.net
b. palmer, bpalmer@crimelabs.net