Basic querys for PostgreSQL administration

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

psql

\g

\i somedir/script2.sql

psql

\h
\?

http://www.postgresql.org/docs/7.4/static/app-psql.html

psql

\l

\l+

\db

&

SELECT spcname FROM pg_tablespace;

\dn

SELECT * FROM pg_settings ;

SELECT * FROM pg_authid  ;
SELECT * FROM pg_user ;

\du

\dD

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

psql

\d

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

\d+ "table_name"

\da

\dd

\dd [pattern]

\dT

\da

GRANT SYNTAX

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

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

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 ;

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

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

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;

• Link