Pre/post Schema validation report before/after schema backup/restore

select * from pg_roles where NOT rolcanlogin order by 1;

select * from pg_roles where  rolcanlogin order by 1;

select rarolename,ramembername from gp_toolkit.gp_roles_assigned where ramembername is NOT NULL order by 1;

select tableowner,schemaname,count(*) from pg_tables group by 1,2 order by 1,3;

select * from pg_tables limit 10;

select * from pg_appendonly;

select blkdirrelid,blkdiridxid,count(*) from pg_appendonly group by 1,2

select * from

(

SELECT b.obj_name, b.obj_type, b.grantee, b.grantor,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%r%'::text THEN true

                ELSE false

            END

        END AS select_priv,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%a%'::text THEN true

                ELSE false

            END

        END AS insert_priv,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%w%'::text THEN true

                ELSE false

            END

        END AS update_priv,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%d%'::text THEN true

                ELSE false

            END

        END AS delete_priv,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%x%'::text THEN true

                ELSE false

            END

        END AS reference_priv,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%t%'::text THEN true

                ELSE false

            END

        END AS trigger_priv,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%X%'::text THEN true

                ELSE false

            END

        END AS execute_priv

  FROM ( SELECT a.obj_name, a.obj_type, split_part(a.priv, '='::text, 1) AS grantee, split_part(split_part(a.priv, '='::text, 2), '/'::text, 2) AS grantor, split_part(split_part(a.priv, '='::text, 2), '/'::text, 1) AS acc

           FROM ( SELECT (n.nspname::text || '.'::text) || c.relname::text AS obj_name,

                        CASE c.relkind

                            WHEN 'r'::"char" THEN

                            CASE

                                WHEN c.relstorage = 'x'::"char" THEN 'external table'::text

                                ELSE 'table'::text

                            END

                            WHEN 'v'::"char" THEN 'view'::text

                            WHEN 'S'::"char" THEN 'sequence'::text

                            ELSE NULL::text

                        END AS obj_type, privs.priv

                   FROM pg_class c

              LEFT JOIN ( SELECT pg_class.oid, regexp_split_to_table(array_to_string(pg_class.relacl, ','::text), ','::text) AS priv

                           FROM pg_class) privs ON privs.oid = c.oid

         LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

        WHERE c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'S'::"char"])

        UNION ALL

                 SELECT (n.nspname::text || '.'::text) || p.proname::text AS obj_name, 'function' AS obj_type, privs.priv

                   FROM pg_proc p

              LEFT JOIN ( SELECT pg_proc.oid, regexp_split_to_table(array_to_string(pg_proc.proacl, ','::text), ','::text) AS priv

                           FROM pg_proc) privs ON privs.oid = p.oid

        LEFT JOIN pg_namespace n ON n.oid = p.pronamespace) a) b) c where grantee='r_rrp_etl_1';

 

select grantor,grantee,obj_type,

count(case when select_priv = 't' then 1 end),

count(case when insert_priv = 't' then 1 end),

count(case when update_priv = 't' then 1 end),

count(case when delete_priv = 't' then 1 end),

count(case when reference_priv = 't' then 1 end),

count(case when trigger_priv = 't' then 1 end),

count(case when execute_priv = 't' then 1 end)

from

