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
\dn
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>
===== 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() ;
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' ;