Search Postgresql Archives

pg_restore - selective restore use cases. HINT use DROP CASCADE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux