PostgreSQL handy queries

Version check
SELECT  version();

 

Check Size of Database
SELECT pg_size_pretty(pg_database_size('mydatabasename')) As
fulldbsize;
SELECT datname, pg_database_size(datname),
pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY 2 DESC;

Get All Catalog Tables
\dt pg_catalog.*

Top 10 WRITE Tables

select schemaname as "Schema Name", relname as "Table Name",
n_tup_ins+n_tup_upd+n_tup_del as "no.of writes" from
pg_stat_all_tables where schemaname not in ('snapshots','
pg_catalog')
order by n_tup_ins+n_tup_upd+n_tup_del desc limit 10;

Top 10 READ Tables

SELECT schemaname as "Schema Name", relname as "Table
Name",seq_tup_read+idx_tup_fetch as "no. of reads" FROM
pg_stat_all_tables WHERE (seq_tup_read + idx_tup_fetch) > 0 and
schemaname NOT IN ('snapshots','pg_catalog') ORDER BY
seq_tup_read+idx_tup_fetch desc limit 10;

Table Size

SELECT schemaname, relname, pg_total_relation_size(schemaname
|| '.' || relname ) ,
pg_size_pretty(pg_total_relation_size(schemaname || '.' ||
relname ))
FROM pg_stat_user_tables
ORDER BY 3 DESC;

Slow Running Queries on DB from Last 5 Min

select now()-query_start as Running_Since,pid, datname,
usename, application_name, client_addr, left(query,60)
from pg_stat_activity where state in ('active','idle in
transaction') and (now() - pg_stat_activity.query_start) >
interval '2 minutes';

Transaction
SELECT pid, datname, usename, query FROM pg_stat_activity;


Active session
SELECT datname, usename, state, query FROM pg_stat_activity WHERE state = 'active';

현재 실행 중인 SQL 상태 

SELECT
    current_timestamp - query_start AS runtime,
    datname, usename, query
FROM pg_stat_activity
WHERE state = 'active' ORDER BY 1 DESC;

1분 이상 실행되는 쿼리 확인
SELECT
    current_timestamp - query_start AS runtime,
    datname, usename, query
FROM pg_stat_activity
WHERE state = 'active'
      AND current_timestamp - query_start > '1 min'
ORDER BY 1 DESC;

Current user:

select
current_date,
current_time,
current_timestamp,
current_role,
current_user,
current_catalog,
current_schema
\gx

 

********************************

postgres=#  select
postgres-# current_date,
postgres-# current_time,
postgres-# current_timestamp,
postgres-# current_role,
postgres-# current_user,
postgres-# current_catalog,
postgres-# current_schema
postgres-# \gx
-[ RECORD 1 ]-----+------------------------------
current_date      | 2023-03-17
current_time      | 03:14:28.080827-04
current_timestamp | 2023-03-17 03:14:28.080827-04
current_role      | postgres
current_user      | postgres
current_catalog   | postgres
current_schema    | public

postgres=#

 

File search

 

find / -name "*pg_rman.ini*" 2>/dev/null

Recourse: "PostgreSQL_Configuration_Best_Practices_for_Performance_and_Security"  book