I am cleaning up a database and I have a list of unused tables, views and column and I want to drop these entities.
I have the following constraints:
The database is live and thats mean I need to be able to revoke the changes as quick and possible.
I need to restore a certain set of the deprecated table,... But not the whole database
I need to automate this Job because the number of objects that I need to drop is over than 200
I want to use the cascade option when dropping a table, column, and view, which will cause the deletion of foreign key constraints and views if you drop tables and column, and the deletion of other views if you drop a view ( am I right ....). I had a quick look on the pg_dumb and pg_restore and there is the t option which I think I can use for this task.However, I am a little bit afraid of the approach that I am going to implement. For the following reasons.
For views, I do not think I will have a problem because the view is stateless. i.e the definition will not change.
Tables and column might have different scenarios such as
suppose that table b depends on a, and let us say that other tables depends on b.
What will happen if
1. dropped table a 2. insert data on b and the other relations 3. restore table a and it's dependency (table b).
Is there is a general drop and restore strategy for my case. Also, what are your advices?
