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 FUNCTIONNAME'': <code sql> select prosrc from pgproc where proname='FUNCTIONNAME' ; </code> ====== Section: GRANTS ====== GRANT SYNTAX ===== Column grants ===== <code sql> SELECT * FROM informationschema.rolecolumngrants where grantee not in ('postgres', 'PUBLIC' ) ; </code> or <code sql> SELECT grantee, tablecatalog, tableschema, tablename, columnname, privilegetype, isgrantable FROM informationschema.rolecolumngrants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) order by grantee, tablecatalog, tableschema, tablename, columnname ; </code> ===== Routine grants ===== <code sql> SELECT * FROM informationschema.roleroutinegrants where grantee not in ('postgres', 'PUBLIC' ) ; </code> ===== Table grants ===== <code sql> SELECT * FROM informationschema.roletable_grants where grantee not in ('postgres', 'PUBLIC' ) ; </code> <code sql> select * from (select grantee, tableschema, tablename, stringagg( privilegetype, ', ' ) from informationschema.roletablegrants WHERE grantee NOT in ( 'postgres', 'PUBLIC' ) group by grantee, tablecatalog, tableschema, tablename) t order by table_schema, grantee ; </code> ===== Udt grants ===== <code sql> SELECT * FROM informationschema.roleudt_grants where grantee not in ('postgres', 'PUBLIC' ) ; </code> ===== Usage grants ===== <code sql> SELECT * FROM informationschema.roleusage_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 pgcatalog.pgauthmembers m JOIN pgcatalog.pgroles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof , r.rolreplication , r.rolbypassrls FROM pgcatalog.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 pgroles WHERE rolname NOT IN ('postgres', 'replicator', 'pgsignal_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, clientaddr, applicationname, query from pgstatactivity where datname <>'postgres' ; </code> <code sql>select datname, usename, applicationname, state , backendstart , xactstart , now()-querystart queryduration, statechange, query from pgstatactivity ; </code> without sql: <code sql>select datname, usename, applicationname, state , backendstart , xactstart , now()-querystart queryduration, statechange from pgstatactivity ; </code> Fits fhd screen: <code sql>select datname, usename, applicationname, state , backendstart , xactstart , querystart , statechange from pgstat_activity ; </code> * Link ===== Long running queries ===== <code sql> SELECT pid, user, pgstatactivity.querystart, now() - pgstatactivity.querystart AS querytime, query, state, waiteventtype, waitevent FROM pgstatactivity WHERE (now() - pgstatactivity.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.clientaddr, psa.applicationname, pl.mode, psa.query FROM pgstatactivity 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 official documentation: <code sql> SET applicationname='%yourlogicalname%'; SELECT blockedlocks.pid AS blockedpid, blockedactivity.usename AS blockeduser, blockinglocks.pid AS blockingpid, blockingactivity.usename AS blockinguser, blockedactivity.query AS blockedstatement, blockingactivity.query AS currentstatementinblockingprocess, blockedactivity.applicationname AS blockedapplication, blockingactivity.applicationname AS blockingapplication FROM pgcatalog.pglocks blockedlocks JOIN pgcatalog.pgstatactivity blockedactivity ON blockedactivity.pid = blockedlocks.pid JOIN pgcatalog.pglocks blockinglocks ON blockinglocks.locktype = blockedlocks.locktype AND blockinglocks.DATABASE IS NOT DISTINCT FROM blockedlocks.DATABASE AND blockinglocks.relation IS NOT DISTINCT FROM blockedlocks.relation AND blockinglocks.page IS NOT DISTINCT FROM blockedlocks.page AND blockinglocks.tuple IS NOT DISTINCT FROM blockedlocks.tuple AND blockinglocks.virtualxid IS NOT DISTINCT FROM blockedlocks.virtualxid AND blockinglocks.transactionid IS NOT DISTINCT FROM blockedlocks.transactionid AND blockinglocks.classid IS NOT DISTINCT FROM blockedlocks.classid AND blockinglocks.objid IS NOT DISTINCT FROM blockedlocks.objid AND blockinglocks.objsubid IS NOT DISTINCT FROM blockedlocks.objsubid AND blockinglocks.pid != blockedlocks.pid JOIN pgcatalog.pgstatactivity blockingactivity ON blockingactivity.pid = blockinglocks.pid WHERE NOT blocked_locks.GRANTED; </code> ===== Change user password ===== <code sql> ALTER USER “username” WITH PASSWORD 'newpassword'; </code> ===== dump/export all users with password ===== <code bash> pgdumpall –globals-only –file=allrolesandusers.sql -h 127.0.0.1 </code> ===== List extensions installed ===== <code sql> select * from pg_extension ; </code> ===== List extensions available ===== <code sql> SELECT * FROM pgavailableextensions ; </code> ===== show version===== <code sql> select version() ; </code> ===== List Foreign keys (FK) ===== From this page: <code sql> select kcu.tableschema || '.' ||kcu.tablename as foreigntable, '>-' as rel, reltco.tableschema || '.' || reltco.tablename as primarytable, stringagg(kcu.columnname, ', ') as fkcolumns, kcu.constraintname from informationschema.tableconstraints tco join informationschema.keycolumnusage kcu on tco.constraintschema = kcu.constraintschema and tco.constraintname = kcu.constraintname join informationschema.referentialconstraints rco on tco.constraintschema = rco.constraintschema and tco.constraintname = rco.constraintname join informationschema.tableconstraints reltco on rco.uniqueconstraintschema = reltco.constraintschema and rco.uniqueconstraintname = reltco.constraintname where tco.constrainttype = 'FOREIGN KEY' group by kcu.tableschema, kcu.tablename, reltco.tablename, reltco.tableschema, kcu.constraintname order by kcu.tableschema, kcu.tablename;
</code> ====== Section: VACUUM ====== ===== vacuum date by table ===== <code sql> select relname, lastvacuum, lastautovacuum, lastanalyze, lastautoanalyze from pgstatuser_tables; </code> ====== Section: Streaming Replication ====== ===== Check if the database is in recovery mode ===== <code sql> select pgisin_recovery(); </code> ===== Current wall address ===== Master: <code sql> SELECT pgcurrentwallsn(); </code> Slave: <code sql> SELECT pglastwalreceive_lsn(); </code> ===== Wal apply lag (on the slave) ===== <code sql> SELECT pgwallsndiff(pglastwalreceivelsn(), pglastwalreplay_lsn()) ; </code> ===== Status of the slaves (on master) ===== <code sql> select * from pgstatreplication ; </code> ===== Status of the slave (on the slave) ===== <code sql> select * from pgstatwal_receiver; </code> ====== Section: Logical Replication ====== ===== Status of the origin ===== <code sql> select * from pgreplicationorigin_status; </code> ===== Status of the subscriber ===== <code sql> select * from pgstatsubscription; </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 pgreplicationslots; </code> ===== drop a replication slot (master) ===== <code sql> select pgdropreplicationslot('subscriptionname') ; </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> pcpwatchdoginfo -h $(hostname) -p 9898 -U pgpool –verbose -w </code> ==== Recover node (postgre) ==== <code bash> pcprecoverynode -h $(hostname) -p 9898 -U pgpool -w -n <NODEID> </code> ==== Node (postgre) information ==== <code bash> pcpnodeinfo -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: EMPTY ====== <code> </code> <code> </code> <code> </code> <code> </code> <code> </code> <code> </code> <code> </code> <code> </code> <code> </code> <code> </code>