-
Notifications
You must be signed in to change notification settings - Fork 44
/
postgres-size-report
executable file
·59 lines (50 loc) · 1.61 KB
/
postgres-size-report
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
#!/bin/bash
TABLEQUERY=$(cat <<-END
SELECT table_name, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a order by total_bytes DESC LIMIT 30;
END
)
if [ -f /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf ]; then
PGSQL_DIR="/var/opt/rh/rh-postgresql12/lib/pgsql"
else
PGSQL_DIR="/var/lib/pgsql"
fi
# echo $TABLEQUERY
cd ~postgres
echo ""
echo "************* Candlepin Tablesizes *************"
echo ""
echo $TABLEQUERY | su - postgres -c "psql candlepin"
echo ""
echo "************** Foreman Tablesizes **************"
echo ""
echo $TABLEQUERY | su - postgres -c "psql foreman"
if PULPCORE_TABLESIZES=$(echo $TABLEQUERY | su - postgres -c "psql pulpcore" 2>/dev/null)
then
echo ""
echo "************** Pulpcore Tablesizes *************"
echo ""
echo "$PULPCORE_TABLESIZES"
fi
echo ""
echo "*************** FileSystem Usage ***************"
echo ""
echo "du -hs $PGSQL_DIR/*"
du -hs $PGSQL_DIR/*
echo
echo "du -hs $PGSQL_DIR/data/*"
du -hs $PGSQL_DIR/data/*