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 |
