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