#!/bin/bash

echo "=== postgres info script v1 ==="
echo "(description: show info about postgres database, files, config and usage stats)"
echo "(needs to be run as root)"

echo "... postgres: installed version?"
su - postgres -c "psql -d postgres -c \"SELECT version();\""

echo "... postgres: where is the main config file?"
su - postgres -c "psql -d postgres -c \"SHOW config_file;\""

echo "... postgres: where is the main data directory? (where files are stored)"
su - postgres -c "psql -d postgres -c \"SHOW data_directory;\""

echo "... postgres: usage stats?"
su - postgres -c "
psql -d postgres -c \"
SELECT
    'Database Name' AS database,
    pg_database.datname AS database,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size,
    pg_stat_database.numbackends AS connections,
    pg_stat_database.xact_commit AS commits,
    pg_stat_database.xact_rollback AS rollbacks,
    pg_stat_database.blks_read AS blocks_read,
    pg_stat_database.blks_hit AS blocks_hit,
    pg_stat_database.tup_returned AS rows_returned,
    pg_stat_database.tup_fetched AS rows_fetched,
    pg_stat_database.tup_inserted AS rows_inserted,
    pg_stat_database.tup_updated AS rows_updated,
    pg_stat_database.tup_deleted AS rows_deleted
FROM
    pg_database
JOIN
    pg_stat_database
ON
    pg_database.datname = pg_stat_database.datname
WHERE
    pg_database.datistemplate = false
ORDER BY
    pg_database.datname;\"
"