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

Code from the \du command but expanded (for Aurora/RDS):

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
WHERE
    r.rolname !~ '^pg_'
ORDER BY
    1;

\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

Without system tables:

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 np.nspname NOT IN ('information_schema', 'pg_catalog')
AND c.relkind IN ('r', 'v')
ORDER BY np.nspname, c.relname
;

\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+

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

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

SELECT
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM
    pg_stat_user_tables
WHERE
    relname = '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

snippet.sql

WITH rol AS (
    SELECT oid,
            rolname::text AS role_name
        FROM pg_roles
    UNION
    SELECT 0::oid AS oid,
            'public'::text
),
schemas AS ( -- Schemas
    SELECT oid AS schema_oid,
            n.nspname::text AS schema_name,
            n.nspowner AS owner_oid,
            'schema'::text AS object_type,
            COALESCE ( n.nspacl, acldefault ( 'n'::"char", n.nspowner ) ) AS acl
        FROM pg_catalog.pg_namespace n
        WHERE n.nspname !~ '^pg_'
            AND n.nspname <> 'information_schema'
),
classes AS ( -- Tables, views, etc.
    SELECT schemas.schema_oid,
            schemas.schema_name AS object_schema,
            c.oid,
            c.relname::text AS object_name,
            c.relowner AS owner_oid,
            CASE
                WHEN c.relkind = 'r' THEN 'table'
                WHEN c.relkind = 'v' THEN 'view'
                WHEN c.relkind = 'm' THEN 'materialized view'
                WHEN c.relkind = 'c' THEN 'type'
                WHEN c.relkind = 'i' THEN 'index'
                WHEN c.relkind = 'S' THEN 'sequence'
                WHEN c.relkind = 's' THEN 'special'
                WHEN c.relkind = 't' THEN 'TOAST table'
                WHEN c.relkind = 'f' THEN 'foreign table'
                WHEN c.relkind = 'p' THEN 'partitioned table'
                WHEN c.relkind = 'I' THEN 'partitioned index'
                ELSE c.relkind::text
                END AS object_type,
            CASE
                WHEN c.relkind = 'S' THEN COALESCE ( c.relacl, acldefault ( 's'::"char", c.relowner ) )
                ELSE COALESCE ( c.relacl, acldefault ( 'r'::"char", c.relowner ) )
                END AS acl
        FROM pg_class c
        JOIN schemas
            ON ( schemas.schema_oid = c.relnamespace )
        WHERE c.relkind IN ( 'r', 'v', 'm', 'S', 'f', 'p' )
),
cols AS ( -- Columns
    SELECT c.object_schema,
            NULL::INTEGER AS oid,
            c.object_name || '.' || a.attname::text AS object_name,
            'column' AS object_type,
            c.owner_oid,
            COALESCE ( a.attacl, acldefault ( 'c'::"char", c.owner_oid ) ) AS acl
        FROM pg_attribute a
        JOIN classes c
            ON ( a.attrelid = c.oid )
        WHERE a.attnum > 0
            AND NOT a.attisdropped
),
procs AS ( -- Procedures and functions
    SELECT schemas.schema_oid,
            schemas.schema_name AS object_schema,
            p.oid,
            p.proname::text AS object_name,
            p.proowner AS owner_oid,
            CASE p.prokind
                WHEN 'a' THEN 'aggregate'
                WHEN 'w' THEN 'window'
                WHEN 'p' THEN 'procedure'
                ELSE 'function'
                END AS object_type,
            pg_catalog.pg_get_function_arguments ( p.oid ) AS calling_arguments,
            COALESCE ( p.proacl, acldefault ( 'f'::"char", p.proowner ) ) AS acl
        FROM pg_proc p
        JOIN schemas
            ON ( schemas.schema_oid = p.pronamespace )
),
udts AS ( -- User defined types
    SELECT schemas.schema_oid,
            schemas.schema_name AS object_schema,
            t.oid,
            t.typname::text AS object_name,
            t.typowner AS owner_oid,
            CASE t.typtype
                WHEN 'b' THEN 'base type'
                WHEN 'c' THEN 'composite type'
                WHEN 'd' THEN 'domain'
                WHEN 'e' THEN 'enum type'
                WHEN 't' THEN 'pseudo-type'
                WHEN 'r' THEN 'range type'
                WHEN 'm' THEN 'multirange'
                ELSE t.typtype::text
                END AS object_type,
            COALESCE ( t.typacl, acldefault ( 'T'::"char", t.typowner ) ) AS acl
        FROM pg_type t
        JOIN schemas
            ON ( schemas.schema_oid = t.typnamespace )
        WHERE ( t.typrelid = 0
                OR ( SELECT c.relkind = 'c'
                        FROM pg_catalog.pg_class c
                        WHERE c.oid = t.typrelid ) )
            AND NOT EXISTS (
                SELECT 1
                    FROM pg_catalog.pg_type el
                    WHERE el.oid = t.typelem
                        AND el.typarray = t.oid )
),
fdws AS ( -- Foreign data wrappers
    SELECT NULL::oid AS schema_oid,
            NULL::text AS object_schema,
            p.oid,
            p.fdwname::text AS object_name,
            p.fdwowner AS owner_oid,
            'foreign data wrapper' AS object_type,
            COALESCE ( p.fdwacl, acldefault ( 'F'::"char", p.fdwowner ) ) AS acl
        FROM pg_foreign_data_wrapper p
),
fsrvs AS ( -- Foreign servers
    SELECT NULL::oid AS schema_oid,
            NULL::text AS object_schema,
            p.oid,
            p.srvname::text AS object_name,
            p.srvowner AS owner_oid,
            'foreign server' AS object_type,
            COALESCE ( p.srvacl, acldefault ( 'S'::"char", p.srvowner ) ) AS acl
        FROM pg_foreign_server p
),
all_objects AS (
    SELECT schema_name AS object_schema,
            object_type,
            schema_name AS object_name,
            NULL::text AS calling_arguments,
            owner_oid,
            acl
        FROM schemas
    UNION
    SELECT object_schema,
            object_type,
            object_name,
            NULL::text AS calling_arguments,
            owner_oid,
            acl
        FROM classes
    UNION
    SELECT object_schema,
            object_type,
            object_name,
            NULL::text AS calling_arguments,
            owner_oid,
            acl
        FROM cols
    UNION
    SELECT object_schema,
            object_type,
            object_name,
            calling_arguments,
            owner_oid,
            acl
        FROM procs
    UNION
    SELECT object_schema,
            object_type,
            object_name,
            NULL::text AS calling_arguments,
            owner_oid,
            acl
        FROM udts
    UNION
    SELECT object_schema,
            object_type,
            object_name,
            NULL::text AS calling_arguments,
            owner_oid,
            acl
        FROM fdws
    UNION
    SELECT object_schema,
            object_type,
            object_name,
            NULL::text AS calling_arguments,
            owner_oid,
            acl
        FROM fsrvs
),
acl_base AS (
    SELECT object_schema,
            object_type,
            object_name,
            calling_arguments,
            owner_oid,
            ( aclexplode ( acl ) ).grantor AS grantor_oid,
            ( aclexplode ( acl ) ).grantee AS grantee_oid,
            ( aclexplode ( acl ) ).privilege_type AS privilege_type,
            ( aclexplode ( acl ) ).is_grantable AS is_grantable
        FROM all_objects
)
SELECT acl_base.object_schema,
        acl_base.object_type,
        acl_base.object_name,
        acl_base.calling_arguments,
        owner.role_name AS object_owner,
        grantor.role_name AS grantor,
        grantee.role_name AS grantee,
        acl_base.privilege_type,
        acl_base.is_grantable
    FROM acl_base
    JOIN rol owner
        ON ( owner.oid = acl_base.owner_oid )
    JOIN rol grantor
        ON ( grantor.oid = acl_base.grantor_oid )
    JOIN rol grantee
        ON ( grantee.oid = acl_base.grantee_oid )
    WHERE acl_base.grantor_oid <> acl_base.grantee_oid ;

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 grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE TABLE_NAME='mytable' ;

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,
    pid,
    usename,
    application_name,
    state ,
    backend_start ,
    xact_start    ,
    now()-query_start query_duration, 
    state_change