(

SELECT b.obj_name, b.obj_type, b.grantee, b.grantor,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%r%'::text THEN true

                ELSE false

            END

        END AS select_priv,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%a%'::text THEN true

                ELSE false

            END

        END AS insert_priv,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%w%'::text THEN true

                ELSE false

            END

        END AS update_priv,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%d%'::text THEN true

                ELSE false

            END

        END AS delete_priv,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%x%'::text THEN true

                ELSE false

            END

        END AS reference_priv,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%t%'::text THEN true

                ELSE false

            END

        END AS trigger_priv,

        CASE

            WHEN b.grantee IS NULL THEN NULL::boolean

            ELSE

            CASE

                WHEN b.acc ~~ '%X%'::text THEN true

                ELSE false

            END

        END AS execute_priv

  FROM ( SELECT a.obj_name, a.obj_type, split_part(a.priv, '='::text, 1) AS grantee, split_part(split_part(a.priv, '='::text, 2), '/'::text, 2) AS grantor, split_part(split_part(a.priv, '='::text, 2), '/'::text, 1) AS acc

           FROM ( SELECT (n.nspname::text || '.'::text) || c.relname::text AS obj_name,

                        CASE c.relkind

                            WHEN 'r'::"char" THEN

                            CASE

                                WHEN c.relstorage = 'x'::"char" THEN 'external table'::text

                                ELSE 'table'::text

                            END

                            WHEN 'v'::"char" THEN 'view'::text

                            WHEN 'S'::"char" THEN 'sequence'::text

                            ELSE NULL::text

                        END AS obj_type, privs.priv

                   FROM pg_class c

              LEFT JOIN ( SELECT pg_class.oid, regexp_split_to_table(array_to_string(pg_class.relacl, ','::text), ','::text) AS priv

                           FROM pg_class) privs ON privs.oid = c.oid

         LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

        WHERE c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'S'::"char"])

        UNION ALL

                 SELECT (n.nspname::text || '.'::text) || p.proname::text AS obj_name, 'function' AS obj_type, privs.priv

                   FROM pg_proc p

              LEFT JOIN ( SELECT pg_proc.oid, regexp_split_to_table(array_to_string(pg_proc.proacl, ','::text), ','::text) AS priv

                           FROM pg_proc) privs ON privs.oid = p.oid

         LEFT JOIN pg_namespace n ON n.oid = p.pronamespace) a) b) c where grantee is NOT NULL group by grantee,grantor,obj_type order by 4 desc,1,2;

 

select * from pg_class limit 1;       

 

select owner,object_type,object_schema, count(*) from

(

select nsp.nspname as object_schema,

       cls.relname as object_name,

       rol.rolname as owner,

       case cls.relkind

         when 'r' then 'TABLE'

         when 'i' then 'INDEX'

         when 'S' then 'SEQUENCE'

         when 'v' then 'VIEW'

         when 'c' then 'COMPOSITE TYPE'

         when 'o' then 'Append-Only Tables'

         when 's' then 'Special'

         else cls.relkind::text

       end as object_type

from pg_class cls

  join pg_roles rol on rol.oid = cls.relowner

  join pg_namespace nsp on nsp.oid = cls.relnamespace

where nsp.nspname not in ('information_schema', 'pg_catalog')

  and nsp.nspname not like 'pg_toast%') as a

  group by 1,2,3

  order by 1,4 desc,3 ;

1. Get the count of schema objects.

sachi=# 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;

 Schema                  | # of Objects 

--------------------           +--------------

 dbadmin                  |  8

 gp_toolkit                |  45

 information_schema |  55

 pg_aoseg                |  216

 pg_catalog              |  240

 pg_toast                 |  628

 public                     |  753

 sachi                      |  277

 sachi2014               |  277

(9 rows)

2. Get the count of schema objects by object type.

sachi=#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);

Schema                    |    Object Type     | # of Objects 

--------------------           +--------------------+--------------

 dbadmin                  | Table              |      1

 dbadmin                  | View               |      7

 dbadmin                  |                       |      8

 gp_toolkit                | Composite Type     |            3

 gp_toolkit                | Table              |     5

 gp_toolkit                | View               |     37

 gp_toolkit                |                       |     45

 information_schema | Table              |      7

 information_schema | View               |     48

 information_schema |                       |     55

 pg_aoseg                | Index              |     108

 pg_aoseg                | Append-only Tables |          108

 pg_aoseg                |                    |          216

 pg_catalog              | Composite Type     |            1

 pg_catalog              | Index              |          116

 pg_catalog              | Table              |           76

 pg_catalog              | View               |           47

 pg_catalog              |                    |          240

 pg_toast                 | Index              |          314

 pg_toast                 | Toast Table        |          314

 pg_toast                 |                    |          628

 public                     | Index              |            1

 public                     | Table              |          745

 public                     | View               |            7

 public                     |                    |          753

 sachi                      | Index              |            4

 sachi                      | Table              |          273

 sachi                      |                    |          277

 sachi2014               | Index              |            4

 sachi2014               | Table              |          273

 sachi2014               |                    |          277

                               |                    |         2499

(32 rows)

3. Get information about table indexes

sachi=# SELECT 

a.schemaname || '.' || a.relname as "Table name",

a.indexrelname as "Index Name",

case b.indisunique when 't' then 'Unique'

else 'non-unquie'

end as "index Type"

FROM pg_stat_user_indexes a , pg_index b

WHERE a.indexrelid=b.indexrelid;

     Table name         |    Index Name    | index Type 

---------------------+------------------+------------

 sachi.abc                | abc_id           | non-unquie

 sachi.films              | films_pkey       | Unique

 public.abc               | index_abc        | non-unquie

 sachi.abc                | index_abc        | non-unquie

 sachi.bloattest         | idx_bloattest_id | non-unquie

 sachi2014.films        | films_pkey       | Unique

 sachi2014.abc         | abc_id           | non-unquie

 sachi2014.bloattest  | idx_bloattest_id | non-unquie

 sachi2014.abc         | index_abc        | non-unquie

(9 rows)

4. Append Only Tables/partitions

sachi=# 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"

FROM pg_class a,pg_namespace b,(select relid,segrelid,segidxid,columnstore from pg_appendonly) c

WHERE b.oid=a.relnamespace

and a.oid=c.relid

ORDER BY 4;

Table OID | Table Name | AO Table OID | AO Table | AO Index OID | AO Index | AO Type

-----------+---------------------------------+--------------+----------------------------+--------------+----------------------------------+-----------------

---

151156 | sachi2014.cms_qlz | 151161 | pg_aoseg.pg_aocsseg_151156 | 151162 | pg_aoseg.pg_aocsseg_151156_index | Column Orientati

on

151181 | sachi2014.cms_qlz_1_prt_p1 | 151186 | pg_aoseg.pg_aocsseg_151181 | 151187 | pg_aoseg.pg_aocsseg_151181_index | Column Orientati

on

151207 | sachi2014.cms_qlz_1_prt_p2 | 151212 | pg_aoseg.pg_aocsseg_151207 | 151213 | pg_aoseg.pg_aocsseg_151207_index | Column Orientati

on

151233 | sachi2014.cms_qlz_1_prt_p3 | 151238 | pg_aoseg.pg_aocsseg_151233 | 151239 | pg_aoseg.pg_aocsseg_151233_index | Column Orientati

on

151259 | sachi2014.cms_qlz_1_prt_p4 | 151264 | pg_aoseg.pg_aocsseg_151259 | 151265 | pg_aoseg.pg_aocsseg_151259_index | Column Orientati

on

151285 | sachi2014.cms_qlz_1_prt_p5 | 151290 | pg_aoseg.pg_aocsseg_151285 | 151291 | pg_aoseg.pg_aocsseg_151285_index | Column Orientati

on

151311 | sachi2014.cms_qlz_1_prt_p6 | 151316 | pg_aoseg.pg_aocsseg_151311 | 151317 | pg_aoseg.pg_aocsseg_151311_index | Column Orientati

on

151337 | sachi2014.cms_qlz_1_prt_p7 | 151342 | pg_aoseg.pg_aocsseg_151337 | 151343 | pg_aoseg.pg_aocsseg_151337_index | Column Orientati

on

151363 | sachi2014.cms_qlz_1_prt_p8 | 151368 | pg_aoseg.pg_aocsseg_151363 | 151369 | pg_aoseg.pg_aocsseg_151363_index | Column Orientati

on

151389 | sachi2014.cms_qlz_1_prt_p9 | 151394 | pg_aoseg.pg_aocsseg_151389 | 151395 | pg_aoseg.pg_aocsseg_151389_index | Column Orientati

on

151415 | sachi2014.cms_qlz_1_prt_p10 | 151420 | pg_aoseg.pg_aocsseg_151415 | 151421 | pg_aoseg.pg_aocsseg_151415_index | Column Orientati

on

151441 | sachi2014.cms_qlz_1_prt_p11 | 151446 | pg_aoseg.pg_aocsseg_151441 | 151447 | pg_aoseg.pg_aocsseg_151441_index | Column Orientati

on

151467 | sachi2014.cms_qlz_1_prt_p12 | 151472 | pg_aoseg.pg_aocsseg_151467 | 151473 | pg_aoseg.pg_aocsseg_151467_index | Column Orientati

