From: | "Day, David" <dday(at)redcom(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pg_restore - selective restore use cases. HINT use DROP CASCADE |
Date: | 2014-01-10 21:45:57 |
Message-ID: | 401084E5E73F4241A44F3C9E6FD79428AC6CE7CA@exch-01 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Adrian.
Based on your earlier remarks and further investigation I find that the restoration of a schema ( -n ) goes smoothly if there are no foreign key
References to the tables being restored from a schema that is not part of the restoration. I had a couple of those that I had not initially appreciated and was able to redesign to accommodate that.
Similarly if using the -t table restoration option of tables within a schema, one has to include all tables that have a foreign key reference to the table(s) being restored. I have to rethink some layout based on this but at least I understand this all now.
I still think a drop cascade or defer constraints options might be useful. I can probably pipe the pg_restore output to a perl script that could "tweak" the pg_restore output and in turn pipe that to psql if I really need this capability.
Thanks again for your assistance.
Dave
-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)gmail(dot)com]
Sent: Thursday, January 09, 2014 6:09 PM
To: Day, David; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] pg_restore - selective restore use cases. HINT use DROP CASCADE
On 01/09/2014 01:51 PM, Day, David wrote:
> Adrian,
>
> Thank you for your response.
>
> I would note that the original dump archive created by pg_dump
> included all schemas and that I only intend to restore a schema from it that is self contained, or a group of related tables from it.
I just tried that here and succeeded. I did a pg_dump and then restored only the public schema which in this database is self contained. I did get the HINT because I used the -c switch and it tried to drop the public schema and there where existing objects dependent on it. The restore threw the HINT and a subsequent ERROR over trying to CREATE SCHEMA public where it already existed, but it completed the restore.
>
> I acknowledge the dangers inherent in selective restoration, it just
> seems that a couple of additional options ( disable constraints, drop
> cascade ) to pg_restore would improve this utility to users who have
> put some thought into laying out the database design and failure cases from which they would like to recover.
>
> To have a pg_restore selective restoration options, (-n, -t ), and
> have it fail simply because there are foreign keys amongst the tables
> within that schema seems like to much protection or protection that I would at least like to have option to over-ride.
We will probably need to see more detail on why that failed in your case because I did not see that in mine. Another way to influence the outcome is to use the -l and -L options to pg_restore. -l returns the -Fc dump file table of contents(TOC) as a list. You can redirect that to a file and in that file comment out(using ;) items and rearrange the order of the TOC to suit your needs. Then you use pg_restore with the -L option to feed it the edited TOC.
http://www.postgresql.org/docs/9.3/interactive/app-pgrestore.html
>
> It may well be that I could shoot myself in the foot, but I'd still
> like to own the firearm :+)
>
>
> Regards
>
>
> Dave Day
>
>
--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Ramsey | 2014-01-10 23:12:54 | Re: round(real,integer) |
Previous Message | Tom Lane | 2014-01-10 21:09:23 | Re: round(real,integer) |