Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:postgresql:postgresql_basic_querys

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Next revisionBoth sides next revision
postgresql:postgresql_basic_querys [2022/01/11 11:21] – [Sessions] dodgerdba:postgresql:postgresql_basic_querys [2023/10/09 09:11] – [list functions] dodger
Line 57: Line 57:
  
 ===== autocommit ===== ===== autocommit =====
-<code sql> +*WARNING* ''\set '' has to be on lowercase: \\ 
-\set AUTOCOMMIT off +\\ 
-</code>+''\set AUTOCOMMIT off'' 
  
 Check: Check:
Line 108: Line 109:
  
 ===== list schemas ===== ===== list schemas =====
 +Sortcode:
 <code> <code>
 \dn \dn
 +</code>
 +
 +Query:
 +<code sql>
 +SELECT n.nspname AS "Name",
 +  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
 +FROM pg_catalog.pg_namespace n
 +WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
 +ORDER BY 1;
 </code> </code>
  
Line 150: Line 161:
  
 ===== list functions ===== ===== list functions =====
 +
 +<code sql>
 +\df
 +</code>
 +
 +Or
 +<code sql>
 +SELECT n.nspname as "Schema",
 +  p.proname as "Name",
 + CASE p.prokind
 +  WHEN 'a' THEN 'agg'
 +  WHEN 'w' THEN 'window'
 +  WHEN 'p' THEN 'proc'
 +  ELSE 'func'
 + END as "Type"
 +FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 +WHERE pg_catalog.pg_function_is_visible(p.oid)
 +      AND n.nspname <> 'pg_catalog'
 +      AND n.nspname <> 'information_schema'
 +ORDER BY 1, 2;
 +</code>
 +
 +
 +or complete:
 +<code sql>
 +SELECT n.nspname as "Schema",
 +  p.proname as "Name",
 +  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
 +  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 + CASE p.prokind
 +  WHEN 'a' THEN 'agg'
 +  WHEN 'w' THEN 'window'
 +  WHEN 'p' THEN 'proc'
 +  ELSE 'func'
 + END as "Type"
 +FROM pg_catalog.pg_proc p
 +     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 +WHERE pg_catalog.pg_function_is_visible(p.oid)
 +      AND n.nspname <> 'pg_catalog'
 +      AND n.nspname <> 'information_schema'
 +ORDER BY 1, 2;
 +</code>
 +Note:
 +  "Result data type" and "Argument data types" are normally multi-lined...
 +
 +==== Aggregate functions ====
 +
 <code sql> <code sql>
 \da \da
Line 195: Line 253:
          specific_name;          specific_name;
 </code> </code>
 +
 +
 +====== Section: Space ======
 +
 +===== Database size =====
 +
 +<code sql>
 +SELECT pg_database_size('named_db');
 +SELECT pg_size_pretty(pg_database_size('named_db'));
 +</code>
 +
 +or
 +<code sql>
 +\l+
 +</code>
 +
 +===== 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 ('postgres', 'template1', 'template0')
 +    LOOP
 +        select pg_size_pretty(pg_database_size( thedb )) into dbsize ;
 +        raise notice 'Database % size : %', thedb, dbsize;
 +    END LOOP;
 +END$$;
 +</code>
 +===== Space used for each table =====
 +<code sql>
 +\d+
 +</code>
 +
 +===== Space used for each relation =====
 +
 +<code sql>
 +
 +SELECT pg_size_pretty(pg_total_relation_size('named_table'));
 +
 +</code>
 +
 +
 +
  
  
Line 224: Line 327:
 WHERE TABLE_NAME   = 'table_name' WHERE TABLE_NAME   = 'table_name'
 order by ordinal_position order by ordinal_position
 +;
 +</code>
 +==== 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 'concession'
 ; ;
 </code> </code>
Line 237: Line 361:
 FROM INFORMATION_SCHEMA.STATISTICS FROM INFORMATION_SCHEMA.STATISTICS
 WHERE TABLE_SCHEMA = 'your_schema'; WHERE TABLE_SCHEMA = 'your_schema';
 +</code>
 +
 +
 +
 +===== 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 = 'r'
 +    AND a.attname like '%COLUMN_NAME%'
 +    AND i.relname like '%INDEX_NAME%'
 +    AND t.relname like '%TABLE_NAME%'
 +ORDER BY
 +    t.relname,
 +    a.attname;
 </code> </code>
  
