Create PostgreSQL database report with HTML
I created a shell script to monitor and report PostgreSQL database status. If you need a HTML based PostgreSQL report, please use this script. Please locate the following two scripts in a same directory.Shell script to generate HTML based PostgreSQL report
Save the following shell script code as "pgsql_report.sh". Please change database host, port number, database name, database user based on your database setting.#!/bin/bash
#
# pgsql_report.sh
#
echo ${SQL} | psql \
-h 127.0.0.1 \
-p 5432 \
-d hoge \
-U postgres \
-f pgsql_report.sql
SQL script to generate HTML based PostgreSQL report
Save the following SQL code as "pgsql_report.sql".\o settings_report.html
\T 'cellspacing=0 cellpadding=0'
\qecho '<html><head><style>H2{background:#e6e6e6}</style>'
\qecho '<title>PostgreSQL Report</title></head><body>'
\qecho '<table><tr valign=''top''><td>'
\qecho '<h2>Report Date</h2>'
\pset format html
SELECT current_timestamp AS report_timestamp;
\qecho '<h2>PostgreSQL Basic Information</h2>'
\pset format html
SELECT version() AS PostgreSQL_Version;
\H
\qecho '<h2>PostgreSQL Database Settings</h2>'
\x on
\t on
\pset format html
SELECT category, string_agg(name || '=' || setting, E'\n' ORDER BY name) As settings
FROM pg_settings
WHERE 1 = 1
GROUP BY category
ORDER BY category;
\x off
\t off
\H
\qecho '<h2>Current Sessions</h2>'
\pset format html
SELECT procpid, usename, application_name, client_addr, query_start, waiting, current_query
FROM pg_stat_activity ORDER BY query_start;
\H
\qecho '<h2>Database Sizing Report</h2>'
\pset format html
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC -- nulls first
;
\H
\qecho '<h2>Relation Sizing Report (Top 10)</h2>'
\pset format html
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 10;
\qecho '</td></tr></table>'
\qecho '</body></html>'
\o
Run the shell script and see the PostgreSQL report
Once you located the shell script and SQL file in a same directory, run the following command.#!/bin/bash $ chmod 744 ./pgsql_report.sh $ ./pgsql_report.sh
As a result of that, you can get the following PostgreSQL report.
It's better to email the report through cron job, so you just need to add one more line to email the report.
