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

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

SHOW INDEX FROM yourtable;

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

aka dba_ind_columns:

SELECT
    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
WHERE
    t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND a.attrelid = t.oid
    AND a.attnum = ANY(ix.indkey)
    AND t.relkind = 'r'
    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';

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

ALTER USER "user_name" WITH PASSWORD 'new_password';

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