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;