select count(id) from TABLE where updated_at > 'now'::timestamp - '1 month'::interval;

kubectl run -i --rm --tty postgresql-schema-dumper --image=postgres:10-stretch -- pg_dump -s "DBURL"

WITH indexed_tables AS (
  select
    ns.nspname,
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names,
    ix.indrelid,
    ix.indkey
    FROM pg_class i
         JOIN pg_index ix ON i.OID = ix.indrelid
         JOIN pg_class t ON ix.indrelid = t.oid
         JOIN pg_namespace ns ON ns.oid = t.relnamespace
         JOIN pg_attribute a ON a.attrelid = t.oid
   where
a.attnum = ANY(ix.indkey)
     and t.relkind = 'r'
     and nspname in ('your-namespace-1','your-ns-2')
   group by
ns.nspname,
t.relname,
i.relname,
ix.indrelid,
ix.indkey
   order by
ns.nspname,
t.relname,
i.relname,
ix.indrelid,
ix.indkey
) SELECT conrelid::regclass
         ,conname
         ,reltuples::bigint
    FROM pg_constraint pgc
         JOIN pg_class ON (conrelid = pg_class.oid)
   WHERE contype = 'f'
     AND NOT EXISTS(
       SELECT 1 FROM indexed_tables WHERE indrelid = conrelid
                                      AND public.sortarray(conkey) = public.sortarray(indkey)
                                       OR (array_length(indkey, 1) > 1 AND indkey::smallint[] @> conkey)
     )
   ORDER BY reltuples DESC;

BEGIN;
CREATE FUNCTION tc_column_size(table_name text, column_name text)
  RETURNS BIGINT AS
  $$
  declare response BIGINT;
BEGIN
  EXECUTE 'select sum(pg_column_size(t."' || column_name || '")) from ' || table_name || ' t ' into response;
  return response;
END;
$$
LANGUAGE plpgsql;

SELECT
  z.table_name,
  z.column_name,
  pg_size_pretty(z.size)
  FROM (
    SELECT
      table_name,
      column_name,
      tc_column_size(table_name, column_name) size
      FROM
        information_schema.columns
     WHERE
table_schema = 'public') AS z
 WHERE
size IS NOT NULL
   and z.table_name =  'TABLE_NAME' -- <--- uncomment to filter a table

 ORDER BY
z.size DESC;

ROLLBACK; -- <--- You may not want to keep that function

SELECT
  relname AS "relation",
  pg_size_pretty (
    pg_total_relation_size (C .oid)
  ) AS "total_size"
  FROM
    pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
 WHERE
nspname NOT IN (
  'pg_catalog',
  'information_schema'
)
   AND C .relkind <> 'i'
   AND nspname !~ '^pg_toast'
 ORDER BY
pg_total_relation_size (C .oid) DESC
 LIMIT 5;

SELECT n.nspname, c.relname
  FROM   pg_catalog.pg_class c, pg_catalog.pg_namespace n,
         pg_catalog.pg_index i
 WHERE  (i.indisvalid = false OR i.indisready = false) AND
        i.indexrelid = c.oid AND c.relnamespace = n.oid AND
        n.nspname != 'pg_catalog' AND
        n.nspname != 'information_schema' AND
        n.nspname != 'pg_toast';

SELECT
  pid
  ,datname
  ,usename
  ,application_name
  ,client_hostname
  ,client_port
  ,backend_start
  ,query_start
  ,query
  FROM pg_stat_activity
 WHERE state <> 'idle'
   AND pid<>pg_backend_pid();

SELECT name, setting FROM pg_settings WHERE name LIKE '%autovacuum%';
SHOW autovacuum;

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

SELECT pg_cancel_backend(pid)
  FROM pg_stat_activity
 WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

grant select on clusters.* to 'username'@'%' identified by '';
flush privileges;

base64 </dev/urandom | tr -dc 'a-zA-Z0-9' | head -c32

create role readonly login password '';
grant connect on database DBNAME to readonly;
grant usage on schema public to readonly;
grant select on all tables in schema public to readonly;

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

SELECT pg_cancel_backend(procpid);

SELECT pg_terminate_backend(procpid);

VACUUM (VERBOSE, ANALYZE);

select * from pg_stat_activity where current_query not like '<%';

select * from pg_user;

select datname, pg_size_pretty(pg_database_size(datname))
  from pg_database
 order by pg_database_size(datname) desc;

SELECT sum(heap_blks_read) as heap_read,
       sum(heap_blks_hit)  as heap_hit,
       (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
  FROM pg_statio_user_tables;

SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,
       n_live_tup rows_in_table
  FROM pg_stat_user_tables
 ORDER BY n_live_tup DESC;

SELECT sum(idx_blks_read) as idx_read,
       sum(idx_blks_hit)  as idx_hit,
       (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
  FROM pg_statio_user_indexes;