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

The gadget spec URL could not be found

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
The gadget spec URL could not be found
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';
The gadget spec URL could not be found

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 ;
The gadget spec URL could not be found

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)

The gadget spec URL could not be found

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

The gadget spec URL could not be found

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)

The gadget spec URL could not be found

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)

The gadget spec URL could not be found

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;

The gadget spec URL could not be found

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;

The gadget spec URL could not be found

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;

The gadget spec URL could not be found

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;

The gadget spec URL could not be found

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;

The gadget spec URL could not be found

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;

The gadget spec URL could not be found

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;

The gadget spec URL could not be found

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;

The gadget spec URL could not be found

Comments