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

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

\dn

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 <SCHEMANAME>: <code sql> select n.nspname as schemaname, p.proname as specificname, l.lanname as language, case when l.lanname = 'internal' then p.prosrc else pggetfunctiondef(p.oid) end as definition, pggetfunctionarguments(p.oid) as arguments, p.probin from pgproc p left join pgnamespace n on p.pronamespace = n.oid left join pglanguage l on p.prolang = l.oid left join pgtype t on t.oid = p.prorettype where n.nspname in (<SCHEMANAME>) order by schemaname, specificname; </code> ====== Section: Space ====== ===== Database size ===== <code sql> SELECT pgdatabasesize('nameddb'); SELECT pgsizepretty(pgdatabasesize('nameddb')); </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 pgdatabase where datname not in ('postgres', 'template1', 'template0') LOOP select pgsizepretty(pgdatabase_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 pgsizepretty(pgtotalrelationsize('namedtable')); </code> ====== Section: objects information ====== =====Table description===== <code sql> \d+ tablename </code> ===== list table columns ===== <code> \d+ “table_name” </code> <code sql> SELECT tablecatalog, tableschema, tablename, columnname, ordinalposition, datatype, charactermaximumlength, columndefault, isnullable FROM informationschema.columns WHERE TABLENAME = 'tablename' order by ordinalposition ; </code> ===== List table objects ===== <code sql> SHOW INDEX FROM yourtable; </code> <code sql> SELECT DISTINCT TABLENAME, INDEXNAME FROM INFORMATIONSCHEMA.STATISTICS WHERE TABLESCHEMA = 'your_schema'; </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 FUNCTIONNAME:

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 pid,
        usename, 
--         application_name, 
        client_addr, 
        backend_start, 
--         xact_start, 
        now()-query_start Query_duration, 
--         state_change, 
        waiting, 
        state, 
        query
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() ;

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 ;

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