graphbear

select
  relname
from
  pg_class
where
  reltoastrelid = ( select oid from pg_class where relname = 'toast_table_name' );

graphbear

# you can encrypt the whole cluster in one go

read -sp "Postgres passphrase: " PGENCRYPTIONKEY
export PGENCRYPTIONKEY=$PGENCRYPTIONKEY
initdb –data-encryption pgcrypto --data-checksums -D cryptotest

graphbear

SELECT
    waiting.locktype           AS waiting_locktype,
    waiting.relation::regclass AS waiting_table,
    waiting_stm.query          AS waiting_query,
    waiting.mode               AS waiting_mode,
    waiting.pid                AS waiting_pid,
    other.locktype             AS other_locktype,
    other.relation::regclass   AS other_table,
    other_stm.query            AS other_query,
    other.mode                 AS other_mode,

graphbear

SELECT
  c.relname,
  c.reltype,
  t.spcname
FROM
  pg_class c
  JOIN pg_tablespace t ON c.reltablespace = t.oid
ORDER BY
  t.spcname,
  c.relname;

graphbear

SELECT 
    schemaname||'.'||tablename 
FROM 
    pg_tables 
WHERE 
    has_table_privilege (
        'user or role name',
        schemaname||'.'||tablename, 
        'select'
     ) 

graphbear

;; add this to your emacs.d
;; wraps the lines in org-mode

(setq org-startup-truncated nil)

;; or in the case of spacemacs...
(org :variables
          org-startup-truncated nil)

graphbear

SELECT
    nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM
    pg_class C
    LEFT JOIN
    pg_namespace N ON (N.oid = C.relnamespace)
WHERE
    nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY

graphbear

SELECT
    relname AS name,
    pg_size_pretty(sum(relpages::bigint*current_setting('block_size')::bigint)::bigint) AS size
FROM
    pg_class
WHERE
    reltype=0
GROUP BY
    relname
ORDER BY

graphbear

-- http://stackoverflow.com/questions/7336413/query-grants-for-a-table-in-postgres

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='mytable'

graphbear

-- https://dba.stackexchange.com/questions/72641/checking-whether-two-tables-have-identical-content-in-postgresql
-- more sql for other cases and good discussion in reply to this question

(TABLE a EXCEPT TABLE b)
UNION ALL
(TABLE b EXCEPT TABLE a) ;