pg_restore - selective restore use cases. HINT use DROP CASCADE

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

Responses

Browse pgsql-general by date

  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