on

151493 | sachi2014.cms_qlz_1_prt_p13 | 151498 | pg_aoseg.pg_aocsseg_151493 | 151499 | pg_aoseg.pg_aocsseg_151493_index | Column Orientati

on

151519 | sachi2014.cms_qlz_1_prt_p14 | 151524 | pg_aoseg.pg_aocsseg_151519 | 151525 | pg_aoseg.pg_aocsseg_151519_index | Column Orientati

on

151545 | sachi2014.cms_qlz_1_prt_p15 | 151550 | pg_aoseg.pg_aocsseg_151545 | 151551 | pg_aoseg.pg_aocsseg_151545_index | Column Orientati

on

151571 | sachi2014.cms_qlz_1_prt_p16 | 151576 | pg_aoseg.pg_aocsseg_151571 | 151577 | pg_aoseg.pg_aocsseg_151571_index | Column Orientati

on

151597 | sachi2014.cms_qlz_1_prt_other | 151602 | pg_aoseg.pg_aocsseg_151597 | 151603 | pg_aoseg.pg_aocsseg_151597_index | Column Orientati

on

151838 | sachi2014.cms_zlib | 151843 | pg_aoseg.pg_aocsseg_151838 | 151844 | pg_aoseg.pg_aocsseg_151838_index | Column Orientati

on

151863 | sachi2014.cms_zlib_1_prt_p1 | 151868 | pg_aoseg.pg_aocsseg_151863 | 151869 | pg_aoseg.pg_aocsseg_151863_index | Column Orientati

on

151889 | sachi2014.cms_zlib_1_prt_p2 | 151894 | pg_aoseg.pg_aocsseg_151889 | 151895 | pg_aoseg.pg_aocsseg_151889_index | Column Orientati

on

151915 | sachi2014.cms_zlib_1_prt_p3 | 151920 | pg_aoseg.pg_aocsseg_151915 | 151921 | pg_aoseg.pg_aocsseg_151915_index | Column Orientati

on

151941 | sachi2014.cms_zlib_1_prt_p4 | 151946 | pg_aoseg.pg_aocsseg_151941 | 151947 | pg_aoseg.pg_aocsseg_151941_index | Column Orientati

on

151967 | sachi2014.cms_zlib_1_prt_p5 | 151972 | pg_aoseg.pg_aocsseg_151967 | 151973 | pg_aoseg.pg_aocsseg_151967_index | Column Orientati

on

151993 | sachi2014.cms_zlib_1_prt_p6 | 151998 | pg_aoseg.pg_aocsseg_151993 | 151999 | pg_aoseg.pg_aocsseg_151993_index | Column Orientati

on

152019 | sachi2014.cms_zlib_1_prt_p7 | 152024 | pg_aoseg.pg_aocsseg_152019 | 152025 | pg_aoseg.pg_aocsseg_152019_index | Column Orientati

on

152045 | sachi2014.cms_zlib_1_prt_p8 | 152050 | pg_aoseg.pg_aocsseg_152045 | 152051 | pg_aoseg.pg_aocsseg_152045_index | Column Orientati

on

152071 | sachi2014.cms_zlib_1_prt_p9 | 152076 | pg_aoseg.pg_aocsseg_152071 | 152077 | pg_aoseg.pg_aocsseg_152071_index | Column Orientati

on

152097 | sachi2014.cms_zlib_1_prt_p10 | 152102 | pg_aoseg.pg_aocsseg_152097 | 152103 | pg_aoseg.pg_aocsseg_152097_index | Column Orientati

on

152123 | sachi2014.cms_zlib_1_prt_p11 | 152128 | pg_aoseg.pg_aocsseg_152123 | 152129 | pg_aoseg.pg_aocsseg_152123_index | Column Orientati

on

152149 | sachi2014.cms_zlib_1_prt_p12 | 152154 | pg_aoseg.pg_aocsseg_152149 | 152155 | pg_aoseg.pg_aocsseg_152149_index | Column Orientati

on

152175 | sachi2014.cms_zlib_1_prt_p13 | 152180 | pg_aoseg.pg_aocsseg_152175 | 152181 | pg_aoseg.pg_aocsseg_152175_index | Column Orientati

