Basic querys for PostgreSQL administration

Official "app" documentation (list of \aliases)

export PAGER=less

Permanent:

cat >> ~/.bash_profile<<EOF
export PAGER=less
EOF

Working modes:

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

psql

\g

\i somedir/script2.sql

psql

\h
\?

WARNING \set has to be on lowercase:

\set AUTOCOMMIT off

Check:

\echo :AUTOCOMMIT

set search_path=SCHEMANAME ;

This will work with \d, \l…

\SET ECHO_HIDDEN ON

psql

\l

\l+

SELECT datname FROM pg_database;

\db

&

SELECT spcname FROM pg_tablespace;

SELECT setting FROM pg_settings WHERE name = 'data_directory'; 

Sortcode:

\dn

Query:

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;

SELECT * FROM pg_settings ;

SELECT * FROM pg_authid  ;

\du

\dD

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
;

Where:

• r = regular table
• v = view

link

\df

Or

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;

or complete:

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;

Note:

"Result data type" and "Argument data types" are normally multi-lined...

\da

\dT

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;

Change <SCHEMA_NAME> :

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;

SELECT pg_database_size('named_db');
SELECT pg_size_pretty(pg_database_size('named_db'));

or

\l+

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$$;

\d+

SELECT pg_size_pretty(pg_total_relation_size('named_table'));

\d+ tablename

\d+ "table_name"

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
;

inefficient but who cares

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'
;

SHOW INDEX FROM yourtable;

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

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;

show search_path;

\dd

\dd [pattern]

Change FUNCTION_NAME:

SELECT prosrc FROM pg_proc WHERE proname='FUNCTION_NAME'  ;

GRANT SYNTAX

SELECT * FROM information_schema.role_column_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;

or

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 ;

SELECT * FROM information_schema.role_routine_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;

SELECT * FROM information_schema.role_table_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;

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 ;

SELECT * FROM information_schema.role_udt_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;

SELECT * FROM information_schema.role_usage_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;

Or:

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;

or:

\du+

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$$;

SELECT datid,
    datname,
    pid,
    usename,
    client_addr,
    application_name,
    query
FROM pg_stat_activity
WHERE datname <>'postgres'
;

SELECT
    datname,
    usename,
    application_name,
    state ,
    backend_start ,
    xact_start    ,
    now()-query_start query_duration, 
    state_change,
    query
FROM pg_stat_activity
;

without sql:

SELECT
    datname,
    usename,
    application_name,
    state ,
    backend_start ,
    xact_start    ,
    now()-query_start query_duration, 
    state_change
FROM pg_stat_activity
;

Fits fhd screen:

SELECT
    datname,
    usename,
    application_name,
    state ,
    backend_start ,
    xact_start    ,
    query_start   ,
    state_change
FROM pg_stat_activity
;

• Link

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';

This query will list all locks:

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' )
;

This query will list all blocking sessions, see official documentation:

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;

ALTER USER "user_name" WITH PASSWORD 'new_password';

pg_dumpall --globals-only --file=all_roles_and_users.sql -h 127.0.0.1

SELECT * FROM pg_extension ;

SELECT * FROM pg_available_extensions ;

SELECT version() ;

From this page:

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;
 

SELECT
	relname,
	last_vacuum,
	last_autovacuum,
	last_analyze,
	last_autoanalyze
FROM
	pg_stat_user_tables;

SELECT pg_is_in_recovery();

Master:

SELECT pg_current_wal_lsn();

Slave:

SELECT pg_last_wal_receive_lsn();

SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) ;

SELECT * FROM pg_stat_replication ;

SELECT * FROM pg_stat_wal_receiver;

SELECT * FROM pg_replication_origin_status;

SELECT * FROM pg_stat_subscription;

SELECT * FROM pg_publication ;

SELECT * FROM pg_subscription ;

SELECT * FROM pg_replication_slots;

SELECT pg_drop_replication_slot('subscription_name') ;

SHOW pool_nodes;

 SHOW pool_cache;

pcp_watchdog_info -h  $(hostname) -p 9898 -U pgpool --verbose -w

pcp_recovery_node -h $(hostname) -p 9898 -U pgpool -w -n <NODEID>

pcp_node_info -h $(hostname) -p 9898 -U pgpool -w -v <NODEID>

SELECT * FROM pgagent.pga_job ;

Less info:

SELECT jobid,
    jobname,
    jobdesc,
    jobenabled
FROM pgagent.pga_job ;

All:

SELECT * FROM pgagent.pga_joblob ;

Last 10 days:

SELECT *
FROM pgagent.pga_joblog
WHERE jlgstart > now() - INTERVAL '10 days' ;

Failed on the last 10 days:

SELECT *
FROM pgagent.pga_joblog
WHERE jlgstart > now() - INTERVAL '10 days' 
AND jlgstatus <> 's' ;

SELECT *
FROM pgagent.pga_joblog
WHERE jlgstart > now() - INTERVAL '10 days' 
AND jlgstatus <> 's' ;