Pivotal Knowledge Base

Follow

Script - Object/Relation level Administration

Goal

Is to provide a simple scripts to help administrator in identifying relation level information .

Note : Please verify the script on a test cluster , before running it on production .

Solution

Relation Counts

-- Count of objects per schema.

SELECT 
    nspname as "Schema",
    count(*) as "# of Objects"
FROM
    pg_class a, pg_namespace b
WHERE
    a.relnamespace=b.oid
GROUP BY nspname
ORDER BY nspname;

-- Count of objects per schema group by type of relation.

SELECT 
    nspname as "Schema",
    case relkind when 'r' then 'Table'
		 when 'i' then 'Index'
		 when 'S' then 'Sequence'
		 when 't' then 'Toast Table'
		 when 'v' then 'View'
		 when 'c' then 'Composite Type'
		 when 'o' then 'Append-only Tables'
		 when 's' then 'Special'
    end "Object Type",
    count(*) as "# of Objects"
FROM 
    pg_class a, pg_namespace b
WHERE 
    a.relnamespace=b.oid
GROUP BY 
    rollup(nspname,relkind);

List of all user Index

SELECT
    t.relname as "Table Name",
    i.relname as "Index name",
    array_to_string(array_agg(a.attname), ', ') as "Index Columns",
    case ix.indisunique when 't' then 'Unique'
    else 'non-unique'
    end as "index Type"
FROM pg_class t, pg_class i, pg_index ix, pg_attribute a , pg_stat_user_indexes uix
WHERE
    t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND uix.indexrelid=ix.indexrelid
    AND a.attrelid = t.oid
    AND a.attnum = ANY(ix.indkey)
    AND t.relkind = 'r'
GROUP BY
    t.relname,i.relname,ix.indisunique
ORDER BY
    t.relname,i.relname;

List of Append-only Tables w.r.t to parent table.

SELECT 
   a.oid as "Table OID",
   b.nspname||'.'||a.relname as "Table Name",
   c.segrelid as "AO Table OID",
   (select 'pg_aoseg.'||relname from pg_class d where d.oid=c.segrelid) as "AO Table",
   c.segidxid as "AO Index OID",
   (select 'pg_aoseg.'||relname from pg_class d where d.oid=c.segidxid) as "AO Index",
   case c.columnstore when 'f' then 'Row Orientation' 
        	      when 't' then 'Column Orientation' 
   end as "AO Type",
   case COALESCE(c.compresstype,'') when '' then 'No Compression' 
        	      else c.compresstype
   end as "Compression Type"
FROM pg_class a,pg_namespace b,(select relid,segrelid,segidxid,columnstore,compresstype from pg_appendonly) c
WHERE b.oid=a.relnamespace
and a.oid=c.relid 
ORDER BY 4;

List of Toast Tables w.r.t to parent table.

SELECT 
   a.oid as "Table Oid" , 
   (select d.nspname||'.'||a.relname as "Table Name" from pg_namespace d where d.oid=a.relnamespace) as "Table Name", 
   b.relname as "Toast Table" , 
   b.oid as "Toast OID" , 
   c.relname as "Toast index", 
   c.oid as "Toast Index OID" 
FROM pg_class a , pg_class b , pg_class c 
WHERE a.reltoastrelid=b.oid 
and b.reltoastidxid=c.oid 
ORDER BY 4;

Relation Size/Usage

-- Check the size of a relation (Excluding Index)

SELECT pg_size_pretty(pg_relation_size('< Relation name >'));

-- Check the size of an object (Including Index)

SELECT pg_size_pretty(pg_total_relation_size('< Relation name >'));

-- Size / Total Size of objects in the database.

SELECT 
    b.nspname ||'.'|| a.relname AS "Table Name",
    case relkind when 'r' then 'Table'
                 when 'i' then 'Index'
    end "Object Type",
    pg_size_pretty(pg_relation_size(a.oid)) AS "Table Size",
    pg_size_pretty(pg_total_relation_size(a.oid)) AS "Total Size"
