dba:postgresql:postgresql_basic_querys
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
postgresql:postgresql_basic_querys [2021/12/20 11:57] – dodger | dba:postgresql:postgresql_basic_querys [2023/10/01 14:59] – [List column index] dodger | ||
---|---|---|---|
Line 6: | Line 6: | ||
===== psql documentation ===== | ===== psql documentation ===== | ||
- | [[https:// | + | [[https:// |
====== Section: Usage ====== | ====== Section: Usage ====== | ||
Line 57: | Line 57: | ||
===== autocommit ===== | ===== autocommit ===== | ||
- | <code sql> | + | *WARNING* '' |
- | \set AUTOCOMMIT off | + | \\ |
- | </ | + | '' |
Check: | Check: | ||
Line 108: | Line 109: | ||
===== list schemas ===== | ===== list schemas ===== | ||
+ | Sortcode: | ||
< | < | ||
\dn | \dn | ||
+ | </ | ||
+ | |||
+ | Query: | ||
+ | <code sql> | ||
+ | SELECT n.nspname AS " | ||
+ | pg_catalog.pg_get_userbyid(n.nspowner) AS " | ||
+ | FROM pg_catalog.pg_namespace n | ||
+ | WHERE n.nspname !~ ' | ||
+ | ORDER BY 1; | ||
</ | </ | ||
Line 195: | Line 206: | ||
| | ||
</ | </ | ||
+ | |||
+ | |||
+ | ====== Section: Space ====== | ||
+ | |||
+ | ===== Database size ===== | ||
+ | |||
+ | <code sql> | ||
+ | SELECT pg_database_size(' | ||
+ | SELECT pg_size_pretty(pg_database_size(' | ||
+ | </ | ||
+ | |||
+ | or | ||
+ | <code sql> | ||
+ | \l+ | ||
+ | </ | ||
+ | |||
+ | ===== ALL Databases size ===== | ||
+ | |||
+ | <code sql> | ||
+ | DO $$ | ||
+ | DECLARE thedb text; | ||
+ | DECLARE dbsize text; | ||
+ | BEGIN | ||
+ | FOR thedb IN SELECT datname FROM pg_database where datname not in (' | ||
+ | LOOP | ||
+ | select pg_size_pretty(pg_database_size( thedb )) into dbsize ; | ||
+ | raise notice ' | ||
+ | END LOOP; | ||
+ | END$$; | ||
+ | </ | ||
+ | ===== Space used for each table ===== | ||
+ | <code sql> | ||
+ | \d+ | ||
+ | </ | ||
+ | |||
+ | ===== Space used for each relation ===== | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | SELECT pg_size_pretty(pg_total_relation_size(' | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | |||
Line 224: | Line 280: | ||
WHERE TABLE_NAME | WHERE TABLE_NAME | ||
order by ordinal_position | order by ordinal_position | ||
+ | ; | ||
+ | </ | ||
+ | ==== dba_tab_columns ==== | ||
+ | |||
+ | inefficient but who cares | ||
+ | <code sql> | ||
+ | SELECT * from | ||
+ | ( | ||
+ | SELECT table_catalog DB_NAME, | ||
+ | table_schema SCHEMA_NAME, | ||
+ | table_name, | ||
+ | column_name, | ||
+ | ordinal_position, | ||
+ | data_type, | ||
+ | character_maximum_length, | ||
+ | column_default, | ||
+ | is_nullable | ||
+ | FROM information_schema.columns | ||
+ | order by ordinal_position | ||
+ | ) as dba_tab_columns | ||
+ | WHERE COLUMN_NAME LIKE ' | ||
; | ; | ||
</ | </ | ||
Line 237: | Line 314: | ||
FROM INFORMATION_SCHEMA.STATISTICS | FROM INFORMATION_SCHEMA.STATISTICS | ||
WHERE TABLE_SCHEMA = ' | WHERE TABLE_SCHEMA = ' | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== List column index ===== | ||
+ | ==== dba_ind_columns ==== | ||
+ | |||
+ | |||
+ | <code sql> | ||
+ | SELECT | ||
+ | np.nspname AS schema_name, | ||
+ | t.relname AS table_name, | ||
+ | a.attname AS column_name, | ||
+ | i.relname AS index_name | ||
+ | FROM | ||
+ | pg_class t, | ||
+ | pg_class i, | ||
+ | pg_index ix, | ||
+ | pg_attribute a, | ||
+ | pg_namespace np | ||
+ | WHERE | ||
+ | t.oid = ix.indrelid | ||
+ | AND i.oid = ix.indexrelid | ||
+ | AND a.attrelid = t.oid | ||
+ | AND a.attnum = ANY(ix.indkey) | ||
+ | AND np.oid = t.relnamespace | ||
+ | AND t.relkind = ' | ||
+ | AND a.attname like ' | ||
+ | AND i.relname like ' | ||
+ | AND t.relname like ' | ||
+ | ORDER BY | ||
+ | t.relname, | ||
+ | a.attname; | ||
</ | </ | ||
Line 361: | Line 471: | ||
</ | </ | ||
- | <code sql>SELECT pid, | + | <code sql>select |
- | usename, | + | datname, |
- | -- application_name, | + | usename, |
- | | + | application_name, |
- | backend_start, | + | |
- | -- xact_start, | + | backend_start , |
- | now()-query_start | + | xact_start |
- | -- state_change, | + | now()-query_start |
- | | + | state_change, |
- | state, | + | query |
- | | + | from pg_stat_activity |
- | from pg_stat_activity; | + | ; |
</ | </ | ||
+ | |||
+ | |||
+ | **without** sql: | ||
+ | <code sql> | ||
+ | datname, | ||
+ | usename, | ||
+ | application_name, | ||
+ | state , | ||
+ | backend_start , | ||
+ | xact_start | ||
+ | now()-query_start query_duration, | ||
+ | state_change | ||
+ | from pg_stat_activity | ||
+ | ; | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | Fits fhd screen: | ||
+ | <code sql> | ||
+ | datname, | ||
+ | usename, | ||
+ | application_name, | ||
+ | state , | ||
+ | backend_start , | ||
+ | xact_start | ||
+ | query_start | ||
+ | state_change | ||
+ | from pg_stat_activity | ||
+ | ; | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
* [[http:// | * [[http:// | ||
+ | ===== Long running queries ===== | ||
+ | |||
+ | <code sql> | ||
+ | SELECT | ||
+ | pid, | ||
+ | user, | ||
+ | pg_stat_activity.query_start, | ||
+ | now() - pg_stat_activity.query_start AS query_time, | ||
+ | query, | ||
+ | state, | ||
+ | wait_event_type, | ||
+ | wait_event | ||
+ | FROM pg_stat_activity | ||
+ | WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'; | ||
+ | </ | ||
Line 419: | Line 578: | ||
</ | </ | ||
+ | |||
+ | |||
+ | ===== List Foreign keys (FK) ===== | ||
+ | |||
+ | From [[https:// | ||
+ | <code sql> | ||
+ | select kcu.table_schema || ' | ||
+ | '> | ||
+ | | ||
+ | | ||
+ | | ||
+ | from information_schema.table_constraints tco | ||
+ | join information_schema.key_column_usage kcu | ||
+ | on tco.constraint_schema = kcu.constraint_schema | ||
+ | and tco.constraint_name = kcu.constraint_name | ||
+ | join information_schema.referential_constraints rco | ||
+ | on tco.constraint_schema = rco.constraint_schema | ||
+ | and tco.constraint_name = rco.constraint_name | ||
+ | join information_schema.table_constraints rel_tco | ||
+ | on rco.unique_constraint_schema = rel_tco.constraint_schema | ||
+ | and rco.unique_constraint_name = rel_tco.constraint_name | ||
+ | where tco.constraint_type = ' | ||
+ | group by kcu.table_schema, | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | order by kcu.table_schema, | ||
+ | | ||
+ | |||
+ | </ | ||
+ | ====== Section: VACUUM ====== | ||
+ | |||
+ | ===== vacuum date by table ===== | ||
+ | <code sql> | ||
+ | select | ||
+ | relname, | ||
+ | last_vacuum, | ||
+ | last_autovacuum, | ||
+ | last_analyze, | ||
+ | last_autoanalyze | ||
+ | from | ||
+ | pg_stat_user_tables; | ||
+ | </ | ||
====== Section: Streaming Replication ====== | ====== Section: Streaming Replication ====== | ||
Line 533: | Line 736: | ||
pcp_node_info -h $(hostname) -p 9898 -U pgpool -w -v < | pcp_node_info -h $(hostname) -p 9898 -U pgpool -w -v < | ||
</ | </ | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ====== Section: pgagent ====== | ||
+ | |||
+ | ===== List jobs ===== | ||
+ | |||
+ | <code sql> | ||
+ | select * from pgagent.pga_job ; | ||
+ | </ | ||
+ | |||
+ | Less info: | ||
+ | <code sql> | ||
+ | select jobid, | ||
+ | jobname, | ||
+ | jobdesc, | ||
+ | jobenabled | ||
+ | from pgagent.pga_job ; | ||
+ | </ | ||
+ | |||
+ | ===== List jobs executions ===== | ||
+ | All: | ||
+ | <code sql> | ||
+ | select * from pgagent.pga_joblob ; | ||
+ | </ | ||
+ | |||
+ | |||
+ | Last 10 days: | ||
+ | <code sql> | ||
+ | select * | ||
+ | from pgagent.pga_joblog | ||
+ | where jlgstart > now() - interval '10 days' ; | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | Failed on the last 10 days: | ||
+ | <code sql> | ||
+ | select * | ||
+ | from pgagent.pga_joblog | ||
+ | where jlgstart > now() - interval '10 days' | ||
+ | and jlgstatus <> ' | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== Failed Job step logs ===== | ||
+ | |||
+ | <code sql> | ||
+ | select * | ||
+ | from pgagent.pga_joblog | ||
+ | where jlgstart > now() - interval '10 days' | ||
+ | and jlgstatus <> ' | ||
+ | </ | ||
+ | |||
Line 539: | Line 798: | ||
====== Section: EMPTY ====== | ====== Section: EMPTY ====== | ||
+ | |||
dba/postgresql/postgresql_basic_querys.txt · Last modified: 2024/04/19 07:37 by dodger