postgres command

postgres-notes.md

report size information for all tables

-- report size information for all tables
-- Finding the size of your biggest relations
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 relation asc, size asc
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 150;

Get the amount of space used by all databases

SELECT pg_size_pretty(sum(pg_database_size(oid))::BIGINT) FROM pg_database;

Activity connection

SELECT * FROM pg_stat_activity 
where pg_stat_activity.usename like 'smuapp' 
order by state, application_name;

แสดง locks ของ active transactions

select pg_locks.mode, pg_database.datname, pg_class.relname, pg_locks.pid, pg_locks.*
from pg_locks
 inner join pg_database on pg_locks.database = pg_database.oid
 inner join pg_class on pg_locks.relation = pg_class.oid
order by pid;

find the sessions that are blocking another session.

-- find the sessions that are blocking another session.
select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

replica commands

select * from pg_stat_replication;

SELECT slot_name, slot_type, active FROM pg_replication_slots;

connect w/ ssl?

SELECT datname,usename, ssl, client_addr 
  FROM pg_stat_ssl
  JOIN pg_stat_activity
    ON pg_stat_ssl.pid = pg_stat_activity.pid;

reload postgresql conf

SELECT pg_reload_conf();

change user search path

ALTER ROLE smudba IN DATABASE smartu 
SET search_path TO smu, extensions, "$user";

show postgresql settings

SELECT * FROM pg_settings;

SELECT * FROM pg_settings WHERE name = '%param%';