on

152201 | sachi2014.cms_zlib_1_prt_p14 | 152206 | pg_aoseg.pg_aocsseg_152201 | 152207 | pg_aoseg.pg_aocsseg_152201_index | Column Orientati

on

152227 | sachi2014.cms_zlib_1_prt_p15 | 152232 | pg_aoseg.pg_aocsseg_152227 | 152233 | pg_aoseg.pg_aocsseg_152227_index | Column Orientati

on

152253 | sachi2014.cms_zlib_1_prt_p16 | 152258 | pg_aoseg.pg_aocsseg_152253 | 152259 | pg_aoseg.pg_aocsseg_152253_index | Column Orientati

on

152279 | sachi2014.cms_zlib_1_prt_other | 152284 | pg_aoseg.pg_aocsseg_152279 | 152285 | pg_aoseg.pg_aocsseg_152279_index | Column Orientati

on

152520 | sachi2014.cms_zlib9 | 152525 | pg_aoseg.pg_aocsseg_152520 | 152526 | pg_aoseg.pg_aocsseg_152520_index | Column Orientati

on

152545 | sachi2014.cms_zlib9_1_prt_p1 | 152550 | pg_aoseg.pg_aocsseg_152545 | 152551 | pg_aoseg.pg_aocsseg_152545_index | Column Orientati

on

152571 | sachi2014.cms_zlib9_1_prt_p2 | 152576 | pg_aoseg.pg_aocsseg_152571 | 152577 | pg_aoseg.pg_aocsseg_152571_index | Column Orientati

on

152597 | sachi2014.cms_zlib9_1_prt_p3 | 152602 | pg_aoseg.pg_aocsseg_152597 | 152603 | pg_aoseg.pg_aocsseg_152597_index | Column Orientati

on

152623 | sachi2014.cms_zlib9_1_prt_p4 | 152628 | pg_aoseg.pg_aocsseg_152623 | 152629 | pg_aoseg.pg_aocsseg_152623_index | Column Orientati

on

152649 | sachi2014.cms_zlib9_1_prt_p5 | 152654 | pg_aoseg.pg_aocsseg_152649 | 152655 | pg_aoseg.pg_aocsseg_152649_index | Column Orientati

on

152675 | sachi2014.cms_zlib9_1_prt_p6 | 152680 | pg_aoseg.pg_aocsseg_152675 | 152681 | pg_aoseg.pg_aocsseg_152675_index | Column Orientati

on

152701 | sachi2014.cms_zlib9_1_prt_p7 | 152706 | pg_aoseg.pg_aocsseg_152701 | 152707 | pg_aoseg.pg_aocsseg_152701_index | Column Orientati

on

152727 | sachi2014.cms_zlib9_1_prt_p8 | 152732 | pg_aoseg.pg_aocsseg_152727 | 152733 | pg_aoseg.pg_aocsseg_152727_index | Column Orientati

on

152753 | sachi2014.cms_zlib9_1_prt_p9 | 152758 | pg_aoseg.pg_aocsseg_152753 | 152759 | pg_aoseg.pg_aocsseg_152753_index | Column Orientati

on

152779 | sachi2014.cms_zlib9_1_prt_p10 | 152784 | pg_aoseg.pg_aocsseg_152779 | 152785 | pg_aoseg.pg_aocsseg_152779_index | Column Orientati

on

152805 | sachi2014.cms_zlib9_1_prt_p11 | 152810 | pg_aoseg.pg_aocsseg_152805 | 152811 | pg_aoseg.pg_aocsseg_152805_index | Column Orientati

on

152831 | sachi2014.cms_zlib9_1_prt_p12 | 152836 | pg_aoseg.pg_aocsseg_152831 | 152837 | pg_aoseg.pg_aocsseg_152831_index | Column Orientati

on

152857 | sachi2014.cms_zlib9_1_prt_p13 | 152862 | pg_aoseg.pg_aocsseg_152857 | 152863 | pg_aoseg.pg_aocsseg_152857_index | Column Orientati

on

152883 | sachi2014.cms_zlib9_1_prt_p14 | 152888 | pg_aoseg.pg_aocsseg_152883 | 152889 | pg_aoseg.pg_aocsseg_152883_index | Column Orientati

