--Get number of connections per database:
SELECT numbackends, datname FROM pg_stat_database;

--Get total number of connections in database:
SELECT sum(numbackends) FROM pg_stat_database;

-- Show long running queries (>9.2)
SELECT now() - query_start as "runtime", pid, usename, datname, state, query
FROM  pg_stat_activity
WHERE now() - query_start > '2 minutes'::interval
ORDER BY runtime DESC;

-- Show running queries (pre 9.2)
SELECT procpid, age(query_start, clock_timestamp()), usename, current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

-- Show running queries (9.2)
SELECT pid, age(query_start, clock_timestamp()), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

-- Kill running query
SELECT pg_cancel_backend(procpid);

-- Kill idle query
SELECT pg_terminate_backend(procpid);

-- Vacuum command
VACUUM (VERBOSE, ANALYZE);

-- Get the disk usase of all databases
DO
$$
DECLARE
r   RECORD;
db_size TEXT;
BEGIN
FOR r in
SELECT datname FROM pg_database
WHERE datistemplate = false
LOOP
db_size:= (SELECT pg_size_pretty(pg_database_size(r.datname)));

RAISE NOTICE 'Database:% , Size:%', r.datname , db_size;

END LOOP;
END;
$$;


-- Get the disk usase of tables in a database
SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;