-
Notifications
You must be signed in to change notification settings - Fork 503
Expand file tree
/
Copy pathqueryVCDBvPostgres.sh
More file actions
executable file
·29 lines (28 loc) · 1.17 KB
/
queryVCDBvPostgres.sh
File metadata and controls
executable file
·29 lines (28 loc) · 1.17 KB
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
#!/bin/bash
# # Author: William Lam
# Site: www.williamlam.com
# Description: Script to query Config + SEAT Data usage for vPostgres based VCDB
# Reference: http://www.williamlam.com/2016/10/how-to-check-the-size-of-your-config-seat-data-in-the-vcdb-in-vpostgres.html
VCDB_SQL_QUERY="
SELECT tabletype,
sum(reltuples) as rowcount,
ceil(sum(pg_total_relation_size(oid)) / (1024*1024)) as usedspaceMB
FROM (
SELECT CASE
WHEN c.relname LIKE 'vpx_alarm%' THEN 'Alarm'
WHEN c.relname LIKE 'vpx_event%' THEN 'ET'
WHEN c.relname LIKE 'vpx_task%' THEN 'ET'
WHEN c.relname LIKE 'vpx_hist_stat%' THEN 'Stats'
WHEN c.relname LIKE 'vpx_topn%' THEN 'Stats'
ELSE 'Core'
END AS tabletype,
c.reltuples, c.oid
FROM pg_class C
LEFT JOIN pg_namespace N
ON N.oid = C.relnamespace
WHERE nspname IN ('vc', 'vpx') and relkind in ('r', 't')) t
GROUP BY tabletype;
"
# read-only connection
export PGOPTIONS="-c default_transaction_read_only=on"
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "${VCDB_SQL_QUERY}"