on

152909 | sachi2014.cms_zlib9_1_prt_p15 | 152914 | pg_aoseg.pg_aocsseg_152909 | 152915 | pg_aoseg.pg_aocsseg_152909_index | Column Orientati

on

152935 | sachi2014.cms_zlib9_1_prt_p16 | 152940 | pg_aoseg.pg_aocsseg_152935 | 152941 | pg_aoseg.pg_aocsseg_152935_index | Column Orientati

on

152961 | sachi2014.cms_zlib9_1_prt_other | 152966 | pg_aoseg.pg_aocsseg_152961 | 152967 | pg_aoseg.pg_aocsseg_152961_index | Column Orientati

on

84486 | sachi.cms_qlz | 84491 | pg_aoseg.pg_aocsseg_84486 | 84492 | pg_aoseg.pg_aocsseg_84486_index | Column Orientati

on

84511 | sachi.cms_qlz_1_prt_p1 | 84516 | pg_aoseg.pg_aocsseg_84511 | 84517 | pg_aoseg.pg_aocsseg_84511_index | Column Orientati

on

84537 | sachi.cms_qlz_1_prt_p2 | 84542 | pg_aoseg.pg_aocsseg_84537 | 84543 | pg_aoseg.pg_aocsseg_84537_index | Column Orientati

on

84563 | sachi.cms_qlz_1_prt_p3 | 84568 | pg_aoseg.pg_aocsseg_84563 | 84569 | pg_aoseg.pg_aocsseg_84563_index | Column Orientati

on

84589 | sachi.cms_qlz_1_prt_p4 | 84594 | pg_aoseg.pg_aocsseg_84589 | 84595 | pg_aoseg.pg_aocsseg_84589_index | Column Orientati

on

84615 | sachi.cms_qlz_1_prt_p5 | 84620 | pg_aoseg.pg_aocsseg_84615 | 84621 | pg_aoseg.pg_aocsseg_84615_index | Column Orientati

on

84641 | sachi.cms_qlz_1_prt_p6 | 84646 | pg_aoseg.pg_aocsseg_84641 | 84647 | pg_aoseg.pg_aocsseg_84641_index | Column Orientati

on

84667 | sachi.cms_qlz_1_prt_p7 | 84672 | pg_aoseg.pg_aocsseg_84667 | 84673 | pg_aoseg.pg_aocsseg_84667_index | Column Orientati

on

84693 | sachi.cms_qlz_1_prt_p8 | 84698 | pg_aoseg.pg_aocsseg_84693 | 84699 | pg_aoseg.pg_aocsseg_84693_index | Column Orientati

on

84719 | sachi.cms_qlz_1_prt_p9 | 84724 | pg_aoseg.pg_aocsseg_84719 | 84725 | pg_aoseg.pg_aocsseg_84719_index | Column Orientati

on

84745 | sachi.cms_qlz_1_prt_p10 | 84750 | pg_aoseg.pg_aocsseg_84745 | 84751 | pg_aoseg.pg_aocsseg_84745_index | Column Orientati

on

84771 | sachi.cms_qlz_1_prt_p11 | 84776 | pg_aoseg.pg_aocsseg_84771 | 84777 | pg_aoseg.pg_aocsseg_84771_index | Column Orientati

on

84797 | sachi.cms_qlz_1_prt_p12 | 84802 | pg_aoseg.pg_aocsseg_84797 | 84803 | pg_aoseg.pg_aocsseg_84797_index | Column Orientati

on

84823 | sachi.cms_qlz_1_prt_p13 | 84828 | pg_aoseg.pg_aocsseg_84823 | 84829 | pg_aoseg.pg_aocsseg_84823_index | Column Orientati

on

84849 | sachi.cms_qlz_1_prt_p14 | 84854 | pg_aoseg.pg_aocsseg_84849 | 84855 | pg_aoseg.pg_aocsseg_84849_index | Column Orientati

on

84875 | sachi.cms_qlz_1_prt_p15 | 84880 | pg_aoseg.pg_aocsseg_84875 | 84881 | pg_aoseg.pg_aocsseg_84875_index | Column Orientati

on

