This is an internal documentation. There is a good chance you’re looking for something else. See Disclaimer.

Clean up Unsed DBs

Useful Queries

• 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;
  

  Same as \l+ but sorted by size.