================================================================================================

================================================================================================

. /usr/local/greenplum/greenplum-db/greenplum_path.sh

( echo -n "<pre>" && \
psql -e -X -h hostname -d dbname -f <(cat <<EOF

\\timing on

DROP EXTERNAL WEB TABLE IF EXISTS dbsysreports.disk_usage;

CREATE EXTERNAL WEB TABLE dbsysreports.disk_usage (
size_bytes int8,
path text
)
EXECUTE
E'find /data/ -type d -iname base | xargs -I{} du --block-size 1 --all --separate-dirs {} 2>/dev/null || true' ON HOST FORMAT 'TEXT' (DELIMITER E'\\t');

DROP TABLE IF EXISTS dbsysreports.disk_usage_aot;

CREATE TABLE dbsysreports.disk_usage_aot
WITH (ORIENTATION=COLUMN, APPENDONLY=TRUE, COMPRESSTYPE=QUICKLZ, COMPRESSLEVEL=1)
AS SELECT * FROM dbsysreports.disk_usage
DISTRIBUTED RANDOMLY;

-- db size

INSERT INTO dbsysreports.cluster_size (ctime, bytes)
SELECT
now(),
SUM(size_bytes) AS raw_size
FROM dbsysreports.disk_usage_aot
WHERE path ~ E'^/data/vol[0-9]+/gpdb_p[0-9]/.*/base/[0-9]+/[0-9]+\\\\.?[0-9]*?#39;

-- schema report

INSERT INTO dbsysreports.schema_sizes (ctime, schemaname, bytes)
SELECT
now(),
REGEXP_REPLACE(pn.nspname, '^pg_temp_[0-9]+#39;, 'pg_temp') AS schema,
SUM(size_bytes)::BIGINT as bytes
FROM
(
SELECT
SUM(size_bytes) AS size_bytes, /* to merge 52345.1 style relfilenodes */
REGEXP_REPLACE(path,
E'^/data/vol[0-9]+/gpdb_p([0-9])/.*/base/[0-9]+/[0-9]+\\\\.?[0-9]*?#39;,
E'\\\\1') AS content,
REGEXP_REPLACE(path,
E'^/data/vol[0-9]+/gpdb_p[0-9]/.*/base/([0-9]+)/[0-9]+\\\\.?[0-9]*?#39;,
E'\\\\1') AS database_oid,
REGEXP_REPLACE(path,
E'^/data/vol[0-9]+/gpdb_p[0-9]/.*/base/[0-9]+/([0-9]+)\\\\.?[0-9]*?#39;,
E'\\\\1') AS relfilenode
FROM dbsysreports.disk_usage_aot
WHERE path ~ E'^/data/vol[0-9]+/gpdb_p[0-9]/.*/base/[0-9]+/[0-9]+\\\\.?[0-9]*?#39;
GROUP BY 2, 3, 4
) AS rfd
LEFT JOIN pg_class pc ON pc.relfilenode = rfd.relfilenode
LEFT JOIN pg_database pd ON pd.oid = rfd.database_oid
LEFT JOIN pg_namespace pn ON pn.oid = pc.relnamespace
WHERE pd.datname = 'dbname'
GROUP BY 1,2;

Disk Cleanup activity - Finding space used by backup, database and log files.

========================================================

4. Find out total space used by un-cleaned temporary spool files. You can check using:

gpssh -f ~/hostfile "du -h -s /data[12]/primary/gpseg*/base/*/pgsql_tmp"

$gpssh -f ~/hostfile "du -h -s /data[12]/primary/gpseg*/pg_log*"

[gpadmin@ms00597 ~]$ gpssh -f ~/hostfile "du -h -s /data[12]/primary/gpseg*/db_dumps*"