====== [DOC] PostgreSQL basic queries ======
====== Section: Description ======
Basic querys for PostgreSQL administration
===== psql documentation =====
[[https://www.postgresql.org/docs/current/static/app-psql.html|Official "app" documentation]] (list of ''\aliases'')
====== Section: Usage ======
===== Change pager =====
export PAGER=less
Permanent:
cat >> ~/.bash_profile<
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
[[http://www.postgresql.org/docs/8.0/static/catalog-pg-class.html|link]]
===== 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 '''' :
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 ()
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 ''FUNCTION_NAME'':
select prosrc from pg_proc where proname='FUNCTION_NAME' ;
====== Section: GRANTS ======
[[http://www.postgresql.org/docs/current/static/sql-grant.html|GRANT SYNTAX]]
===== 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 TO ' || r.rolname;
EXECUTE 'GRANT INSERT, DELETE, UPDATE, SELECT ON ALL TABLES IN SCHEMA 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
;
* [[http://www.chrismiles.info/systemsadmin/databases/articles/viewing-current-postgresql-queries/|Link]]
===== 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 =====
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 [[https://wiki.postgresql.org/wiki/Lock_Monitoring|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;
===== Change user password =====
ALTER USER "user_name" WITH PASSWORD 'new_password';
===== dump/export all users with password =====
pg_dumpall --globals-only --file=all_roles_and_users.sql -h 127.0.0.1
===== List extensions installed =====
select * from pg_extension ;
===== List extensions available =====
SELECT * FROM pg_available_extensions ;
===== show version=====
select version() ;
===== List Foreign keys (FK) =====
From [[https://dataedo.com/kb/query/postgresql/list-foreign-keys|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
==== Node (postgre) information ====
pcp_node_info -h $(hostname) -p 9898 -U pgpool -w -v
====== 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' ;
====== Section: XML (tables) ======
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
;
====== Section: EMPTY ======