$ cat bin/report-database-size.sh
#!/bin/bash

set -euo pipefail
trap 'exit 1' INT

path=/var/log/postgres-size/$(date +%Y)
mkdir -p $path

file=postgres-size-$(date +%FT%T).log

PAGER= psql -X -c '\l+' -c '\echo Size of tables in "example" database:' -f ~/sql-cron/calculate-table-sizes.sql example > $path/$file


# from https://stackoverflow.com/questions/2596624/how-do-you-find-the-disk-size-of-a-postgres-postgresql-table-and-its-indexes

$ cat sql-cron/calculate-table-sizes.sql
SELECT *,
    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,
                relkind,
                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 IN ('r', 'm')
    ) b
) a
ORDER BY table_schema, table_name;