Postgres Queries - NDDCoder

Fork

--show all indexes
SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public' and tablename = 'activities'

--show all constraints
SELECT conrelid::regclass AS table_name, 
       conname AS foreign_key, 
       pg_get_constraintdef(oid) 
FROM   pg_constraint 
WHERE  contype = 'f' 
AND    connamespace = 'public'::regnamespace   
ORDER  BY conrelid::regclass::text, contype DESC;

--check running query
SELECT pid, age(clock_timestamp(), query_start), usename, wait_event, query, state
FROM pg_stat_activity
WHERE state = 'active' and query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

--fix sequence out of sync
SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
  AND S.oid = D.objid
  AND D.refobjid = T.oid
  AND D.refobjid = C.attrelid
  AND D.refobjsubid = C.attnum
  AND T.relname = PGT.tablename
ORDER BY S.relname;

--check autovaccum
select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

--create new user and db
CREATE USER newuser WITH PASSWORD '<password>';
CREATE DATABASE newdb;
GRANT ALL PRIVILEGES ON DATABASE newdb to newuser;

--update existed user
ALTER USER rolename WITH PASSWORD '<password>';

--alter default permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES on TABLES to role;

--permission
GRANT role to user;
REVOKE user from role;
ALTER DATABASE dbname owner to role;
GRANT ALL PRIVILEGES ON DATABASE dbname to role;
GRANT ALL PRIVILEGES ON SCHEMA public to role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO role;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO role;

--create read-only
REVOKE ALL ON DATABASE dbname FROM user;
GRANT CONNECT ON DATABASE dbname TO user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user;