Ciberterminal Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== [DOC] PostgreSQL basic queries ======


====== Section: Description ======
Basic querys for PostgreSQL administration

===== psql documentation =====
[[https://www.postgresql.org/docs/current/static/app-psql.html|Official "app" documentation]] (list of ''\aliases'')

====== Section: Usage ======
===== Change pager =====
<code>
export PAGER=less
</code>
Permanent:
<code>
cat >> ~/.bash_profile<<EOF
export PAGER=less
EOF
</code>
Working modes:
<code>
postgres=# \pset pager on;
Pager is used for long output.

postgres=# \pset pager off;
Pager is used for long output.

postgres=# \pset pager always;
Pager is used for long output.

postgres=# \pset pager occasionally
Pager is used for long output.

postgres=# \pset pager at random
Pager is used for long output.
\pset: extra argument "random" ignored
</code>

===== execute query =====
psql
<code>
\g
</code>

==== Execute sql script ====
<code>
\i somedir/script2.sql
</code>

===== help =====
psql
<code>
\h
\?
</code>

===== autocommit =====
*WARNING* ''\set '' has to be on lowercase: \\
\\
''\set AUTOCOMMIT off''


Check:
<code sql>
\echo :AUTOCOMMIT
</code>


===== change current schema =====
<code>
set search_path=SCHEMANAME ;
</code>


===== Print query aliases for sortcodes =====
This will work with ''\d'', ''\l''...
<code sql>
\set ECHO_HIDDEN on
</code>

====== Section: Listing Objects ======
===== list databases =====
<code>psql</code>
<code>
\l
</code>
<code>
\l+
</code>
<code sql>
select datname from pg_database;
</code>

===== list tablespaces =====
<code>
\db
</code>
&
<code sql>
SELECT spcname FROM pg_tablespace;
</code>

===== list data directory PGDATA =====

<code sql>
select setting from pg_settings where name = 'data_directory'; 
</code>

===== list schemas =====
Sortcode:
<code>
\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>

===== list parameters =====
<code sql>
select * from pg_settings ;
</code>

===== list users =====
<code sql>
select * from pg_authid  ;
</code>
<code sql>
\du
</code>

===== list domains =====
<code sql>
\dD
</code>


===== list ALL tables in database =====
<code sql>
select c.relname, np.nspname, u.usename, c.relkind
from pg_class c,
    pg_user u,
    pg_namespace np
where u.usesysid=c.relowner
and np.oid= c.relnamespace
and c.relkind in ('r', 'v')
order by np.nspname, c.relname
;
</code>
Where:
  * r = regular table
  * v = view
[[http://www.postgresql.org/docs/8.0/static/catalog-pg-class.html|link]]


===== 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>
\da
</code>

===== list data types =====
<code sql>
\dT
</code>

===== list triggers =====
<code sql>
select event_object_schema as table_schema,
       event_object_table as table_name,
       trigger_schema,
       trigger_name,
       string_agg(event_manipulation, ',') as event,
       action_timing as activation,
       action_condition as condition,
       action_statement as definition
from information_schema.triggers
group by 1,2,3,4,6,7,8
order by table_schema,
         table_name;
</code>


===== list objects in schema =====
Change ''<SCHEMA_NAME>'' :


<code sql>
select n.nspname as schema_name,
       p.proname as specific_name,
       l.lanname as language,
       case when l.lanname = 'internal' then p.prosrc
            else pg_get_functiondef(p.oid)
            end as definition,
       pg_get_function_arguments(p.oid) as arguments,
       p.probin
from pg_proc p
left join pg_namespace n on p.pronamespace = n.oid
left join pg_language l on p.prolang = l.oid
left join pg_type t on t.oid = p.prorettype 
where n.nspname in (<SCHEMA_NAME>)
order by schema_name,
         specific_name;
</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>





====== Section: objects information ======


=====Table description=====
<code sql>
\d+ tablename
</code>


===== list table columns =====
<code>
\d+ "table_name"
</code>

<code sql>
SELECT table_catalog,
    table_schema,
    table_name,
    column_name,
    ordinal_position,
    data_type,
    character_maximum_length,
    column_default,
    is_nullable
FROM information_schema.columns
WHERE TABLE_NAME   = 'table_name'
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>

===== List table objects =====
<code sql>
SHOW INDEX FROM yourtable;
</code>
<code sql>
SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
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>

===== show current schema =====
<code>
show search_path;
</code>


===== show object description =====
<code sql>
\dd
</code>
<code sql>
\dd [pattern]
</code>

===== show object source =====
Change ''FUNCTION_NAME'':


<code sql>
select prosrc from pg_proc where proname='FUNCTION_NAME'  ;
</code>
====== Section: GRANTS ======
[[http://www.postgresql.org/docs/current/static/sql-grant.html|GRANT SYNTAX]]
===== Column grants =====
<code sql>
SELECT * FROM information_schema.role_column_grants where grantee not in ('postgres', 'PUBLIC' ) ;
</code>

or
<code sql>
SELECT 
    grantee, 
    table_catalog, 
    table_schema, 
    table_name, 
    column_name, 
    privilege_type, 
    is_grantable 
FROM information_schema.role_column_grants 
WHERE grantee NOT IN ('postgres', 'PUBLIC' ) 
order by grantee, 
    table_catalog, 
    table_schema, 
    table_name, 
    column_name ;
</code>
===== Routine grants =====
<code sql>
SELECT * FROM information_schema.role_routine_grants where grantee not in ('postgres', 'PUBLIC' ) ;
</code>
===== Table grants =====
<code sql>
SELECT * FROM information_schema.role_table_grants where grantee not in ('postgres', 'PUBLIC' ) ;
</code>


<code sql>
select * from (select grantee, table_schema, table_name, string_agg( privilege_type, ', ' ) from information_schema.role_table_grants WHERE grantee NOT in ( 'postgres', 'PUBLIC' ) group by grantee, table_catalog, table_schema, table_name) t order by table_schema, grantee ;
</code>


===== Udt grants =====
<code sql>
SELECT * FROM information_schema.role_udt_grants where grantee not in ('postgres', 'PUBLIC' ) ;
</code>
===== Usage grants =====


<code sql>
SELECT * FROM information_schema.role_usage_grants where grantee not in ('postgres', 'PUBLIC' ) ;
</code>

Or:
<code sql>
SELECT 
      r.rolname, 
      r.rolsuper, 
      r.rolinherit,
      r.rolcreaterole,
      r.rolcreatedb,
      r.rolcanlogin,
      r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
ORDER BY 1;
</code>

or:
<code>
\du+
</code>
===== Grant all on SCHEMANAME to all users =====
<code sql>
DO $$
DECLARE r record;
BEGIN
    FOR r IN SELECT rolname FROM pg_roles WHERE rolname NOT IN ('postgres', 'replicator', 'pg_signal_backend')
    LOOP
        EXECUTE 'GRANT USAGE ON SCHEMA <SCHEMANAME> TO ' || r.rolname;
        EXECUTE 'GRANT INSERT, DELETE, UPDATE, SELECT ON ALL TABLES IN SCHEMA <SCHEMANAME> TO ' || r.rolname;
    END LOOP;
END$$;
</code>

====== Section: DBA ======
===== Sessions =====
<code sql>
select datid,
    datname,
    pid,
    usename,
    client_addr,
    application_name,
    query
from pg_stat_activity
where datname <>'postgres'
;
</code>

<code sql>select
    datname,
    usename,
    application_name,
    state ,
    backend_start ,
    xact_start    ,
    now()-query_start query_duration, 
    state_change,
    query
from pg_stat_activity
;
</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:
<code sql>select
    datname,
    usename,
    application_name,
    state ,
    backend_start ,
    xact_start    ,
    query_start   ,
    state_change
from pg_stat_activity
;
</code>





  * [[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>


===== Locks =====
This query will list all locks:
<code sql>
SELECT psa.datid,
    psa.datname,
    psa.pid,
    psa.usename,
    psa.client_addr,
    psa.application_name,
        pl.mode,
    psa.query
FROM pg_stat_activity psa, pg_locks pl
WHERE datname <>'postgres'
and pl.pid=psa.pid
and pl.mode in ('ExclusiveLock', 'RowExclusiveLock', 'ShareUpdateExclusiveLock' )
;
</code>

This query will list all //blocking sessions//, see [[https://wiki.postgresql.org/wiki/Lock_Monitoring|official documentation]]:
<code sql>
SET application_name='%your_logical_name%';
SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process,
         blocked_activity.application_name AS blocked_application,
         blocking_activity.application_name AS blocking_application
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;
</code>


===== Change user password =====
<code sql>
ALTER USER "user_name" WITH PASSWORD 'new_password';
</code>


===== dump/export all users with password =====
<code bash>
pg_dumpall --globals-only --file=all_roles_and_users.sql -h 127.0.0.1
</code>

===== List extensions installed =====
<code sql>
select * from pg_extension ;
</code>


===== List extensions available =====
<code sql>
SELECT * FROM pg_available_extensions ;
</code>


===== show version=====
<code sql>
select version() ;
</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 ======

===== Check if the database is in recovery mode =====
<code sql>
select pg_is_in_recovery();
</code>


===== Current wall address =====
Master:
<code sql>
SELECT pg_current_wal_lsn();
</code>
Slave:
<code sql>
SELECT pg_last_wal_receive_lsn();
</code>

===== Wal apply lag (on the slave) =====
<code sql>
SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) ;
</code>



===== Status of the slaves (on master) =====
<code sql>
select * from pg_stat_replication ;
</code>

===== Status of the slave (on the slave) =====
<code sql>
select * from pg_stat_wal_receiver;
</code>

====== Section: Logical Replication ======

===== Status of the origin =====
<code sql>
select * from pg_replication_origin_status;
</code>

===== Status of the subscriber =====
<code sql>
select * from pg_stat_subscription;
</code>


===== list publications =====
<code sql>
select * from pg_publication ;
</code>


===== list subscriptions =====
<code sql>
select * from pg_subscription ;
</code>



===== list replication slots (master) =====
<code sql>
select * from pg_replication_slots;
</code>



===== drop a replication slot (master) =====
<code sql>
select pg_drop_replication_slot('subscription_name') ;
</code>
====== Section: Pgpool ======

===== queries =====

==== pool nodes ====

<code sql>
show pool_nodes;
</code>

==== pool cache ====

<code sql>
 show pool_cache;
</code>




===== pcp commands =====


==== cluster info ====
<code bash>
pcp_watchdog_info -h  $(hostname) -p 9898 -U pgpool --verbose -w
</code>



==== Recover node (postgre) ====
<code bash>
pcp_recovery_node -h $(hostname) -p 9898 -U pgpool -w -n <NODEID>
</code>




==== Node (postgre) information ====
<code bash>
pcp_node_info -h $(hostname) -p 9898 -U pgpool -w -v <NODEID>
</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>






====== Section: XML (tables) ======
This is an example on how to use XMLTABLE function to extract data from a XML column:
<code sql>
    select
        bdef.build_definition_id as build_definition_id,
        tasks.task_definition_id as task_definition_id,
        tasks.user_description as user_description,
        tasks.is_enabled as is_enabled,
        tasks.plugin_key as plugin_key,
        tasks.finalising as finalising,
        config_items.item_key as item_key,
        config_items.item_value as item_value
    FROM
        build_definition bdef,
        XMLTABLE('//taskDefinition' PASSING XMLPARSE(DOCUMENT xml_definition_data)
                  COLUMNS
                      task_definition_id integer PATH 'id',
                      user_description text PATH 'userDescription',
                      is_enabled boolean PATH 'isEnabled',
                      plugin_key text PATH 'pluginKey',
                      finalising boolean PATH 'finalising',
                      config XML PATH 'config') AS tasks
    left join XMLTABLE('//item' PASSING tasks.config COLUMNS item_key text PATH 'key',item_value text PATH 'value') AS config_items
    on true
    where xml_definition_data LIKE '%\/dip\/%'
    order by 1
;
</code>




====== Section: EMPTY ======


<code sql>
</code>


<code>
</code>
<code>
</code>
<code>
</code>
<code>
</code>
<code>
</code>
<code>
</code>
<code>
</code>
<code>
</code>
<code>
</code>