Line 361: Line 518:
 </code> </code>
  
-<code sql>SELECT pid, +<code sql>select 
-        usename,  +    datname, 
---         application_name,  +    usename, 
-        client_addr,  +    application_name, 
-        backend_start,  +    state , 
---         xact_start,  +    backend_start , 
-        now()-query_start Query_duration,  +    xact_start    , 
---         state_change,  +    now()-query_start query_duration,  
-        waiting,  +    state_change, 
-        state,  +    query 
-        query +from pg_stat_activity 
-from pg_stat_activity;+;
 </code> </code>
 +
 +
 +**without** sql:
 +<code sql>select
 +    datname,
 +    usename,
 +    application_name,
 +    state ,
 +    backend_start ,
 +    xact_start    ,
 +    now()-query_start query_duration, 
 +    state_change
 +from pg_stat_activity
 +;
 +</code>
 +
 +
  
 Fits fhd screen: Fits fhd screen:
Line 395: Line 569:
   * [[http://www.chrismiles.info/systemsadmin/databases/articles/viewing-current-postgresql-queries/|Link]]   * [[http://www.chrismiles.info/systemsadmin/databases/articles/viewing-current-postgresql-queries/|Link]]
  
 +===== 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';
 +</code>
  
  
Line 436: Line 625:
 </code> </code>
  
 +
 +
 +===== List Foreign keys (FK) =====
 +
 +From [[https://dataedo.com/kb/query/postgresql/list-foreign-keys|this page]]:
 +<code sql>
 +select kcu.table_schema || '.' ||kcu.table_name as foreign_table,
 +       '>-' as rel,
 +       rel_tco.table_schema || '.' || rel_tco.table_name as primary_table,
 +       string_agg(kcu.column_name, ', ') as fk_columns,
 +       kcu.constraint_name
 +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 = 'FOREIGN KEY'
 +group by kcu.table_schema,
 +         kcu.table_name,
 +         rel_tco.table_name,
 +         rel_tco.table_schema,
 +         kcu.constraint_name
 +order by kcu.table_schema,
 +         kcu.table_name;
 +         
 +</code>
 +====== Section: VACUUM ======
 +
 +===== vacuum date by table =====
 +<code sql>
 +select
 + relname,
 + last_vacuum,
 + last_autovacuum,
 + last_analyze,
 + last_autoanalyze
 +from
 + pg_stat_user_tables;
 +</code>
  
 ====== Section: Streaming Replication ====== ====== Section: Streaming Replication ======
Line 550: Line 783:
 pcp_node_info -h $(hostname) -p 9898 -U pgpool -w -v <NODEID> pcp_node_info -h $(hostname) -p 9898 -U pgpool -w -v <NODEID>
 </code> </code>
 +
 +
 +
 +
 +
 +====== Section: pgagent ======
 +
 +===== List jobs =====
 +
 +<code sql>
 +select * from pgagent.pga_job ;
 +</code>
 +
 +Less info:
 +<code sql>
 +select jobid,
 +    jobname,
 +    jobdesc,
 +    jobenabled
 +from pgagent.pga_job ;
 +</code>
 +
 +===== List jobs executions =====
 +All:
 +<code sql>
 +select * from pgagent.pga_joblob ;
 +</code>
 +
 +
 +Last 10 days:
 +<code sql>
 +select *
 +from pgagent.pga_joblog
 +where jlgstart > now() - interval '10 days' ;
 +</code>
 +
 +
 +
 +Failed on the last 10 days:
 +<code sql>
 +select *
 +from pgagent.pga_joblog
 +where jlgstart > now() - interval '10 days' 
 +and jlgstatus <> 's' ;
 +</code>
 +
 +
 +===== Failed Job step logs  =====
 +
 +<code sql>
 +select *
 +from pgagent.pga_joblog
 +where jlgstart > now() - interval '10 days' 
 +and jlgstatus <> 's' ;
 +</code>
 +
  
  
Line 556: Line 845:
  
 ====== Section: EMPTY ====== ====== Section: EMPTY ======
 +
  
  
dba/postgresql/postgresql_basic_querys.txt · Last modified: 2024/04/19 07:37 by dodger