84901 | sachi.cms_qlz_1_prt_p16 | 84906 | pg_aoseg.pg_aocsseg_84901 | 84907 | pg_aoseg.pg_aocsseg_84901_index | Column Orientati

on

84927 | sachi.cms_qlz_1_prt_other | 84932 | pg_aoseg.pg_aocsseg_84927 | 84933 | pg_aoseg.pg_aocsseg_84927_index | Column Orientati

on

85157 | sachi.cms_zlib | 85162 | pg_aoseg.pg_aocsseg_85157 | 85163 | pg_aoseg.pg_aocsseg_85157_index | Column Orientati

on

85182 | sachi.cms_zlib_1_prt_p1 | 85187 | pg_aoseg.pg_aocsseg_85182 | 85188 | pg_aoseg.pg_aocsseg_85182_index | Column Orientati

on

85208 | sachi.cms_zlib_1_prt_p2 | 85213 | pg_aoseg.pg_aocsseg_85208 | 85214 | pg_aoseg.pg_aocsseg_85208_index | Column Orientati

on

85234 | sachi.cms_zlib_1_prt_p3 | 85239 | pg_aoseg.pg_aocsseg_85234 | 85240 | pg_aoseg.pg_aocsseg_85234_index | Column Orientati

on

85260 | sachi.cms_zlib_1_prt_p4 | 85265 | pg_aoseg.pg_aocsseg_85260 | 85266 | pg_aoseg.pg_aocsseg_85260_index | Column Orientati

on

85286 | sachi.cms_zlib_1_prt_p5 | 85291 | pg_aoseg.pg_aocsseg_85286 | 85292 | pg_aoseg.pg_aocsseg_85286_index | Column Orientati

on

85312 | sachi.cms_zlib_1_prt_p6 | 85317 | pg_aoseg.pg_aocsseg_85312 | 85318 | pg_aoseg.pg_aocsseg_85312_index | Column Orientati

on

85338 | sachi.cms_zlib_1_prt_p7 | 85343 | pg_aoseg.pg_aocsseg_85338 | 85344 | pg_aoseg.pg_aocsseg_85338_index | Column Orientati

on

85364 | sachi.cms_zlib_1_prt_p8 | 85369 | pg_aoseg.pg_aocsseg_85364 | 85370 | pg_aoseg.pg_aocsseg_85364_index | Column Orientati

on

85390 | sachi.cms_zlib_1_prt_p9 | 85395 | pg_aoseg.pg_aocsseg_85390 | 85396 | pg_aoseg.pg_aocsseg_85390_index | Column Orientati

on

85416 | sachi.cms_zlib_1_prt_p10 | 85421 | pg_aoseg.pg_aocsseg_85416 | 85422 | pg_aoseg.pg_aocsseg_85416_index | Column Orientati

on

85442 | sachi.cms_zlib_1_prt_p11 | 85447 | pg_aoseg.pg_aocsseg_85442 | 85448 | pg_aoseg.pg_aocsseg_85442_index | Column Orientati

on

85468 | sachi.cms_zlib_1_prt_p12 | 85473 | pg_aoseg.pg_aocsseg_85468 | 85474 | pg_aoseg.pg_aocsseg_85468_index | Column Orientati

on

85494 | sachi.cms_zlib_1_prt_p13 | 85499 | pg_aoseg.pg_aocsseg_85494 | 85500 | pg_aoseg.pg_aocsseg_85494_index | Column Orientati

on

85520 | sachi.cms_zlib_1_prt_p14 | 85525 | pg_aoseg.pg_aocsseg_85520 | 85526 | pg_aoseg.pg_aocsseg_85520_index | Column Orientati

on

85546 | sachi.cms_zlib_1_prt_p15 | 85551 | pg_aoseg.pg_aocsseg_85546 | 85552 | pg_aoseg.pg_aocsseg_85546_index | Column Orientati

on

85572 | sachi.cms_zlib_1_prt_p16 | 85577 | pg_aoseg.pg_aocsseg_85572 | 85578 | pg_aoseg.pg_aocsseg_85572_index | Column Orientati

on

85598 | sachi.cms_zlib_1_prt_other | 85603 | pg_aoseg.pg_aocsseg_85598 | 85604 | pg_aoseg.pg_aocsseg_85598_index | Column Orientati

