This is an internal documentation. There is a good chance you’re looking for something else. See Disclaimer.
Clean up Unsed DBs¶
Find DBs to with no users (=no one is connected):
SELECT datname FROM pg_database WHERE NOT exists(SELECT * FROM pg_stat_activity WHERE pg_stat_activity.datname = pg_database.datname) AND NOT datistemplate;
Check when DB was last used:
SELECT now() - max(last_login) FROM nice_principal;
Check when DB was last used (history DB):
SELECT now() - max(insertion_time) FROM nice_history;
List all DBs ordered by age (=last login):
n2sql-on-all-dbs --csv "SELECT (extract('epoch' from now() - max(last_login)) / 86400)::int from nice_principal" \ | sort -g -t , -k 2 -r
Age is in days.
List all DBs sorted by size:
SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges", pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) as "Size", t.spcname as "Tablespace", pg_catalog.shobj_description(d.oid, 'pg_database') as "Description" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid ORDER BY pg_catalog.pg_database_size(d.datname) DESC;
\l+but sorted by size.