postgresql:basic_postgre_sql_querys
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | |||
postgresql:basic_postgre_sql_querys [2018/05/11 15:30] – dodger | postgresql:basic_postgre_sql_querys [2018/05/11 15:43] (current) – removed dodger | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== [DOC] PostgreSQL basic queries (1) ====== | ||
- | ====== Section: Description ====== | ||
- | Basic querys for PostgreSQL administration | ||
- | |||
- | |||
- | ====== Section: Usage ====== | ||
- | ===== Change pager ===== | ||
- | < | ||
- | export PAGER=less | ||
- | </ | ||
- | Permanent: | ||
- | < | ||
- | cat >> ~/ | ||
- | 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 " | ||
- | </ | ||
- | |||
- | ===== execute query ===== | ||
- | psql | ||
- | < | ||
- | \g | ||
- | </ | ||
- | |||
- | ==== Execute sql script ==== | ||
- | < | ||
- | \i somedir/ | ||
- | </ | ||
- | ===== help ===== | ||
- | psql | ||
- | < | ||
- | \h | ||
- | \? | ||
- | </ | ||
- | |||
- | |||
- | |||
- | |||
- | ====== Section: Main information ====== | ||
- | ===== psql documentation ===== | ||
- | [[http:// | ||
- | |||
- | ===== list databases ===== | ||
- | < | ||
- | < | ||
- | \l | ||
- | </ | ||
- | < | ||
- | \l+ | ||
- | </ | ||
- | |||
- | ===== list tablespaces ===== | ||
- | < | ||
- | \db | ||
- | </ | ||
- | & | ||
- | <code sql> | ||
- | SELECT spcname FROM pg_tablespace; | ||
- | </ | ||
- | |||
- | |||
- | ===== list schemas ===== | ||
- | < | ||
- | \dn | ||
- | </ | ||
- | |||
- | ===== list parameters ===== | ||
- | <code sql> | ||
- | select * from pg_settings ; | ||
- | </ | ||
- | |||
- | ===== list users ===== | ||
- | <code sql> | ||
- | select * from pg_authid | ||
- | select * from pg_user ; | ||
- | </ | ||
- | <code sql> | ||
- | \du | ||
- | </ | ||
- | ===== list domains ===== | ||
- | <code sql> | ||
- | \dD | ||
- | </ | ||
- | |||
- | |||
- | |||
- | ===== pg internal tables/ | ||
- | <code sql> | ||
- | 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 (' | ||
- | ; | ||
- | </ | ||
- | Where: | ||
- | * r = regular table | ||
- | * v = view | ||
- | [[http:// | ||
- | |||
- | |||
- | |||
- | ====== Section: objects information ====== | ||
- | |||
- | ===== list tables in database ===== | ||
- | psql | ||
- | < | ||
- | \d | ||
- | </ | ||
- | |||
- | <code sql> | ||
- | 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 (' | ||
- | ; | ||
- | </ | ||
- | ===== list columns in table ===== | ||
- | |||
- | < | ||
- | \d+ " | ||
- | </ | ||
- | |||
- | ===== list functions ===== | ||
- | <code sql> | ||
- | \da | ||
- | </ | ||
- | |||
- | |||
- | ===== list object description ===== | ||
- | <code sql> | ||
- | \dd | ||
- | </ | ||
- | <code sql> | ||
- | \dd [pattern] | ||
- | </ | ||
- | |||
- | |||
- | ===== list data types ===== | ||
- | <code sql> | ||
- | \dT | ||
- | </ | ||
- | |||
- | |||
- | ===== list functions ===== | ||
- | <code sql> | ||
- | \da | ||
- | </ | ||
- | |||
- | |||
- | ====== Section: GRANTS ====== | ||
- | [[http:// | ||
- | ===== Column grants ===== | ||
- | <code sql> | ||
- | SELECT * FROM information_schema.role_column_grants where grantee not in (' | ||
- | </ | ||
- | ===== Routine grants ===== | ||
- | <code sql> | ||
- | SELECT * FROM information_schema.role_routine_grants where grantee not in (' | ||
- | </ | ||
- | ===== Table grants ===== | ||
- | <code sql> | ||
- | SELECT * FROM information_schema.role_table_grants where grantee not in (' | ||
- | </ | ||
- | |||
- | |||
- | <code sql> | ||
- | select * from (select grantee, table_schema, | ||
- | </ | ||
- | |||
- | |||
- | ===== Udt grants ===== | ||
- | <code sql> | ||
- | SELECT * FROM information_schema.role_udt_grants where grantee not in (' | ||
- | </ | ||
- | ===== Usage grants ===== | ||
- | <code sql> | ||
- | SELECT * FROM information_schema.role_usage_grants where grantee not in (' | ||
- | </ | ||
- | |||
- | |||
- | ====== Section: Sesiones ====== | ||
- | ===== Listar sesiones ===== | ||
- | |||
- | <code sql> | ||
- | usename, | ||
- | -- | ||
- | client_addr, | ||
- | backend_start, | ||
- | -- | ||
- | now()-query_start Query_duration, | ||
- | -- | ||
- | waiting, | ||
- | state, | ||
- | query | ||
- | from pg_stat_activity; | ||
- | </ | ||
- | |||
- | |||
- | * [[http:// | ||
- | |||
- | |||
- | ====== Section: GRANTS ====== | ||
- | ====== Section: GRANTS ====== | ||
- | ====== Section: GRANTS ====== | ||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | <code sql> | ||
- | </ | ||
- | |||
- | |||
- | < | ||
- | </ | ||
- | < | ||
- | </ | ||
- | < | ||
- | </ | ||
- | < | ||
- | </ | ||
- | < | ||
- | </ | ||
- | < | ||
- | </ | ||
- | < | ||
- | </ | ||
- | < | ||
- | </ | ||
- | < | ||
- | </ |
postgresql/basic_postgre_sql_querys.1526052606.txt.gz · Last modified: 2018/05/11 15:30 by dodger