How to drop and recreate catalog view in postgres [closed]

Question

Can any one please help me to drop and recreation of sys views in EDB(Postgres).

Answer 1

Manipulating the catalogs (the contents) is already something that is absolutely discouraged:

Normally, one should not change the system catalogs by hand [...]

The reason for this is that you have to know the structure very well in order to know exactly what to change so that you don't corrupt your catalog. For example, this is how an ALTER TYPE ... ADD VALUE ... used to work pre-9.1 (one of the simpler things - try to write a query for changing a column type):

INSERT INTO pg_enum(enumtypid, enumlabel)
    SELECT t_oid, 'new_value'
      FROM pg_type as t
     WHERE t.typtype = 'e'
       AND t.oid = 'your_schema.your_type'::regtype::oid;

Take a factor of 1000, multiply 'absolutely' with it, and then you get how much it is discouraged to change the structure. Fortunately, it does not even seem possible (as user postgres, and you cannot have more privileges than that):

CREATE OR REPLACE VIEW pg_catalog.pg_stat_activity AS
SELECT [...], 'bla' AS bla
  FROM [...]
 WHERE [...];

ERROR:  permission denied: "pg_stat_activity" is a system catalog

Answer 2

I guess you can recreate a new cluster. That will create a fresh bunch of new sys views.

And then you move (logically with pg_dumpall) your databases to that new cluster...