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
      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.