FROM pg_stat_activity
;

NON-IDLE sql:

SELECT
    datname,
    pid,
    usename,
    application_name,
    state ,
    backend_start ,
    xact_start    ,
    now()-query_start query_duration, 
    state_change
FROM pg_stat_activity
WHERE state <> 'idle'
;

• Link

TIP: You can get pid from pg_stat_activity:

SELECT
    pg_terminate_backend(pid)
;

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 but with no shit…

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;

snippet.sql

SELECT
    schemaname AS schema,
    relname AS tablename,
    n_live_tup AS livetuples,
    n_dead_tup AS deadtuples,
    n_tup_del,
    n_tup_upd,
    last_autovacuum AS last_autovacuum,
    last_vacuum AS last_manualvacuum
FROM
    pg_stat_user_tables
ORDER BY schemaname, relname;

Or ordered by last vacuum's

snippet.sql

SELECT
    schemaname AS schema,
    relname AS tablename,
    n_live_tup AS livetuples,
    n_dead_tup AS deadtuples,
    n_tup_del,
    n_tup_upd,
    last_autovacuum AS last_autovacuum,
    last_vacuum AS last_manualvacuum
FROM
    pg_stat_user_tables
ORDER BY last_autovacuum, last_vacuum DESC;

snippet.sql

DO $$
DECLARE
    tab RECORD;
    schemaName VARCHAR := 'SCHEMA_NAME';
BEGIN
    FOR tab IN (
        SELECT
            t.relname::VARCHAR AS TABLE_NAME
        FROM
            pg_class t
            JOIN pg_namespace n ON n.oid = t.relnamespace
        WHERE
            t.relkind = 'r'
            AND n.nspname::VARCHAR = schemaName
        ORDER BY
            1)
            LOOP
                RAISE NOTICE 'ANALYZE %.%', schemaName, tab.table_name;
                EXECUTE format('ANALYZE %I.%I', schemaName, tab.table_name);
            END LOOP;
END
$$;

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

This is an example on how to use XMLTABLE function to extract data from a XML column:

    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
;