From: | "Day, David" <dday(at)redcom(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | pg_restore - selective restore use cases. HINT use DROP CASCADE |
Date: | 2014-01-09 16:51:20 |
Message-ID: | 401084E5E73F4241A44F3C9E6FD79428AC6CE29A@exch-01 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Panneerselvam Posangu | 2014-01-09 17:03:42 | SQL State XX000 : XML namespace issue |
Previous Message | Tom Lane | 2014-01-09 16:25:36 | Re: getting domain information from query results |