FROM pg_class a , pg_namespace b
WHERE 
    b.oid = a.relnamespace
AND relkind in ('r','i')
ORDER BY pg_relation_size(a.oid) DESC;

-- Top 5 User Objects (Excluding index) in descending order.

SELECT     
    relname "Object Name",
    nspname "Schema Name",
    case relkind when 'r' then 'Table'
                 when 'i' then 'Index'
                 when 'S' then 'Sequence'
                 when 't' then 'Toast Table'
                 when 'v' then 'View'
                 when 'c' then 'Composite Type'
                 when 'o' then 'Append-only Tables'
                 when 's' then 'Special'
    end "Object Type",
    pg_size_pretty(pg_relation_size(a.oid)) AS "size"
FROM
    pg_class a , pg_namespace b
WHERE
      b.oid = a.relnamespace
      AND nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(a.oid) DESC
LIMIT 5;

-- Top 5 User objects (including index) in descending order.

SELECT     
    relname "Object Name",
    nspname "Schema Name",
    case relkind when 'r' then 'Table'
                 when 'i' then 'Index'
                 when 'S' then 'Sequence'
                 when 't' then 'Toast Table'
                 when 'v' then 'View'
                 when 'c' then 'Composite Type'
                 when 'o' then 'Append-only Tables'
                 when 's' then 'Special'
    end "Object Type",
    pg_size_pretty(pg_total_relation_size(a.oid)) AS "size"
FROM
    pg_class a , pg_namespace b
WHERE
      b.oid = a.relnamespace
      and nspname NOT IN ('pg_catalog', 'information_schema')
and a.relkind!='i'
and b.nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(a.oid) DESC
LIMIT 5;

List of User permissions and privileges on the relation.

SELECT
   grantor as "Who Gave",
   grantee as "To Whom",
   table_catalog as "DB Name",
   table_schema ||'.'|| table_name as "TABLE NAME",
   privilege_type as "Privilege",
   is_grantable as "Is it grantable"
FROM information_schema.role_table_grants
ORDER BY 4;

Last ANALYZE on the given table

SELECT
    objid::regclass as Relation, 
    staactionname As Command, 
    Statime as "Time last analyzed"  
FROM 
    pg_stat_last_operation 
WHERE 
    objid='<Relation_name>'::regclass
    and staactionname='ANALYZE'
ORDER BY 3 desc 
LIMIT 1;

List of DDL operations on a given table

SELECT
    objid::regclass as Relation, 
    staactionname As Command, 
    Statime as "Time when executed"  
FROM 
    pg_stat_last_operation 
WHERE 
    objid='<Relation_name>'::regclass;

List of objects

select * from (
SELECT
c.oid as object_oid,
quote_ident(n.nspname) as Schema_name,
quote_ident(c.relname) as Object_Name,
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
WHEN 'c' THEN 'composite type'
END as object_Type,
pg_catalog.pg_get_userbyid(c.relowner) as object_Owner,
CASE c.relstorage
WHEN 'h' THEN 'heap'
WHEN 'x' THEN 'external'
WHEN 'a' THEN 'append only'
WHEN 'v' THEN 'none'
WHEN 'c' THEN 'append only columnar'
WHEN 'f' THEN 'foreign' END as object_Storage
,substring(array_to_string(reloptions, ',') from 'compresslevel=([0-9])') AS compresslevel,
substring(array_to_string(reloptions, ',') from 'compresstype=([a-z0-9])') AS compresstype,
coalesce(substring(array_to_string(reloptions, ',') from 'orientation=([a-z0-9]*)'),'row') AS orientation
,relacl as object_grants
FROM pg_catalog.pg_class c
inner JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
order by Schema_name
,Object_Name
object_oid | schema_name | object_name | object_type | object_owner | object_storage | compresslevel | compresstype | orientation | object_grants ------------+--------------------+----------------------------------------------------------------+----------------+--------------+----------------+---------------+--------------+-------------+--------------------------------------- 10699 | information_schema | administrable_role_authorizations | view | gpadmin | none | | | row | {gpadmin=arwdDxt/gpadmin,=r/gpadmin} 10696 | information_schema | applicable_roles | view | gpadmin | none | | | row | {gpadmin=arwdDxt/gpadmin,=r/gpadmin} 10702 | information_schema | attributes | view | gpadmin | none | | | row | {gpadmin=arwdDxt/gpadmin,=r/gpadmin} 9906 | pg_toast | pg_toast_9903_index | index | gpadmin | heap | | | row | 45914 | public | comp_ao | table | gpadmin | append only | | | row | 45884 | public | comp_quicklz | table | gpadmin | append only | | q | row | 45854 | public | comp_zlib | table | gpadmin | append only | | z | row | 28896 | public | t1 | table | gpadmin | heap | | | row |

Miscellaneous

Postgress/Pivotal Greenplum(GPDB) has many In-built function/shortcuts to get most of the information related to relation in the database.

For Example:

List of tables in the database.

gpadmin=# \d
                                        List of relations
 Schema |                      Name                      | Type  |  Owner  |       Storage        
--------+------------------------------------------------+-------+---------+----------------------
 public | a                                              | table | gpadmin | heap
 public | b                                              | table | gpadmin | heap

List of functions in the database.

gpadmin=# \df
                            List of functions
 Schema |     Name     | Result data type | Argument data types |  Type  
--------+--------------+------------------+---------------------+--------
 public | expl         | boolean          | q text              | normal
 public | test_create  | text             | tablename text      | normal
 public | test_create1 | void             | tablename text      | normal
(3 rows)

You can also wildcard search to get the relevant information

To list tables starting with process

gpadmin=# \d process*
           Table "public.process_err"
  Column  |           Type           | Modifiers 
----------+--------------------------+-----------
 cmdtime  | timestamp with time zone | 
 relname  | text                     | 
 filename | text                     | 
 linenum  | integer                  | 
 bytenum  | integer                  | 
 errmsg   | text                     | 
 rawdata  | text                     | 
 rawbytes | bytea                    | 
Distributed randomly 

To list all the tables with employee in any schema.

gpadmin=# \dt *.employee
               List of relations
 Schema |   Name   | Type  |  Owner  | Storage 
--------+----------+-------+---------+---------
 baby   | employee | table | gpadmin | heap
 base   | employee | table | gpadmin | heap

a quick way to check the list of shortcut available is to use help feature which is retrieved by

    \?

Some of the shortcut to get general useful information from the database are

( please note : This is specific to the database , you must be connected to the database to retrieve the information you are looking for)

Informational (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \db[+]  [PATTERN]      list tablespaces
  \dc[S]  [PATTERN]      list conversions
  \dC     [PATTERN]      list casts
  \dd[S]  [PATTERN]      show comments on objects
  \ddp    [PATTERN]      list default privileges
  \dD[S]  [PATTERN]      list domains
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions
  \dF[+]  [PATTERN]      list text search configurations
  \dFd[+] [PATTERN]      list text search dictionaries
  \dFp[+] [PATTERN]      list text search parsers
  \dFt[+] [PATTERN]      list text search templates
  \dg[+]  [PATTERN]      list roles (groups)
  \di[S+] [PATTERN]      list indexes
  \dl                    list large objects, same as \lo_list
  \dn[+]  [PATTERN]      list schemas
  \do[S]  [PATTERN]      list operators
  \dp     [PATTERN]      list table, view, and sequence access privileges
  \dr[S+] [PATTERN]      list foreign tables
  \drds [PATRN1 [PATRN2]] list per-database role settings
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types
  \du[+]  [PATTERN]      list roles (users)
  \dv[S+] [PATTERN]      list views
  \dx     [PATTERN]      list external tables
  \l[+]                  list all databases
  \z      [PATTERN]      same as \dp

Comments

Powered by Zendesk