This is an old revision of the document!
Table of Contents
[DOC] PostgreSQL basic queries
Section: Description
Basic querys for PostgreSQL administration
psql documentation
Official "app" documentation (list of \aliases
)
Section: Usage
Change pager
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
execute query
psql
\g
Execute sql script
\i somedir/script2.sql
help
psql
\h \?
autocommit
WARNING \set
has to be on lowercase:
\set AUTOCOMMIT off
Check:
\echo :AUTOCOMMIT
change current schema
set search_path=SCHEMANAME ;
Print query aliases for sortcodes
This will work with \d
, \l
…
\SET ECHO_HIDDEN ON
Section: Listing Objects
list databases
psql
\l
\l+
SELECT datname FROM pg_database;
list tablespaces
\db
&
SELECT spcname FROM pg_tablespace;
list data directory PGDATA
SELECT setting FROM pg_settings WHERE name = 'data_directory';
list schemas
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;
list parameters
SELECT * FROM pg_settings ;
list users
SELECT * FROM pg_authid ;
\du
list domains
\dD
list ALL tables in database
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
list functions
\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...
Aggregate functions
\da
list data types
\dT
list triggers
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;
list objects in schema
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>
==== dbatabcolumns ====
inefficient but who cares
<code sql>
SELECT * from
(
SELECT tablecatalog DBNAME,
tableschema SCHEMANAME,
tablename,
columnname,
ordinalposition,
datatype,
charactermaximumlength,
columndefault,
isnullable
FROM informationschema.columns
order by ordinalposition
) as dbatabcolumns
WHERE COLUMN_NAME LIKE 'concession'
;
</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>
===== List column index =====
==== dbaindcolumns ====
<code sql>
SELECT
np.nspname AS schemaname,
t.relname AS tablename,
a.attname AS columnname,
i.relname AS indexname
FROM
pgclass t,
pgclass i,
pgindex ix,
pgattribute a,
pgnamespace 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 '%COLUMNNAME%'
AND i.relname like '%INDEXNAME%'
AND t.relname like '%TABLENAME%'
ORDER BY
t.relname,
a.attname;
</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' ;
Section: GRANTS
Column grants
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 ;
Routine grants
SELECT * FROM information_schema.role_routine_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;
Table grants
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 ;
Udt grants
SELECT * FROM information_schema.role_udt_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;
Usage grants
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+
Grant all on SCHEMANAME to all users
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$$;
Section: DBA
Sessions
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 ;
Long running queries
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';
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' ) ;
Change user password
ALTER USER "user_name" WITH PASSWORD 'new_password';
List extensions installed
SELECT * FROM pg_extension ;
List extensions available
SELECT * FROM pg_available_extensions ;
show version
SELECT version() ;
List Foreign keys (FK)
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;
Section: VACUUM
vacuum date by table
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables;
Section: Streaming Replication
Check if the database is in recovery mode
SELECT pg_is_in_recovery();
Current wall address
Master:
SELECT pg_current_wal_lsn();
Slave:
SELECT pg_last_wal_receive_lsn();
Wal apply lag (on the slave)
SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) ;
Status of the slaves (on master)
SELECT * FROM pg_stat_replication ;
Status of the slave (on the slave)
SELECT * FROM pg_stat_wal_receiver;
Section: Logical Replication
Status of the origin
SELECT * FROM pg_replication_origin_status;
Status of the subscriber
SELECT * FROM pg_stat_subscription;
list publications
SELECT * FROM pg_publication ;
list subscriptions
SELECT * FROM pg_subscription ;
list replication slots (master)
SELECT * FROM pg_replication_slots;
drop a replication slot (master)
SELECT pg_drop_replication_slot('subscription_name') ;
Section: Pgpool
queries
pool nodes
SHOW pool_nodes;
pool cache
SHOW pool_cache;
pcp commands
cluster info
pcp_watchdog_info -h $(hostname) -p 9898 -U pgpool --verbose -w
Recover node (postgre)
pcp_recovery_node -h $(hostname) -p 9898 -U pgpool -w -n <NODEID>
Node (postgre) information
pcp_node_info -h $(hostname) -p 9898 -U pgpool -w -v <NODEID>
Section: pgagent
List jobs
SELECT * FROM pgagent.pga_job ;
Less info:
SELECT jobid, jobname, jobdesc, jobenabled FROM pgagent.pga_job ;
List jobs executions
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' ;
Failed Job step logs
SELECT * FROM pgagent.pga_joblog WHERE jlgstart > now() - INTERVAL '10 days' AND jlgstatus <> 's' ;