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
\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>
===== 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 =====
aka dbaind_columns
SELECT 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 WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) AND t.relkind = 'r' 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 =====
<code sql>
SELECT psa.datid,
psa.datname,
psa.pid,
psa.usename,
psa.clientaddr,
psa.applicationname,
pl.mode,
psa.query
FROM pgstatactivity psa, pglocks pl
WHERE datname <>'postgres'
and pl.pid=psa.pid
and pl.mode in ('ExclusiveLock', 'RowExclusiveLock', 'ShareUpdateExclusiveLock' )
;
</code>
===== Change user password =====
<code sql>
ALTER USER “username” WITH PASSWORD 'new_password';
</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>