on

85828 | sachi.cms_zlib9 | 85833 | pg_aoseg.pg_aocsseg_85828 | 85834 | pg_aoseg.pg_aocsseg_85828_index | Column Orientati

on

85853 | sachi.cms_zlib9_1_prt_p1 | 85858 | pg_aoseg.pg_aocsseg_85853 | 85859 | pg_aoseg.pg_aocsseg_85853_index | Column Orientati

on

85879 | sachi.cms_zlib9_1_prt_p2 | 85884 | pg_aoseg.pg_aocsseg_85879 | 85885 | pg_aoseg.pg_aocsseg_85879_index | Column Orientati

on

85905 | sachi.cms_zlib9_1_prt_p3 | 85910 | pg_aoseg.pg_aocsseg_85905 | 85911 | pg_aoseg.pg_aocsseg_85905_index | Column Orientati

on

85931 | sachi.cms_zlib9_1_prt_p4 | 85936 | pg_aoseg.pg_aocsseg_85931 | 85937 | pg_aoseg.pg_aocsseg_85931_index | Column Orientati

on

85957 | sachi.cms_zlib9_1_prt_p5 | 85962 | pg_aoseg.pg_aocsseg_85957 | 85963 | pg_aoseg.pg_aocsseg_85957_index | Column Orientati

on

85983 | sachi.cms_zlib9_1_prt_p6 | 85988 | pg_aoseg.pg_aocsseg_85983 | 85989 | pg_aoseg.pg_aocsseg_85983_index | Column Orientati

on

86009 | sachi.cms_zlib9_1_prt_p7 | 86014 | pg_aoseg.pg_aocsseg_86009 | 86015 | pg_aoseg.pg_aocsseg_86009_index | Column Orientati

on

86035 | sachi.cms_zlib9_1_prt_p8 | 86040 | pg_aoseg.pg_aocsseg_86035 | 86041 | pg_aoseg.pg_aocsseg_86035_index | Column Orientati

on

86061 | sachi.cms_zlib9_1_prt_p9 | 86066 | pg_aoseg.pg_aocsseg_86061 | 86067 | pg_aoseg.pg_aocsseg_86061_index | Column Orientati

on

86087 | sachi.cms_zlib9_1_prt_p10 | 86092 | pg_aoseg.pg_aocsseg_86087 | 86093 | pg_aoseg.pg_aocsseg_86087_index | Column Orientati

on

86113 | sachi.cms_zlib9_1_prt_p11 | 86118 | pg_aoseg.pg_aocsseg_86113 | 86119 | pg_aoseg.pg_aocsseg_86113_index | Column Orientati

on

86139 | sachi.cms_zlib9_1_prt_p12 | 86144 | pg_aoseg.pg_aocsseg_86139 | 86145 | pg_aoseg.pg_aocsseg_86139_index | Column Orientati

on

86165 | sachi.cms_zlib9_1_prt_p13 | 86170 | pg_aoseg.pg_aocsseg_86165 | 86171 | pg_aoseg.pg_aocsseg_86165_index | Column Orientati

on

86191 | sachi.cms_zlib9_1_prt_p14 | 86196 | pg_aoseg.pg_aocsseg_86191 | 86197 | pg_aoseg.pg_aocsseg_86191_index | Column Orientati

on

86217 | sachi.cms_zlib9_1_prt_p15 | 86222 | pg_aoseg.pg_aocsseg_86217 | 86223 | pg_aoseg.pg_aocsseg_86217_index | Column Orientati

on

86243 | sachi.cms_zlib9_1_prt_p16 | 86248 | pg_aoseg.pg_aocsseg_86243 | 86249 | pg_aoseg.pg_aocsseg_86243_index | Column Orientati

on

86269 | sachi.cms_zlib9_1_prt_other | 86274 | pg_aoseg.pg_aocsseg_86269 | 86275 | pg_aoseg.pg_aocsseg_86269_index | Column Orientati

on

(108 rows)

sachi=# 

5. Listing Toast tables - Optional

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;

5. Get the 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;

6.  Top 20 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 20;

7.  Top 20 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 20;

8. 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;

9. 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;

10. 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;