Postgres useful command & queries.

pg_queries.md

Dump all schema & user's credentials/privileges.

pg_dumpall -U postgres -s > schema_all.sql

---

Grant Default PRIVILEGES to tables. (also auto apply for new tables.)

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public \
  GRANT SELECT,INSERT,REFERENCES,TRIGGER,UPDATE ON TABLES TO users001;

---

Disable pager in shell

\pset pager 0

---

List all tables and sorted by Size.

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size
FROM (
  SELECT
    pg_catalog.pg_namespace.nspname AS schema_name,
    relname,
    pg_relation_size(pg_catalog.pg_class.oid) AS table_size
  FROM pg_catalog.pg_class
    JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

---

List all schema=public tables detail sizes ()

SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
FROM (
  SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
          , c.reltuples AS row_estimate
          , pg_total_relation_size(c.oid) AS total_bytes
          , pg_indexes_size(c.oid) AS index_bytes
          , pg_total_relation_size(reltoastrelid) AS toast_bytes
      FROM pg_class c
      LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE relkind = 'r'
  ) a
) a where table_schema = 'public' ORDER BY total_bytes DESC;

    oid    | table_schema | table_name |  row_estimate  |  total_bytes  | index_bytes  |  toast_bytes  | table_bytes  |  total  |  index  |  toast  |  table
-----------+--------------+-----------------------------+----------------+---------------+--------------+---------------+--------------+---------+---------+---------+---------
  79665848 | public       | users      |  1.7980000e+08 | 1765561000000 | 168095000000 | 1451590000000 | 145875000000 | 1600 GB | 150 GB  | 1300 GB | 130 GB
  93864937 | public       | products   |  1.2210000e+08 | 1649972000000 |   7144000000 | 1316745000000 | 326083000000 | 1500 GB | 6800 MB | 1200 GB | 300 GB
  94168756 | public       | messages   |  1.5310000e+08 |  342210000000 |   7533000000 |         32768 | 334676000000 | 300 GB  | 7100 MB | 32 kB   | 300 GB

---

Alter one column's compression type to lz4

ALTER TABLE public.users ALTER COLUMN column1 set COMPRESSION lz4;

---

List running queries

SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age 
FROM pg_stat_activity
WHERE state <> 'idle' 
    AND query NOT LIKE '% FROM pg_stat_activity %' 
ORDER BY age;