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 <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;
Section: Space
Database size
SELECT pg_database_size('named_db'); SELECT pg_size_pretty(pg_database_size('named_db'));
or
\l+
ALL Databases size
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$$;
Space used for each table
\d+
Space used for each relation
SELECT pg_size_pretty(pg_total_relation_size('named_table'));
Section: objects information
Table description
\d+ tablename
list table columns
\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 ;
dba_tab_columns
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' ;
List table objects
SHOW INDEX FROM yourtable;
SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'your_schema';
List column index
dba_ind_columns
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 current schema
show search_path;
show object description
\dd
\dd [pattern]
show object source
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>