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 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 grantor NOT LIKE 'postgres' ;

Routine grants

SELECT * FROM information_schema.role_routine_grants WHERE grantor NOT LIKE 'postgres' ;

Table grants

SELECT * FROM information_schema.role_table_grants WHERE grantor NOT LIKE 'postgres' ;

Udt grants

SELECT * FROM information_schema.role_udt_grants WHERE grantor NOT LIKE 'postgres' ;

Usage grants

SELECT * FROM information_schema.role_usage_grants WHERE grantor NOT LIKE 'postgres' ;

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: 2015/03/19 13:12 by dodger