I have needs to do selective schema or table restorations and the pg_restore
utility seems to have hooks for this, yet seems deficient for this type of problem.
It appears that I have to develop a custom script to do what I think would
be a standard kind of restorations.
---
Given: Postgres 9.3 and I have dumped my database via:
pg_dump –Fc –U <superuser> my_db –f archive_file
I have no problem doing a total restoration
e.g.
pg_restore –c –U <supeuser> -d my_db archive_file
Assuming I have not had a catastrophic error but merely wish to address schemas
or tables used for decision making that have been altered by users into sub-optimal
condition, then if I attempt to do selective restorations of a schema or tables in the database:
( e.g. pg_restore –c -n <some_schema> -U <superuser> -d my_db archive_file )
I encounter restoration problems over dependencies with suggestions/Hints:
“ HINT: Use DROP ... CASCADE to drop the dependent objects too.”
So it appears that I now have to develop a custom script to do what I think would
be a standard kind of restoration.
I would think that there ought to be an some additional options to pg_restore.
i.e an option that turns a DROP into a DROP with CASCADE and or DISABLES constraint checking while the schema/table is being restored.
In addition I would think that with “-a” , data only option, there ought to be an assistive option that allows for the table to truncated/cleaned so that
the generated COPY commands do not append to the table resulting in PRIMARY KEY violations.
In any event I have not found a straight forward way of using pg_restore to do selective restorations or have found some ways of doing certain tables but had to remove Foreign Keys to make it work which seems like a poor bargain. I think I know how to customize the output to do the task, it just seems that pg_restore should be
able to do this without my additional efforts.
I am hopeful that there might be some instructive thoughts on selective restorations that have not occurred to me.
( options that I have played with: -n –t –section=data –a –c –disable-triggers -1 )
Regards
Dave Day