Re: pg_restore - selective restore use cases. HINT use DROP CASCADE
От | Adrian Klaver |
---|---|
Тема | Re: pg_restore - selective restore use cases. HINT use DROP CASCADE |
Дата | |
Msg-id | 52CF0ABF.40506@gmail.com обсуждение исходный текст |
Ответ на | pg_restore - selective restore use cases. HINT use DROP CASCADE ("Day, David" <dday@redcom.com>) |
Ответы |
Re: pg_restore - selective restore use cases. HINT use
DROP CASCADE
|
Список | pgsql-general |
On 01/09/2014 08:51 AM, Day, David wrote: > 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.” Well for what it is worth, that is documented. http://www.postgresql.org/docs/9.3/interactive/app-pgdump.html "Note: When -n is specified, pg_dump makes no attempt to dump any other database objects that the selected schema(s) might depend upon. Therefore, there is no guarantee that the results of a specific-schema dump can be successfully restored by themselves into a clean database." > > So it appears that I now have to develop a custom script to do what I > think would > > be a standard kind of restoration. Well I think that is the issue that it is not really standard or at least easy. The possible combinations of schemas, dependencies across schemas, ownership across objects makes this complicated to do on a selective basis. Furthermore when you are using -n using are asking for a specific schema, it sort of breaks the contract to drag in objects from other schemas. > > 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. So you want to start dropping objects outside the schema you are restoring, seems like a foot gun to me. > > 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 Probably because it is relatively easy to roll your own solution to this. > > 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. In the foreseeable future that is how you will need to handle it. Where the choice is either roll your own script or use one of the existing migration solutions, e.g Alembic, Sqitch, etc > > 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 > -- Adrian Klaver adrian.klaver@gmail.com
В списке pgsql-general по дате отправления:
Предыдущее
От: Raymond O'DonnellДата:
Сообщение: Re: Add custom properties to a column's definition (pg_attribute)
Следующее
От: Chris HiestandДата:
Сообщение: postgres.app OS X psql character encoding (utf-8) problem