PostgreSQL : Part 2

Login to the PostgresSQL command-line interface

    psql -U [username] [database_name]

***********************************************************************
To list all databases
> \l

To list all tables
> \dt

To list all indexs
> \di

To list all sequences
> \ds

To list all views
> \dv

To list all privileges
> \dp

To list all large objects
> \dl

To list all aggregates
> \da

To list all functions
> \df

To list all operators
> \do

To list all types
> \dT

To show all tables, views and sequences.
> \dS+

To show all schema.
> \dn

To show all tables for all schema.
> \dt *.

To show all tables for a schema.
> \dt [schema_name].

To show a table definition under a schema.
> \dt [schema_name].[table_name]

To exit from psql command prompt
> \q

To get the list of the available tables of a database in PostgreSQL.

> SELECT tablename FROM pg_tables
     WHERE tablename NOT LIKE ‘pg\\_%’
     AND tablename NOT LIKE ‘sql\\_%’;

To get the list of columns for a particular table in that database.
> SELECT attname FROM pg_attribute, pg_type
      WHERE typname = ‘table_name‘
      AND attrelid = typrelid
      AND attname NOT IN (‘cmin’, ‘cmax’, ‘ctid’, ‘oid’, ‘tableoid’, ‘xmin’, ‘xmax’);

To view table/row locks in PostgreSQL
> SELECT t.relname,l.locktype,page,virtualtransaction,pid,mode,granted 
      FROM pg_locks l, pg_stat_all_tables t WHERE l.relation=t.relid order by relation asc;

To show all enum label for an enum table.
> SELECT e.enumlabel FROM pg_enum e JOIN pg_type t ON e.enumtypid = t.oid WHERE t.typname = '[enum_table_name]';

How to find tables without primary keys.
> SELECT table_catalog, table_schema, table_name
     FROM information_schema.tables
     WHERE (table_catalog, table_schema, table_name) NOT IN
     (SELECT table_catalog, table_schema, table_name
     FROM information_schema.table_constraints
     WHERE constraint_type = 'PRIMARY KEY')
     AND table_schema NOT IN ('information_schema', 'pg_catalog');

How to find postgreSQL transaction in idle.

> SELECT procpid, datname, usename, client_addr,  current_query 
     FROM pg_stat_activity WHERE current_query!='<IDLE>';
> SELECT pg_cancel_backend (procpid);

To find how long locks have been active by executing the following query:

select
pg_stat_activity.datname,
pg_class.relname,
pg_locks.transactionid,
pg_locks.mode,
pg_locks.granted,
pg_stat_activity.usename,
substr(pg_stat_activity.current_query,1,40) as current_query,
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid
from pg_stat_activity,pg_locks
left outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid and usename<>'postgres' order by query_start;

How to check postgreSQL errors.
Important: All postgresql errors are logged to /var/lib/pgsql/9.1/data/pg_log/ .