Usefull postgres queries

Usefull postgres queries.sql

-- Get all tables in the db sorted by size. 
-- Can be usefull in cases you want to do a pg_restore but want to know which tables you can exclude 
-- and get the most amount of value from in terms of time saved
SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
                                                                   
-- Get current postgres version
SELECT version();

-- Get amount of currently active connections and how much is left: https://dba.stackexchange.com/questions/161760/number-of-active-connections-and-remaining-connections
select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal 
from 
  (select count(*) used from pg_stat_activity) t1,
  (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
  (select setting::int max_conn from pg_settings where name=$$max_connections$$) t3