rvkulikov/pg-deps-management

[FEATURE] export of all views possible?

Closed this issue · 1 comments

fschaeffer commented

Hello,

I really like your script and it solved a lot of trouble for me in the past. Now I'm facing some issues where an external DB update will change a lot of tables (that I don't know before the update runs) and fails due to dependant views...

Is there a way to perform a backup of ALL views in a schema and save them using your script? I especially need your dependency-aware backup for all views so I could completely save all views, drop all of them and recreate them in the correct order.

As all bits and bolts are already there is there an easy way to implement this? I digged into the code and found a couple of places where the "starting" table is used as reference for the selects and found no easy way to replace it. Maybe somebody has an idea?

Or is there another way to store all views from a schema, drop them and recreate them is the correct order after the tables where changed?

Regs
Florian

rvkulikov commented

@fschaeffer Hello.
I think it would be fine if you list all your tables and backup theirs dependencies via such sql:

select
  public.deps_save_and_drop_dependencies(
    table_schema,
    table_name,
    '{"dry_run": true, "verbose": true, "populate_materialized_view": false}'::jsonb
  )
from
  information_schema.tables 
where true
  and table_type = 'BASE TABLE'
  and table_schema in ('public');

(parameters in example are safe - script would just print out it's log without actually doing backups)

BUTT BEWARE.
You should be aware of the Problem with procedures backup-and-restore currently available.
If some of your views are generated via procedure or some procedure uses some view
then current implementation is not reliable for you. #10 tracks the problem.

Relevant links are: