User Tools

Site Tools


basic_postgre_sql_querys

Section: Description

Basic querys for PostgreSQL administration

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
\?

Section: Main information

psql documentation

list databases

psql
\l
\l+

list tablespaces

\db

&

SELECT spcname FROM pg_tablespace;

list schemas

\dn

list parameters

SELECT * FROM pg_settings ;

list users

SELECT * FROM pg_authid  ;
SELECT * FROM pg_user ;
\du

list domains

\dD

pg internal tables/views

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')
;

Where:

  • r = regular table
  • v = view

link

Section: objects information

list tables in database

psql

\d

list columns in table

\d+ "table_name"

list functions

\da

list object description

\dd
\dd [pattern]

list data types

\dT

list functions

\da

Section: GRANTS

Column grants

SELECT * FROM information_schema.role_column_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;

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' ) ;

Section: Sesiones

Listar sesiones

SELECT pid,
        usename, 
--         application_name, 
        client_addr, 
        backend_start, 
--         xact_start, 
        now()-query_start Query_duration, 
--         state_change, 
        waiting, 
        state, 
        query
FROM pg_stat_activity;

Section: GRANTS

Section: GRANTS

Section: GRANTS

 










basic_postgre_sql_querys.txt · Last modified: 2017/08/14 09:53 by dodger