dba:postgresql:postgresql_basic_querys
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 <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 ;
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
aka dba_ind_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 FUNCTION_NAME
:
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' ;
Section: EMPTY
dba/postgresql/postgresql_basic_querys.txt · Last modified: 2023/09/20 10:08 by dodger